MySql教程

mysql 异步复制

本文主要是介绍mysql 异步复制,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
  主从配置   1)主库和从库 创建复制用户并授权         异步复制 mysql -uroot -proot create user 'repuser'@'%' identified by 'repuser123';  grant replication slave on *.* to 'repuser'@'%';  flush privileges; select user,host from mysql.user;   2) 修改my.cnf 主库   改bin-address     server-id 二进制日志打开 log_bin=/mysql/log/3306/binlog/mysqldb-binlog  log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index binlog_format=row binlog_rows_query_log_events=on   expire_logs_days = 7 innodb_support_xa =1 binlog_cache_size = 1m max_binlog_size = 2048m log_bin_trust_function_creators = 1 innodb_flush_log_at_trx_commit =1 sync_binlog = 1 transaction-isolation = read-committed   #slave parameter 等着切换的时候用 #relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log #log-slave-updates = 1 #read_only=1 #slave-parallel-type=logical_clock #slave-parallel-workers=4 #master_info_repository=table #relay_log_info_repository=table #relay_log_recovery=1 #slave_skip_errors = ddl_exist_errors #slave_preserve_commit_order=1   备库     改bin-address     server-id log_bin=/mysql/log/3306/binlog/mysqldb-binlog  log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index binlog_format=row binlog_rows_query_log_events=on skip_name_resolve = on expire_logs_days = 7 innodb_support_xa =1 binlog_cache_size = 1m max_binlog_size = 2048m log_bin_trust_function_creators = 1 innodb_flush_log_at_trx_commit =1 sync_binlog = 1 transaction-isolation = read-committed #slave parameter relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log log-slave-updates = 1 read_only=1 slave-parallel-type=logical_clock slave-parallel-workers=4 master_info_repository=table relay_log_info_repository=table relay_log_recovery=1 slave_skip_errors = ddl_exist_errors slave_preserve_commit_order=1     3)重启 主备库 服务 4)主库查看binlog show master status ; 5)从库和master建立连接 stop slave; change master to  master_host='172.17.0.2', master_user='repuser', master_password='repuser123', master_log_file='master-mysql-bin.000001',             master_log_pos= 753; # 异步同步方式   ,在主库 log_file ,log_pos 填上去   start slave ; show slave status \G; 从库显示一个sql线程,和4个work线程 主库显示 dump线程   建好后,主库新建应用使用的用户 create user 'itpux01'@'%' identified by 'itpux01'; grant all privileges on deen.* to 'itpux01'@'%'; flush privileges;s   change master 命令 改传输密码,从库执行 stop slave; change master to master_password='repuser123';     #这个密码改的是 传输用的密码,改掉的话, io线程报错 报no,连接不上,和登录用的用户没关系 start slave;   修改主库ip, stop slave ; change master to  master_host='192.168.0.55', master_user='repuser', master_password='repuser123', master_log_file='',             master_log_pos= ; start slave; 延迟复制:延迟60秒复制到备库,不想就改成0 stop slave ; change master to MASTER_DELAY=60; start slave; 故障恢复: 主库: reset master;   # 最后再考虑这一步     从库: reset master ; reset slave ;            #这两步会将Master_Info 滞空,备库相当于铲掉重来 change master to  master_host='192.168.0.55', master_user='repuser', master_password='repuser123', master_log_file='',             master_log_pos= ; start slave;       6)参数讲解 此时,主备机上同时输入:show processlist;  显示下面两张图是配置成功 图解:dump线程 图解:备机 sql线程和4个调度线程     显示下面三个是一样的,都是786,是说明同步,数据一致 备库: 主库:   图解:sql线程读取的中继日志的位置   图解:sql线程执行到了主库的哪个文件
这篇关于mysql 异步复制的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!