21
votes
Accepted
MySQL difference between wait_timeout and connect_timeout
Right from the MySQL Documentation
wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it.
connect_timeout : The number of seconds that ...
- 183k
7
votes
Accepted
Passwordless mysqldump via shell script in /etc/cron.daily
Solved with the help of this answer.
cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file.
The proper passwordless mysqldump ...
- 223
6
votes
How can I make MySQL client read password from mylogin.cnf?
I had the same issue as OP, but the answers in this thread confused me. Vinay Mandala's link actually worked for me. Reposted here for clarity.
My issue:
ERROR 1045 (28000): Access denied for user '&...
- 61
3
votes
unknown variable 'lower_case_table_names=1'
You have misplaced your setting; lower_case_table_names belongs to the [mariadb] section, as shown in the docs. If the section is missing, you simply need to add it.
You also should get rid of a ...
- 26k
3
votes
Accepted
mysql backup password without root home
What's the best way to provide a MySql ... password
Using a separate configuration file, as described in the Manual.
What's the best way to provide a MySql root password
You should not use any high-...
- 8,741
2
votes
How can I make MySQL client read password from mylogin.cnf?
I was getting this error, except it said (using password: YES)
The reason as mentioned by Giovanni was because of a "#" in the password. I found that a workaround is to insert quotes around your ...
- 21
2
votes
Enabling LOAD DATA LOCAL INFILE in mysql
For default installation of MySQL 8.0.20 on Windows:
Look for the initialization file located at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.
Open my.ini file with a text editor.
Look for the ...
2
votes
MySQL 5.6 OS X 10.8: how tell if my.cnf read?
On OS X, here is a simple method to check whether your file is read.
Run: sudo fs_usage | grep my.cnf
This will report any filesystem activity in real-time related to that file.
In another Terminal, ...
- 475
2
votes
Accepted
Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts?
In a Galera cluster, any node can play the part of a Master node.
Such a node can execute DML (INSERTs, UPDATEs, and DELETEs).
If DML statements are spread across multiple Master nodes and are ...
- 183k
2
votes
Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts?
This information may not be of interest to the DBA. If the application is handling conflicts correctly, there is no need to log them. It would be expected behaviour. You would only enable this level ...
- 2,078
2
votes
Mariadb default logs location & --log-error
Going off the documentation, the error log should be in the default location of /var/lib/mysql and it should be named the default hostname.err, but it's just not there.
In the directory /var/lib/...
- 161
2
votes
MySQL fails to start after editing my.cnf
This is too big for a comment.
For comparison I'll paste the contents of my custom "my.cnf file".
The command to generate this was grep -v "#" mysqld.cnf executed in
/etc/mysql/mysql.conf.d
My file ...
- 151
2
votes
Mariadb configuration is not loaded from my.cnf
I am having the same problem in ~/.my.cnf file (MySQL 5.5.5-10.2.6-MariaDB installed via Homebrew).
Apparently it is a recorded bug in both MySQL and MariaDB:
Bug #84172 The pid-file value is ...
- 121
2
votes
MySQL Server Sparks up to 400% Please help!
(Preliminary review of the pastbin)
There are 13 identical indexes like this
KEY `post_status` (`post_status`,`post_type`,`post_date`),
Similarly for
KEY `autoload` (`autoload`),
Is something ...
- 78.1k
2
votes
Accepted
Mysql Percona 5.6 Configuration File
No there is no global variable for the config file locations. mysqld --help --verbose is best to show which ones are used.
Take a look at the linux processlist to see how mysqld is executed by init/...
- 7,719
2
votes
Accepted
Show a Warning on logon in MariaDB
Your my.cnf file should have:
[mysqld]
init_connect="CALL audit.login_trigger()"
Or any other group name from running mysqld --help --verbose, there's a line like The following groups are read: ...
- 7,719
2
votes
Why “sudo vim /etc/my.cnf” file is empty?
.my.cnf in your home directory is only read by the mysql clients, so this is not where you should set system variables for the mysql server. .my.cnf can be used e.g. for your credentials so that you ...
- 6,351
2
votes
Accepted
Importing a 16 GB MySQL file silently fails
Those 3 settings are probably wrong. When autocommit=0, nothing gets stored until a COMMIT is issued.
Look in the dump file; it will probably have a lot of settings, plus some huge (multi-row) ...
- 78.1k
1
vote
Where should I store a custom ".my.cnf" file?
To keep the things simple place all custom server settings into the /etc/my.cnf file. Some user-specific options can be stored in the ~/.my.cnf where ~/ means "the homedir for the given system user". ...
- 4,348
1
vote
Override database option in a user-specific .my.cnf config file
In fact I am realizing that setting database option in a [client] section is may be nonsense as many client tools won't be compatible.
mysqldump and mysql_upgrade for example are complaining about ...
- 2,368
1
vote
Accepted
table_open_cache increase issue - MariaDB 10.2
I can see 3 different reasons why MariaDB might reset this variable. In order of most to least likely explanation:
You have another .cnf file where the table_open_cache variable is being set to 4895. ...
- 6,351
1
vote
Accepted
Next step to configuring MariaDB after successful installation
The my.cnf settings (including my.cnf.d/*), if any, are in front of built-in defaults. All of this is "reasonable" for a generic install.
Adding to them (or changing them) is important only after ...
- 78.1k
1
vote
MariaDB 5.5-56 not reading the my.cnf file and taking default values
On a Linux OS, make sure the file /etc/my.cnf is owned by root.
- 365
1
vote
Error - enabling bin log in mysql
Please never mind, I found a solution on this blog.
Still the mysql data directory points to /var/lib/mysql
https://schworak.com/blog/e53/move-mysql-binlog-to-another-disk-or-folder/
Then did chmod ...
- 1,940
1
vote
MySQL using 100% of CPU with 1500 active connections
You may have encountered a Von Neumann Bottleneck.
CPU usage is expected to increase as the number of connections increases. As the connections increase the number of active queries should also ...
- 4,468
1
vote
Accepted
InnoDB Read and Write speed constantly spikes
(Analysis of VARIABLES and STATUS)
The More Important Issues
Ponder why there are so many deletes/second and deletes/insert. Deletes need to eventually update indexes; this may be part of the ...
- 78.1k
1
vote
Need help with my.cnf: 32GB RAM and 3GB of InnoDB tables
"You can't tune your way out of performance problems". That being said, here is some tuning advice, followed by other ways to solve the issues you are facing.
"Thread cache hit rate: 49% (134K ...
- 78.1k
1
vote
Mariadb configuration is not loaded from my.cnf
Not sure if this is some default behaviour of Mariadb or lack of our knowledge to figure how Mariadb picks its my.cnf.
I have worked to resolve similar issue and made mysqld_safe to point to the my....
- 1,561
1
vote
MySQL difference between wait_timeout and connect_timeout
wait_timeout is how long to keep a connection as idle state. connect_timeout is how long to wait for a connection to be made.
This is my best understanding, please comment or edit as needed.
- 319
1
vote
How are multiple my.cnf's loaded by MySQL?
The standard config file format applies. It is on group and key basis.
So if you have:
[mysql]
some_variable = 1
[client]
some_variable = 3
and after another file has
[mysql]
some_variable = 2
...
- 3,020
Only top scored, non community-wiki answers of a minimum length are eligible