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

ゝ一世哀愁。 2022-10-02 05:46 55阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

**                                                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

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/f384841b652b41d2b579862b7a3b1800.png