Shulou(Shulou.com)06/01 Report--

The following mainly brings you what the principle of MySQL-5.5 master-slave replication is and how to configure it. I hope that what the principle of MySQL-5.5 master-slave replication is and how to configure it can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

Environment:

[root@SQL-M ~] # cat / etc/redhat-release

CentOS release 6.8 (Final)

[root@SQL-M ~] # uname-r

2.6.32-642.el6.x86_64

Master IP 192.168.0.88/24 eth0

Slave IP 192.168.0.90/24 eth0

Principle of master-slave replication:

When a user adds, deletes or modifies data, a copy is saved locally in the main database. In addition, the user's operations are recorded in binlog (binlog is the basis for master-slave replication). The index file of binlog is mysql-bin.index. The slave IO thread connects the main library IO thread according to the ip, port, user, password, binlog name and pos recorded in the local master.info file. The master library judges the information and returns the data correctly. The returned data includes the binlog name and posvalue of the starting point of the next replication. Write the data to relay-log after receiving the data from the library, and refresh the binlog name and posvalue of the starting point of the next replication into the master.info file, then read the SQL statement in relay-log from the SQL thread of the library, execute the statement to write the data to the local disk, and the master-slave replication is completed.

Key points of configuration:

Master-slave replication. Master database enables bin-log. Slave database enables relay-log. Master-slave server-id cannot be the same.

Master configuration:

[root@SQL-M ~] # vim / etc/my.cnf [client] port=3306socket= / usr/local/mysql/mysql.sockdefault-character-set = UTF8 [MySQL] no-auto-rehashprompt=Master >\ _ [mysqld] user = mysqlport = 3306socket= / usr/local/mysql/mysql.sockbasedir = / usr/local/mysqldatadir = / usr/local/mysql/datacharacter-set-server = utf8skip-character-set-client-handshake init-connect = 'SET NAMES utf8' open_files_limit=1024back_log = 600max_connections = 800max_ Connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet = 8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2kthread_stack = 192Ktmp_table_size = 2Mmax_heap_table_size = 2Mserver-id = 1 grant replication slave on *. * to rep@'192.168.0.%' identified by '123' # create a user Query OK dedicated to master-slave replication, 0 rows affected (0.00 sec) Master > select user,host from mysql.user +-+ | user | host | +-+-+ | root | 127.0.0.1 | | rep | 192.168.0.0.% | | root | localhost | +-+-+ 3 rows in set ( 0.00 sec) Master > show grants for replicaton@'192.168.0.%' ERROR 1141 (42000): There is no such grant defined for user 'replicaton' on host' 192.168.0.%'Master > show grants for rep@'192.168.0.%' +- -+ | Grants for rep@192.168.0.% | + -- + | GRANT REPLICATION SLAVE ON *. * TO 'rep'@'192.168.0.%' IDENTIFIED BY PASSWORD' * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | +-- -+ 1 row in set (0.00 sec)

The slave side has no data and is in the initial state.

[root@SQL-S1] # mysqlWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.5.55 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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.Slave01 > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec)

Master side uses mysqldump to export data

Because mysqldump is a logical backup program, make sure that the MySQL service is started.

[root@SQL-M ~] # mysqldump-uroot-p-A-B-F-- master-data=1-- events > / tmp/sql_full_back.sql Enter password: [root@SQL-M ~] # ll-h / tmp/total 152Kmuri RWMULY RWMULY-1 root root 151K Apr 23 12:48 sql_full_back.sql

Mysqldump parameters:

-A back up all the database tables-B adds a database building statement to the exported sql file, which saves a lot of trouble when importing files from the library.-F refresh bin log, which is useful for incremental recovery-the function of the parameter master-data=1 is to add a statement "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxx" to the exported sql file. "so that you do not have to add bin-log and poss values when performing CHANGE MASTER after importing data from the library = 2 is a comment. -- events ignores warning Warning: Skipping the data of table mysql.event. Specify the-- events option explicitly. Gzip compression when large amount of data can be backed up: mysqldump-uroot-p-A-B-F-- master-data=1-- events | gzip > / tmp/sql_full_back.sql.gz

Scp backup files to slave library

