Shulou(Shulou.com)05/31 Report--

This article introduces how to synchronize the database in the master-slave synchronization of mysql database. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Background: recently, there is a requirement for mysql database synchronization, which I use mysql master-slave synchronization method to achieve. Let's record the steps.

Environment and Topology

Operating system: Centos6.6 X64

Mysql version: 5.1.73

Master: 10.6.1.210

Slave: 10.6.1.211

Requirements: synchronize test libraries on Master to Slave, but do not synchronize AA tables under this library

1. Configure my.cnf on Master

# vim / etc/my.cnf add content to [mysqld], and set to synchronize only test database:

[mysqld]

Log-bin=mysql-bin

Binlog_format=mixed

Binlog_do_db=test

Server-id=1

2. Configure my.cnf on 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 (ignore synchronizing a table)

3. Set up a backup account in Master: each slave connects to Master with a standard MySQL username and password, and the password for the user name is stored in the text file master.info. The user performing the replication operation is granted REPLICATION SLAVE permission.

The command is as follows:

# set up an account repluser, and only allow the host 10.6.1.211 to log in with a password of 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 and keep the data consistent in the database. This step can be ignored in the new installation.

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 root@10.6.1.211:/tmp/

Import the new test database on Slave. Log in and run from behind

# mysql-u root-p password123 test 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 of the slave server, log in to the mysql of the slave server and execute the following command. The red part is the bin file and node information just queried on the master server:

Mysql > change master 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)

# start the replication process of the slave server node to achieve master-slave replication

Mysql > start slave

# View the slave server status, mainly focusing on the enabled status of IO thread and SQL thread:

Mysql > show slave status\ G

...

...

Whether Slave_IO_Running:Yes # IO thread is running

Whether Slave_SQL_Running:Yes # SQL thread is running

...

...

8. View the thread status on the 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 master-slave synchronous replication configuration of the mysql database is complete.

Verification

1. Create a new test table under the test library on Master. Insert the record and see if it is synchronized to the Slave.

Build tables on Master

Mysql > CREATE TABLE `test` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (10) DEFAULT NULL

PRIMARY KEY (`id`)

)

Insert record on Master

Mysql > insert into test (id,name) values

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | steven |

+-+ +

1 row in set (0.00 sec)

Check on Slave to see if you synchronize the past.

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | steven |

+-+ +

1 row in set (0.00 sec)

two。 Create a new AA table under the test library on Master. Insert the record and see if it is synchronized to the Slave.

Query the AA table on Master.

Mysql > select * from AA

+-+ +

| | id | name |

+-+ +

| | 1 | Angelababy |

+-+ +

1 row in set (0.00 sec)

The query AA record on Slave is empty.

Mysql > select * from AA

Empty set (0.00 sec)

On how to carry out mysql database master-slave synchronization database synchronization configuration is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.