MySQL Master-slave architecture

MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data in real-time synchronization. It is based on the Binlog, the main device must be opened binlog to master and slave.

The master-slave process is roughly 3 steps:

1) The master device logs the change operation to the Binlog;

2) from synchronizing the Binlog event (SQL statement) of the main device to the machine and recording it in Relaylog;

3) Execute sequentially from the SQL statements that are based on the Relaylog.

There is a log dump thread on the main device that is used to pass binlog to and from the I/O thread. There are two threads from the device, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to place the SQL statements inside the Relaylog.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/05/89/wKiom1mmxVLDrgOOAAHlMAz2Xnk817.png "title=" 1.png "alt=" Wkiom1mmxvldrgooaahlmaz2xnk817.png "/>

Preparatory work

1. Prepare two MySQL-equipped servers and start the MySQL service.

2, assign the role, determine the equipment master and slave.

Configuring the Master Device

1. Edit the configuration file

[Email protected] ~]# Vim/etc/my.cnf[mysqld]server-id=88log_bin=bin01

2. Restart MySQL

[Email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL .... success! Starting MySQL. success!

3, check Log_bin

[Email protected] ~]# Ls/data/mysql/bin01.*/data/mysql/bin01.000001/data/mysql/bin01.index

4. Create a Database

[Email protected] mysql]# cd/data/mysql[[email protected] mysql]#/usr/local/mysql/bin/mysql-uroot-e ' CREATE database Db01 '

5. Increase test data

[Email protected] mysql]#/usr/local/mysql/bin/mysqldump-uroot zrlog >/tmp/zrlog.sql[[email protected] mysql]#/ Usr/local/mysql/bin/mysql-uroot DB01 </tmp/zrlog.sql

6. Back Up all databases

[[email protected] mysql]#/usr/local/mysql/bin/mysqldump-uroot db01 >/tmp/db01.sql[[email protected] mysql]#/usr/ Local/mysql/bin/mysqldump-uroot Zrlog >/tmp/zrlog.sql

7. Create user

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootwelcome to the  mysql monitor.  commands end with ; or \g.your mysql  Connection id is 414server version: 5.6.35-log mysql community server   (GPL) copyright  (c)  2000, 2016, 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> grant replication slave on *.* to  ' repl ' @ ' 122.112.197.192 '  identified by  ' 123456 '; Query ok, 0 rows affected  (0.01 SEC) 

8. Lock table and view status

mysql> flush tables with read lock;  # #锁表 to prevent write query ok, 0  rows affected  (0.01 sec) mysql> show master status;   ## The two parameters below need to be configured on the device +--------------+----------+--------------+------------------+-------------------+| file          | Position | Binlog_Do_DB |  binlog_ignore_db | executed_gtid_set |+--------------+----------+--------------+--------------- ---+-------------------+| bin01.000001 |    10472 |               |                   |                    |+--------------+----------+--------------+- -----------------+-------------------+1 row in set  (0.00 SEC) 

Configuring Slave devices

1. Edit the configuration file

[Email protected] mysql]# vi/etc/my.cnf[mysqld]server-id=192

2. Restart MySQL

[Email protected] mysql]#/etc/init.d/mysqld restartshutting down MySQL. success! Starting MySQL. success!

3. Copy the primary device database backup file

[[Email protected] mysql]# scp 122.112.253.88:/tmp/*.sql /tmp/the authenticity  of host  ' 122.112.253.88  (122.112.253.88) '  can ' t be established. Ecdsa key fingerprint is 2e:6e:90:32:87:05:9e:63:63:d6:2d:44:a5:5f:be:51.are you  sure you want to continue connecting  (yes/no)? yeswarning:  permanently added  ' 122.112.253.88 '   (ECDSA)  to the list of known  Hosts. [email protected] ' s password: db01.sql                                      100% 9877     9.7kb/s    00:00          zrlog.sql                                     100% 9878     9.7KB/s    00:00

4. Create the corresponding database

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootwelcome to the  mysql monitor.  commands end with ; or \g.your mysql  connection id is 1server version: 5.6.35 mysql community server  ( GPL) copyright  (c)  2000, 2016, 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 database db01; query ok, 1 row affected  (0.00 sec) mysql> create database  Zrlog; Query ok, 1 row affected  (0.00&NBSP;SEC) [[Email protected] mysql]# /usr/local/mysql/bin/mysql  -uroot db01 </tmp/db01.sql[[email protected] mysql]# /usr/local/mysql/bin/ Mysql -uroot zrlog </tmp/zrlog.sql

5. Configuring Master and Standby synchronization

[[email protected] mysql]# /usr/local/mysql/bin/mysql -urootwelcome to the  mysql monitor.  commands end with ; or \g.your mysql  connection id is 4server version: 5.6.35 mysql community server  ( GPL) copyright  (c)  2000, 2016, 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> stop slave; query ok, 0 rows affected, 1 warning  (0.00 sec) Mysql> change  master to master_host= ' 122.112.253.88 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' bin01.000001 ', master_log_pos=10472; query ok, 0 rows affected, 2 warnings  (0.03 sec) Mysql> start  slave; query ok, 0 rows affected  (0.01 SEC)

6, view master and slave status

mysql> show slave status\g;  # #如果出现错误, may be the cloud host policy did not miss 3306 Port ***************************  1. row ***************************                slave_io_state: waiting for master to send event                    master_host: 122.112.253.88                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: bin01.000001           Read_Master_Log_Pos: 10708                Relay_Log_File: ecs-89c1-relay-bin.000003                 Relay_Log_Pos: 515         Relay_Master_Log_File: bin01.000001              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_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:  10708              relay_log_space:  691              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_Error:                 last_sql_errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:               Master_Server_Id: 88                   Master_UUID:  79b66469-8cc8-11e7-ae36-fa163eb51d35              Master_Info_File: /data/mysql/master.info                     SQL_Delay: 0           sql_remaining_delay: null      slave_ Sql_running_state: slave has 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) error: no query specified

7. Unlock the main device

[Email protected] mysql]#/usr/local/mysql/bin/mysql-urootwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 875Server version:5.6.35-log mysql Community Server (GPL) Copyright (c), Orac Le 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> unlock tables; Query OK, 0 rows Affected (0.00 sec)

Test Master-Slave synchronization

1, delete the DB01 database table on the main device;

Mysql> Show tables;+----------------+| TABLES_IN_DB01 |+----------------+| Comment | | link | | Log | | Lognav | | Plugin | | Tag | | Type | | User |+----------------+8 rows in Set (0.00 sec) mysql> drop table tag; Query OK, 0 rows affected (0.01 sec)

2, in the view from the device corresponding table also no longer exists.

Mysql> Show tables;+----------------+| TABLES_IN_DB01 |+----------------+| Comment | | link | | Log | | Lognav | | Plugin | | Type | | User |+----------------+7 rows in Set (0.00 sec)

Extended Learning

▎ Configuration Parameters

1. On the primary server:

binlog-do-db=//Synchronize only the specified libraries (other libraries are not synchronized)

binlog-ignore-db=//Ignore specified libraries (other libraries are synchronized)

2. From the server:

replicate_do_db=//(not used)

replicate_ignore_db=//(not used)

replicate_do_table=//(not used)

replicate_ignore_table=//(not used)

replicate_wild_do_table=//such as aming.%, (wildcard% supported)

replicate_wild_ignore_table=

This article is from the "Gorilla City" blog, please be sure to keep this source http://juispan.blog.51cto.com/943137/1961233

MySQL Master-slave architecture