Implementation of two-way MySQL Data Synchronization

If you want to perform one-way MySQL Data Synchronization, read another article:

I. Prepare the environment
Master:
Ip: 192.168.211.128
Mysql version: 5.0.77 (you can view it by running the command mysqladmin-uroot-p123 version)
Slave:
Ip: 192.168.211.131
Mysql version: 5.0.77
2. Set Synchronization
Set master Synchronization
1. modify my. cnf
Vi/etc/my. cnf
Add the following content:
Server-id = 1 (mysql id)
Log-bin = mysql-bin (enable the log-bin binary log file. The log file in/var/lib/mysql starts with mysql-bin by default)
Binlog-do-db = backup (backup is the name of the database to be synchronized. To synchronize multiple databases, you can start another operation, such as binlog-do-db = backup1)
# Binlog-ignore-db = db_name (the database that does not synchronize logs, comment out if not needed)
Max-binlog-size = 104857600 (the log size is exceeded and a new one is automatically generated)
Master-host = 192.168.211.131 (synchronous host)
Master-user = backup (synchronous user)
Master-password = 123 (synchronize user password)
Master-port = 3306
Replicate-do-db = backup (synchronize database)

Save and exit after modification, and restart mysql, for example, service mysql restart (restart is normal, indicating that the configuration is correct)
2. Create an account on mysql as the master for slave data synchronization.

Mysql> grant file on *. * to backup @ '192. 168.211.131 'identified by '20140901 ';
 

Mysql> flush privileges;

 
Mysql> create database backup;

Test whether account backup can access mysql on mster on slave:

# Mysql-u backup-p-h 192.168.211.128 (enter the password 123. The access instructions are correct)

Set slave Synchronization
1. modify my. cnf
Vi/etc/my. cnf
Add the following content:
Server-id = 2 (mysql id, which cannot be repeated)

Log-bin = mysql-bin (enable the log-bin binary log file. The log file in/var/lib/mysql starts with mysql-bin by default)

Binlog-do-db = backup (backup is the name of the database to be synchronized. To synchronize multiple databases, you can start another operation, such as binlog-do-db = backup1)

Max-binlog-size = 104857600 (the log size will automatically generate a new

Master-host = 192.168.211.128 (synchronize the IP address of the Master)

Master-user = backup (account required for synchronization)

Master-password = 123 (synchronization account password)

Master-port = 3306 (mysql access port in mstart)

Replicate-do-db = backup (name of the database to be synchronized)

# Replicate-ignore-db = mysql (do not synchronize the mysql database)
# Replicate-do-table = backup. stu (if you only need to synchronize the stu table in the backup database)

# Replicate-do-table = backup. teacher (if you only need to synchronize the stu table in the backup database)

Master-connect-retry = 60 (the number of seconds of sleep before the slave server thread retries to connect to the master server when the master server goes down or the connection is lost

Log-bin = mysql-bin (enable the log-bin binary log file)
Save and exit after modification, and restart mysql, for example, service mysql restart (restart is normal, indicating that the configuration is correct)
2. Create an account and database for synchronization on mysql as slave

Mysql> grant file on *. * to backup @ '192. 168.211.128 'identified by '123 ';
 

Mysql> flush privileges;

 
Mysql> create database backup;

1 2 3 Next Page