MySQL Database master-Slave synchronization configuration Tutorial--Database synchronization

background : Recently there is a MySQL database synchronization requirements, I use the MySQL master-slave synchronization method to achieve. Take a look at the steps below.

Environment and topology

Operating system: Centos6.6 X64

MySQL version: 5.1.73

master:10.6.1.210

slave:10.6.1.211

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/82/F6/wKioL1dnh8CwnoQKAAAo92B86eQ742.jpg "title=" 4c9.jpg "alt=" Wkiol1dnh8cwnoqkaaao92b86eq742.jpg "/>

Requirements: Implement the test library on master to synchronize to slave, but disable the synchronization of AA tables under this library

1. Configure the MY.CNF on master

#vim/etc/my.cnf Add content to [Mysqld], set only the test database to synchronize:

[Mysqld]

Log-bin=mysql-bin

Binlog_format=mixed

binlog_do_db=Test

Server-id=1

2. Configuring MY.CNF on the Slave

#vim/etc/my.cnf

Add content to [mysqld]:

Log-bin=mysql-bin

Binlog_format=mixed

server-id=10

Relay-log =relay-bin

Log_slave_updates=1

replicate_ignore_table=AA(ignoring synchronization of a table)

3. Create a backup account in master: Each slave uses a standard MySQL username and password to connect to master.

The user who makes the copy operation grants replication SLAVE permissions.

The command is as follows:

#建立一个帐户repluser, and can only allow 10.6.1.211 this host to log on, the password is 123456.

Mysql>grant replication Client,replication Slave on * * to ' repluser ' @ ' 10.6.1.211 ' identified by ' 123456 ';

queryok,0 rows Affected (0.00SEC)

Mysql>flush privileges;

queryok,0 rows Affected (0.00SEC)

4. Copy the data, keep the data in the database consistent, and the new installation can ignore this step.

Back up the test library on master and copy it to the slave server.

#mysqldump-u root-p password123 test>/tmp/test.sql

Copy the exported database to the slave server.

#scp/tmp/test.sql [Email protected]:/tmp/

Import the new test database on the slave. Log in from the back run

#mysql-u root-p password123 test</tmp/test.sql

5. Restart the MySQL service and both master and slave servers will restart.

#service MySQL Restart

6. View the bin file and point in time on the master database. Log on to master server after MySQL execution:

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|     mysql-bin.000015 | 2474 |                  Test | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Here, the bin file is mysql-bin.000015 and the node is 2474.

7. Start the relay log from the server, log in from the server after MySQL executes the following command, the Red section is the bin file that was just queried on the primary server and the node information:

mysql> changeMaster to master_host= ' 10.6.1.210 ', master_user= ' Repluser ',

Master_password= ' 123456 ', master_log_file= ' mysql-bin.000015 ', master_log_pos=2474,

Master_connect_retry=5

queryok,0 rows Affected (0.03SEC)

#开启从服务器节点的复制进程 to achieve master-slave replication;

Mysql>start slave;

#查看从服务器状态, focusing on the opening of IO Threads and SQL Threads:

mysql>show Slave Status \g

...

...

Slave_io_running:yes #IO Thread is running

Slave_sql_running:yes #SQL Thread is running

...

...

8. To view thread status on a master-slave server

Primary server:

Mysql>show processlist \g

...

...

State:has sent all binlog to slave; Waiting for Binlog to be updated

...

...

From the server

Mysql>show processlist \g

...

...

State:has read all relay log; Waiting for the slave I/O thread to update it

...

...

At this point, the MySQL database master-slave synchronous replication configuration is complete.

Verify

1. Create a new test table under the test library on master. Insert the records and see if they are synced to the slave.

Build table on Master

mysql> CREATE TABLE ' test ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar () DEFAULT NULL,
PRIMARY KEY (' id ')
) ;

Insert record on Master

mysql>INSERT INTO Test (Id,name) VALUES (1, ' Steven ');

Mysql> select * from test;
+----+--------+
| ID | name |
+----+--------+
| 1 | Steven |
+----+--------+
1 row in Set (0.00 sec)

Slave to see if the past is synchronized.

Mysql> select * from test;
+----+--------+
| ID | name |
+----+--------+
| 1 | Steven |
+----+--------+
1 row in Set (0.00 sec)

2. Create a new AA table under the test library on master. Insert the records and see if they are synced to the slave.

Query the AA table on master.

Mysql> select * from AA;
+----+--------+
| ID | name |
+----+--------+
| 1 | Angelababy |
+----+--------+
1 row in Set (0.00 sec)

Slave on the query AA record is empty.

Mysql> select * from AA;
Empty Set (0.00 sec)

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1791003

MySQL Database master-Slave synchronization configuration Tutorial--Database synchronization