MySQL slave synchronization Management

MySQL management-master-slave synchronization Management

MySQL master-slave synchronization architecture is currently one of the most widely used database architectures, especially for websites with large loads. Therefore, it is very important to manage master-slave synchronization, beginners often do not know how to start when a master-slave synchronization error occurs.ArticleMySQL master-slave management is described in detail based on your own experience.

Role of MySQL master-slave Synchronization

(1)Data Distribution
(2)Load Balancing)
(3)Backup
(4)High Availability and fault tolerance

Principle of MySQL master-slave Synchronization

For MySQL master-slave synchronization, the most important thing is to understand how MySQL master-slave synchronization works, that is, the principle of master-slave synchronization. It can clearly guide its working process:

Roughly describe the process: Obtain binary logs from the master server from the server's Io thread, save them as relay logs locally, and then execute the relay logs from the top through the SQL thread, so that the slave database is consistent with the master database. The detailed process of master-slave synchronization is as follows:

1. The master server verifies the connection.

2. The master server opens a thread for the slave server.

3. The slave server informs the master server of the log offset.

4. The master server checks whether the value is smaller than the offset of the current binary log.

5. If the value is smaller than the value, the slave server is notified to fetch data.

6. The slave server continues to fetch data from the master server until the data is obtained. At this time, the slave server thread goes to sleep while the master server thread goes to sleep.

7. When the master server is updated, the master server thread is activated, binary logs are pushed to the slave server, and the slave server thread is notified to enter the working state.

8. Run the binary log from the SQL thread on the server and then go to sleep.

How to Build MySQL master-slave Synchronization

The establishment of master-slave synchronization is a relatively fine technical activity. If you do some work well in the early stage, you will be able to reduce a lot of work in the future. You need to pay attention to some problems during the establishment, during the building process, we will discuss the issues that need attention, so that beginners can effectively avoid some potential problems at the beginning (MySQL installation is not described here ):

1. Introduction to the master-slave synchronization Environment

Operating System Environment: centos 5.5 64 bit

MySQL version: MySQL 5.1.50

IP address of the master server: 10.1.1.75

Slave Server IP: 10.1.1.76

2. Create a synchronization account on the master server

Grant replication slave, file on *. * To 'replicase' @ '10. 1.1.% 'identified by '123 ';

Flush privileges;

Note:Do not set the password too easily when setting permissions!

3. Change the slave server configuration file

Server-id = 2

Replicate-wild-ignore-table = MySQL. %

Log-slave-Updates # enable this function if necessary

Note:

1)Server-IDThis item must be carefully checked and cannot conflict with the master server. Otherwise, the problem of Mo minyu will occur, because during synchronizationServer-IDMake a judgment, ifServer-IDOtherwise, it may lead to an endless loop.(During master/Master synchronization or loop Synchronization).

2)Some people may wonder why I want to use it here.Replicate-wild-ignore-tableParameter insteadReplicate-do-DBOrReplicate-ignore-DBTo filter the databases to be synchronized and those not to be synchronized. There are several reasons:

A.Replicate-wild-ignore-tableParameters can synchronize all cross-database updates, suchReplicate-do-DBOrReplicate-ignore-DBWill not synchronize similar

Use MySQL;

Update test. AAA set amount = Amount + 10;

B.Replicate-wild-ignore-table = MySQL. %When you need to add a database for synchronization in the future, you can easily add a database without restarting the slave server. Because it is likely that other databases need to be synchronized in the future.

3)Auto_increment_incrementAndAuto_increment_offsetParameterTwo parameters are generally used in the master-master synchronization to stagger the auto-increment value.,
Prevent key-Value Conflicts.

4)-- Slave-Skip-errors parameters. Do not skip the incorrect parameters unless you are very sure what you are doing. When you use these parameters, MySQL will ignore those errors, which will cause data inconsistency between your master and slave servers.

4. Get a snapshot version from the master server

If you are using MyISAM or InnoDB, use the following command on the master server to export a snapshot of the server:

Mysqldump-uroot-p -- lock-tables -- events -- triggers -- routines -- flush-logs -- master-Data = 2 -- databases test> dB. SQL

If you have tried InnoDB only, use the following command:

