MySQL 主从复制原理与实践详解

吾爱主题阅读：192 2024-04-05 16:21:22 评论：0

本文实例讲述了mysql 主从复制原理与实践。分享给大家供大家参考，具体如下：

简介

mysql 的主从复制又叫 replication、ab 复制。至少需要两个 mysql 服务（可以是同一台机器，也可以是不同机器之间进行）。
比如a服务器做主服务器，b服务器做从服务器，在a服务器上进行数据的更新，通过 binlog 日志记录同步到b服务器上，并重新执行同步过来的 binlog 数据，从而达到两台服务器数据一致。

mysql 数据库的主从复制方案，与使用 scp/rsync 等命令进行的文件级别复制类似，都是数据的远程传输。

只不过 mysql 的主从复制是其自带的功能，无需借助第三方工具，而且mysql的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的 binlog 日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的 sql 语句，重新应用到 mysql 数据库中。

作用

1. 可以实时灾备，用于故障切换；

2. 读写分离，提供查询服务，实现负载均衡；

3. 数据热备，避免影响业务。

原理

1、主服务器mysql服务将所有的写操作记录在 binlog 日志中，并生成 log dump 线程，将 binlog 日志传给从服务器mysql服务的 i/o 线程。
2、从服务器mysql服务生成两个线程，一个是 i/o 线程，另一个是 sql 线程。

3、从库 i/o 线程去请求主库的 binlog 日志，并将 binlog 日志中的文件写入 relaylog（中继日志）中。

4、从库的 sql 线程会读取 relaylog 中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终两个数据库数据一致的目的。

1 2 3 4 5 6 注意点： - 主从复制是异步的逻辑的 sql 语句级的复制； - 复制时，主库有一个 i/o 线程，从库有两个线程，及 i/o 和 sql 线程； - 实现主从复制的必要条件是主库要开启记录 binlog 的功能； - 作为复制的所有 mysql 节点的 server-id 都不能相同； - binlog 文件只记录对数据内容有更改的 sql 语句，不记录任何查询语句。

形式

一主一从

主主复制

一主多从

多主一从（5.7后开始支持）

联级复制

实践

需求

实现一主一从复制模式，同一台主机两个mysql实例

环境

1 2 3 4 5 6 mac：10.15.1 docker：2.0.0.3//采取docker安装mysql主要是管理维护方便、独立ip、启动秒级 mysql-master：5.7.29//主服务器 mysql-master ip：172.17.0.3 mysql-slave：5.7.29//从服务器 mysql-slave ip：172.17.0.4

步骤

第一步：准备好两台mysql服务器

mysql-master（主服务器）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 1. 创建目录结构： master/conf、master/data、master/logs 2. 启动运行实例容器： docker run --name mysql-master > -p 3310:3306 > -v ~/docker/master/conf:/etc/mysql/conf.d > -v ~/docker/master/data:/var/lib/mysql > -v ~/docker/master/logs:/var/log/mysql > -e mysql_root_password=123456 > -d mysql:5.7 3. 进入容器 docker exec -it mysql-master bash 4. 登录mysql mysql -uroot -p

mysql-slave（从服务器）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 1. 创建目录结构： slave/conf、slave/data、slave/logs 2. 启动运行实例容器： docker run --name mysql-slave > -p 3310:3306 > -v ~/docker/slave/conf:/etc/mysql/conf.d > -v ~/docker/slave/data:/var/lib/mysql > -v ~/docker/slave/logs:/var/log/mysql > -e mysql_root_password=123456 > -d mysql:5.7 3. 进入容器 docker exec -it mysql-slave bash 4. 登录mysql mysql -uroot -p

第二步：配置文件（my.cnf）修改

1 2 3 4 5 6 7 8 9 10 11 主服务器： [mysqld] port = 3306 server-id = 1 #要同步的数据库 binlog-do-db = school #要生成二进制日志文件主服务器一定要开启 log-bin = mysql-bin 重启mysql: docker restart mysql-master

1 2 3 4 5 6 7 8 9 10 11 从服务器： [mysqld] port = 3306 server-id = 2 #要同步的数据库 binlog-do-db = school #要生成二进制日志文件（从服务器可选） log-bin = mysql-bin 重启mysql: docker restart mysql-slave

第三步：创建主服务器复制用户及相关权限

1 2 3 4 create user 'slave' @ '%' identified by '123456' ;//创建用户 grant replication slave,replication client on *.* to 'slave' @ '%' ;//设置用户权限 flush privileges ;//刷新权限 show grants for 'slave' @ '%' ;//查看用户权限

第四步：数据备份同步

1 2 3 4 5 6 7 8 9 1. 登录master，执行锁表操作 mysql -uroot -p flush tables with read lock; 2. 将master中需要同步的db的数据dump出来 mysqldump -uroot -p school > school.dump 3. 将数据导入slave mysql -uroot -h172.17.0.4 -p school < school.dump 4. 解锁master unlock tables;

第五步：主服务器复制状态

1 2 3 4 5 6 7 8 9 10 1. 创建新数据表及增加数据 create table user ( id int (10) auto_increment, name varchar (30), primary key (id) )charset=utf8mb4; insert into user ( name ) values (222); 2. 主服务器 binlog 记录状态 mysql> show master status; + ------------------+----------+--------------+------------------+-------------------+ | file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set | + ------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 769 | school | | | + ------------------+----------+--------------+------------------+-------------------+

第六步：从服务器开始复制

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 1. 连接主服务器及设置复制的起始节点 mysql> change master to master_host= '172.17.0.3' , -> master_port=3306, -> master_user= 'slave' , -> master_password= '123456' , -> master_log_file= 'mysql-bin.000001' , -> master_log_pos=769; 2. 开始复制 start slave; 3. 查看复制状态 mysql> show slave status \g *************************** 1. row *************************** slave_io_state: waiting for master to send event master_host: 172.17.0.3 master_user: slave master_port: 3306 connect_retry: 60 master_log_file: mysql-bin.000001 read_master_log_pos: 961 relay_log_file: 87dc5224655d-relay-bin.000003 relay_log_pos: 320 relay_master_log_file: mysql-bin.000001 slave_io_running: yes //表示i/o线程读取成功 slave_sql_running: yes //表示sql线程执行成功 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: 961 relay_log_space: 892 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: 1 master_uuid: 45540733-4e0c-11ea-b0ac-0242ac110003 master_info_file: /var/lib/mysql/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: slave has read all relay 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_tls_version: 4. 查看数据表数据 mysql> show create table user \g *************************** 1. row *************************** table : user create table : create table ` user ` ( `id` int (10) not null auto_increment, ` name ` varchar (30) default null , primary key (`id`) ) engine=innodb auto_increment=2 default charset=utf8mb4 mysql> select * from user ; + ----+------+ | id | name | + ----+------+ | 1 | 222 | + ----+------+

1 2 3 4 5 6 7 复制的控制相关命令： stop salve //停止slave连路 reset slave //重置slave连路 start slave //开启slave连路 stop master //停止master连路 reset master //重置master连路 start master //开启master连路