MySQL database hot backup _ MySQL

[Switch] MySQL database hot backup bitsCN.com

1. system environment description:
1. Two database servers, A and B;
2. Currently, A is in use and serves as the master server. B is used as the backup database server;
3. the database for hot backup contains data tables of MyISAM and InnoDB type;

II. operation process description:
1. ensure that the initial data of A and B is fully synchronized. Therefore, the data in A needs to be completely copied to B;

Since multiple servers are connecting to A and modifying the data in A in real time, you need to disconnect all programs that will modify the database data, or use the lock table method (but this operation may affect the normal operation of other services ). Use the mysqldump program to back up data in A as A script file.
Method: Open the command line window and enter mysqldump-uusername-ppassword -- opt databasename> sqlstoragepath. username is the username of mysql and password is the password of mysql, databasename is the name of the database for hot backup, and sqlstoragepath is the storage location of the backup script (c:/backup. SQL ).

2. add an authorized account for backup in;

Method: log on to mysql through the command line. after successful logon, the "mysql>" symbol will appear. Input: grant replication slave on *. * to 'username' @ 'slavedbip' identified by 'password'; (the semicolon cannot be omitted ). Here, username and password are the username and password used by the backup server for hot backup; slaveIP is the IP address of the backup server, indicating that this account can only be used on this IP address.

3. stop the mysql service of server;
4. open the data directory under the mysql installation directory of server A and delete all log files except the folder. Then open the databasename directory and delete all the files;
5. open the mysql installation directory of server A, modify the my. ini file, add the following configuration information in the [mysqld] section, and then save:

# Master server ID
Server-id = 1
# Enable the binary log function of the master server
Log-bin = binary_log
# Databases to be ignored, that is, databases that do not require hot backup
Binlog-ignore-db = mysql, test
# Databases for hot backup
Binlog-do-db = stv

6. Open Server B and use the mysqladmin program to create an empty database named databasename;

Method: Open the command line window and enter mysqladmin create databasename.

7. stop the mysql service of server B;
8. open the data directory under the mysql installation directory of server B and delete all log files except the folder. Then open the databasename directory and delete all the files;
9. open the mysql installation directory of server B, modify the my. ini file, add the following configuration information in the [mysqld] section, and then save:

# Slave server ID
Server-id = 2
# IP address of master server
Master-host = 10.20.1.21
# Port used by the master server mysql
Master-port = 3306
# Backup account username, that is, the username of the authorized account added in step 2
Master-user = username
# Backup the account password, that is, the authorization account password added in step 2
Master-password = slavedb
# Databases for hot backup
Replicate-do-db = stv
# Interval between attempts to connect to the master server
Master-connect-retry = 60

10. start the mysql service of server;
11. start the mysql service of server B;
12. check the status of server;

Method: mysql> showprocesslist;
Mysql> show master status;

13. check the status of server B;

Method: mysql> show slave status/G;
The displayed list must exist: Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

14. open server A and run the mysql command to re-import the data script backed up to the database;

Method: Open the command line window and enter mysql-uusername-ppassword databaseName <backup. SQL

15. after the data is imported, check whether server B imports data synchronously;
16. add, modify, and delete data in server A, and check whether server B has changed accordingly;
17. restore other services;

So far, all MySQL database hot backup operations have been completed.
Note: If all the table types in the database to be backed up are MyISAM, you can directly copy the database directory to be backed up under the data Directory of server A to the corresponding directory of server B.

From http://mazd1002.blog.163.com/blog/static/66574965201141383621934

BitsCN.com