MySQL Master-slave replication

First, MySQL master-slave principle 1, master-slave form

(1) one Master one from
(2) Primary master replication
(3) A master many from the---extended system read performance, because the read is read from the library;
(4) Multi-primary support from---5.7
(5) Joint level replication

2. Principle of master-slave copying

(1) Slave server execution Start Slave, turn on master-slave copy switch, generate IO thread and SQL thread thread.
(2) At this point, the IO thread of the Slave server connects to the master server based on the content request of the Master.info file and requests the Master
The server sends the Binlog log after the specified location point of the specified Binlog log file.
(3) After the master server receives a request from the IO thread of the Slave server, the master server first verifies that the
The Slave IO thread requests to read Binlog log information after the specified location of the designated Binlog log file, along with the current
The IO that is sent to the Slave server with the new Binlog file name on the Master server and the location of the next specified update after the log contents are returned.
Thread.
(4) After the IO thread of the Slave server receives the information, the Binlog log content is written sequentially to the last of the relay log (Relay log) file.
MYSQL-RELAY-BIN.XXXXXX, log the new Binlog file name and location to the Master.info file so that the next
Location read.
(5) The SQL thread of the Slave server detects the newly added log content in the local Relay log in real time, and if there is an update, the Relay log
The contents of the file are parsed into SQL statements executed on Master and executed sequentially on the Slave server. and cleans up the applied logs after the application is complete.
(6) After the above steps, it is generally possible to ensure that the same SQL statements are executed on Master and Slave. Under normal circumstances, you can guarantee that the Master
is exactly the same as the data on the Slave.

Second, summarize the master-slave replication steps:

1. Main Library operation:

• (1) Turn on Binlog function

  grep log-bin /data/3306/my.cnflog-bin = /data/3306/mysql-bin

• (2) Ensure that all instances are server-id different

egrep server-id /data/{3306,3307,3308}/my.cnf#以上两个参数在[mysqld]下增加

• (3) User rep for the main library authorized replication

mysql -uroot -p123456 -S /data/3306/mysql.sockgrant replication slave on *.* to [email protected]‘192.168.56.%‘ identified by "123456";flush privileges;select user,host from mysql.user;show grants for [email protected]‘192.168.56.%‘;

• (4) Lock table, view Binlog file and location point (--master-data=2).

flush table with read lock;show master status;

• (5) New open Window Main Library export fully prepared

mkdir /server/backup -pmysqldump -uroot -p123456 -A -B --events -S /data/3306/mysql3306.sock |gzip > /server/backup/rep_bak_$(date +%F).sql.gzls -lrt /server/backup/

• (6) Unlock table, open user write function

unlock table;

2, from the library operation:

• (1) Ensure that all instances are server-id different

egrep "server-id" /data/{3306,3307,3308}/my.cnf

• (2) Import the master library to the Slave library

  gzip -d /server/backup/rep_bak_2018-01-09.sql.gzmysql -uroot -p123456 -S /data/3307/mysql.sock </server/backup/rep_bak_2018-01-09.sql.gz

• (3) Find location point, configure Master.info

  mysql-bin.000138 |3320mysql>change master to,master_host=‘192.168.56.11‘,master_port=3306,master_user=rep,master_password=‘123456‘,master_log_file=‘mysql-bin.000138‘,master_log_pos=3320;find /data/3307/data/ -type f -name "*.info"

• (4) Turn on the slave switch

start slave;show slave status\G

• (5) test
  The main library creates a library to see if there are any

Third, MySQL master and slave combat

