Master-Slave synchronization management of MySQL management

Master-Slave synchronization management of MySQL management

MySQL master-Slave synchronization architecture is currently one of the most used database architecture, especially the load is relatively large site, so for master-slave synchronization management is very important, novice often in the case of master-slave synchronization errors do not know how to start, This article is a detailed description of MySQL master-slave management according to its own experience.

The role of MySQL master-slave synchronization

(1) Data distribution
(2) Load balancing (load Balancing)
(3) Backup
(4) High availability (Hi availability) and fault tolerance

The principle of MySQL master-slave synchronization

The main thing about MySQL's master-slave synchronization is to understand how MySQL's master-slave synchronization works, that is, the principle of master-slave synchronization, through the process that can clearly guide its work:

Describe the process roughly: from the server's IO thread, get the binary log from the primary server, save it locally as a trunk log, and then execute the contents of the relay log from the top through the SQL thread to keep it consistent from the library and the main library. The detailed process of master-slave synchronization is as follows:

1. The primary server verifies the connection.

2. The primary server opens a thread from the server.

3. Shift the primary server log from the server to the primary server.

4. The primary server checks to see if the value is less than the current binary log bias shift.

5. If it is less than, notify the server to fetch the data.

6. From the server continues to fetch data from the primary server until the completion, at this time, from the server thread into sleep, the primary server thread into sleep.

7. When the primary server has an update, the primary server thread is activated and the binary log is pushed to the slave server, and the server thread is notified to enter the working state.

8. Executes the binary log from the server SQL thread and then goes to sleep.

MySQL master-slave synchronization build combat

Master-Slave synchronization is a relatively thin technical work, pre-done a number of things will make you in the future job to reduce a lot of work, build a time need to pay attention to some problems, a time will be set aside to introduce the need to pay attention to the problem, so that beginners can at the beginning of the effective evasion of some potential problems ( MySQL installation does not introduce here):

1. Introduction to Master-Slave synchronization environment

Operating system environment: Centos 5.5 bit

MySQL version: MySQL 5.1.50

ip:10.1.1.75 of the primary server

ip:10.1.1.76 from the server

1. Set up a sync account on the primary server

GRANT REPLICATION slave,file on * * to ' REPLICATION ' @ ' 10.1.1.% ' identified by ' 123456 ';

FLUSH privileges;

Note: Do not set the password too simple when you set the permissions!

1. Changes from the server configuration file

Server-id = 2

Replicate-wild-ignore-table=mysql.%

Log-slave-updates #这个有需要可以开启

Attention:

1) Server-id this need to be carefully checked, must not and the main server conflict, or the time will appear Mo people its wonderful problem, because the synchronization will be based on the Server-id to make judgments, if the Server-id same does not synchronize, otherwise it may lead to a dead loop ( When the primary master is synchronized or the loop is synchronized).

2) Some people will feel strange why do I use the replicate-wild-ignore-table parameter instead of replicate-do-db or replicate-ignore-db to filter the database that needs to be synchronized and the database that does not need to be synchronized. Here are a few reasons:

1. The replicate-wild-ignore-table parameter synchronizes all cross-database updates, such as replicate-do-db or replicate-ignore-db, and does not synchronize similar

Use MySQL;

UPDATE test.aaa SET amount=amount+10;

1. Replicate-wild-ignore-table=mysql.% can be added at a later time to add synchronization databases without having to restart the database from the server. Because it is likely that you will need to synchronize other databases later.

3) auto_increment_increment and Auto_increment_offset parameters, these two parameters are generally used in the main master synchronization, used to stagger the self-increment, to prevent the key value conflict.

4)--slave-skip-errors parameters, do not use these skipped error parameters indiscriminately, unless you are very sure what you are doing. When you use these parameters, MySQL ignores those errors, which can cause your master-server data to be inconsistent.

1. Get a snapshot version from the master server

If you have a MyISAM or both MyISAM and InnoDB, use the following command on the primary 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

The only InnoDB is to use the following command:

