双主备份 - MySQL

#主库操作
##修改配置文件/etc/my.cnf
	[mysqld]
	sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
	collation-server=utf8_general_ci
	character-set-server=utf8
	lower_case_table_names=1	#1：表名不再区分大小写
	max_connections=1000		#最大连接数
	innodb_buffer_pool_size = 4G
	max_allowed_packet = 80M
	
	server-id=1		#server-id:注意两主得配置一定要不通
	binlog-ignore-db=mysql,information_schema,performance_schema,sys	#不记录库
	log-bin=mysql-bin	#开启二进制日志
	binlog_cache_size=1M
	binlog-format=mixed
	expire_logs_days=7		#日志过期
	slave_skip_errors=1032,1062,1007,1050
	relay-log=relay-log
	log_slave_updates=1
	auto_increment_increment=2		#双主特别注意此参数
	auto_increment_offset=1		#双主特别注意此参数
	innodb_flush_log_at_trx_commit=0
	log-bin-trust-function-creators=1	#不限制，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建

datadir=/var/lib/mysql
	socket=/var/lib/mysql/mysql.sock
	
	# Disabling symbolic-links is recommended to prevent assorted security risks
	symbolic-links=0
	
	log-error=/var/log/mysqld.log
	default_password_lifetime=0
	pid-file=/var/run/mysqld/mysqld.pid

---
##创建同步账户
	GRANT ALL PRIVILEGES ON *.* TO 'repl'@'%' IDENTIFIED BY 'Bak2021!!';
	flush privileges;

---
##重启mysql使配置生效
	systemctl restart mysqld

---
#从库操作
##修改配置文件
	[mysqld]
	sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
	collation-server=utf8_general_ci
	character-set-server=utf8
	lower_case_table_names=1	#1：表名不再区分大小写
	max_connections=1000		#最大连接数
	innodb_buffer_pool_size = 4G
	max_allowed_packet = 80M
	
	server-id=2		#server-id:注意两主得配置一定要不通
	binlog-ignore-db=mysql,information_schema,performance_schema,sys	#不记录库
	log-bin=mysql-bin	#开启二进制日志
	binlog_cache_size=1M
	binlog-format=mixed
	expire_logs_days=7		#日志过期
	slave_skip_errors=1032,1062,1007,1050
	relay-log=relay-log
	log_slave_updates=1
	auto_increment_increment=2	#双主特别注意此参数
	auto_increment_offset=2		#双主特别注意此参数
	innodb_flush_log_at_trx_commit=0
	log-bin-trust-function-creators=1	#不限制，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建

---
##创建同步账户
	GRANT ALL PRIVILEGES ON *.* TO 'repl'@'%' IDENTIFIED BY 'Bak2021!!';
	flush privileges;

---
#使用逻辑备份，或者物理备份，是数据同步到从库
####逻辑备份
	http://baodoc.frp.bao-server.club:22080/project/10/91/
####物理备份
	http://baodoc.frp.bao-server.club:22080/project/10/92/

---
##配置slave
	mysql > change master to 
        master_host = '192.168.14.130',
        master_port = 3306,
        master_user = 'repl',
        master_password = 'passwd',
        master_log_file = 'mysql.bin.0001',
        master_log_pos = 120;
---
##启动与查看slave
	mysql > start slave;          #启动slave
	mysql > show slave status\G;    #查看slave

---
#待主从同步后，将主也做为备的从库
	#主库锁表
	flush tables with read lock;   
	#插查看从库状态
	show slave status\G;
	
	#待slave没有东西同步后。主库操作

![](/media//202112/2021-12-16135951944657.png)

mysql > change master to 
        master_host = '192.168.14.131',
        master_port = 3306,
        master_user = 'repl',
        master_password = 'passwd',
        master_log_file = 'mysql.bin.0001',
        master_log_pos = 120;

mysql > start slave;          #启动slave
	mysql > show slave status\G;    #查看slave
	mysql > unlock tables;         #主库解锁