8. mysql dual-host Master/Slave, mysql dual-host

8. mysql dual-host Master/Slave, mysql dual-host

In this era of data explosion, data backup is the most important thing for every company. As a data storage warehouse, the importance of database is self-evident. Major databases include: 1. Large database oracle 2. Medium SQL server 3. Small database mysql. It can be said that each has its own advantages, and I will not go into details here.

Mysql dual-host master/backup is to write all the data in the primary database to the backup database to achieve hot backup of the mysql database.

I. preparation:

To implement dual-host master/backup, the versions of the two machines must be the same, or the version of the master machine is low. Otherwise, the two machines cannot succeed. Therefore, the same version of mysql is installed on the two machines.

1. 192.168.1.111 master

2. 192.168.1.112 from

Ii. Configuration:

Modify the mysql configuration file: when the path here is/etc/mysql/my. cnf

1. Master

[Mysqld]

Server-id = 1

Log-bin = mysql-bin

Binlog-do-db = test // database to be backed up

Binlog-ignore-db = mysql // database that is not backed up

2. Slave

[Mysqld]

Server-id = 2 // another server is 3, which cannot be the same

Log-bin = mysql-bin

Replicate-do-db = test // database to be backed up

Replicate-ignore-db = mysql // do not back up the database

3. Both the master and slave mysql instances must be restarted.

Iii. Synchronization:

1. Master

Log on to the master using root,

Mysql> grant replication slave on *. * to 'Here is the Login User' @ 192.168.1.20.identified by 'your password'

Mysql> flush privileges;

Mysql> show master status; displays the following

2. Slave

Log On with root

Mysql> stop slave; // stop slave first

Mysql> change master

> Master_host = '1970. 168.1.111 ',

> Master_user = 'your logon account ',

> Master_password = 'Password ',

> Master_log_file = 'mysql-bin.000199 ',

> Master_log_pos = 1321;

Mysql> start slave;

Mysql> show slave status \ G // display as follows

YES indicates that the operation is successful!

3. Note:

The data tables in the master and slave databases must be the same. If the data tables are different, the operation fails. (You need to synchronize the data table first)

Iv. test:

Test it on your own!

Suggestion: you are welcome to give some suggestions or comments!