MySQL5.7 搭建主从-传统方式
MySQL mysqlpump About 11,301 words版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.27-log |
+------------+
1 row in set (0.00 sec)主从原理
- 从库的
IO thread与主库的dump thread交互，dump thread读取二进制文件bin-log写入IO thread。 - 从库的
IO thread将读取的二进制数据写入中继日志relay-log中。 - 从库的
SQL thread从中继日志relay-log中读取二进制数据，并加载至从库中完成同步。
主库设置
[client]
port=3306
socket=/home/mysql/data/mysql.sock
[mysqld]
##########################
##### Basic Settings #####
##########################
user=mysql
port=3306
server_id=1
datadir=/home/mysql/data
tmpdir=/home/mysql/temp
socket=/home/mysql/data/mysql.sock
pid_file=/home/mysql/data/mysqld.pid
log_error=/home/mysql/mysqld.log
default_time_zone='+8:00'
character_set_server=utf8
collation_server=utf8_general_ci
init_connect='SET NAMES utf8'
lower_case_table_names=1
skip_name_resolve=OFF
transaction_isolation=READ-COMMITTED
#建议在备库设置replicate-do-db、replicate-ignore-db, 让备库做过滤逻辑减轻主库压力
#要给从机同步的库（如果不写，默认全部同步）
#binlog-do-db=db01
#不给从机同步的库(多个写多行)
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#binlog-ignore-db=sys
########################
##### Log Settings #####
########################
slow_query_log_file=/home/mysql/log/slow.log
general_log_file=/home/mysql/log/general.log
log_bin=/home/mysql/log/bin.log
log_bin_index=/home/mysql/log/binlog.index
binlog_format=ROW
binlog_rows_query_log_events=ON
binlog_cache_size=1M
max_binlog_size=1024M
log_bin_trust_function_creators=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=0
slow_query_log=ON
log_queries_not_using_indexes=ON
log_slow_admin_statements=ON
log_slow_slave_statements=ON
log_throttle_queries_not_using_indexes=10
long_query_time=5
min_examined_row_limit=100主库创建同步用户
The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS. The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.
REPLICATION CLIENT权限用于执行show master status等命令。这些命令是用来查看复制状态的。
REPLICATION SLAVE是用于连接主库进行复制的。
CREATE USER 'syncuser'@'%' IDENTIFIED BY 'pwd123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncuser'@'%';或者
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'syncuser'@'192.168.100.2' IDENTIFIED BY 'pwd123456'; 从库设置
[client]
port=3306
socket=/home/mysql/data/mysql.sock
[mysqld]
##########################
##### Basic Settings #####
##########################
user=mysql
port=3306
server_id=2
datadir=/home/mysql/data
tmpdir=/home/mysql/temp
socket=/home/mysql/data/mysql.sock
pid_file=/home/mysql/data/mysqld.pid
log_error=/home/mysql/mysqld.log
default_time_zone='+8:00'
character_set_server=utf8
collation_server=utf8_general_ci
init_connect='SET NAMES utf8'
lower_case_table_names=1
skip_name_resolve=OFF
replicate-do-db=db01
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
# 默认OFF
# 如果在slave机器上对数据库进行修改或删除，会导致主从的不一致，需要对slave机器设置为read_only=ON，让slave提供只读操作。
# 注意：read_only仅仅对没有SUPER权限的用户有效（即mysql.user表的Super_priv字段为Y）
read_only=ON
# 默认OFF
# 可以将SUPER权限的用户也设置为只读。
# 设置ON后，read_only也自动设置为ON。
super_read_only=ON
########################
##### Log Settings #####
########################
slow_query_log_file=/home/mysql/log/slow.log
general_log_file=/home/mysql/log/general.log
log_bin=/home/mysql/log/bin.log
log_bin_index=/home/mysql/log/binlog.index
binlog_format=ROW
binlog_rows_query_log_events=ON
binlog_cache_size=1M
max_binlog_size=1024M
log_bin_trust_function_creators=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=0
slow_query_log=ON
log_queries_not_using_indexes=ON
log_slow_admin_statements=ON
log_slow_slave_statements=ON
log_throttle_queries_not_using_indexes=10
long_query_time=5
min_examined_row_limit=100
#####################
###### 中继日志 #####
#####################
relay_log=/home/mysql/log/relay.log
relay_log_index=/home/mysql/log/relay.index
# 默认OFF
# 打开replication中继日志崩溃恢复模式，replication支持中继日志的自我修复功能。
# 当slave从库宕机后，如果relay-log发生损坏，导致一部分中继日志没有处理，就自动放弃未执行的replay-log，重新从master上获取日志，完成了中继日志的恢复。
# 该参数表示当前接收到的relay-log全部删除，然后从SQL线程回放到的位置重新拉取。
relay_log_recovery=ON
# 默认FILE
# SQL线程的数据回放是写数据库操作，relay-log是写文件操作，这两个操作很难保证一致性。
# relay-info将写入到mysql.slave_relay_log_info这张表中。
relay_log_info_repository=TABLE
# 默认是FILE
# IO线程也是接收一个个的event，将接收到的event通过设置参数master_info_repository可以将master-info信息写到什么位置，性能上比设置FILE有很高的提升，可靠性也得到保证。
# 设置为TABLE后，master-info将信息保存到mysql.slave_master_info这张表中。
master_info_repository=TABLE
# 默认DATABASE
# -DATABASE：基于库的并行复制方式，兼容MySQL5.6基于schema级别的并发复制。
# -LOGICAL_CLOCK：基于组提交的并行复制方式，组提交是一堆事务的集合，减轻IO压力。
slave_parallel_type=LOGICAL_CLOCK
# 4个SQL线程（coordinator线程）来进行并行复制。
# 可以动态调整复制线程数。
# set global slave_parallel_workers=8
# stop slave;--必须要重启一下slave才能生效
# start slave;
# show processlist;
slave_parallel_workers=4
# slave上commit的顺序保持一致，必须为ON，否则可能会有GAP锁产生。
# ERROR 3031 (HY000): slave_preserve_commit_order is not supported unless both log_bin and log_slave_updates are enabled.
# ERROR 3031 (HY000): slave_preserve_commit_order is not supported when slave_parallel_type is DATABASE.
slave_preserve_commit_order=ON
# 当从库log_slave_updates参数没有开启时，从库的binlog不会记录来源于主库的操作记录。
# 只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志。
# 如果是多个从库，切换后，建议关掉log_slave_updates参数，否则重置成主库以后，可能会将已经执行过的二进制曰志重复传送给S2 ,导致S2同步错误。
log_slave_updates=ON
# 默认OFF
# 一些ErrorCode:
# 1007: 数据库已存在，创建数据库失败
# 1008: 数据库不存在，删除数据库失败
# 1050: 数据表已存在，创建数据表失败
# 1051: 数据表不存在，删除数据表失败
# 1053: 复制过程中主服务器宕机
# 1054: 字段不存在，或程序文件跟数据库有冲突
# 1060: 字段重复，导致无法插入
# 1061: 重复键名
# 1062: 主键冲突 duplicate entry ‘%s' for key %d
# 1068: 定义了多个主键
# 1094：位置线程ID
# 1146: 数据表缺失，请恢复数据库
slave_skip_errors=ddl_exist_errors主库查看postion及导出数据
锁表
flush tables with read lock;查看当前状态
show master status;输出
mysql> show master status;
+------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+-------------------------------------------------+-------------------+
| bin.000002 | 1314 | | mysql,information_schema,performance_schema,sys | |
+------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)导出数据（--exclude-databases排除指定库后，备份其他全部库）
system mysqlpump -uroot -p --add-drop-database --exclude-databases=mysql,sys,information_schema,performance_schema --single-transaction --set-gtid-purged=OFF --default-parallelism=5 --extended-insert=10000 --compress-output=LZ4 > pump.lz4导出数据（-B备份指定库，若同时设置了--exclude-databases，则以--exclude-databases为准）
system mysqlpump -uroot -p --add-drop-database -B test test2 --single-transaction --set-gtid-purged=OFF --default-parallelism=5 --extended-insert=10000 --compress-output=LZ4 > pump.lz4查看服务器目录
system ls解锁
unlock tables;传输至备库
scp -r /home/mysql/backup/pump.lz4 testuser@192.168.100.102:/home/mysql/pump.lz4从库设置
Linux shell中解压lz4
lz4_decompress pump.lz4 pump.sql查看从库super用户是否只读
select @@super_read_only;暂时关闭只读
set global super_read_only=OFF;导入数据
source pump.sql开启super只读
set global super_read_only=ON;关联主库
change master to
master_host='192.168.100.1',
master_port=3306,
master_user='syncuser',
master_password='pwd123456',
master_log_file='bin.000002',
master_log_pos=1314;从库开启同步
start 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.100.1
Master_User: syncuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000002
Read_Master_Log_Pos: 1314
Relay_Log_File: relay.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: bin.000002
Slave_IO_Running: Yes
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: 1314
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
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: dd9c152f-5c88-11eb-b400-286ed488cddb
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)可能出现的问题
问题一
发现Slave_IO_Running和Slave_SQL_Running都为No，说明从库未正常工作。
解决
stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;问题二
Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
配置文件中，我们将master_info_repository设置为了存表方式，则主库信息都存在mysql库下的slave_master_info中。
use mysql
select * from slave_master_info\G输出
mysql> select * from slave_master_info\G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: bin.000002
Master_log_pos: 1314
Host: 10.95.218.32
User_name: syncuser
User_password: pwd123456
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid:
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 0
Channel_name:
Tls_version:
1 row in set (0.00 sec)解决
从库开启备份时指定用户名密码。
start slave user='syncuser' password='pwd123456';备注
可使用停止备库SQL线程或IO线程来模拟容灾情况。
停止SQL线程。
stop slave sql_thread;停止IO线程。
stop slave io_thread;
Views: 1,913 · Posted: 2021-03-01
———— END ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...