主从复制配置
功能
为什么要用主从复制？
主从复制目的：
- 可以做数据库的实时备份，保证数据的完整性；
- 可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。
注意：不能完全依赖主从复制做数据库备份，比如误执行了不可恢复的操作，主从复制就解决不了此问题。
同步是靠log文件同步读写完成的。
docker-compose部署
version: '3'
services:
mysql_1:
image: mysql:5.7.30
container_name: mysql_1
restart: unless-stopped
hostname: mysql_1
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=test@001
volumes:
- /etc/localtime:/etc/localtime:ro
- /opt/data/mysql_ha/mysql_1:/var/lib/mysql
- ./master.cnf:/etc/mysql/conf.d/master.cnf
command:
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
ports:
- "5306:3306"
networks:
- mysql-hs
mysql_2:
image: mysql:5.7.30
container_name: mysql_2
restart: unless-stopped
hostname: mysql_2
environment:
- TZ=Asia/Shanghai
- MYSQL_ROOT_PASSWORD=test@001
volumes:
- /etc/localtime:/etc/localtime:ro
- /opt/data/mysql_ha/mysql_2:/var/lib/mysql
- ./slave.cnf:/etc/mysql/conf.d/slave.cnf
command:
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
ports:
- "5307:3306"
networks:
- mysql-hs
networks:
mysql-hs:
配置文件
mysql的master和slave实例的配置文件（my.cnf）是有差异的。同时要确保各实例的**server-id**是不同的，最好master实例的server-id最小。
master
log-bin = /var/lib/mysql/mysql-bin #可以是绝对路径，也可以是相对于datadir的路径
server-id = 1
slave
log-bin = /var/lib/mysql/mysql-bin
server-id = 2
建立用于从库复制的账号
通常会创建一个用于主从复制的专用账户，并授予合适的权限。
-- 授权（replication slave）
create user rep identified by '001@pass';
grant replication slave on *.* to rep;
flush privileges;
从库初始化与主库保持初始状态一致
master操作
-- 在主库上加锁，使只有只读权限。
flush table with read lock;
-- 查看master当前状态。会显示日志文件以及对应的事务position。
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 755 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
完整备份master
#备份所有数据库，并把log文件的信息包含在文件中
mysqldump -uroot -ptest@001 -A -B --events --master-data=2 | gzip > /var/lib/mysql/rep.sql.gz
可以使用vim打开gz文件。/var/lib/mysql/rep.sql.gz 数据文件包含一些脚本。最开始是：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=999;
然后是系统数据库mysql的库表结构DDL以及DML。
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
... ...
-- Dump completed on 2021-04-08 11:59:11
master操作
-- 查看master status；数值是否正常
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 999 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 解锁库
unlock tables;
把备份文件拷贝到slave，操作
#把master备份的数据的脚本 在slave上执行。
gunzip < /var/lib/mysql/rep.sql.gz | mysql -uroot -ptest@001
配置从库同步master库
配置从库
-- 配置与master同步
CHANGE MASTER TO
MASTER_HOST='mysql_1',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='001@pass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=999;
-- 开始同步
start slave;
-- 检查同步状态
show slave status\G
MASTER_LOG_FILE，MASTER_LOG_POS参数通过master库show master status查询。
查看更改的参数
在数据目录/var/lib/mysql下有master信息文件master.info,可以查看master信息。
读写分离
忽略MySQL主从复制授权表同步，读写分离。
master配置
log-bin = /var/lib/mysql/mysql-bin
server-id = 1
#添加以下四行
replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
slave配置
log-bin = /var/lib/mysql/mysql-bin
server-id = 2
#增加只读
read-only
slave实例在配置用户时，不能指定有
super或all privileges权限。不然没效果。
mysql启动配置文件
my.cnf的读取顺序
1、/etc/my.cnf
2、/etc/mysql/my.cnf
3、DEFAULT_SYSCONFDIR 编译时配置下的my.cnf
4、MYSQL_HOME 设置。mysqld_safe会设置MYSQL_HOME，就会读取下面的my.cnf。
5、–defaults-extra-file的设置，my_print_defaults和mysqld均由这个设置。
6、~/.my.cnf
7、从解析的顺序来看最后会加载命令行参数。
解析完成后全部参数罗列，多个同名参数以最后一个生效。
或者可以使用mysql --help查看启动顺序
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
在my.cnf中可能会有包含其他目录的命令，目录中的文件的后缀必须为 .cnf，不然识别不了。
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
my.cnf配置
my.cnf配置文件的配置是分段的。
- client：客户端设置，即客户端默认的连接参数。注意只有MySQL附带的客户端应用程序保证可以读取这段内容，对其他客户端无效。
- mysqld_safe：
- mysqld：mysql启动参数
- mysqldump：
- mysql：
- isamchk：
- myisamchk：
- … …
mysql binlog详解
简介
MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启binlog日志大概会有1%的性能损耗。
binlog日志有两个最重要的使用场景。
-
mysql主从复制
-
数据恢复：通过
mysqlbinlog工具来恢复数据。
binlog日志包括两类文件：
- 二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。
- 二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML语句事件。
.index文件是文本类型的，里面存储的是日志文件的路径信息。
./mysql-bin.000001
./mysql-bin.000002
日志文件是二进制的。
mysql二进制日志有三种格式：statement、row、mixed
一般在主从复制过程中采用的格式都是mixed。这种格式兼具了基于行(row)和基于语句(statement)的优点，速度和效率是最高的。
但是在使用mysqlbinlog查看混合模式的二进制文件时，基于行的部分默认是以base64编码显示的，那么如何对其进行解码
mysqlbinlog工具的命令语法如下：
mysqlbinlog [options] log-files
常用option：
--start-datetime=name #读取等于或晚于指定时间的事件。时间是本地时间，格式包括日期和时间。
-j, --start-position=# #读取等于或大于指定position的事件。
--stop-datetime=name #读取等于或早于指定时间的事件。时间是本地时间，格式包括日期和时间。
--stop-position=# #读取等于或小于指定position的事件。
--database=ops #指定只恢复指定数据库
--base64-output=name #binlog中 sql语句是base64编码的，never；decode-rows：base64解码，需加上-v；auto，默认值，
开启binlog日志
1、编辑my.cnf
[mysqld]
log-bin=mysql-bin #(也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin)
server-id=1
binlog_format=MIXED #(加入此参数才能记录到insert语句，默认的就可以)
2、重启mysqld服务
3、查看binlog日志是否开启
show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | stderr |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+--------------------------------+
binlog日志操作命令
1、查看所有binlog日志列表
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1611 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2、查看master状态，即最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值。
show master status
3、flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;
flush logs;
注意：每当
mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加**-F**选项也会刷新binlog日志；
4、重置（清空）所有binlog日志
reset master;
查看binlog日志内容
mysqlbinlog工具
mysqlbinlog mysql-bin.000001
#指定时间
mysqlbinlog mysql-bin.000001 --start-datetime="2021-04-08 13:21:11"
日志内容
# at 1064
#210408 13:20:50 server id 1 end_log_pos 1161 CRC32 0xc0c0ddbd Query thread_id=20 exec_time=0 error_code=0
SET TIMESTAMP=1617859250/*!*/;
create database test1
/*!*/;
# at 1161
#210408 13:21:11 server id 1 end_log_pos 1226 CRC32 0xae25488f Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1226
#210408 13:21:11 server id 1 end_log_pos 1330 CRC32 0x97595837 Query thread_id=20 exec_time=0 error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1617859271/*!*/;
create table test1 ( a int )
/*!*/;
#===============以下是一个insert语句的log
# at 1395
#210408 13:21:24 server id 1 end_log_pos 1468 CRC32 0x17fd5eb5 Query thread_id=20 exec_time=0 error_code=0
SET TIMESTAMP=1617859284/*!*/;
BEGIN
/*!*/;
# at 1468
#210408 13:21:24 server id 1 end_log_pos 1517 CRC32 0x6499d99c Table_map: `test1`.`test1` mapped to number 151
# at 1517
#210408 13:21:24 server id 1 end_log_pos 1557 CRC32 0xfe943ea8 Write_rows: table id 151 flags: STMT_END_F
BINLOG '
1JJuYBMBAAAAMQAAAO0FAAAAAJcAAAAAAAEABXRlc3QxAAV0ZXN0MQABAwABnNmZZA==
1JJuYB4BAAAAKAAAABUGAAAAAJcAAAAAAAEAAgAB//4BAAAAqD6U/g==
'/*!*/;
# at 1557
#210408 13:21:24 server id 1 end_log_pos 1588 CRC32 0xc40aa33b Xid = 491
COMMIT/*!*/;
at 1064 ：事务从pos ，1064开始
server id 1：server-id 为 1
end_log_pos 1161： 事务结束pos是1161。下一个事务的开始pos是1161。
CRC32 值为 0xc0c0ddbd
thread_id： 线程id为 20
sql查询
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
-- FROM 必须是存在的pos。LIMIT相对 FROM的。
show binlog events in 'mysql-bin.000002' FROM 0 limit 2,20;
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| mysql-bin.000001 | 398 | Anonymous_Gtid | 1 | 463 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 463 | Query | 1 | 598 | use `mysql`; GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' |
| mysql-bin.000001 | 598 | Anonymous_Gtid | 1 | 663 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 663 | Query | 1 | 755 | use `mysql`; flush privileges |
| mysql-bin.000001 | 755 | Anonymous_Gtid | 1 | 820 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
利用binlog日志恢复mysql数据
前提是有完整备份（全备份）。
1、定位误操作的语句的 position。
2、恢复完整部分
3、通过日志恢复备份。需要指定start position和 stop position。
mysqlbinlog mysql-bin.0000xx options | mysql -uroot -p
主从配置相关命令
show master status
*************************** 1. row ***************************
File: mysql-bin.000001 #日志文件名称
Position: 999
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
show slave status
SLAVE_IO线程：把master日志复制到slave的中继日志（relay log）。一般出问题是连接问题或权限问题。
SLAVE_SQL线程：把中继日志（relay log）在slave重做。
从库会出现错误的时候Slave_SQL_Running的状态会变为No，而Last_SQL_Error和Last_SQL_Error_Timestamp会记录错误的原因和发生时间；
当错误修复后，只需用stop slave和start slave重启下同步，Slave_SQL线程会重新的尝试工作，如果没有问题，那么Slave_SQL的状态会变回Yes（但此时主从并没有完全同步，需要一点时间）
判断主从完全同步：
- 首先
Master_Log_File和Relay_Master_Log_File所指向的文件必须一致； - 其次
Relay_Log_Pos和Exec_Master_Log_Pos的值也要一致才行； Slave_SQL_Running_State：显示为wait。 意思是中继日志的sql语句已经全部执行完毕
正确状态：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql_1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 412
Relay_Log_File: mysql_2-relay-bin.000004
Relay_Log_Pos: 625
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #IO已启动
Slave_SQL_Running: Yes
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: 412
Relay_Log_Space: 1589
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: 9f47a18c-9816-11eb-b08b-0242ac180002
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:
1 row in set (0.00 sec)
错误状态：
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mysql_1 #
Master_User: rep #同步用户
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 999
Relay_Log_File: mysql_2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No #IO未运行
Slave_SQL_Running: Yes
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: 999
Relay_Log_Space: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
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: 210408 13:14:38
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:
1 row in set (0.00 sec)
show processlist\G
查看系统线程。
master：
*************************** 1. row *************************** # master 发送日志线程。
Id: 2
User: rep
Host: 172.24.0.2:46050
db: NULL
Command: Binlog Dump
Time: 4630
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
slave：
*************************** 1. row *************************** #监控master事件线程
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 4552
State: Waiting for master to send event
Info: NULL
*************************** 2. row *************************** #slave重放日志线程
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 13
State: Slave has read all relay log; waiting for more updates
Info: NULL
遇到的问题
变更未同步
通过查看slave同步状态，发现server-id与master设置成一样了。
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
修改server-id之后，重启slave，会自动同步master，不需要再重新执行change master命令。
Binlog日志清理
查看所有binlog日志
show binary logs;
3种处理方式
自动删除
cat /etc/my.cnf
expire_logs_days = 2
#保留最近2天的binlog文件；expire_logs_days 为binlog过期时间，默认值为0,表示“永不过期”,重启数据库生效
#临时生效：
show variables like 'expire_logs_days';
set global expire_logs_days=1;
设置之后不会立即清除，触发条件是：
binlog大小超过max_binlog_size- 手动执行flush logs
- 重新启动时(MySQL 将会new一个新文件用于记录binlog)
手动删除（安全处理）
#清除MySQL-bin.010日志
PURGE MASTER LOGS TO 'MySQL-bin.010';
#清除2008-06-22 13:00:00前binlog日志
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
#清除3天前binlog日志BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。
PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 3 DAY);
所有BINLOG日志
RESET MASTER;
#该命令将删除所有BINLOG日志
扫一扫
2976
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
