本文主要是介绍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 异步复制的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!