Mysql传统复制空库搭建过程中reset slave以及reset slave all对复制的影响
来源:互联网 发布:js 判断是否等于nan 编辑:程序博客网 时间:2024/06/05 05:01
Mysql传统复制空库搭建过程中reset slave以及reset slave all对复制的影响
主库信息
从库信息
操作过程
主库上操作
从库上操作
主库上查看
解决方法
思路探讨
参考资料
主库信息
从库信息
操作过程
主库上操作
从库上操作
主库上查看
解决方法
思路探讨
参考资料
Mysql传统复制空库搭建过程中reset slave以及reset slave all对复制的影响
主库信息
- IP:192.168.1.21
- 版本:5.7.18
- 空库搭建主从
从库信息
- IP:192.168.1.128
- 版本:5.6.36
- 空库搭建主从
操作过程
主库上操作
- 赋予复制账户:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.128' IDENTIFIED by 'repl';
- 查看master信息:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000022 | 612 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库上操作
- 直接指定新的master:
root@localhost : (none) 05:16:09> change master to master_host='192.168.1.21',master_user='repl',master_password='repl',master_log_file='mysql-bin.000022',master_log_pos=612;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
#报错是因为128这台mysql server之前是使用gtid复制指向另一台mysql主库;而且没有经过reset slave清理相关数据,直接指向新的master肯定也会有问题。
- 关闭gtid设置:
[root@host-192-168-1-128 ~]# service mysql stop
Shutting down MySQL.... SUCCESS!
[root@host-192-168-1-128 ~]# vim /etc/my.cnf 在配置文件中关闭gtid
#gtid-mode=on # GTID only
#enforce-gtid-consistency=true # GTID only
或者
enforce_gtid_consistency=OFF
gtid_mode=OFF
[root@host-192-168-1-128 ~]# service mysql start
Starting MySQL.. SUCCESS!
- 重新指定master,并开启slave:
root@localhost : (none) 05:19:34> change master to master_host='192.168.1.21',master_user='repl',master_password='repl',master_log_file='mysql-bin.000022',master_log_pos=612;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
root@localhost : (none) 05:20:21> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
root@localhost : (none) 05:20:37> reset slave;
Query OK, 0 rows affected (0.03 sec)
root@localhost : (none) 05:20:56> start slave;
Query OK, 0 rows affected (0.10 sec)
- 查看复制情况:
root@localhost : (none) 05:20:57> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 612
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 333
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'sbtest'; database doesn't exist' on query. Default database: 'sbtest'. Query: 'drop database sbtest'
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 964
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'sbtest'; database doesn't exist' on query. Default database: 'sbtest'. Query: 'drop database sbtest'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12001
Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170516 05:20:58
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
# 注意Exec_Master_Log_Pos: 154
主库上查看
- 查看主库binlog中的events:
mysql> show binlog events in 'mysql-bin.000022';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 12001 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids | 12001 | 154 | |
| mysql-bin.000022 | 154 | Anonymous_Gtid | 12001 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 219 | Query | 12001 | 317 | drop database sbtest |
| mysql-bin.000022 | 317 | Anonymous_Gtid | 12001 | 382 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 382 | Query | 12001 | 612 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.128' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039' |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
# 对比slave的Exec_Master_Log_Pos: 154,确实会去执行drop database sbtest。问题来了:为什么我指定的master_log_pos=612但是却会执行这个位置之前的binlog日志?
解决方法
- 先关闭slave:
root@localhost : (none) 05:39:13> stop slave;
Query OK, 0 rows affected (0.02 sec)
- 清空master.info文件和relay-log.info 文件以及所有的relay log 文件,all也包括在内存中的信息:
root@localhost : (none) 05:39:17> reset slave all;
Query OK, 0 rows affected (0.08 sec)
- 重新指定master并开启slave:
root@localhost : (none) 05:39:26> change master to master_host='192.168.1.21',master_user='repl',master_password='repl',master_log_file='mysql-bin.000022',master_log_pos=612;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
root@localhost : (none) 05:39:44> start slave;
Query OK, 0 rows affected (0.02 sec)
- 查看复制情况:
root@localhost : (none) 05:39:48> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 612
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 286
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 612
Relay_Log_Space: 459
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12001
Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
思路探讨
- reset slave删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件,但并不会清理存储于内存中的复制信息比如master host, master port, master user, or master password,也就是说如果没有使用change master 命令做重新定向,执行start slave 还是会指向旧的master 上面。
- 在5.5.16 版本以及以后,可以使用 RESET SLAVE ALL 来完全的清理复制连接参数信息。
In MySQL 5.5.16 and later, you can instead use RESET SLAVE ALL to reset these connection parameters - 至于明明指定了master_log_pos=612,从库却执行了这之前的位置Exec_Master_Log_Pos: 154。
猜测:这应该和128这个从库之前指定的是另一个master(192.168.1.12),而这些旧的信息还保存在内存中导致的。 - 当时出现错误的第一反应是用skip去跳过错误,问了小波之后说尝试下reset slave all,才能进一步去了解这其中的区别。
参考资料
https://www.percona.com/blog/2013/04/17/reset-slave-vs-reset-slave-all-disconnecting-a-replication-slave-is-easier-with-mysql-5-5/
https://dev.mysql.com/doc/refman/5.5/en/reset-slave.html
阅读全文
0 0
- Mysql传统复制空库搭建过程中reset slave以及reset slave all对复制的影响
- MySQL的reset slave与reset slave all
- MySQL RESET MASTER与RESET SLAVE和RESET SLAVE ALL
- mysql 清理slave 配置信息 reset slave 和 reset slave all 区别;
- MySQL RESET MASTER与RESET SLAVE
- MySQL主从配置中RESET SLAVE和RESET MASTER的区别详解
- 搭建mysql的master-slave环境(mysql主从复制)
- Mysql Master slave复制
- MySQL Master-Slave 主从复制环境搭建
- MySQL复制中slave延迟监控
- MySQL复制中 slave延迟监控
- 重置mysql主从同步:reset master和reset slave
- mysql 主从复制 建立新的slave
- MySQL的Master-Slave复制配置步骤
- MySQL的Master-Slave复制配置步骤
- RESET MASTER 和RESET SLAVE 命令的使用方法 注意事项
- mysql 主从复制(master-slave)
- MYSQL:master/slave主从复制
- c#.net如何将API返回json对象转换成自己想要的List<T>
- 提高jquery 性能方法整理
- Binder invocation to an incorrect interface 异常分析
- 如何下载tizen源码(图文教程)
- ORACLE查询删除重复记录三种方法
- Mysql传统复制空库搭建过程中reset slave以及reset slave all对复制的影响
- java四种位运算
- win7_32下编译FFmpeg(更新截止版本为ffmpeg-3.2.1.tar.bz2)
- Linux 运行进程实时监控pidstat命令详解
- mybatis使用oracle关键字
- BNUOJ 36012 magic
- jakewharton大神的hugo插件—让你轻松的知道程序方法的运行耗时
- Failed resolution of: Landroid/support/v7/internal/widget/TintManager
- java8__forEach内部迭代(转载)