MySQL FAQ¶

• Table of contents
• MySQL FAQ
  • Maintenance
    • How To Check, Repair & Optimize All Tables in All Databases
  • General MySQL problems
    • Too many connections
      • max_connections of mysql server too low
      • limit for open file descriptors too low (Linux)
  • I am getting errors when using mysqldump
    • mysqldump: Got error: 23: Out of resources when opening file
    • Other
  • I want to tunnel my MySQL client through SSH
  • How to change the Database Engine (e.g. MyISAM -> InnoDB)
  • Replication

---

see also MySql for further MySql related help

Maintenance¶

How To Check, Repair & Optimize All Tables in All Databases¶

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

if you need to exclude a db:

echo "show databases;" | mysql -u root --password=$password

General MySQL problems¶

Too many connections¶

This can have two reasons:

max_connections of mysql server too low¶

1. check the in your /etc/mysql/my.conf

[mysqld]
max_connections        = 1000

limit for open file descriptors too low (Linux)¶

Check the ulimit setting @ulimit -n@, this often too low
In order to increase this permanently and globally edit /etc/security/limits.conf

mysql            soft    nofile           24000
mysql            hard    nofile           32000

Links:

• http://dev.mysql.com/doc/refman/5.1/en/too-many-connections.html
• http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_open-files-limit
• http://dev.mysql.com/doc/refman/5.1/en/linux-installation.html

I am getting errors when using mysqldump¶

mysqldump: Got error: 23: Out of resources when opening file¶

This usually occurs with the cdm databases since these are containing over 400 Tables and mysqldump will create too many open file handles and thus will exceed the system limits.
You need to increase the open-files limitation in the mysqld or in the system limits (see: http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit)

Configure mysqld: /etc/mysql/my.cnf , from mysql 5.5 on the configuration is different!

pre mysql 5.5

[mysqld_safe]
# increase the filelimit to avoid problems when creating dumps of cdm dbs
open_files_limit = 5000

or adjust the open file limits in /etc/security/limits.conf

 mysql            soft    nofile           24000
 mysql            hard    nofile           32000

after mysql 5.5

(from https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied)

For Debian-based distributions:

1. Create /lib/systemd/system/mysql.service.d/limit_nofile.conf file with the following content:

# cat /lib/systemd/system/mysql.service.d/limit_nofile.conf
[Service]
LimitNOFILE=4096

1. Reload daemons and restart MySQL service:

# systemctl daemon-reload
# systemctl restart mysql

Other¶

• Error 29: use --single-transaction e.g: mysqldump --opt --single-transaction -u root -p drupal_dataportal > drupal_dataportal-2009-12-11.sql
• something with "cannot open or write to tmp/#table.myd" -> use --skip-locktables ???

Maybe you will find a solution here: http://forge.mysql.com/wiki/Category:FAQ

I want to tunnel my MySQL client through SSH¶

ssh -N -L 3307:domain.name.of.mysqlserver:3306 username@domain.name.of.gatewayserver

This will open a tunnel, listening on localhost:3307 and forwarding everything to mysqlserver:3306, and doing it all via the ssh service on the gateway machine. Most of the time mysqlserver and gatewayserver are the same machine.
This example shows us specifying port 3307 on the local end of the tunnel; We do this because maybe we are running a MySQL server on the local machine, so we can't re-use the default MySQL port.

You'll now have a terminal open on the gateway machine, but you don't need it for this procedure, so set it aside.

Now, on your local machine, execute a mysql connection like so:

mysql -u username -p -h 127.0.0.1 -P 3307 databasename

(or use HeidiSQL, MySQLadminstrator etc.)

In other words, mysql thinks it's connecting to localhost, but on a different port. In fact, the connection is being made securely to the remote mysql server, via the gateway machine and the local "mouth" of the ssh tunnel on your own machine.

When you're finished with your mysql session, log out of the session on the gateway machine. That will properly close the tunnel.

How to change the Database Engine (e.g. MyISAM -> InnoDB)¶

InnoDB is the default database engine since MySQL 5.5, before it was MyISAM.

To change the engine of a single or multiple tables you may use HeidiSQL (v8.0), select the table(s) via right click and choose "Mass-table-editor" from the menu.

Changing the engine automatically you may write a script using

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = <dbName> AND TABLE_NAME = 'MyISAM'

and

ALTER TABLE <dbName>.<tableName> ENGINE InnoDB

See also #3371 for further information.

Replication¶

see https://www.thomas-krenn.com/de/wiki/MySQL_Replikation