see also MySQLMaintenanceScripts for further MySql related help


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
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


The server time zone value '...' is unrecognized

related to #8118

A general solution to this problem is described in :

Import the system timezones in the mysql system db

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

configure the timezone in /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf depending of your ditro

default_time_zone = Europe/Berlin

Restart the mysql server

systemctl restart mysql.service

(or the appropriate command depending on your distro).

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:

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

pre mysql 5.5

# 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


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
  1. Reload daemons and restart MySQL service:
# systemctl daemon-reload
# systemctl restart mysql


  • 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:

How to ...

I want to tunnel my MySQL client through SSH

ssh -N -L

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 -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



ALTER TABLE <dbName>.<tableName> ENGINE InnoDB

See also #3371 for further information.



Updated by Andreas Kohlbecker almost 3 years ago ยท 24 revisions