MySQL实现主从架构

一.主从复制
1.主从复制的定义
主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库上，然后在从数据库上对这些日志进行重新执行，从而使从数据库和主数据库的数据保持一致。

2.MySQL支持的复制类型
基于语句（ statement ）的复制
在主服务器上执行 SQL 语句，在从服务器上执行同样的语句。 MySQL 默认采用基于语句的复制，效率比
较高。
基于行（ row ）的复制
把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从 MySQL 5.0 开始支持。
混合型（ mixed ）的复制
默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。
2.主从复制的原理
1 、 MySQL 将数据变化记录到二进制日志中；
2 、 Slave 将 MySQL 的二进制日志拷贝到 Slave 的中继日志中；
3 、 Slave 将中继日志中的事件在做一次，将数据变化，反应到自身（ Slave ）的数据库
注：
主要基于 MySQL 二进制日志
主要包括三个线程（2个 I/O 线程， 1 个 SQL 线程）

 3.主从复制常用的拓扑结构
主从类型（ Master-Slave ）
主主类型（ Master-Master ）
级联类型（ Master-Slave-Slave ）
4.主从复制的过程
在主服务器（master）上
1.启用二进制日志
2.选择一个唯一的server-id
3.创建具有复制权限的用户
在从服务器（slave）上
1.启用中继日志
2.（二进制日志可开启，也可不开启）
3.选择一个唯一的 server-id
4.连接至主服务器，并开始复制
5.主从复制实战练习
环境准备：我使用的是一主两从

主机    IP

master	192.168.52.136
slave1	192.168.52.137
slave2	192.168.52.138

1.首先我们需要在主服务器上：
1 ）设置 server - id 值并开启 binlog 参数

[mysqld]
log_bin=mysql-bin  ----开启二进制日志
server_id=1        ----设置server_id（唯一）

重启数据库
2 ） 建立同步账号
mysql> grant replication slave on *.* to 'rep'@'192.168.41.%' identified by
'123456'; ------创建并赋予rep用户复制权限（MySQL8.0以后需要先创建再赋权）
mysql> show grants for 'rep'@'192.168.95.%';----查看rep的权限
3 ）锁表设置只读
为后面备份准备，注意生产环境要提前申请停机时间；
mysql> flush tables with read lock;
4 ）查看主库状态
查看主库状态，即当前日志文件名和二进制日志偏移量
mysql> show master status;
5 ）备份数据库数据

mysqldump -uroot -p -A -B |gzip > /backup/mysql_bak.$(date +%F).sql.gz

6 ）解锁
mysql> unlock tables;
7 ）主库备份数据上传到从库（主从架构需要从库更主库的数据库数据相同）
scp /backup/mysql_bak.2023-07-24.sql.gz 192.168.41.135:/backup/
scp /backup/mysql_bak.2023-07-24.sql.gz 192.168.41.138:/backup/
2.从库上设置（只写了一个，另一个配置方法一致）
1 ）设置 server - id 值并关闭 binlog 参数
[mysqld]
#log_bin
server_id=2
重启数据库：
2 ）还原从主库备份数据
cd /backup/
gzip -d mysql_mysql_bak.2023-07-24.sql.gz
mysql -uroot -p'密码' < mysql_bak.2023-07-24.sql
检查还原：
# mysql -uroot -p -e 'show databases;'
3 ）设定从主库同步

mysql> change master to
MASTER_HOST='192.168.52.136',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154;

以上是小写
 4）启动从库同步开关

mysql> start slave;
检查状态：
mysql> show slave status\G
 当出现即表示连接配置成功

 5）测试主从同步：

主库创建一个数据库：
# mysql -uroot -p -e 'create database test_m_s;'
从库检查：
# mysql -uroot -p -e 'show databases;' |grep "test_m_s"