I. concept:
① Database Synchronization (master-slave synchronization-write data to the slave server while writing data to the master database)
② Database Synchronization (master-master synchronization-two database servers write data to each other)
Ii. Example
Database Server (A) master database IP Address: 192.168.1.134
Database Server (B) master database IP Address: 192.168.1.138
The user name for synchronization between the two servers is: bravedu password: brave123
I. primary database operation settings ():
① Create a user IP address (non-local IP address) that can be connected with the synchronization User Name)
Copy codeThe Code is as follows:
Grant replication slave on *. * to 'bravedu' @ '2017. 192.% 'identified by 'brave123 ';
Flush privileges;
② Change the mysql configuration file
Copy codeThe Code is as follows:
[Mysqld]
Server-id = 1
Log-bin =/www/mysql/binlog (the path must be based on your own installation settings)
Binlog-do-db = dbname (name of the database to be synchronized)
Binlog-ignore-db = mysql
Restart the mysql server
③ View the synchronization status of the primary database
Copy codeThe Code is as follows:
Mysql> flush tables with read lock;
Mysql> show master status \ G
* *************************** 1. row ***************************
File: mysql-bin.000001 (note to use this when setting the slave server)
Position: 106 (use this parameter when setting the slave server)
Binlog_Do_DB: dbname
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
Mysql> unlock tables;
******* The master server has been set up to the current location ***********
Ii. Slave database operation settings (B ):
① Create a synchronization User Name
Copy codeThe Code is as follows:
Grant replication slave on *. * to 'bravedu' @ '2017. 192.% 'identified by 'brave123 ';
Flush privileges;
② Change the mysql configuration file
Copy codeThe Code is as follows:
[Mysqld]
Server-id = 2
Log-bin =/www/mysql/binlog (the path must be based on your own installation settings)
Binlog-do-db = dbname (name of the database to be synchronized)
Binlog-ignore-db = mysql, information_schema
Restart the mysql server
③ Specify the master-slave Database Server synchronization command
Note: The IP addresses, user names, passwords, log_file, and log_post of the master server are consistent with those of the master server.
This operation may need to unlock the table, stop the database, and start the table after running.
Copy codeThe Code is as follows:
Mysql> stop slave;
Mysql> change master to master_host = '192. 168.1.134 ', master_user = 'bravedu', master_password = 'brave123', master_log_file = 'mysql-bin.000001 ', master_log_pos = 192;
Mysql> start slave;
Mysql> unlock tables;
④ Checking the synchronization status of the primary database will result in a lot of information, but you can only check the two statuses if they are both yes.
Copy codeThe Code is as follows:
Mysql> show slave status \ G;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
So far, the master-slave Database Synchronization configuration is complete.