MySql 主从复制

MySql 复制介绍

MySQL复制允许将主实例(master)上的数据同步到一个或多个从实例(slave)上，默认情况
下复制是异步进行的，从库也不需要一直连接到主库来同步数据
MySQL复制的数据粒度可以是主实例上所有的数据库，也可以是指定的一个或多个数据库
，也可以是一个数据库里的指定的表

MySQL复制所带来的优势在于：
扩展能力：通过复制功能可以将MySQL的性能压力分担到一个或多个slave上。这要求所有
的写操作和修改操作都必须在Master上完成，而读操作可以被分配到一个或多个slave上。
将读写分离到不同服务器执行之后， MySQL的读写性能得到提升

数据库备份：由于从实例是同步主实例的数据，所以可以将备份作业部署到从库

数据分析和报表：同样，一些数据分析和报表的实现可以在从实例执行，以减少对主库的
性能影响

容灾能力：可以在物理距离较远的另一个数据中心建立一个slave，保证在主实例所在地区
遭遇灾难时，在另一个数据中心能快速恢复

MySQL复制有两种方法：
传统方式：基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的
Gtid方式： global transaction identifiers是基于事务来复制数据，因此也就不依赖日志文件，同时又能更好的保证主从库数据一致性

MySQL复制有多种类型：

异步复制：一个主库，一个或多个从库，数据异步同步到从库
同步复制：在MySQL Cluster中特有的复制方式
半同步复制：在异步复制的基础上，确保任何一个主库上的事务在提交之前至少有一个从库已经收到该事务并日志记录下来
延迟复制：在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数

复制的工作原理简单来说是数据库修改事件记录到bin log中并传递到slave，然后slave在本地还原的过程。而事件记录到bin log的格式会有所不同。

MySQL复制有三种核心格式:
基于语句的复制(statement based replication)：基于主库将SQL语句写入到bin log中完成复制

基于行数据的复制(row based replication)：基于主库将每一个行数据变化的信息作为事件写入到bin log中完成日志 (默认使用)

混合复制(mixed based replication)：上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不安全的情况下才会自动切换为基于行数据的复制

MySQL基于binlog的复制：

主从复制构建

mysql 5.7 版本

ip	host
192.168.44.128	mysql-master01
192.168.44.131	mysql-slave01

从以下步骤

1. 主库开启bin-log，并且指定一个唯一的server-id，重启数据库

在配置文件/etc/my.cnf中添加

[mysqld]
log-bin=mysql-bin
server-id=1

在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1确保主库的my.cnf中skip-networking参数为非开启状态，否则会导致主从库不能通信而复制失败

mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)
# 跳过网络通信
 
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

2. 在主库创建一个专门用来做复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限
虽然可以用任何拥有复制权限的MySQL用户来建立复制关系，但由于被使用的用户名和密码
会明文保存在备库的master.info文件中，所以为安全起见，最好是使用仅有复制权限的独立用户

mysql> CREATE USER 'repl'@'192.168.44.%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.44.%';

3.从slave库验证远程连接主库是否正常

4.获取主库的日志信息
为了确保建立的备库能从正确的bin log位置开启复制，要首先获取主库的bin log信息，
包括当前的日志文件名和日志文件内的位置

5. 配置从库

　　在/etc/my.cnf 文件跟主库的配置一致，把server-id 该变一下就可以

6. 主库数据生成镜像并上传到从库

两种方式生成镜像，一种是用mysqldump，是innodb存储引擎推荐的方式；另一种是将数据文件从主库拷贝到从库，这种方式效率更高(省去了dump/import过程中insert语句执行导致的更新index的行为)，但innodb不推荐使用

第一种:
shell> bin/mysqldump –all-databases –master-data -u root -p > dbdump.db ##mysqldump方
式导出所有数据库数据到dbdump.db文件， –master-data表示导出数据直接加上change master to参数以便备库使用

#进行锁表，防止写入数据
mysql> FLUSH TABLES WITH READ LOCK; ##主库上所有表加锁，停止修改
mysql> SHOW MASTER STATUS; ##获取主库的日志信息， file表示当前日志，
position表示当前日志里的位置
mysql> drop table t; #
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
#在另一个会话窗口打开，锁表哪个窗口不要关闭
shell> mysqldump --all-databases --master-data -u root -p > dbdump.db
#通过scp 方式拷贝到备库 ，可以看看这个文件vi 是否备份成功，里面都是sql语句
shell>  scp dbdump.db 192.168.44.131:/root/

第二种：

如果使用文件拷贝的办法：
将主库临时关闭，并将相关文件拷贝到从库上

停止服务

service mysql stop

进入到mysql的数据库

进行打包， tar zcf db128.tar

拷贝到从库

scp 原始文件拷贝的镜像，将文件复制到和主库相同的目录下

7. 从库应用主句的数据镜像
Mysqldump的镜像，通过source命令执行

mysql> source dbdump.db

8. 配置从库，搭建主从关系
从库配置唯一server-id，并重启mysql实例，其他配置文件一致
从库的bin log属性可以打开也可以不打开

从库指定主库的日志信息和链接信息

mysql> CHANGE MASTER TO
MASTER_HOST=‘master_host_name’, ##主库的主机名
MASTER_PORT=port_number ##主库的端口号
MASTER_USER=‘replication_user_name’, ##复制的数据库用户名
MASTER_PASSWORD=‘replication_password’, ##复制的用户密码
MASTER_LOG_FILE=‘recorded_log_file_name’, ##主库的日志文件名
MASTER_LOG_POS=recorded_log_position; ##主库的日志文件位置
 
 
CHANGE MASTER TO
MASTER_HOST='192.168.44.128',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;
 
从库启动复制进程
mysql> START SLAVE;

9. 查看主备库复制是否正常：
在slave上执行show slave status\G命令

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.44.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 540
               Relay_Log_File: mysql02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes  #  IO 线程
            Slave_SQL_Running: Yes  #  sql 线程
。。。。。。

10. 主库释放锁，验证主从复制，主库随意添加数据

mysql> UNLOCK TABLES;
 
#主库添加数据
mysql> insert init t1 values(4,456);
mysql> select * from t1;
+---+---------+
| a | message |
+---+---------+
| 1 | testing |
| 2 | table   |
| 3 | t1      |
| 4 | 456     |
+---+---------+
 
#在slave验证：
mysql> select * from t1;
+---+---------+
| a | message |
+---+---------+
| 1 | testing |
| 2 | table   |
| 3 | t1      |
| 4 | 456     |
+---+---------+
 
#成功

posted on 2025-01-13 20:53 数据派阅读(43) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 如何解决MySQL主从复制延时问题

· MySQL主从延迟原理详解

· Mysql主从复制原理及配置

· MySQL主从复制(Master-Slave)

· MySQL主从复制(Master-Slave)实践

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）

数据派