Main Library operation: (1) Turn on Binlog function [[email protected] ~]# grep log-bin/data/3306/my.cnflog-bin =/data/3306/ Mysql-bin (2) Ensure that all instances are server-id different [[email protected] ~]# egrep server-id/data/{3306,3307}/my.cnf/data/3306/ My.cnf:server-id = 1/data/3307/my.cnf:server-id = 2 (3) Main Library authorized replication user rep[[email protected] ~]# mysql-uroot-p123456-s/  Data/3306/mysql3306.sock Welcome to the MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.6.12 mysql Community Server (GPL) Copyright (c) $, Oracle, Ma RIADB Corporation Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. MySQL [(None)]> select User,host from Mysq.user; ERROR 1146 (42S02): Table ' mysq.user ' doesn ' t existmysql [(None)]> grant replication Slave on * * to [Email protec Ted] ' 192.168.56.% ' identified by "123456"; Query OK, 0 rows Affected (0.00 sec) MySQL [(none)]> flush privileges; Query OK, 0 rows Affected (0.00 sec) MySQL [(None)]> Select User,host frommysql.user;+------+--------------+| user | Host |+------+--------------+| Root | 127.0.0.1 | | Rep | 192.168.56.% | | Root |      :: 1 | | | Linux-node2 | | Root |      Linux-node2 | | | localhost | | Root | localhost |+------+--------------+7 rows in Set (0.00 sec) MySQL [(none)]> show grants for [email protected] ' 192 .168.56.% '; +--------------------------------------------------------------------------------------------------- ------------------------+|                                                                                               Grants for [email protected]% |+------------------------------------------------------------------------------------------------------------- --------------+| GRANT REPLICATION SLAVE on * * to ' rep ' @ ' 192.168.56.% ' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 ' |+------------------------------------------------------------------------------------------------------------ ---------------+1 Row in Set (0.00 sec) (4) Lock table, view Binlog file and location point MySQL [(none)]> flush table with read lock; Query OK, 0 rows Affected (0.00 sec) MySQL [(none)]> Show Master status;+------------------+----------+--------------+ ------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+|      mysql-bin.000001 |              120 |                  |                   | |+------------------+----------+--------------+------------------+-------------------+ (5) New open Window Main Library export fully prepared [[email  protected] mysql]# mkdir/server/backup-p[[email protected] mysql]# mysqldump-uroot-p123456-a-B--events- S/data/3306/mysql3306.sock |gzip >/server/backup/rep_bak_$ (Date +%f). sql.gz[[email protected] mysql]# ll/ server/backup/total dosage 160-rw-r--r--1 root root 160789 January 9 16:42 rep_bak_2018-01-09.sql.gz (6) Main Library unlock table, open user write function MySQL [(none)]&G T Unlock table; operation from Library: (1) Import the master library to the Slave library [[EMAIl protected] mysql]# gzip-d/server/backup/rep_bak_2018-01-09.sql.gz [[email protected] mysql]# MySQL- Uroot-p654321-s/data/3307/mysql3307.sock </server/backup/rep_bak_2018-01-09.sql [[email protected] MySQL]  # mysql-uroot-p654321-s/data/3307/mysql3307.sockwelcome to the MariaDB Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.6.12 mysql Community Server (GPL) Copyright (c) $, Oracle, Ma RIADB Corporation Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Student | | Test |+--------------------+5 rows in Set (0.00 sec) MySQL [(none)]> use student; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W ith -adatabase changedmysql [student]> show tables;+-------------------+| Tables_in_student |+-------------------+| Test |+-------------------+1 row in Set (0.00 sec) MySQL [student]> select * from test;+----+-------------- +-----+| ID | name |  Age |+----+--------------+-----+| 1 |  Gaoyuliang |  60 | | 2 |  Gaoxiaoqin |  30 | | 3 |  Chenhai |  40 | | 4 |  houliangping | |+----+--------------+-----+4 rows in Set (0.00 sec) (2) Find location point, configure Master.infomysql [(none)]> change Master to Master_ho St= ' 192.168.56.12 ', master_user= ' rep ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000001 ', Master_log_ pos=120,master_port=3306; Query OK, 0 rows affected, 2 warnings (0.04 sec) (3) Turn on slave switch MySQL [(none)]> start slave; Query OK, 0 rows affected (0.04 sec) MySQL [(none)]> show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos        t:192.168.56.12          Master_user:rep master_port:3306 connect_retry:60 Master_log_fil                e:mysql-bin.000001 read_master_log_pos:120 relay_log_file:linux-node2-relay-bin.000002 relay_log_pos:283 relay_master_log_file:mysql-bin.000001 Slave_io_running:yes Slave _sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Repli                   cate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0               last_error:skip_counter:0 exec_master_log_pos:120 relay_log_space:462 Until_condition:none until_log_file:until_log_pos:0 Master_ssl_al Lowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_S              Sl_cipher:  Master_ssl_key:seconds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0              last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignore_server_ids: Master_server_id:1 master_uuid:88efb286-f50f-11e7-9631-000c29876389 Master_info_fi Le:/data/3307/data/master.info sql_delay:0 sql_remaining_delay:null Slave_sql_running_ State:slave have read all relay log; Waiting for the slave I/O thread to update it master_retry_count:86400 Master_bind:last            _io_error_timestamp:last_sql_error_timestamp:master_ssl_crl:master_ssl_crlpath: Retrieved_gtid_set:executed_gtid_set:auto_position:01 row in Set (0.00 sec) (4) test the main library creation Cour The SE library, and the test table are inserted and checked from the library for synchronization. MySQL [student]> CREATE DATABASE course; Query OK, 1 row Affected (0.00 sec) MySQL[student]> INSERT INTO test values (5, "Lili", 10); Query OK, 1 row affected (0.01 sec) MySQL [student]> select * from test;+----+--------------+-----+| ID | name |  Age |+----+--------------+-----+| 1 |  Gaoyuliang |  60 | | 2 |  Gaoxiaoqin |  30 | | 3 |  Chenhai |  40 | | 4 |  houliangping |  40 | | 5 |  Lili | |+----+--------------+-----+5 rows in Set (0.00 sec)

Iv. main points of master-slave replication principle

（1）异步方式同步（2） 逻辑同步模式（binlog 有三种模式： SQL、混合、 rowlevel），默认是通过 SQL 语句执行（3）主库通过记录 binlog 实现对从库的同步（4）主库 1 个线程（IO 线程），从库 2 个线程（IO 和 SQL）来完成（5）从库的关键文件： master.info、 relay-log、 relay-info（6）如果从库还想级联从库，需打开 log-bin 和 log-slave-updates 参数

Five, the production scene quickly configure MySQL master-slave replication scheme

（1）安装好要配置从库的数据库，配置好 log-bin 和 server-id 参数。（2）无需配置主库的 my.cnf 文件，主库的 log-bin 和 server-id 参数默认就是配置好的。（3）登陆主库增加用于从库连接的帐户，并授 replication slave 的权限。（4）半夜使用 mysqldump 带--master-data=1 参数全备主库，并在从库进行恢复。（5）在从库执行 change master to 语句，无需 binlog 文件及对应位置点。（6）在从库开启同步开关 start slave。（7）从库 show slave status\G，检查同步状态，并在主库进行更新测试。tips：（1）主从库服务器的配置差距不要太大。（2）撰写方案文档和实施步骤。如可能需要停机维护，需要事先申请停机维护时间

MySQL Master-slave replication