MySQL database configuration master-Slave synchronization

1. Environment Description:DB master: 172.16.1.55

DB slave: 172.16.1.56

2.server-id configuration (/etc/my.cnf):

The main library configuration server-id is smaller than the ID from the library .

Main Library ID settings:server-id=55

From library ID settings:server-id=56

3. Log format settings (/ETC/MY.CNF):

Log-bin=mysql-bin ( Main Library, set from library )

4. Permission settings for the Master library from the library:

grant replication Slave on * .' slave'@'172.16.1.56' identified by '[email protected]';

Flush privileges;

5. The main library sets the database for master-slave synchronization and the database that does not require master-slave synchronization:

manually specify the database to be synchronized using binlog-do-db=xxx ;

Use binlog-ignore-db=xxx To manually specify a database that does not require synchronization;

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. from the library, set up the databases to synchronize from the main library and the databases that do not need to be synchronized from the main library:

manually specify the database to be synchronized using replicate-do-db=xxx ;

Use replicate-ignore-db=xxx To manually specify a database that does not require synchronization;

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 from the main library and from the library separately.

Service MySQL Restart

8. Perform on the main library :

Show master status;

Record the file and location of the Mysql-bin.

For example, you can see the location:mysql-bin.000014 107

9. Stop the slave State from the library first :

Slave stop;

10. Manually perform data synchronization from the library:

Change Master tomaster_host='172.16.1.55', master_user='slave', master_password='[email protected]', master_log_file='mysql-bin.000014', master_log_pos=107;

11. Open the slave mechanism from the library:

Slave start;

12. View slave slave status\g;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/58/D8/wKiom1S-FWrjoc_kAAJ0u1EroMg165.jpg "title=" MySQL master-slave copy. jpg "alt=" wkiom1s-fwrjoc_kaaj0u1eromg165.jpg "/>

test MySQL master-slave Status:

Create a table on the main library, and then execute Show tables on the database that corresponds to it from the library; statement.

Insert data into the main Library data table, and then View the data records in the Synchronized data table from the SELECT query statement from the library.

CREATE TABLE Linxi (ID int not NULL primary key,name char (20));

INSERT INTO Linxi values (1,'lutaoxu');

To execute a query statement from the library:

Use test;

SELECT * from Linxi;

This article is from the "Lin XI" blog, please be sure to keep this source http://lutaoxu.blog.51cto.com/4224602/1606209

MySQL database configuration master-Slave synchronization