Skip to main content
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 ...
RolandoMySQLDBA's user avatar
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 ...
Vila's user avatar
  • 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 '&...
quexy's user avatar
  • 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 ...
mustaccio's user avatar
  • 26.3k
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-...
Phill  W.'s user avatar
  • 8,860
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 ...
gamorten's user avatar
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 ...
New Kid On MySQL's user avatar
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, ...
kenorb's user avatar
  • 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 ...
RolandoMySQLDBA's user avatar
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 ...
Andrew Brennan's user avatar
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/...
Matthew Goheen's user avatar
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 ...
knb's user avatar
  • 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 ...
osman's user avatar
  • 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 ...
Rick James's user avatar
  • 78.6k
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/...
danblack's user avatar
  • 7,809
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: ...
danblack's user avatar
  • 7,809
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 ...
dbdemon's user avatar
  • 6,381
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) ...
Rick James's user avatar
  • 78.6k
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". ...
Kondybas's user avatar
  • 4,538
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 ...
Nicolas Payart's user avatar
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. ...
dbdemon's user avatar
  • 6,381
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 ...
Rick James's user avatar
  • 78.6k
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.
andres's user avatar
  • 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 ...
TheDataGuy's user avatar
  • 1,950
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 ...
BillThor's user avatar
  • 4,478
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 ...
Rick James's user avatar
  • 78.6k
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 ...
Rick James's user avatar
  • 78.6k
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....
Mannoj's user avatar
  • 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.
Goose's user avatar
  • 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 ...
Károly Nagy's user avatar
  • 3,030

Only top scored, non community-wiki answers of a minimum length are eligible