mysql基于传统的log_file及log_pos主从复制

最新推荐文章于 2021-09-16 14:24:23 发布

weixin_33805992

最新推荐文章于 2021-09-16 14:24:23 发布

阅读量131

点赞数

原文链接：https://my.oschina.net/wangyunlong/blog/3040829

版权

2019独角兽企业重金招聘Python工程师标准>>>

mysql基于传统的log_file及log_pos主从复制

1.配置my.cnf

#查看binlog日志状态：
show variables like 'server_id'; 
show variables like 'log_bin';
#配置my.cnf
vim /usr/local/mysql/my.cnf
server-id=1  #主节点
#server-id=2 #从节点
log-bin=/data/log/mysql-bin
#两个可选参数（2选1）
binlog-do-db=db1,db2 #需要同步的库
#binlog-ignore-db=db1,db2 #忽略不同步的库
其余优化参数请参考mysql优化

mysql简单优化:
[mysqld]
log_bin=/data/log/mysql-bin
binlog-ignore-db=information_schema,performance_schema,mysql,sys
server-id=1
#gtid_mode=ON
#enforce_gtid_consistency=1
auto_increment_offset = 1 
auto_increment_increment = 2 
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/slow.log
long_query_time=2
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/data/3306/mysql.sock
innodb_buffer_pool_size = 8G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
show_compatibility_56=on
symbolic-links=0
character_set_server=utf8
max_connections=5000
tmp_table_size=200M
query_cache_size=64M
query_cache_type=1
max_error_count=5000

2.主从库授权

grant replication slave on *.* to 'rep'@'%' identified by 'rep123';

3.主节点备份

mysql -uroot -pmintmath -e "show databases;"| grep -Ev "Database|information_schema|sys|performance_schema|mysql"|xargs mysqldump -uroot -umintmath -B -F -R --master-data=1 --single-transaction --events|gzip >./mysql_$(date +%F).sql.gz    

#主从复制添加:主备份时添加:--master-data=2这个选项，这个选项可以自动帮我们锁表（--lock-all-tables）和识别binlog临界文件，备份完毕后会自动（unlnock tables解锁）。整个过程就不需要我们锁表，查看看临界文件编号，再执行CHANGE MASTER填写binglong位置信息执行到从库，可以说这个过程提高了从库部署效率吧,对于innodb引擎数据来说备份添加--single-transaction进行热备这样就不会进行全局锁表。
#添加--master-data=1参数类似于在备份文件中添加
CHANGE MASTER TO
Master_Log_File='mysql1-bin.000001',
Master_Log_Pos=123;

4.从节点还原

gunzip <mysql_bak.sql.gz | mysql -uroot -pxxx

5.配置从连接主节点

CHANGE MASTER TO MASTER_HOST='172.16.2.10', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='oldboy123';
#注意并没有指定:Master_Log_File和Master_Log_Pos;

6.开启从slave查看从状态

start slave;show slave status\G; 
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes
#Seconds_Behind_Master: 0 主从延迟

7. reset master|reset slave命令使用场景

reset master功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；
show master status;binlog日志将从000001 开始记录

reset slave功能说明:用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；RESET SLAVE将使SLAVE忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。使用场景：当原来的主从关系被破坏之后，从库经过重新初始化后直接连接会报 ERROR 1201的错误，运行reset slave后，重新配置主从连接就可以了；

8.主从差异问题处理

stop slave; set global sql_slave_skip_counter=1; start slave;#主从不同步跳过一个事物进行下一个同步。一个事物可以多条sql组成。
通过从show slave status\G; 记录:mysql-bin.000003及Read_Master_Log_Pos: 12992
在master查找出错开始binlog日志事务组列表：一个row代表一个事务组
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003' from13973 \G
经过列表信息确认出错语句及确定需要跳过的事物
主从不同步问题处理：https://blog.csdn.net/seteor/article/details/17264633

9.mysql主从延迟复制防止悲剧发生

防止误操作导致数据丢失:比如让从库比主库慢1小时。这样，如果在1小时内发现数据重大数据异常，还能补救。
MySQL 5.6 已经支持延迟复制， 可设置备节点的延迟时间， 延迟复制是有意义的，例如防止主节点数据误删，查看数据库历史状态等。 做完主从后，再加上这句： CHANGE MASTER TO MASTER_DELAY = N； 这里的N单位是秒，这样从库则会比主库延时N秒。做完之后，在show slave status的时候，就可以看到SQL_Delay的值。

转载于:https://my.oschina.net/wangyunlong/blog/3040829