Mysqldump-uroot-p--single-transaction--events--triggers--routines--flush-logs--master-data=2--databases test ; Db.sql

There are several parameters that need to be used:

--single-transaction This parameter applies only to InnoDB.

--databases behind the library name of all the other databases except MySQL, I have only one test library here.

The--master-data parameter records the location of the MySQL binary log at the time of the export snapshot, which will be used later.

1. To restore a snapshot version to a 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 from the server is consistent with the data from the primary server.

1. Synchronizing from the primary server using change master from the server

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

[Email protected] ~]# grep-i "Change Master" Db.sql

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

The change master statement is generated and then executed from the

STOP SLAVE;

Change MASTER to master_host= ' 10.1.1.75 ', master_user= ' replication ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000006 ', master_log_pos=106;

START SLAVE;

This completes the master-slave synchronization of the building, and finally use show SLAVE status\g; Check the status of Slave_io_running and slave_sql_running, if all is yes, you are done.

Note: Do not write synchronized information to the configuration file, not easy to manage, especially if there is a change to restart.

MySQL master-Slave synchronization management

Here are some commands to manage MySQL master-slave synchronization:

1. Stop MySQL Sync

STOP SLAVE Io_thread; #停止IO进程

STOP SLAVE Sql_thread; #停止SQL进程

STOP SLAVE; #停止IO和SQL进程

1. Start MySQL Sync

START SLAVE Io_thread; #启动IO进程

START SLAVE Sql_thread; #启动SQL进程

START SLAVE; #启动IO和SQL进程

1. Reset MySQL Sync

RESET SLAVE;

Used to let the subordinate server forget its copy location in the primary server's binary log, it will delete the Master.info and relay-log.info files, and all the relay logs, and start a new relay log, when you do not need master and slave can be performed on the slave. Otherwise it will be synchronized later, may overwrite your database, I have encountered such a silly thing before. Ha ha!

1. View MySQL sync status

SHOW SLAVE STATUS;

This command primarily looks at the values of slave_io_running, slave_sql_running, Seconds_behind_master, Last_io_error, and last_sql_error to grasp the status of replication.

1. Temporarily skipping MySQL sync errors

Often friends MySQL master-slave synchronization encountered an error, such as a primary key conflict, then I need to ensure that the row of data consistent with the case of a temporary skip this error, it will need to use the Sql_slave_skip_counter = n command, N is to skip the following n events, for example, I skipped an event as follows:

STOP SLAVE;

SET GLOBAL sql_slave_skip_counter=1;

START SLAVE;

1. Resync from a specified location

Sometimes the master-slave synchronization has a problem, you need to synchronize from the next location of the log position, the equivalent of skipping that error, this time can also use the Change Master command to handle, as long as the corresponding log location can be found, such as:

Change MASTER to master_host= ' 10.1.1.75 ', master_user= ' replication ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000006 ', master_log_pos=106;

START SLAVE;

MySQL master-Slave synchronization management Experience Introduction

1. Do not use the Sql_slave_skip_counter command indiscriminately.

This command will likely cause your master-slave data to be inconsistent after skipping, so be sure to record the specified errors before checking the consistency of the data, especially the core business data.

1. Combine the Percona-toolkit tool pt-table-checksum periodically to see if the data is consistent.

This is what DBAs have to do on a regular basis, hehe, what's the right tool for it? In addition, Percona-toolkit provides a solution to the database inconsistency, which can be used with pt-table-sync, which does not change the master's data. You can also use pt-heartbeat to view replication lag from the server. For details, please see: http://blog.chinaunix.net/uid-20639775-id-3229211.html.

1. Use the replicate-wild-ignore-table option instead of using REPLICATE-DO-DB or replicate-ignore-db.

The reason has been explained above.

1. Adjust the log mode of the primary server to mixed.
2. Each table is prefixed with a primary key, and the primary key has an impact on the database synchronization, especially in the Row replication mode.

Master-Slave synchronization management of MySQL management