brief introduction

What does MySQL master-slave replication do
1.MySQL master-slave replication does not affect the normal operation of the business during the table locking process. The master database is responsible for writing and the slave database is responsible for reading. In this way, the master database can also lock the table and run the business normally through the slave database.
2. Be able to perform hot backup of data.
3. With the expansion of the architecture, the business volume is getting larger and larger, and the l/o access frequency is too high, which can not be met by a single machine. At this time, do multi library storage to reduce the disk l/o access frequency and improve the l/o performance of a single machine.
4.MySQL master-slave replication refers to data that can be copied from a MySQL database server master node to one or more nodes for data security.
5.MySQL adopts asynchronous replication by default, so that the slave node does not have to always access the master-slave server to update its own data. The data can be updated on the remote connection, and the slave node can copy all databases (single database) or specific tables in the master database.

MySQL replication principle

1. The change of MySQL service server data is recorded in the binary binlog log. When the data on the master changes, the change is written into the binary log.
2. The slave server will detect whether the master binary log has changed within a certain time interval. If it has changed, it will start an l/o thread to request the master binary event.
3. At the same time, the master node starts a dump thread for each l/o thread to send binary events to it and save them in the local relay log of the slave node. Start the sql thread from the node to read the binary log from the relay log and replay it locally to make its data consistent with the node. Finally, l/othread and sql thread will enter the sleep state and wait for the next wake-up.
Introduction Language Overview
>>Two threads will be generated from the library, one l/o thread and one sql thread.
>>The l/o thread will request the binlog of the main database and write the binlog to the local relay log file.
>>The master library will generate a log dump thread to thread binlog to the slave library l/o thread.
>>The sql thread will read the log in the relay log file and parse it into one of the sql statements for execution.

Different versions of master-slave configuration

The experiment begins

MySQL master-slave setup

Versions 5.7 and 5.6 are used this time. Different versions will cause various compatibility problems. Unable to connect. You need to add a configuration server to the configuration file_ id=15.

Main process

1. Create a user dedicated to backup on the primary server.
2. The backup data of the primary server is used for synchronization.
3. Turn off the log from the server, manually synchronize data, and turn on the log information.
4. Change the master information from the server and start the slave service.
5. Closeout to verify the success of the group slave structure.

Master slave replication premise

1. 2 or more database instances.
2. The main library needs to open binary logs.
3. server_ Different IDS should be used to distinguish different nodes.
4. The master database needs to establish a dedicated replication slave
5. The slave database should copy the history by backing up the master database and replying.
6. Think to tell the library some replication information (ip, port, user, password, binary log starting point).
7. The slave library should open a dedicated replication thread.

-Master server 5.6

Create replication user:
mysql -uroot -ppassword -e "grant reolication slave on . to user@'192.168.1.%' identified by 'password';"
Backup data:
mysqldump -uroot -ppassword -A --master-data=2 --single-transaction -R -E --triggers >> /tmp/fulll.sql
Modify the configuration file my COF is modified as follows

server_id=15
port=3344

(port cannot be less than 3000, otherwise the service starts slowly and may not start)

-Slave server 5.7

Close log:
set sql_log_bin=0;
Restore data:
source /root/full ;
Open log:
set sql_log_bin=1;
Establish connection file

CHANGE MASTER TO      									##Change master
MASTER_HOST='192.168.1.54',				        		 ##Host ip
MASTER_USER='repl',										##Primary user name
MASTER_PASSWORD='123456',								##Password of the primary user
MASTER_PORT=3355,										##port of primary user
MASTER_LOG_FILE='mysql-bin.000005',				 		##Main log file
MASTER_LOG_POS=328,								 		##Master log node
MASTER_CONNECT_RETRY=10;								##Number of primary reconnections

View pos No. 5.6

You can view the backup binary file, which records the MySQL log file and node (pos) number.

Enable master-slave replication 5.7

From validation
mysql > start slave；
mysql > show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.54
                  Master_User: repl
                  Master_Port: 3355
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 328
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
---------------------------------------------------------------------------
                Last_IO_Errno: 0  	------+
                Last_IO_Error: 	   	------|>	Error message	
               Last_SQL_Errno: 0	------|>    Error message
               Last_SQL_Error: 		------+

Verification information

Main 5.6

mysql > create database t1;
mysql > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| event              |
| hello_world        |
| logbin             |
| mysql              |
| performance_schema |
| world              |
| zabbix             |
+--------------------+

From 5.7

mysql > show database;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| event              |
| hello_world        |
| logbin             |
| mysql              |
| performance_schema |
| sys                |
| world              |
| zabbix             |
+--------------------+

One master multi slave structure

1. Establish a master server to create users and authorize replication rights.
2. Back up the master database server and distribute it to the slave database server.
3. From the database server, turn off the log for synchronization, and turn on the log after synchronization. (in order not to generate redundant log information)
4. Modify the master configuration of the slave database server to establish a connection with the master server.
5. Open the slave and check (verify) whether the connection is successfully established.