Mysql主从复制

码客说

发布于 2022-12-05 15:55:04

1.2K0

发布于 2022-12-05 15:55:04

文章被收录于专栏：码客

主从复制要求

MySQL 主从复制对主机和 MySQL 的要求

（1）主从服务器操作系统版本和位数必须一致；（2）主节点（Master）和从节点（Slave）数据库版本必须一致；（3）主节点（Master）和从节点（Slave）数据库中的数据必须一致；（4）主节点（Master）需要开启二进制日志；（5）主节点（Master）和从节点（Slave）的 server-id 在局域网内必须唯一。

主从配置

主节点-配置

（1）安装数据库；（2）修改数据库配置文件，指定 server-id，开启二进制日志(log-bin)；（3）启动数据库，查看当前是哪个日志，position 号是多少；（4）登录数据库，授权数据复制用户(IP 地址为从机 IP 地址)；（5）备份数据库(记得加锁和解锁)；（6）传送备份数据到 Slave；（7）启动数据库。

修改配置

vi /etc/my.cnf

增加以下配置项：

[mysqld]
server-id = 102           ## server-id，设置为 IP 地址的最后一段
binlog-do-db = zdb       ## 复制过滤：需要备份的数据库，输出 binlog
binlog-ignore-db = mysql  ## 复制过滤：不需要备份的数据库，不输出（mysql 库一般不同步）
log-bin = mysql01-bin     ## 开启二进制日志功能
binlog_cache_size = 1M    ## 为每个 session 分配的内存，用来存储二进制日志的缓存
binlog_format = mixed     ## 主从复制的格式（mixed,statement,row，默认格式是 statement）
expire_logs_days = 7      ## 二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
slave_skip_errors = 1062  ## 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
                          ## 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致

## 说明：二进制日志（binlog）的复制类型
## （1）基于语句的复制（statement）：在 Master 上执行的 SQL 语句，在 Slave 上执行同样的语句。
##     MySQL 默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选择基于行的复制。 
## （2）基于行的复制（row）：把改变的内容复制到 Slave，而不是把命令在 Slave 上执行一遍。从ySQL5.0 开始支持。
## （3）混合类型的复制（mixed）：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

注意

主从节点的server-id要不一样。多个数据库用逗号分割 replicate-do-db=db1,db2

重启MySQL

service mysqld restart

创建同步用户

set global validate_password_policy=0;
set global validate_password_policy=0;

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY 'psvmc123456';
FLUSH PRIVILEGES;

锁表

flush tables with read lock;

查看状态

show master status;

结果如图

记录下里面的File和Position的值，从节点同步时要用。

主节点-数据备份

备份 zdb 数据库中的数据

mysqldump -uroot -p --add-drop-table zdb > /tmp/zdb-bak.sql

将主节点的 zdb 备份传送到从节点

scp -P 2222 /tmp/zdb-bak.sql root@192.168.1.11:/tmp/

从节点-数据导入

create database zdb;
use zdb;
source /tmp/zdb-bak.sql
-- 或者退出后执行
mysql -uroot -p zdb < /tmp/zdb-bak.sql

从节点-配置

（1）安装数据库；（2）修改数据库配置文件，指明 server-id；（3）启动数据库，还原备份；（4）指定 Master 的地址、用户、密码等信息；（5）开启同步，查看状态。

修改配置

vi /etc/my.cnf

增加以下配置项：

server-id = 11                 ## server-id，一般设置为 IP 的最后一段
relay_log = mysql02-relay-bin   ## relay_log 配置中继日志
read_only = 1                   ## 防止改变数据(除了特殊的线程)

说明：

如果 Slave 为其它 Slave 的 Master 时，必须设置 bin_log；
relay_log 配置中继日志；
log_slave_updates 表示 slave 将复制事件写进自己的二进制日志。当设置 log_slave_updates 时，你可以让 slave 扮演其它 slave 的 master。此时，slave 把 SQL 线程执行的事件写进行自己的二进制日志（binary log），然后，它的 slave 可以获取这些事件并执行它。

从节点也可以有多级

设置主从复制

change master to master_host = '192.168.1.15',master_user = 'sync',master_password = 'psvmc123456',master_port = 3306,master_log_file = 'mysql01-bin.000002', master_log_pos = 976, master_connect_retry = 30;

参数说明：

master_host = '192.168.1.15'          ## Master 的 IP 地址
master_user = 'sync'                   ## 用于同步数据的用户（在 Master 中授权的用户）
master_password = 'psvmc123456'             ## 同步数据用户的密码
master_port = 3306                     ## Master 数据库服务的端口
master_log_file = 'mysql01-bin.000002' ## 指定 Slave 从哪个日志文件开始读复制数据（可在 Master 上
                                       ## 使用 show master status 查看到日志文件名）
master_log_pos = 598                  ## 从哪个 POSITION 号开始读
master_connect_retry = 30              ## 当重新建立主从连接时，如果连接建立失败，间隔多久后重试。
                                       ## 单位为秒，默认设置为 60 秒。

查看主从同步状态：

show slave status\G

开启主从复制

start slave;

查看主从同步状态：

show slave status\G

主要看以下两个参数，这两个参数如果是 Yes 就表示主从同步正常：

Slave_IO_Running: Yes Slave_SQL_Running: Yes

主节点-解锁表

开启同步后解锁主节点数据库表

unlock tables;

重置主从复制

如果遇到同步出错，可在 Slave 上重置主从复制设置，步骤如下：

重置主从复制设置

stop slave;
reset slave;

重新设置主从复制参数

change master to master_host = '192.168.1.15',master_user = 'sync',master_password = 'psvmc123456',master_port = 3306,master_log_file = 'mysql01-bin.000002', master_log_pos = 976, master_connect_retry = 30;

查看主从同步状态