My linux world » MariaDB Survival Guide

MariaDB Survival Guide


Dump / Restore


Dump ALL MySQL Databases

mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Dump Individual or Multiple MySQL Databases

mysqldump --user=XXXXXXXX --password=XXXXXXX --databases DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL

Dump only certain tables from a MySQL Database

mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

If you want to make you dump compatible with old version of mysql, add this option to the mysqldump command line:


If you want to prevent table lock on production servers



Use the following command to feed back in the contents of a text file:

mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

If you want to restore dump using a different database name, you have to dump in such a way that it does not create or select the database.

# dump
mysqldump --user=XXXXXXXX --password=XXXXXXX --routines --triggers OLD_DB_NAME > /PATH/TO/DUMPFILE.SQL
# restore
mysql --verbose --user=XXXXXXXX --password=XXXXXXXX -A NEW_DB_NAME < /PATH/TO/DUMPFILE.SQL


Host ‘host_name’ is blocked

If you get the following error, it means that mysqld has received many connect requests from the host ‘host_name’ that have been interrupted in the middle:

  Host 'host_name' is blocked because of many connection errors.
  Unblock with 'mysqladmin flush-hosts'

The number of interrupted connect requests permitted is determined by the value of the ”max_connect_errors” system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a ”mysqladmin flush-hosts” command or issue a FLUSH HOSTS statement.

By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell > mysqld_safe --max_connect_errors=10000 &

If you get this error message for a given host, you should first verify that there isn’t anything wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

Could not connect to the specified instance (error 1045)

We must allow the user to connect remotely to the machine.
Example for the root user:

  GRANT ALL ON *.* to root@'%'IDENTIFIED BY 'mypassword';

Access denied for user ‘MYUSER’@’localhost’ (using password: YES)

echo "create user/password MYUSER/MYPASSWORD":

How to optimize mysql process list?

Log on command line (SSH) to your MySQL console (or via phpMyAdmin in the Processes tab on the home) and look at the processes (i.e. SQL statement):


You may see be recurring, or long. Now you know what to optimize.

If your processlist is often empty or full of “sleep”, so good. If too much sleep with too long, reduce your timeout or use mysql_connect instead of mysql_pconnect. 2 minutes can be honest for a duration timeout.

Packet for query is too large

Default packet size is 1MB. You can increase this value for big queries in /etc/my.cnf like this :


Some request

Insert rows from request

insert into mytable (myrow1, myrow2) (select x,y from mytable2 where mycondition);

Replace string in column

# This example will replace all '\' by '/' in mycol:
update `mytable ` SET mycol = REPLACE(mycol , '\\', '/');

List authenticating users

SELECT user, password, host FROM mysql.user;

Copyright © 2024 My linux world - by Marc RABAHI
Design by Marc RABAHI and encelades.