导航：首页 > 数据库 >

MySQL-5.5主从复制原理是什么及如何配置

发表于：2025-01-23 作者：千家信息网编辑

千家信息网最后更新 2025年01月23日，下文主要给大家带来MySQL-5.5主从复制原理是什么及如何配置，希望MySQL-5.5主从复制原理是什么及如何配置能够带给大家实际用处，这也是我编辑这篇文章的主要目的。好了，废话不多说，大家直接看下

千家信息网最后更新 2025年01月23日MySQL-5.5主从复制原理是什么及如何配置

下文主要给大家带来MySQL-5.5主从复制原理是什么及如何配置，希望MySQL-5.5主从复制原理是什么及如何配置能够带给大家实际用处，这也是我编辑这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。

环境：

[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

主从复制原理：

当用户对数据有增删改操作时，主库本地存一份，另外会把用户增删改的操作记录在 binlog 里面(binlog是实现主从复制的基础)，binlog的索引文件是mysql-bin.index；从库的IO线程根据本地master.info文件里面记录的ip、port、user、password、binlog name、pos连接主库IO线程，主库判断信息，正确就返回数据，返回的数据里包括下次复制起始点的binlog名称和pos值；从库收到数据后写入relay-log，同时把下次复制起始点的binlog名称和pos值刷新进master.info文件，之后从库的SQL线程读取relay-log里面的SQL语句，执行语句将数据写入本地磁盘，主从复制完成。

配置要点：

主从复制，主库开启 bin-log 从库开启 relay-log ,主从 server-id 不能相同。

Master 配置：

[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              <<---  id 为 1log-bin = /usr/local/mysql/data/mysql-bin   <<--- 开启 bin logbinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1Mlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062,1007,1008,1050replicate-ignore-db=mysqlinnodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 16Minnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/usr/local/mysql/mysqld.pid

Slave 配置：

[root@SQL-S1 ~]# vim /etc/my.cnf [client]port=3306socket= /usr/local/mysql/mysql.sockdefault-character-set = utf8[mysql]no-auto-rehashprompt=Slave>\_[mysqld]user    = mysqlport    = 3306socket  = /usr/local/mysql/mysql.sockbasedir = /usr/local/mysqldatadir = /usr/local/mysql/datacharacter-set-server = utf8skip-character-set-client-handshakeinit-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 = 2                  <<--- id 为 2relay-log =/usr/local/mysql/data/relay-bin    <<--- 开启 relay logrelay-log-info-file = /usr/local/mysql/data/relay-log.infokey_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1Mlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062,1007,1008,1050replicate-ignore-db=mysqlinnodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 16Minnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Master 端准备数据和创建复制用户

[root@SQL-M ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.55-log 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.Master>  ster> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             |+--------------------+4 rows in set (0.00 sec)Master> use school;Database changedMaster>Master> show tables;+------------------+| Tables_in_school |+------------------+| student          || test01           || test02           |+------------------+3 rows in set (0.00 sec)Master> select * from student;+----+--------+-----+-----+| id | name   | sex | age |+----+--------+-----+-----+|  1 | 小东   | 男  |   0 ||  3 | 小北   | 女  |  12 |+----+--------+-----+-----+2 rows in set (0.00 sec)Master> grant replication slave on *.* to rep@'192.168.0.%' identified by '123';  # 创建专门用于主从复制的用户Query OK, 0 rows affected (0.00 sec)   Master> select user,host from mysql.user;                                       +------+-------------+| user | host        |+------+-------------+| root | 127.0.0.1   || rep  | 192.168.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)

slave 端没有数据，处于初始状态

[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 端用 mysqldump 导出数据

由于 mysqldump 是逻辑备份程序，所以要确保 MySQL 服务是启动状态。

[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 152K-rw-r--r-- 1 root root 151K Apr 23 12:48 sql_full_back.sql

mysqldump 参数：

-A  备份所有库表-B  在导出的 sql 文件里加入建库语句，从库导入文件时就省事很多-F  刷新 bin log，这个在增量恢复时有用--master-data=1 这个参数的作用是在导出的 sql 文件里会加入一条语句"CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxx;" ，这样在从库导入数据后执行 CHANGE MASTER 时就不用加上bin-log和pos值了；=2 则是注释。--events  忽略警告  Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.数据量大可以备份时gzip压缩：mysqldump -uroot -p -A -B -F --master-data=1 --events|gzip >/tmp/sql_full_back.sql.gz

把备份文件 scp 到从库

[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

从库导入备份文件并检查

[root@SQL-S1 ~]# ll /tmp/total 548-rw-r--r-- 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 | 小东   | 男  |   0 ||  3 | 小北   | 女  |  12 |+----+--------+-----+-----+2 rows in set (0.00 sec)

从库导入备份文件检查成功后 CHANGE MASTER

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.01 sec)Slave> start slave;       # 启动 slaveQuery OK, 0 rows affected (0.00 sec)Slave> show slave status\G  # 查看状态*************************** 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         <<--- 正常            Slave_SQL_Running: Yes         <<--- 正常              Replicate_Do_DB:           Replicate_Ignore_DB: mysql           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: 297              Relay_Log_Space: 403              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: 0           <<--- 没有延迟Master_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: 11 row in set (0.00 sec)

slave 的状态判断：

Slave_IO_Running: Yes    # IO线程负责与主库通信传输数据Slave_SQL_Running: Yes   # SQL线程，读取中继日志（rely-log），再把数据写入本地存储Seconds_Behind_Master: 0  # 延迟时间，从主获取数据的延迟时间，

这三个参数是主从复制健康检查的监控的重点。

主库查看线程状态

Master> show processlist; +----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| Id | User | Host               | db   | Command     | Time | State                                                                 | Info             |+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| 27 | root | localhost          | NULL | Query       |    0 | NULL                                                                  | show processlist || 29 | rep  | 192.168.0.90:64017 | NULL | Binlog Dump |  497 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+2 rows in set (0.00 sec)

测试主从复制

Master 添加数据

Master> use school;Database changedMaster> select * from student;+----+--------+-----+-----+| id | name   | sex | age |+----+--------+-----+-----+|  1 | 小东   | 男  |   0 ||  3 | 小北   | 女  |  12 |+----+--------+-----+-----+2 rows in set (0.00 sec)Master> insert into student values(4,'楠楠','男',15);   # 插入新数据Query OK, 1 row affected (0.00 sec)Master> select * from student;                       +----+--------+-----+-----+| id | name   | sex | age |+----+--------+-----+-----+|  1 | 小东   | 男  |   0 ||  3 | 小北   | 女  |  12 ||  4 | 楠楠   | 男  |  15 |+----+--------+-----+-----+3 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 | 小东   | 男  |   0 ||  3 | 小北   | 女  |  12 ||  4 | 楠楠   | 男  |  15 |    <<---- 可以看到新的数据已经复制到位+----+--------+-----+-----+3 rows in set (0.00 sec)

以上主从复制配置完成

===================== 开启半同步模式 =========================

主从复制实际是异步的过程：

Master IO_thread --> Slave IO_thread -->Slave SQL_thread -->Slave localdisk

Master IO_thread 把数据交给 Slave IO_thread 之后就不管了，后面的数据存储有没有成功Master是不知道的，这样对数据来说显然是不够安全的，无法保证数据完整正确地存储在Slave端。

半同步复制

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

半同步是以已经实现主从复制为前提，并且MySQL版本为5.5及以上。

实现半同步的插件：

[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

很清楚，一个Master用的，一个Slave用的。

Master 端操作

Master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  # 安装插件Query OK, 0 rows affected (0.13 sec)    Master> SET GLOBAL rpl_semi_sync_master_enabled = 1;    # 启用插件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 |    <<--- 默认超时，单位毫秒；数据传输超时会自动转为异步复制，传输正常后会自动恢复为半同步。| rpl_semi_sync_master_trace_level   | 32    || rpl_semi_sync_master_wait_no_slave | ON    |+------------------------------------+-------+5 rows in set (0.00 sec)Master> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';+----------------------+---------------+| PLUGIN_NAME          | PLUGIN_STATUS |+----------------------+---------------+| rpl_semi_sync_master | ACTIVE        |+----------------------+---------------+1 row in set (0.00 sec)Master> show status like 'rpl%'; +--------------------------------------------+-------------+| Variable_name                              | Value       |+--------------------------------------------+-------------+| Rpl_semi_sync_master_clients               | 0           |   <<--- 还没有从库连接| Rpl_semi_sync_master_net_avg_wait_time     | 0           || Rpl_semi_sync_master_net_wait_time         | 0           || Rpl_semi_sync_master_net_waits             | 0           || Rpl_semi_sync_master_no_times              | 0           || Rpl_semi_sync_master_no_tx                 | 0           || Rpl_semi_sync_master_status                | ON          || Rpl_semi_sync_master_timefunc_failures     | 0           || Rpl_semi_sync_master_tx_avg_wait_time      | 0           || Rpl_semi_sync_master_tx_wait_time          | 0           || Rpl_semi_sync_master_tx_waits              | 0           || Rpl_semi_sync_master_wait_pos_backtraverse | 0           || Rpl_semi_sync_master_wait_sessions         | 0           || Rpl_semi_sync_master_yes_tx                | 0           || Rpl_status                                 | AUTH_MASTER |+--------------------------------------------+-------------+15 rows in set (0.00 sec)

Slave 端操作

Slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  # 安装slave插件Query OK, 0 rows affected (0.11 sec)Slave> SET GLOBAL rpl_semi_sync_slave_enabled = 1;      # 启用插件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;               # 重启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)

Master端检查从库连接情况

Master> show status like 'rpl%';+--------------------------------------------+-------------+| Variable_name                              | Value       |+--------------------------------------------+-------------+| Rpl_semi_sync_master_clients               | 1           |   <<--- 可以看到有一个从库已经成功连接| Rpl_semi_sync_master_net_avg_wait_time     | 0           || Rpl_semi_sync_master_net_wait_time         | 0           || Rpl_semi_sync_master_net_waits             | 0           || Rpl_semi_sync_master_no_times              | 0           || Rpl_semi_sync_master_no_tx                 | 0           || Rpl_semi_sync_master_status                | ON          || Rpl_semi_sync_master_timefunc_failures     | 0           || Rpl_semi_sync_master_tx_avg_wait_time      | 0           || Rpl_semi_sync_master_tx_wait_time          | 0           || Rpl_semi_sync_master_tx_waits              | 0           || Rpl_semi_sync_master_wait_pos_backtraverse | 0           || Rpl_semi_sync_master_wait_sessions         | 0           || Rpl_semi_sync_master_yes_tx                | 0           || Rpl_status                                 | AUTH_MASTER |+--------------------------------------------+-------------+15 rows in set (0.00 sec)

半同步测试

正常情况下master插入数据的速度很快

Master> insert into student values(6,'小欣','女',13);

Query OK, 1 row affected (0.00 sec) <<--- 速度很快

Master> insert into student values(7,'小倩','女',13);

Query OK, 1 row affected (0.00 sec) <<--- 一样

接下来模仿从库故障，停掉slave的 IO 线程

Slave> stop slave io_thread;

Query OK, 0 rows affected (0.00 sec)

Master 再插入数据

Master> insert into student values(8,'姗姗','女',13);

Query OK, 1 row affected (10.00 sec) <<--- 超时了，自动转为异步