MySQL Replication (master-slave replication)

I. Overview

Keep one server's data in sync with other server data. Data from one master library can be synchronized to multiple repositories, and the repository itself can be configured as the main repository for other servers.

Main applications:

1) Data distribution

2) Load Balancing

3) Pseudo backup. Add more secure technical supplements based on backup

4) High availability and failover. Avoid MySQL single point of failure

5) Upgrade test. Before upgrading the database, copy the data to the standby so that the query executes as expected in the repository.

supports two ways of copying

1) statement-based replication

2) row-based replication

The same principle is duplicated in two ways: recording binary logs on the main library and implementing asynchronous data replication in the standby replay log. Asynchronously represents a delay of several seconds, or even a few hours, of data from the repository at the same point in time that is inconsistent with the main library.

Replication is backwards compatible, that is, the repository database version is higher than the main library and cannot be lower than the main library

About overhead:

1) The cost of enabling the binary log;

2) Each repository also adds some I/O overhead to the main library

3) lock competition can also hinder the submission of transactions

Second, the working principle of replication

1. Log data changes to the binary log in the main library.

2. The repository copies the logs on the main library to the trunk log (relay log).

3. The repository reads the events in the trunk log and puts the data into the standby repository.

　　

Third, configuration replication

1. Create a copy Account

It is recommended that you create the same account for each database server.

Mysql> Grant replication Slave,replication Client on * * to [email protected]'192.168.0.%'  'password';

2. Configure the main library

vi /etc/my.conf     [mysqld]    log-bin=mysql-bin   //[must] enable binary log    server-id=ten      // [must] server unique ID, default is 1, usually take IP last paragraph

　　

3. Configuring the Standby Library

vi /etc/my.conf     [mysqld]    log-bin=mysql-bin   //[not required] enable binary log    server-ID=6     //[must] server unique ID, default is 1, usually take IP last paragraph

4. Restart the database separately

/etc/init.d/mysql restart

5. Log in to the master server for MySQL and query the status of master

Mysql>Show master status;        +------------------+----------+--------------+------------------+       | File             | Position | binlog_do_db | binlog_ignore_db |       +------------------+----------+--------------+------------------+       | mysql-bin. 000004 |      308 |              |                  |       +------------------+----------+--------------+------------------+       1 in Set (0.00  sec)       Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

Note the file and position used to configure the standby library

6. Configuring the Standby Library

Mysql>change Master to master_host='192.168.145.10', master_user='repl  ', master_password='password',    master_log_file='  mysql-bin.000004', master_log_pos=308;   // Be careful not to disconnect, there are no single quotes around 308 digits. 

7. Start the Copy from Server feature

Mysql>start slave;   

8. Check replication feature status from server

Mysql>Show Slave Status\g***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.145. Ten  //Primary server addressMaster_user:repl//authorization account name, try to avoid using rootMaster_port:3306    //database port, some versions do not have this lineConnect_retry: -Master_log_file:mysql-bin.000004Read_master_log_pos: -     //#同步读取二进制日志的位置, greater than or equal to Exec_master_log_posRelay_log_file:ddte-relay-bin.000003Relay_log_pos:251Relay_master_log_file:mysql-bin.000004Slave_io_running:yes//This state must be YesSlave_sql_running:yes//This state must be Yes                    ...... Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error). 

Reference:

1) high-performance MySQL third edition

2) Itwork's Blog

MySQL Replication (master-slave replication)