MySQL master-slave configuration to achieve a primary one from read and write separation

Definition of master-slave introduction

• MySQL master is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, another B will follow the writing data, real-time data synchronization
• MySQL master-slave is based on Binlog, the Lord needs to open Binlog to carry out master and slave

• The master-slave process is about 3 steps.

  • The change operation is recorded in Binlog.
  • Synchronize the main Binlog event (SQL statement) on this machine and record it in Relaylog
  • Execute sequentially from the SQL statements inside the Relaylog
• The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog
• There are two threads from the top, where I/O threads are used to synchronize the SQL statements inside the main binlog application scenario:

  High availability of data. (main: As read and write data, from: real-time synchronization, when the main outage, from the timely provision of services)

Load balancing of the data. (the customer reads the data from this machine (but does not

Master-Slave function

• Real-time disaster preparedness for failover
• Read and write separation, provide query service
• Backup to avoid impacting business master and slave forms

• One Master one from
• Primary master replication
• One master multiple reads from the---extended system because the read is read from the library
• Multi-master one supported from---version 5.7
• The principle of duplicate master-slave replication

Master-slave Replication steps

• The main library logs all writes in the Binlog log, generates a log dump thread, and passes the Binlog log to the I/O thread from the library

• Generate two threads from the library, one I/O thread, and the other is the SQL thread

  • I/O thread requests the Binlog log of the main library and writes the files in the Binlog log to relay log (trunk log)
  • The SQL thread reads the contents of the Relay Loy and resolves it into a specific operation to achieve consistent master-slave operation to achieve the final data consistent master-slave replication configuration steps:
    1. Ensure consistent data from the database and the primary database
    2. Create a sync account in the main database to be licensed to use from the database
    3. Cooperating with the primary database (modifying the configuration file)
       4. Configure the requirements from the database (modify configuration file)

       Set up two MYSQL servers, one as the primary server, one as the slave server, the master server for the write operation, from the server to read operations

       Environment description

Database Roles	IP	Applications and Systems	There is no data
Primary database	192.168.24.18	Centos7 mysql-5.7	Yes
From the database	192.168.24.129	Centos7 mysql-5.7	No

Install MySQL on both servers

Detailed configuration View "MySQL Advanced Simple Analysis"

MySQL master-slave configuration ensures that data from the database is the same as the primary database

Create the libraries and tables that need to be synchronized in the primary database first

[[email protected] ~]# mysql-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 3Server version:5.7.22 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> create database Doudou; Query OK, 1 row affected (0.03 sec) mysql> create database job; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database Mary; Query OK, 1 row Affected (0.00 sec) mysql> use doudou;database changedmysql> CREATE TABLE Tom (ID int. not Null,name V Archar (+) not null,age tinyint); Query OK, 0 rows affected (0.22 sec) mysql> insert Tom (Id,name,age) VALUES (1, "Lisi", 6), (2, "Waangwu", 5), (3, "Zhaotie", 9 ); Query OK, 3 rows affected (0.07 sec) Records:3 duplicates:0 Warnings:0mysQl> SELECT * from tom;+----+---------+------+| ID | name |  Age |+----+---------+------+| 1 |    Lisi |  6 | | 2 |    Waangwu |  5 | | 3 |    Zhaotie |                    9 |+----+---------+------+3 rows in Set (0.01 sec) mysql>

Backing up the main library
When backing up the main library, you need to open a different terminal, read the lock on the database, and avoid inconsistencies in the data synchronization caused by other people writing during the backup.

[[email protected] ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.22 MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> flush tables with read lock;Query OK, 0 rows affected (0.17 sec)mysql> //此锁表的终端必须在备份完成以后才能退出（退出锁表失效）                            

Back up the main library and transfer the backup files to the slave library

[[email protected] ~]# mysqldump -uroot -plinfan123 --all-databases >/opt/all-20180907.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[[email protected] ~]# ls /opt/all-20180907.sql  data[[email protected] ~]# scp /opt/all-20180907.sql [email protected]:/opt/The authenticity of host ‘192.168.24.130 (192.168.24.130)‘ can‘t be established.ECDSA key fingerprint is SHA256:w+sgREnQRuhBiqS0qL9wlAImCSmvSQ6KnNqW6N3znJ0.ECDSA key fingerprint is MD5:f0:fd:ea:c7:97:83:f0:b0:03:84:d2:a6:0a:23:12:e0.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added ‘192.168.24.130‘ (ECDSA) to the list of known hosts.[email protected]‘s password:all-20180907.sql                                                                        100%

Unlock the lock table State of the main library and exit the interactive interface directly

mysql> quitBye

Restore a backup of the main library from the library and see if it is consistent with the data in the main library

[[email protected] ~]# mysql-uroot-plinfan123 </opt/all-20180907.sqlmysql: [Warning] Using a password on the CO Mmand line interface can is insecure.  [[email protected] ~]# mysql-uroot-plinfan123mysql: [Warning] Using a password on the command line interface can is Insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 4Server version:5.7.22 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | Doudou | | Job | | Mary | | MySQL | | Performance_schema | | SYS |+--------------------+7 rows in Set (0.01 sec) mysql> use doudoureading table information for completion of table and column namesyou can turn off this feature To get a quicker startup With-adatabase changedmysql> select * from tom;+----+---------+------+| ID | name |  Age |+----+---------+------+| 1 |    Lisi |  6 | | 2 |    Waangwu |  5 | | 3 |    Zhaotie | 9 |+----+---------+------+3 rows in Set (0.00 sec)

Create a sync account in the primary database to authorize the use from data

[[email protected] ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.22 MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> create user ‘repl‘@‘192.168.24.130‘ identified by ‘123456‘;Query OK, 0 rows affected (0.04 sec)mysql> grant replication slave on *.* to ‘repl‘@‘192.168.24.130‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

Configuring the Primary Database

Editing a configuration file

[[email protected] ~]# vim /etc/my.cnf[[email protected] ~]# cat /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolve//添加以下内容log-bin=mysql-bin //启用binlog日志server-id=1 //主数据库服务器唯一标识符 主的必须必从大log-error=/opt/data/mysql.log

Restart MySQL Service

[[email protected] ~]# service mysqld restartShutting down MySQL... SUCCESS!Starting MySQL.Logging to ‘/opt/data/mysql.log‘........... SUCCESS! [[email protected] ~]# ss -natlState      Recv-Q Send-Q                Local Address:Port                               Peer Address:PortLISTEN     0      128                               *:22                                            *:*LISTEN     0      100                       127.0.0.1:25                                            *:*LISTEN     0      128                              :::22                                           :::*LISTEN     0      100                             ::1:25                                           :::*LISTEN     0      80                               :::3306            

View the status of the main library (the data to remember, will be used later)

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

Configuration from the database

Editing a configuration file

[[email protected] ~]# cat /etc/my.cnf[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306pid-file = /opt/data/mysql.piduser = mysqlskip-name-resolve//添加以下内容：server-id=2 //设置从库的唯一标识符 从的必须比主小relay-log=mysql-relay-bin //启用中继日志relay logerror-log=/opt/data/mysql.log

Restarting the MySQL service from the library

[[email protected] ~]# service mysqld restartShutting down MySQL... SUCCESS!Starting MySQL.Logging to ‘/opt/data/mysql.log‘........... SUCCESS! [[email protected] ~]# ss -natlState      Recv-Q Send-Q                Local Address:Port                               Peer Address:PortLISTEN     0      128                               *:22                                            *:*LISTEN     0      100                       127.0.0.1:25                                            *:*LISTEN     0      128                              :::22                                           :::*LISTEN     0      100                             ::1:25                                           :::*LISTEN     0      80                               :::3306    

Configure and start master-slave replication

mysql> change master to    -> master_host=‘192.168.24.128‘,    -> master_user=‘repl‘,    -> master_password=‘123456‘,    -> master_log_file=‘mysql-bin.000001‘,    

View from server Status

Mysql> Show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.24.128 Master_user:repl master_port:3306 connect_retry:60 master_log_file:mysql-bin.000001 read_master_log_pos:154 Relay_log_file:linfan-relay  -bin.000002 relay_log_pos:320 relay_master_log_file:mysql-bin.000001 slave_io_running: Yes//must be yes here Slave_sql_running:yes//must be yes here replicate_do_db:replicate_i Gnore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore _table:last_errno:0 last_error:skip_counter:0 Exec_master   _log_pos:154 relay_log_space:528 Until_condition:none            until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file:        Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key: Seconds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0 Last_io_err or:last_sql_errno:0 Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_ Id:1 master_uuid:3396fe35-b1e1-11e8-81bb-000c292340f6 master_info_file:/opt/data/master.in Fo sql_delay:0 sql_remaining_delay:null slave_sql_running_state:slave have read all rel Ay log;     Waiting for more updates 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:0 Replicate_Rewrite_DB:Channel_Name:Master_TL  S_version:1 row in Set (0.00 sec)

Test to verify that the Tom table in the Doudou Library of the primary server inserts data:

mysql> use doudou;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from tom;+----+---------+------+| id | name    | age  |+----+---------+------+|  1 | lisi    |    6 ||  2 | waangwu |    5 ||  3 | zhaotie |    9 |+----+---------+------+3 rows in set (0.00 sec)mysql> insert tom(id,name,age) value (4,"mary",18);Query OK, 1 row affected (0.10 sec)mysql> select * from tom;+----+---------+------+| id | name    | age  |+----+---------+------+|  1 | lisi    |    6 ||  2 | waangwu |    5 ||  3 | zhaotie |    9 ||  4 | mary    |   18 |+----+---------+------+4 rows in set (0.00 sec)

To see whether data is synchronized from the database

mysql> use doudou;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from tom;+----+---------+------+| id | name    | age  |+----+---------+------+|  1 | lisi    |    6 ||  2 | waangwu |    5 ||  3 | zhaotie |    9 ||  4 | mary    |   18 |+----+---------+------+4 rows in set (0.00 sec)

MySQL master-slave configuration to achieve a primary one from read and write separation