MySQL双机备份
MySQL双机备份
1. 双主机备份
环境说明：
Master 172.26.1.240:23306 MySQL 5.7.33
Slave 172.26.1.239:23306 MySQL 5.7.33
1. 方案一
# Ubuntu的配置文件： /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
# bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port=23306
lower_case_table_names=1
character-set-server=utf8
collation-server=utf8_general_ci
# 配置主从
server-id=1 #服务器id (主从必须不一样,主为1，从为2)
log-bin=mysql-bin #打开日志(主机需要打开)
binlog_format=mixed # binlog日志格式，mysql默认采用statement，建议使用mixed
relay-log=relay-bin #定义relay_log的名称
relay-log-index=slave-relay-bin.index #定义relay_log的位置和名称
#在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.这样才可>以避免两台服务器同时做更新时自增长字段的值之间发生冲突。
auto-increment-increment=2 #表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
auto-increment-offset=1 #表示自增长字段从那个数开始，他的取值范围是1 .. 65535 （主为1，从为2）
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# Master上面的配置
mysql -uroot -p # 登录上mysql
# 主库上添加同步用户权限，
#replication client权限 -- 查看状态； replication slave权限真正的复制文件
grant replication client,replication slave on *.* to 'repl'@'172.26.1.%' identified by 'backup';
use mysql;
select user,authentication_string,host from user;
以下两个配置，用来设置两个数据库互为主从。
# 配置主从库的通信
mysql> show master status; # 在172.26.1.240上，查看file和position
# 在从库中执行创建 （172.26.1.239上执行以下操作）
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.26.1.240', MASTER_PORT=23306,MASTER_USER='repl', MASTER_PASSWORD='backup', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=721;
mysql> start slave;
mysql> show slave status \G # 查看服务状态
# 配置从主库的通信
mysql> show master status; # 在172.26.1.239上，查看file和position
# 在从库中执行创建 （172.26.1.2240上执行以下操作）
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.26.1.239', MASTER_PORT=23306,MASTER_USER='repl', MASTER_PASSWORD='backup', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=721;
mysql> start slave;
mysql> show slave status \G # 查看服务状态
2. 方案二： GTID方式
PS:方案二没有实际测试。
GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。
mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。
使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。
优点：
1、根据传统的复制原理，当连接发生故障时，需要重新连接到master主机，需要找到binlog和position，然后change master to 连接到master主机，此过程需要人工来做，比较麻烦，也容易出错，尤其是master写操作较多时，更不容易确定position，如果flush table with read lock，势必会影响到线上业务。而GTID复制方式不需要找master的binlog和position，只需要知道master的ip、端口、账号密码，即可进行复制，MySQl会通过内部机制自动找点同步（MASTER_AUTO_POSITION=1）
简单来说就是：简化复制。传统复制是基于file和position来实现的，而file和position是人为确定的，file还好一些，但是position却是实时变动的，难以确定，除非对全库加读锁，但这势必会对线上业务产生影响，GTID会自动找position进行数据同步2、多线程复制（基于库），在MySQL5.6以前的版本，slave的复制是单线程的。一个事件一个事件的读取应用。而master是并发写入的，所以延迟是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制，当只有1个库，多线程复制是没有用的（即：所谓的并行复制）
简单来说就是：跟多线程复制相关。多线程复制是基于组提交方式实现的，而组提交信息是存储在GTID中的。GTID使用限制：
1、 MySQL5.7之后才开始支持动态切换GTID相关的参数
2、 不支持CREATE TABLE ... SELECT statements
3、 不支持CREATE TEMPORARY TABLE statements inside transactions
4、 transaction or statement 既更新了事务表又更新了非事务表
5、 使用GTID复制从库跳过错误时，不支持执行sql_slave_skip_counter参数的语法
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port=23306
# 主节点上，gtid方式配置mysqld.conf
server-id=1
relay-log=relay-log.log
binlog_format=ROW
#log_slave_updates=true #MySQL5.7可以不启用此参数，5.7版本使用了 gtid_executed 表记录同步复制的信息，避免两次写入relay-log和binlog，降低了从库磁盘I/O
gtid_mode=on # 开启gtid模式
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
slave_parallel_workers=2
binlog_checksum=CRC32
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1
log_bin=mysql-bin.log
#replicate_do_db=tt
slave_parallel_type=logical_clock #MySQL5.7新增加的值，配置基于表的组提交并行复制，默认值为database（基于库进行多线程复制，MySQL5.6是基于库的方式进行多线程方式复制）建议改为logical_clock，基于表的组方式复制，提高复制的效率。
auto_increment_increment=2 #自动增长的步长
auto_increment_offset=1 #自动增长的起始数值
# slow query
slow_query_log=on
slow_query_log_file=/usr/local/mysql/slow-query.log
# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M
[mysqld_safe]
log-error=/usr/local/mysql/mysqld.log
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port=23306
# 从节点上，配置mysqld.conf
server-id=2
relay-log=relay-log.log
binlog_format=ROW
#log_slave_updates=true
gtid_mode=on # 开启gtid模式
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_master_info=1
slave_parallel_workers=2
binlog_checksum=CRC32
master_verify_checksum=1
slave_sql_verify_checksum=1
binlog_rows_query_log_events=1
log_bin=mysql-bin.log
#replicate_do_db=tt
slave_parallel_type=logical_clock
auto_increment_increment=2 #自动增长的步长
auto_increment_offset=2 #自动增长的起始数值
# slow query
slow_query_log=on
slow_query_log_file=/usr/local/mysql/slow-query.log
# Adjust as your needed
max_connections=5120
max_connect_errors=52
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M
[mysqld_safe]
log-error=/usr/local/mysql/mysqld.log
# 主库(192.168.0.103)上创建备用库(192.168.0.104)的复制用户
mysql>create user repl@'192.168.0.104' identified by '123123';
mysql>grant replication slave on *.* to zhang@'192.168.0.104';
# 在备用库(192.168.0.104)上执行复制操作
change master to master_host='192.168.0.103', master_port=23306, master_user='zhang', master_password='123123', master_auto_position=1;
# GTID模式下，导出备份注意：
#在dump导出时，添加--set-gtid-purged=off参数，避免将gtid信息导出
mysqldump -uroot -pxxx --all-databases --set-gtid-purged=off > /dir/name.sql
两种方式的区别：
配置文件中开始gtid模式；
配置change master方法中指定MASTER_LOG_FILE、MASTER_LOG_POS
2. 方案一，连接断开。服务器重启后可能造成无法同步
mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是slave_sql_running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程。
错误提示：
- Slave_SQL_Running：no
- slave_io_running：no
MariaDB [(none)]> stop slave;
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
MariaDB [(none)]> show master status\G #查看主机状态
MariaDB [(none)]> slave stop;
MariaDB [(none)]>CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=0;
MariaDB [(none)]> slave start;
MariaDB [(none)]> show slave status\G
参考文档：
https://blog.csdn.net/qq_43345959/article/details/96701515
https://blog.csdn.net/itjackhe/article/details/105426122
https://blog.csdn.net/weixin_43695104/article/details/88368989?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2aggregatepagefirst_rank_v2~rank_aggregation-8-88368989.pc_agg_rank_aggregation&utm_term=keepalived&spm=1000.2123.3001.4430
