MySQL Database Hot Backup

Master/Slave Mode:

Master server server1: 192.168.1.20 mydb, the database to be backed up by MySQL 5.0

Slave server server2: 192.168.1.22 MySQL 6.0

1. Back up existing data:

It is best to stop MySQL service first

Use the [mysqlhome]: \ bin \ mysqldumple.exe tool to back up existing data into an SQL file:

D: \> Cd [mysqlhome]

[Mysqlhome]> Cd Bin

Bin: \> mysqldumple-u root-P mydb> D: \ mydb. SQL //Add the -- routines option when you have a stored procedure or function written by yourself. Otherwise, no backup is performed by default. In addition, adding ';' to the end of the statement will cause dB not found to report an error.

Enter the password of the root user and the database will be exported to D: \ mydb. SQL.

Copy mydb. SQL to D: \ of server2.

Run the following command in MySQL:

Mysql> source mydb. SQL

After running, the data will be imported to MySQL of Server 2.

2. Authorization:

Server1:

Mysql> grant replication slave on *. * to user @ '192. 168.1.22 'identified by 'pass' Note: users in MySQL 192 must be enclosed by quotation marks.'User' @ '192. 168.1.22'

Authorize server2 to use the user name and password pass to connect to server1 for Data Backup

3. server1 Configuration modification:

Database Backup is based on the log replication mechanism.

Server-id = 1 // set the ID of the master server

Log-bin = [log-name] // sets the log file

BINLOG-do-DB = [DB-name] // The database that records logs

BINLOG-ignore-DB = [db1, DB2] // database with no logs recorded

Restart the service after the configuration is modified.

4. configurations in Server 2:

Modify my. ini configuration file

Add the following two lines after [mysqld ]:

Server-id = 2

Replicate-do-DB = mydb

Run the following command in mysql client:

Mysql> change master to master_host = '2017. 168.1.20 ', master_user = 'user', master_password = 'pass', master_log_file =' [log-name] ', master_log_pos = [pos-Val];

[Log-name] and [pos-Val] can be viewed using show Master Status in Server 1.

Restart service