First, the concept:
① Database Synchronization (master-slave Synchronous---Primary database writes to write data from server)
② database Synchronization (primary primary Sync---Two database servers write data to each other)
Second, for example
Primary Primary database Synchronization server configuration
Database Server (A) Primary Database ip:192.168.1.134
Database Server (B) Primary Database ip:192.168.1.138
Two servers sync user name: bravedu Password: brave123
First, the main database operation settings (a):
① Create a sync user name allow connected User IP address (non-native IP)
Copy Code code as follows:
Grant replication slave on *.* to ' bravedu ' @ ' 192.168.1.% ' identified by ' brave123 ';
Flush privileges;
② Change MySQL configuration file
Copy Code code as follows:
[Mysqld]
Server-id = 1
Log-bin=/www/mysql/binlog/binlog (path to be set according to its own installation)
Binlog-do-db = dbname (the name of the database to be synchronized)
Binlog-ignore-db=mysql
#相对应主从数据库同步不同的地方
Copy Code code as follows:
Log-slave-updates
Sync_binlog=1
Auto_increment_offset=1
auto_increment_increment=2
Replicate-do-db = dbname
Replicate-ignore-db = Mysql,information_schema
Restart the MySQL server
③ View Primary Database sync status IP: ***.134
Copy Code code as follows:
Mysql>flush tables with read lock;
Mysql>show Master Status\g
1. Row ***************************
FILE:MYSQL-BIN.000001 (note here to use when setting from the server)
position:106 (note here to use when setting from the server)
Binlog_do_db:dbname
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)
Mysql>unlock tables;
The primary server is set up at the current location *******
two, from the database operation Setup (B):
① Create sync User name
Copy Code code as follows:
Grant replication slave on *.* to ' bravedu ' @ ' 192.168.1.% ' identified by ' brave123 ';
Flush privileges;
② Change MySQL configuration file
Copy Code code as follows:
[Mysqld]
Server-id = 2
Log-bin=/www/mysql/binlog/binlog (path to be set according to its own installation)
Binlog-do-db = dbname (the name of the database to be synchronized)
binlog-ignore-db= Mysql,information_schema
#相对于主从同步不同的地方
Binlog-do-db = dbname
Binlog-ignore-db=mysql
Log-slave-updates
Sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
Restart the MySQL server
View Primary Database Sync status IP: ***.138
Copy Code code as follows:
Mysql>flush tables with read lock;
Mysql>show Master Status\g
1. Row ***************************
file:mysql-bin.000005 (note here to use when setting from the server)
position:106 (note here to use when setting from the server)
Binlog_do_db:dbname
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)
Mysql>unlock tables;
③ Specify the primary primary database server synchronization directives
Note: IP Primary server IP, username, password, log_file,log_post server Unified
This operation may need to unlock the table first, stop the database state, after running in the startup state
Copy Code code as follows:
mysql > Stop slave;
#设置192.168.1.138 database server configuration so the host configuration file information is 134 information
MySQL > Change master to master_host= ' 192.168.1.134 ', master_user= ' bravedu ', master_password= ' brave123 ', Master_log _file= ' mysql-bin.000005 ', master_log_pos=106;
#设置192.168.1.134 database Server configuration so the configuration file information such as host is 134 information
MySQL > Change master to master_host= ' 192.168.1.138 ', master_user= ' bravedu ', master_password= ' brave123 ', Master_log _file= ' mysql-bin.000001 ', master_log_pos=106;
mysql > Start slave;
mysql > Unlock tables;
④ View the primary database synchronization state will come out a lot of information but mainly look at these two states on the line if you are yes.
Copy Code code as follows:
Mysql>show slave status\g;
Slave_io_running:yes
Slave_sql_running:yes
At this point, the primary primary database has been successfully synchronized with complete configuration.