Mysqldump-uroot-p -- Single-transaction -- events -- triggers -- routines -- flush-logs -- master-Data = 2 -- databases test> dB. SQL

Pay attention to the usage of several parameters:

-- Single-transaction: this parameter is only applicable to InnoDB.

-- Databases is followed by the database names of all other databases except mysql. I only have one test database.

-- The Master-data parameter records the MySQL binary log location when the snapshot is exported and will be used later.

5. Restore the snapshot version to the slave server.

Mysqldump-uroot-p-h 10.1.1.76 test <dB. SQL

After the snapshot version is restored to the slave server, the data on the slave server is consistent with that on the master server.

6. Use Change master on the slave server to synchronize data from the master server

Use the grep command to find the name and location of the binary log.

[Root @ NS1 ~] # Grep-I "change master" DB. SQL

-- Change master to master_log_file = 'mysql-bin.000006 ', master_log_pos = 106;

Generate the change master statement, and then execute

Stop slave;

Change master to master_host = '10. 1.1.75', master_user = 'replicase', master_password = '000000', master_log_file = 'mysql-bin.000006 ', master_log_pos = 123456;

Start slave;

This completes the setup of master-slave synchronization, and finally uses show slave status \ G; to view the status of slave_io_running and slave_ SQL _running. If both are yes, it is done.

Note::Do not write the synchronized information into the configuration file, which is inconvenient to manage, especially when there are changes that need to be restarted.

MySQL master-slave synchronization Management

Here are some commands for managing MySQL master-slave synchronization:

1. Stop MySQL Synchronization

Stop slave io_thread; # Stop the IO Process

Stop slave SQL _thread; # Stop the SQL Process

Stop slave; # Stop Io and SQL Processes

2. Start MySQL Synchronization

Start slave io_thread; # Start the IO Process

Start slave SQL _thread; # Start the SQL Process

Start slave; # Start Io and SQL Processes

3. Reset MySQL Synchronization

Reset slave;

Used to make the slave server forget its replication location in the master server's binary log, it will delete the master.info and relay-log.info files, as well as all the relay logs, and start a new relay log, you can perform this operation from the top when you do not need the master or slave node. Otherwise, it will be synchronized in the future, and may overwrite your database. I have encountered such a silly cross before. Haha!

4. view the MySQL synchronization status

Show slave status;

This command mainly checks the values of slave_io_running, slave_ SQL _running, seconds_behind_master, last_io_error, and last_ SQL _error to grasp the replication status.

5. Temporarily skipping MySQL synchronization errors

I often encounter errors in MySQL master-slave synchronization, such as a primary key conflict, so I need to temporarily skip this error while ensuring that the data of that row is consistent, use SQL _slave_skip_counter =
NCommand. N indicates that the next n events are skipped. For example, the operation for skipping an event is as follows:

Stop slave;

Set global SQL _slave_skip_counter = 1;

Start slave;

6. Re-Sync from the specified location

Sometimes, when there is a problem with master-slave synchronization, You need to synchronize data from the next location of the log, which is equivalent to skipping the error. At this time, you can also use the change master command to handle it, you only need to find the corresponding log location, for example:

Change master to master_host = '10. 1.1.75', master_user = 'replicase', master_password = '000000', master_log_file = 'mysql-bin.000006 ', master_log_pos = 123456;

Start slave;

MySQL master-slave synchronization management experience

1. Do not use the SQL _slave_skip_counter command in disorder.

After this command is skipped, The Master/Slave Data may be inconsistent. You must first record the specified errors and then check whether the data is consistent, especially the core business data.

2. Use the percona-Toolkit tool Pt-table-checksum to regularly check whether the data is consistent.

This is something DBAs must do on a regular basis. Why not have a proper tool? In addition, percona-Toolkit also provides a solution for Database Inconsistency. You can use Pt-table-sync, which does not change the master data. You can also use Pt-heartbeat to view slave Server replication lags. For details, see http://blog.chinaunix.net/uid-2064255-id-3229211.html.

3.Use the replicate-wild-ignore-Table option insteadReplicate-do-DBOrReplicate-ignore-DB.

The cause is described above.

4.Change the log mode of the master serverMixed.

5.Each table is added with a primary key, which affects Database Synchronization, especially whenROwReplication mode.