[root@SQL-M] # scp / tmp/sql_full_back.sql 192.168.0.90:/tmp/The authenticity of host '192.168.0.90 (192.168.0.90)' can't be established.RSA key fingerprint is fb:9f:50:cd:ac:59:8b:a3:83:83:95:7c:62:d1:64:d2.Are you sure you want to continue connecting (yes/no)? YesWarning: Permanently added '192.168.0.90' (RSA) to the list of known hosts.root@192.168.0.90's password: sql_full_back.sql 100% 546KB 546.1KB/s 00:00

Import backup files from the library and check

[root@SQL-S1] # ll / tmp/total 548 mysql-1 root root 559192 Apr 23 13:10 sql_full_ back.sql [root @ SQL-S1 ~] # [root@SQL-S1] # mysql-uroot-p show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | +-+ 4 rows in set (0.00 sec) Slave > Slave > use school;Database changedSlave > show tables +-+ | Tables_in_school | +-+ | student | | test01 | | test02 | +-+ 3 rows in set (0.00 sec) Slave > select * from student +-+ | id | name | sex | age | +-+ | 1 | Xiaodong | male | 0 | 3 | Xiaobei | female | 12 | +-+ 2 rows in set (0.00 sec)

CHANGE MASTER after the import of backup files from the library is checked successfully

Slave > CHANGE MASTER TO MASTER_HOST='192.168.0.88',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='123';Query OK, 0 rows affected (0.01sec) Slave > start slave # start slaveQuery OK 0 rows affected (0.00 sec) Slave > show slave status\ G # View status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.88 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000016 Read_Master_Log_Pos: 297 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000016 Slave _ IO_Running: Yes use school Database changedSlave > show tables;+-+ | Tables_in_school | +-+ | student | | test01 | | test02 | +-+ 3 rows in set (0.00 sec) Slave > select * from student +-+ | id | name | sex | age | +-+ | 1 | Xiaodong | male | 0 | 3 | Xiaobei | female | 12 | | 4 | Nannan | male | 15 | Slave SQL_thread-> Slave localdisk

After Master IO_thread gives the data to Slave IO_thread, Master does not know whether the data storage is successful or not, so it is obviously not secure enough for the data to be stored completely and correctly on the Slave side.

Semi-synchronous replication

Between asynchronous replication and full synchronous replication, the main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one received from the library and written to the relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay, which is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

Semi-synchronization is based on the premise that master-slave replication has been implemented, and the MySQL version is 5.5 or above.

Plug-ins that implement semi-synchronization:

[root@SQL-M ~] # ll / usr/local/mysql/lib/plugin/-rwxr-xr-x 1 mysql mysql 170878 Mar 18 13:14 semisync_master.so-rwxr-xr-x 1 mysql mysql 88959 Mar 18 13:14 semisync_slave.so

Clearly, one for Master and one for Slave.

Master end operation

Master > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; # install plug-in Query OK, 0 rows affected (0.13 sec) Master > SET GLOBAL rpl_semi_sync_master_enabled = 1; # enable plug-in Query OK, 0 rows affected (0.00 sec) Master > show status like' Rpl_semi_sync_master_status' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_master_status | ON | +- -+-+ 1 row in set (0.00 sec) Master > show variables like 'rpl%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_recovery_rank | | 0 | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | show status like 'rpl%' | +-+-+ | Variable_name | Value | +-- -+-+ | Rpl_semi_sync_master_clients | 0 | SET GLOBAL rpl_semi_sync_slave_enabled = 1 # enable plug-in Query OK, 0 rows affected (0.00 sec) Slave > show status like 'Rpl_semi_sync_slave_status' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | OFF | +- -+-+ 1 row in set (0.00 sec) Slave > stop slave # restart slaveQuery OK, 0 rows affected (0.00 sec) Slave > start slave;Query OK, 0 rows affected (0.00 sec) Slave > show status like 'Rpl_semi_sync_slave_status' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | +- -+-+ 1 row in set (0.00 sec) Slave > show status like 'rpl%' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | | Rpl_ Status | AUTH_MASTER | +-- +-+ 2 rows in set (0.00 sec) Slave > SELECT PLUGIN_NAME PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE'% semi%' +-+-+ | PLUGIN_NAME | PLUGIN_STATUS | +-- + | rpl_semi_sync_slave | ACTIVE | +- -+-+ 1 row in set (0.00 sec)

The Master side checks the connection of the slave library.

Master > show status like 'rpl%' +-+-+ | Variable_name | Value | +-- -+-+ | Rpl_semi_sync_master_clients | 1 |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.