Mysql backs up and transfers data, mysql backs up and transfers data

Mysql backs up and transfers data, mysql backs up and transfers data

I. Back up data using mysqldump

Directly enter the command mysqldump and you will find that the prompt command does not exist because the system will find the command under/usr/bin by default. If this command is not in this directory, the command will naturally not be found, and an error is reported. If you know where the problem is located, you can handle it directly, first use the find command to find the path contained in mysqldump. find/-name mysql-print // find the file named mysql, and then use the ln command to link to/usr/bin. Ln-fs/usr/local/mysql/bin/mysqldump/usr/bin

Note: The following error occurs:

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect

Solution:

Use find/-name mysql-print to view the mysql. sock path
Check vim/etc/my. cnf and check whether the specified path of socket =/var/lib/mysql. sock is correct or not.
Restart service mysqld restart
If the preceding error is reported, you can use a soft connection to establish a connection ln-s/var/lib/mysql. sock/tmp/mysql. sock.

 

Test migration for Logon Test

Mysql-h Server IP address-P 3306-u root-p

　　

Database lock table: After this command is executed, all tables in the database are locked to read-only (if the session is closed, it is automatically unlocked). The unlock Command unlock tables

mysql> flush tables with read lock;  Query OK, 0 rows affected (0.00 sec)

 

Start backup

[root@localhost ~]# mysqldump -u root -p -A -B --master-data=1 | gzip > /123/test.sql.gz

 

Transmit the backup to the new database for recovery

[root@localhost ~]# scp /123/test.sql.gz 192.168.3.101:/root/

 

Restore data

[root@localhost ~]# gunzip test.sql.gz

[Root @ localhost ~] # Mysql-u root-p <test. SQL

 

Note: The following error indicates that slave is not disabled. Disable the command stop slave.

This operation cannot be performed with a running slave; run STOP SLAVE first

 

The recovery is successful and can be viewed visually.