主从复制-主从复制搭建
简述
Mysql搭建主从复制很简单,简述为如下步骤： 1.主机上备份数据库 2.从机上恢复数据库 3.主机创建一个授权复制权限的用户 4.从机 change master to
重要参数
主(Master)
max_binlog_size = 2048M # 默认为1024M
binlog_format = ROW # 必须为ROW
transaction-isolation = READ-COMMITTED
expire_logs_days = 7 # binlog保留多少天，看公司计划安排
server-id = 10 # 必须和所有从机不一样，且从机之间也不一样
log_bin = bin.log # 开启binlog
binlog_cache_size = # binlog 缓存的大小，设置时要当心
sync_binlog = 1 # 必须设置为1
innodb_flush_log_at_trx_commit = 1 # 提交事物的时候刷新日志
innodb_support_xa = 1 # 启用对两阶段提交的支持，8.0弃用此参数，设置默认启用，不允许关闭
# -------------------以下参数看业务需求--------------------------
binlog-do-db = # 需要复制的库
binlog-ignore-db = # 需要被忽略的库从(Slave)
log_slave_updates # 将SQL线程回放的数据写入到从机的binlog中去（用于级联复制）
server-id = 11 # 必须在一个复制集群环境中全局唯一
log_bin = bin.log # 开启binlog
relay-log-recover = 1 # I/O thread crash safe – IO线程安全
relay_log_info_repository = TABLE # SQL thread crash safe – SQL线程安全
master_info_repository = TABLE
read_only = 1
slave_parallel_workers=16 #开启多个works,看机器支持
slave_parallel_type=LOGICAL_CLOCK # 并行复制，5.7.2起支持
# -------------------以下参数看业务需求--------------------------
replicate-do-db = # 需要复制的库
replicate-ignore-db = # 需要忽略的库
replicate-do-table = # 需要复制的表
replicate-ignore-table = # 需要忽略的表复制环境搭建
服务器信息
主：192.168.20.101 从：192.168.20.102
Master上创建一个复制用户
-- 主机上操作
create user 'rpl'@'%' identified by '123456';
grant replication slave on *.* to 'rpl'@'%';从机上登录验证下连接是否正常
-- 从机上操作
mysql -h192.168.20.101 -urpl -p --测试连接是否正常主从数据备份同步
主机操作- 备份主库信息
# 备份全库
mysqldump --single-transaction --master-data=1 -A > master.sql- 将备份传输文件到从机上去
scp /root/master.sql 192.168.20.102:/root
从库操作- 恢复主库传过来的备份
mysql -uroot -p < /root/master.sql- 查看 MASTER_LOG_FILE 与 MASTER_LOG_POS
head -30 /root/master.sql# 或者在 master 上执行
show master statuslog: bin.000003 和 Pos: 633 表明该备份开始时的 filename 和 postition
- CHANGE MASTER
change master to mysql> change master to master_host='192.168.20.101',master_user='rpl',master_password='123456',master_port=3306,master_log_file='bin.000003',master_log_pos=633;
Query OK, 0 rows affected, 2 warnings (0.01 sec)- 启动 slave
mysql> start slave;- 查看主从复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event # IO 线程的状态
Master_Host: 192.168.20.101 # Master 的地址
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000003 # IO线程读取到的主机文件
Read_Master_Log_Pos: 633 # IO线程读取文件中的位置
Relay_Log_File: relay.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: bin.000003 # 从机SQL线程执行到的文件
Slave_IO_Running: Yes # io thread 状态
Slave_SQL_Running: Yes # sql thread 状态
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: 633 # SQL线程执行到文件的位置
Relay_Log_Space: 511
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 # Slave 落后 Master 的秒数(不精准)
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0 # I/O 错误信息
Last_IO_Error:
Last_SQL_Errno: 0 # SQL 进程错误信息
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: a7776f71-c8be-11e9-838f-0050563bb195
Master_Info_File: mysql.slave_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: a7776f71-c8be-11e9-838f-0050563bb195:1-3
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)Slave_IO_Running和Slave_SQL_Running都显示为Yes,说明复制是正常的。
Relay_Log
Relay_Log_File和Relay_Log_Pos是中继日志(Relay_Log)信息由于IO线程拉取数据的速度快于SQL线程回放数据的速度，所Relay_Log可在两者之间起到一个缓冲的作用Relay_Log的格式和binlog的格式是一样的，但是两者的内容是不一样的(不是和binlog一一对应的),可使用mysqlbinlog relay.000002 -vv查看Relay_Log在SQL线程回放完成后，默认就会被删除，而binlog不会(由expire_logs_days控制)Relay_Log可以通过设置relay_log_purge=0，使得 - - Relay_Log不被删除(MHA中不希望被Purge)，需要通过外部的脚本进行删除
- Seconds_Behind_Master Seconds_Behind_Master 字面意思为Slave落后Master的 秒数 ，但是实际上并不是十分准确。因为回放的SQL线程可能落后很多，比如新加了一个库需要追很多数据。 计算的方式为： Slave的SQL线程执行时刻的时间减去event产生时刻的时间。 可以使用Percona ToolKit中的pt-heartbeat来获得精准的延迟时间
验证主从
- Master
# Master上创建数据库 aaa
mysql> create database aaa;
mysql> use aaa;
mysql> create table t(id int);
mysql> insert into t values(1),(2),(3);- Slave
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use aaa;
Database changed
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
