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
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.
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;
echo "create user/password MYUSER/MYPASSWORD": CREATE USER 'MYUSER'@'localhost' IDENTIFIED BY 'MYPASSWORD'; GRANT ALL PRIVILEGES ON MYDATABASE.* TO 'MYUSER'@'localhost' WITH GRANT OPTION;
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.
Default packet size is 1MB. You can increase this value for big queries in /etc/my.cnf like this :
insert into mytable (myrow1, myrow2) (select x,y from mytable2 where mycondition);
# This example will replace all '\' by '/' in mycol: update `mytable ` SET mycol = REPLACE(mycol , '\\', '/');
SELECT user, password, host FROM mysql.user;
Copyright © 2023 My linux world - by Marc RABAHI
Design by Marc RABAHI and encelades.