LJ你是唯一LT
- 浏览: 238701 次
-
最新评论
环境：
原主库：192.168.10.197 ---新从库
原从库：192.168.10.226 ---新主库
1、切换之前确保主从是同步的
原主库（192.168.10.197）：
mysql> show processlist;
+----+-------------+----------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+----------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 1348 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 16 | repli | 192.168.10.226:50357 | NULL | Binlog Dump | 141 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 17 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+----------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 319
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
原从库（192.168.10.226）：
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 14 | root | localhost | NULL | Query | 0 | init | show processlist |
| 15 | system user | | NULL | Connect | 224 | Waiting for master to send event | NULL |
| 16 | system user | | NULL | Connect | 971 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.03 sec)
mysql>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.197
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 319
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
Seconds_Behind_Master: 0
2、修改参数 vi /etc/my.cnf
原主库：
read-only=1
log_slave_updates=1
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
skip-slave-start=1
innodb_flush_log_at_trx_commit = 1
sync-binlog=1
原备库：
#read-only=1 ---注释掉只读模式
3、操作原从库
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.11 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 16 | system user | | NULL | Connect | 9728 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 17 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.05 sec)
mysql> show slave status \G
.....
Slave_IO_Running: No ---这个已停掉
Slave_SQL_Running: Yes
原从库变新主库
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)
mysql> reset master;
Query OK, 0 rows affected (2.26 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.02 sec)
4、操作原主库
mysql> reset master;
Query OK, 0 rows affected (0.15 sec)
mysql> reset slave;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.09 sec)
原主库变新从库
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.10.226',
-> MASTER_USER='repli',
-> MASTER_PASSWORD='repli',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.226
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、删除新从库上的relay-log.info 文件，否则下次重启slave时会报如下错误。
/var/lib/mysql
-rw-rw---- 1 mysql mysql 127 Jan 5 22:31 master.info
-rw-rw---- 1 mysql mysql 62 Jan 5 22:31 relay-log.info
mv relay-log.info /tmp
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。
出现这个这个问题解决方法：
先删掉文件，再重启mysql服务：service mysql restart
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
查看新从库/var/lib/mysql/master.info内容，从新change master
mysql> change master to
-> master_host='192.168.10.226',
-> master_port=3306,
-> master_user='repli',
-> master_password='repli',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
6、重启新主从库，开启slave进程，检查是否正常
[root@localhost mysql]# service mysql restart
Shutting down MySQL... [ OK ]
Starting MySQL.... [ OK ]
新主库
mysql> show processlist;
+----+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
| 4 | repli | 192.168.10.197:56038 | NULL | Binlog Dump | 283 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+----+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
新从库
start slave;
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show processlist |
| 2 | system user | | NULL | Connect | 67 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 67 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.226
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
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: 120
Relay_Log_Space: 456
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: 60
Master_UUID: c954f3a2-21d0-11e4-a4b2-000c2981e58a
Master_Info_File: /var/lib/mysql/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)
新主从同步，切换完成！
[root@localhost mysql]# cat master.info --文件记录的信息
23
mysql-bin.000002
120
192.168.10.226
repli
repli
3306
60
.......
原主库：192.168.10.197 ---新从库
原从库：192.168.10.226 ---新主库
1、切换之前确保主从是同步的
原主库（192.168.10.197）：
mysql> show processlist;
+----+-------------+----------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+----------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 1348 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 16 | repli | 192.168.10.226:50357 | NULL | Binlog Dump | 141 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 17 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+----------------------+------+-------------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 319
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
原从库（192.168.10.226）：
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 14 | root | localhost | NULL | Query | 0 | init | show processlist |
| 15 | system user | | NULL | Connect | 224 | Waiting for master to send event | NULL |
| 16 | system user | | NULL | Connect | 971 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.03 sec)
mysql>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.197
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 319
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
Seconds_Behind_Master: 0
2、修改参数 vi /etc/my.cnf
原主库：
read-only=1
log_slave_updates=1
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
skip-slave-start=1
innodb_flush_log_at_trx_commit = 1
sync-binlog=1
原备库：
#read-only=1 ---注释掉只读模式
3、操作原从库
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.11 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 16 | system user | | NULL | Connect | 9728 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 17 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.05 sec)
mysql> show slave status \G
.....
Slave_IO_Running: No ---这个已停掉
Slave_SQL_Running: Yes
原从库变新主库
mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)
mysql> reset master;
Query OK, 0 rows affected (2.26 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.02 sec)
4、操作原主库
mysql> reset master;
Query OK, 0 rows affected (0.15 sec)
mysql> reset slave;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.09 sec)
原主库变新从库
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.10.226',
-> MASTER_USER='repli',
-> MASTER_PASSWORD='repli',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.226
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、删除新从库上的relay-log.info 文件，否则下次重启slave时会报如下错误。
/var/lib/mysql
-rw-rw---- 1 mysql mysql 127 Jan 5 22:31 master.info
-rw-rw---- 1 mysql mysql 62 Jan 5 22:31 relay-log.info
mv relay-log.info /tmp
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
由于新的slave改变了服务端口和文件路径，分析应该是由于mysql-relay-bin.index中仍然保存着旧relay日志文件的路径，而这些路径下又找不到合适的文件，因此报错。
出现这个这个问题解决方法：
先删掉文件，再重启mysql服务：service mysql restart
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
查看新从库/var/lib/mysql/master.info内容，从新change master
mysql> change master to
-> master_host='192.168.10.226',
-> master_port=3306,
-> master_user='repli',
-> master_password='repli',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
6、重启新主从库，开启slave进程，检查是否正常
[root@localhost mysql]# service mysql restart
Shutting down MySQL... [ OK ]
Starting MySQL.... [ OK ]
新主库
mysql> show processlist;
+----+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
| 4 | repli | 192.168.10.197:56038 | NULL | Binlog Dump | 283 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+----+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
新从库
start slave;
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show processlist |
| 2 | system user | | NULL | Connect | 67 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 67 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.226
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
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: 120
Relay_Log_Space: 456
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: 60
Master_UUID: c954f3a2-21d0-11e4-a4b2-000c2981e58a
Master_Info_File: /var/lib/mysql/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)
新主从同步，切换完成！
[root@localhost mysql]# cat master.info --文件记录的信息
23
mysql-bin.000002
120
192.168.10.226
repli
repli
3306
60
.......
分享到：
发表评论
-
mysql设置外键约束on delete cascade on update cascade
2016-12-09 16:27 3666mysql设置外键约束on delet ... -
mysql权限管理（实例）
2016-05-10 17:21 1483mysql权限管理实例 本文并没有很详细的介绍对具体的对象授 ... -
mysql简单的碎片清理脚本
2016-05-10 16:52 1460mysql简单的碎片清理脚本 #!/bin/bash date ... -
mysql qpress压缩备份恢复
2016-05-03 16:30 6798说明： 1.前面博客已经介绍过gzip压缩方法，备份正常，但后 ... -
mysql xtrabackup在线搭建主从
2016-04-11 14:59 1887使用xtrabackup进行在线的主从搭建： [root@m ... -
mysql xtrabackup在线备份还原（全备+增备）
2016-04-11 14:47 1007工具安装： [root@mysqlserver var]# t ... -
mysql主库清理数据，从库保留
2016-04-01 15:26 1253因为业务需要，想在mysql主库清理一些数据，但从库想要保留， ... -
oracle，postgresql，mysql一些使用上的区别记录
2015-12-16 11:38 01.限制行数： select * from ta where ... -
数据库调优分享-mysql
2015-12-16 10:02 921数据库调优分享------参考一本mysql资料书 日常的困 ... -
mysql 安装-tina
2015-12-08 17:32 0mysql安装-tina 1、准备安装程序（http://ww ... -
mysqldump 只导入数据或只导结构
2015-12-22 10:36 2680[size=small]mysqldump只导出数据或只导出表 ... -
mysql server has gone away
2015-12-10 09:26 837mysql server has gone away，他的意思 ... -
mysql optimize 清理碎片
2015-12-09 09:26 1176---定期清理脚本 0 1 * * 4 root /root ... -
mysql binlog
2015-12-10 09:26 1320mysqld在每个二进制日志 ... -
mysql远程连接设置
2015-12-10 09:25 972远程连接mysql数据库： 连接上以后，通过这台跳转服务器远 ... -
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY''
2015-12-10 09:25 1684[size=small]-实际遇到的问题： Last_SQL ... -
[ERROR] Slave I/O: error connecting to master
2015-12-09 09:26 8068刚配置的MySQL主从，在从机上看到 点击(此处)折叠或打开 ... -
MySQL常用函数
2015-02-05 10:34 502一、字符串类 1、left（str, length） 从左开始 ... -
MySQL触发器简介
2015-02-05 10:33 864一、触发器基本语法 CREATE TRIGGER trigge ... -
MySQL主从搭建
2015-02-05 10:31 753环境简介 master(主）：192.168.12.101 s ...
相关推荐
线上经验谈MySQL主从切换的一般步骤
提供mysql主从配置详细步骤，并说明主从切换的方法与实现步骤
用于在linux操作系统下设置mysql数据库的主从复制，包括了单项主从和双向主从的配置说明
MySql 主从 双主 主主 同步 文档 ，包含每一步操作
使用keepalived实现对mysql主从复制的主备自动切换.docx
这篇搭建文档包括了MySQL的安装，主从配置，主从切换测试，集群的搭建，故障转移测试（自动手动切换），常用命令，基本概念，是一个全方面的文档，是我搭建过程中整理的文档，一些内容是来自互联网的，但是按照这篇...
MHA实现mysql5.6主从切换安装配置教程.docx
主要介绍了shell脚本监控mysql主从状态,如果异常则邮件警告,需要的朋友可以参考下
网上摘抄，自己整理的，亲测通过
2020-07-02 15:08:09,332 INFO [destination = 1-236 , address = /192.168.3.100:3306 , EventParser] MysqlConnection:293 | Register slave RegisterSlaveCommandPacket[reportHost=192.168.3.1,reportPort=63292...
本文实例讲述了MHA实现mysql主从数据库手动切换的方法，分享给大家供大家参考。具体方法如下： 一、准备工作 1、分别在Master和Slave执行如下,方便mha检查复制: 代码如下:grant all privileges on *.* to ‘root’@...
摘要:本文首先介绍了MySQL主从复制架构的优势,包括实现读写分离减轻主库压力、提供容灾冗余、负载均衡提升并发能力、分离报表分析等。然后详细举例阐述了如何通过主从配置实现读写分离、容灾切换、从库负载均衡等高...
举例两个MySql服务器之间切换主从状态，应急数据库服务器故障问题
数据库的价值可见一斑，数据库的存在为人们提供了更快的查询，那么为了更好地做到数据库的高可用，保证持续提供服务，简化DBA操作，节省数据库故障切换的时间，故开发此数据库主从切换自动化系统。 此系统基于MHA做...
使用MySQL+keepalived是一种非常好的解决方案，在MySQL-HA环境中，MySQL互为主从关系，这样就保证了两台 MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换
1、什么是mysql主从同步？ 当master(主)库的数据发生变化的时候，变化会实时的同步到slave(从)库。 2、主从同步有什么好处？ 水平扩展数据库的负载能力。 容错，高可用。Failover(失败切换)/High Availability ...
MySQL主从的优点主要包含以下三个方面： 主库出现问题，可以快速切换到从库提供服务; 可以在从库上执行查询操作，从主库中更新; 实现读写分离可以在从库中执行备份，以避免备份期间影响主库的服务。
MySQL的主从复制是实现应用的高性能，高可用的基础。对于数据库读操作较密集的应用，通过使数据库请求负载均衡分配到不同MySQL服务器，可有效减轻数据库压力。当遇到MySQL单点故障中，也能在短时间内实现故障切换。...