MySql dual-machine hot backup method, mysql dual-machine Hot Backup

MySql dual-machine hot backup method, mysql dual-machine Hot Backup

MySqlDual-machine hot backup method(BackupMasterMysql databaseTo SlaveMedium)

 Environment]

Operating System: Two 32-bit Windows 7 flagship versions

Mysql version: mysql-5.5.28-win32 (earlier mysql versions differ when configuring slave databases)

Tool: mysql-gui-tools-5.0-r17-win32

Master IP: 192.168.1.4 (Master)

Slave IP: 192.168.1.15 (secondary)

 

MySQL installation path: D: \ Program Files \ MySQL Server 5.5

 

[Settings]

Add another user: Username: mysql, password: mysql (grant all on *. * to 'mysql' @ '%' identified by 'mysql ';); change the value in the host column of the table user in the mysql database to % (or set it to the IP address of the host that allows remote access)

 

On the master (192.168.1.4), test whether the slave (192.168.1.15) mysql user can log on remotely, and test whether the master (192.168.1.4) mysql user can log on remotely on the slave (192.168.1.15, if you cannot log on normally, check whether the user information in the user table in the mysql database is correct. (If you cannot log on, it may be caused by firewall problems)

 

[Configuration]

Find my. ini configuration in the installation path and add the following content under [mysqld:

• MasterConfiguration:

[Mysqld]

Server-id = 1

Log-bin = bin_log

Binlog-do-db = mysql (name of the database to be backed up)

 

Restart the mysql service on the master node. Click and enter the password,

Run the show master status Command.

Record the values of File and Position;

 

• SlaveConfiguration:

[Mysqld]

Server-id = 2

Start the mysql service on slave, click to enter, and enter the root user password on the command line.

Run the following command:

Change master to master_host = '2017. 168.1.4 ', master_user = 'mysql', master_password = 'mysql', master_log_file = 'bin _ log.000001', master_log_pos = 395; (master_log_file and master_log_pos must have the same information as the master database)

Input: slave start;

Input: show slave status

 

If the line part in the figure is "YES", the master-slave synchronization is normal.

 

Tested: data is created and added to the master database, and the slave database can be backed up normally.