MySQL Bidirectional Synchronization

Mysql bidirectional sync configuration under Centos

One, the environment

Master:centos

ip:172.16.100.5

Slave:centos

ip:172.16.100.6

Depending on the installation method, the database-related paths are not consistent,

Yum Installation Database storage path/var/lib/mysql,

Database configuration file path/usr/share/mysql,

Database-related command/usr/bin.

1. View the cnf file under the/usr/share/mysql directory of the main library server

/usr/share/mysql/*.cnf

. CNF Configuration Reference:

MY-SMALL.CNF memory is less than or equal to 64M, only provides very few database services

MY-MEDIUM.CNF exists between 32m-64m and is used in conjunction with other services, such as Web

MY-LARGE.CNF exist 512M main database services provided

1G-2G exists in MY-HUGE.CNF, mainly provides database service

MY-INNODB-HEAVY-4G.CNF memory 4G, mainly provide a large load database service (general server use this)

2. copy files to/etc and rename to My.cnf

# CP/USR/SHARE/MYSQL/MY-INNODB-HEAVY-4G.CNF/ETC/MY.CNF

Two

Master side

1, enter MySQL, create a database testdb;

Mysql>create database TestDB;

2, create a user to synchronize, specify only in 172.26.100.6 login;

Mysql>grant replication Slave on * * to ' testdb ' @ ' 172.26.100.6 ' identified by ' 123456 ';

3. Open My.cnf and add the following fields

# VI/ETC/MY.CNF

Server-id = 1

Log-bin = Log

BINLOG-DO-DB = TESTDB//The database that needs to be synchronized, if there is no bank, that means synchronizing all databases

Master-host = 172.16.100.6

Master-user = TestDB

Master-password = 123456

Master-port = 3306

Master-connect-retry = 10

REPLICATE-DO-DB = testdb//database to be received, if there are multiple databases, one row per database

4, restart the master machine's MySQL service

Service mysqld Restart

5, go to MySQL, execute

Mysql>slave start;

Three

Slave end

1, go to MySQL, create a database TestDB

Mysql>create database TestDB;

2, create a user to synchronize, specify only in 172.26.100.5 login

Mysql>grant replication Slave on * * to ' testdb ' @ ' 172.26.100.5 ' identified by ' 123456 ';

3. Open My.cnf and add the following fields

# VI/ETC/MY.CNF

Server-id = 2

Log-bin = Log

Binlog-do-db = TestDB

Master-host = 172.26.100.5

Master-user = TestDB

Master-password = 123456

Master-port = 3306

Master-connect-retry = 10

REPLCATE-DO-DB = testdb//database to be received, if there are multiple databases, one row per database

4, then restart the slave machine MySQL

Service mysqld Restart

5. Enter MySQL in the slaves machine

Mysql>start slave;

Four

Data synchronization;

1, bidirectional Synchronization is a one-way synchronization in turn to go through.

Enter MySQL on the master server

Mysql> Show master status;

+------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------+----------+--------------+------------------+

|  log.000011 | 4514237 | TestDB | MySQL |

+------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Record the log.000011 and 4514237.

2, execute on the slave server

mysql> stop Slave;

Query OK, 0 rows Affected (0.00 sec)

3, enter the following command:

Mysql> Change Master to

-master_host = ' 172.26.100.5 ',

-Master_user = ' TestDB ',

-Master_password = ' 123456 ',

-Master_log_file = ' log.000011 ',

-Master_log_pos = 4514237;

Query OK, 0 rows Affected (0.00 sec)

mysql> start slave; Start syncing

Query OK, 0 rows Affected (0.00 sec)

4. Enter MySQL on the slave server to view the data.

Mysql> Show master status;

+------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------+----------+--------------+------------------+

|  log.000001 | 4502348 |                  TestDB | |

+------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Record the log.000001 and 4502348.

5. Execute on master server

mysql> stop Slave;

Query OK, 0 rows Affected (0.00 sec)

6, enter the following command:

Mysql> Change Master to

-master_host = ' 172.26.100.6 ',

-Master_user = ' TestDB ',

-Master_password = ' 123456 ',

-Master_log_file = ' log.000001 ',

-Master_log_pos = 4502348;

Query OK, 0 rows Affected (0.00 sec)

mysql> start slave; Start syncing

Query OK, 0 rows Affected (0.00 sec)

five; test.

mysql> slave processlist \g;

Both slave_io_running and slave_sql_running are Yes, indicating a successful configuration.

This article is from the "Logs" blog, make sure to keep this source http://51log.blog.51cto.com/6076767/1852738

MySQL Bidirectional Synchronization