本人水平有限，如有不足希望看到的人多多指教
目录
1.1 实验环境介绍
1.2进入从库 Slave 1
1.3这里就假设Slave 1 为同步最快的，将其提升为主库
1.4简要说明（真对同步授权表主从结构）
1.5 从库一切完毕，修改hosts文件
1.6下面我们就再设置另一个slave 让他跟新的主库同步
1.7下面测试我们的成果
神奇的hosts解析
可以写个shell脚本来实现下面的功能，这样能尽量减少网站停机时间
实验环境介绍
系统 Centos 5.5 32
数据库 Mysql 5.0.16
环境介绍
10.0.0.57 mysql主库
10.0.0.105 SLvae 1 模拟主库宕机后提升为Master
10.0.0.103 Slave 2 做从库
最近看群里人说
Mysql 主从同步 主库挂了怎么办?
1）网站测试环境
模拟用户访问网站
2）此刻我停掉主库模拟主机宕机
root@mysql zongjie 16:30:07 # ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:D6:F6:C7
inet addr:10.0.0.57 Bcast:10.0.0.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fed6:f6c7/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:37460 errors:0 dropped:0 overruns:0 frame:0
TX packets:38576 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:5513260 (5.2 MiB) TX bytes:24159718 (23.0 MiB)
Interrupt:59 Base address:0x2000
root@mysql zongjie 16:30:14 # /etc/init.d/mysqld stop
Shutting down MySQL..... [确定]
用户访问
1.2进入从库 Slave 1
①进入从库操作。建议不要立刻停掉从库，如果从主库复制来的sql语句并未完全执行完，此时stop slave 肯定会丢失数据
②提升主库的从库选择，选择与主库延迟最小的从库提为主库
可以查看
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 30371
State: Reconnecting after a failed master event read
这个参数表示，正在等待重新连接主库
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: -310998
State: Has read all relay log; waiting for the slave I/O thread to update it
这个 自然就是本地的sql 线程了，这里的意思是等待I/O进程更新。
Info: NULL
*************************** 3. row ***************************
Id: 4
User: rep
Host: 10.0.0.105:56720
db: NULL
Command: Binlog Dump
Time: 30320
State: Has sent all binlog to slave; waiting for binlog to be updated
因为这里我数据并不是很大，所以这里显示已经，主服务器已经把二进制文件的更新发到从服务器上了，
线程现在很空闲，意思就是本地已经保了主服务器上所有的Binlog了
这样，即使主库挂了，但此时从库上的数据是相对完整的。即使sql进程执行慢，也不用担心数据的大量丢失
Info: NULL
*************************** 4. row ***************************
Id: 6
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 10.0.0.57
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 10552
Relay_Log_File: relay-bin.000019
Relay_Log_Pos: 10026
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 10552
Relay_Log_Space: 10991
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: 2013
Last_IO_Error: error reconnecting to master 'rep@10.0.0.57:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.01 sec)
ERROR:
No query specified
#################################Slave 2 #####################
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 10.0.0.57
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 10552
Relay_Log_File: mysqld-relay-bin.000017
Relay_Log_Pos: 10018
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: No
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: 10552
Relay_Log_Space: 10018
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
1 row in set (0.00 sec)
可以发现 i/o 进程已经停止了
下面就开始进行操作
1.3这里就假设Slave 1 为同步最快的，将其提升为主库
①停掉slave
mysql> system ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:89:45:B9
inet addr:10.0.0.105 Bcast:255.255.255.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe89:45b9/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:49614 errors:0 dropped:0 overruns:0 frame:0
TX packets:32316 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:17092794 (16.3 MiB) TX bytes:5457395 (5.2 MiB)
Interrupt:59 Base address:0x2000
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
②开启从库log-bin选项（从库不建议开启bin log文件）
[root@MYSQL 3307]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
这里可能需要重启下从库
[root@MYSQL 3307]# /data/3306/mysql restart
Restarting MySQL...
Stoping MySQL...
Enter password:
Starting MySQL..
授权一个同步的用户
mysql> grant replication slave on *.* to 'slave'@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
1.4简要说明（真对同步授权表主从结构）
###########简要说明###################
如果这里有同步主库的授权库，可以不用重新授权一个用户，
此时只需要在Slave 2 上进行如下设置即可
mysql> change master to MASTER_HOST='10.0.0.105',MASTER_PORT=3306;
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.09 sec)
mysql> change master to MASTER_HOST='10.0.0.105',MASTER_PORT=3306;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.105
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 3785
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 3922
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
############End#######################
这里有些人说要执行 reset master,感觉没必要。毕竟一般的mysql主库机器性能相对slave比较好，这样等主库维护后，还是要用主库的
1.5从库一切完毕，下面就该在web server 上做了
这里我将hosts 先备份下 方便比较
[root@APACHE etc]# cp hosts hosts.xp
[root@APACHE etc]# diff hosts hosts.xp
7c7
< 10.0.0.105 master.mysql
---
> 10.0.0.57 master.mysql
这里只是修改下master.mysql 地址
本着实用的原则，应该与开发人员协商好，分发hosts 文件对运维来说很简单。对开发也方便，不用记IP地址。一举两得
此刻再次查看web
到这里已经能正常对外提供服务
1.6下面我们就再设置另一个slave 让他跟新的主库同步
mysql> change master to MASTER_HOST='10.0.0.105',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123';
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.00 sec)
mysql> change master to MASTER_HOST='10.0.0.105',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123';
Query OK, 0 rows affected (0.05 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.105
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 3932
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 4069
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在设置Slave 2 的时候 ，可以不需要指定bin log 文件的位置和位置点，毕竟slave 1 和slave 2 都跟MASTER 同步过，所以可以不用再次指定
1.7下面测试我们的成果
主库创建库操作
mysql> system ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:89:45:B9
inet addr:10.0.0.105 Bcast:255.255.255.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe89:45b9/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:51542 errors:0 dropped:0 overruns:0 frame:0
TX packets:36032 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:17240206 (16.4 MiB) TX bytes:10300211 (9.8 MiB)
Interrupt:59 Base address:0x2000
mysql> create database testzhucong;
Query OK, 1 row affected (0.00 sec)
从库验证结果
mysql> system ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:B9:80:C2
inet addr:10.0.0.103 Bcast:255.255.255.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:feb9:80c2/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:42409 errors:0 dropped:0 overruns:0 frame:0
TX packets:28298 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:44970006 (42.8 MiB) TX bytes:2551998 (2.4 MiB)
Interrupt:59 Base address:0x2000
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| hostceshi |
| mysql |
| test |
| testzhucong |
+--------------------+
6 rows in set (0.08 sec)
转载于:https://blog.51cto.com/8time/1047388
2572
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
