Dump / Restore

Dump

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:

--compatible=mysql323

If you want to prevent table lock on production servers

--lock-tables=FALSE

Restore

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

FAQ

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';
  FLUSH PRIVILEGES;

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

echo "create user/password MYUSER/MYPASSWORD":
CREATE USER 'MYUSER'@'localhost' IDENTIFIED BY 'MYPASSWORD';
GRANT ALL PRIVILEGES ON MYDATABASE.* TO 'MYUSER'@'localhost' WITH GRANT OPTION;

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

 SHOW FULL PROCESSLIST;

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 :

[mysqld]
max_allowed_packet=16M

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;