Configure master-slave synchronization for the MySQL database

Configure master-slave synchronization for the MySQL database

1. Environment Description: DB master: 172.16.1.55
DB slave: 172.16.1.56
2. Server-id configuration (/etc/my. cnf ):
The master database configuration server-id is smaller than the slave Database ID.
Master Database ID settings: Server-id = 55
Slave Database ID settings: Server-id = 56
3. log format settings (/etc/my. cnf ):
Log-bin = mysql-bin (set both the master database and slave database)
4. master database authorization slave database permission settings:
Grant replication slave on *. * to 'slave '@ '192. 16.1.56' identified by 'p @ ssw0rdskycloud ';
Flush privileges;
5. Set the master database for master-slave synchronization and databases that do not require master-slave synchronization:
Use binlog-do-db = XXX to manually specify the database to be synchronized;
Use binlog-ignore-db = XXX to manually specify databases that do not need to be synchronized;
Vi/etc/my. cnf:
[Mysqld]
Log-bin = mysql-bin
Server-id = 55
Binlog-do-db = test
Binlog-do-db = information_schema
Binlog-do-db = performance_schema
Binlog-ignore-db = mysql
6. Set the slave database to be synchronized from the master database and the database that does not need to be synchronized from the master database:
Use replicate-do-db = XXX to manually specify the database to be synchronized;
Use replicate-ignore-db = XXX to manually specify databases that do not need to be synchronized;
Vi/etc/my. cnf:
[Mysqld]
Log-bin = mysql-bin
Server-id = 56
Replicate-do-db = test
Replicate-do-db = information_schema
Replicate-do-db = performance_schema
Replicate-ignore-db = mysql
7. Restart the mysql database in the master database and slave database respectively.
Service mysql restart
8. Run the following command on the master database:
Show master status;
Record the mysql-bin file and location.
For example, the position is: mysql-bin.000014 107
9. Stop the slave status from the database:
Slave stop;
10. manually execute Data Synchronization from the database:
Change master to master_host = '192. 16.1.55', master_user = 'slave ', master_password = 'P @ ssw0rdskycloud', master_log_file = 'mysql-bin.000014 ', master_log_pos = 172;
11. Enable the slave mechanism of the slave database:
Slave start;
12. View slave status \ G;

13. Test the mysql Master/Slave status:
Create a table on the master database and run the show tables Statement on the slave database.
Insert data into the data table of the master database, execute the select query statement from the database, and view the data records in the synchronized data table.
Create table linxi (id int not null primary key, name char (20 ));
Insert into linxi values (1, 'lutaoxu ');
Run the query statement on the database:
Use test;
Select * from linxi;

-------------------------------------- Split line --------------------------------------

Important parameter log_slave_updates for MySQL master-slave Synchronization

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

This article permanently updates the link address: