mysql主从复制测试,【拥抱开源2】MYSQL主从复制以及切换测试

主库：192.168.1.20

从库：192.168.1.21

grant replication client,replication slave on *.* to rep@'192.168.1.21'identified by 'root';

[root@alice data]# cat /etc/my.cnf

socket=/mysql/mysql.sock

datadir=/mysql/data

socket=/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted securityrisks

symbolic-links=0

default_password_lifetime=0 #密码永不过期

#skip-grant-tables

server-id=1

log-bin=mysql-bin

binlog-do-db=mysql

[mysqld_safe]

log-error=/mysql/mysqld.log

pid-file=/mysql/mysqld.pid

[root@alice log]# cat /etc/my.cnf

datadir=/mysql/data

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted securityrisks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=2

relay-log=relay-bin

read-only=1

change master to master_host='192.168.1.20',master_user='rep',master_password='root';

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.20

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Read_Master_Log_Pos: 339

Relay_Log_Pos: 552

Relay_Master_Log_File: mysql-bin.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

此时应该看懂从库的slave_io_state状态正在等待主库发送事件，并且可以看到master_log_file以及开始接收主库的二进制日志mysql-bin.，证明工作正常。

反过来检查主库的同步状态：

mysql> show master status\G;

*************************** 1. row***************************

Position: 339

Binlog_Do_DB: mysql

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

应该看到bin-log与position与从库的信息一致，证明数据同步点一致。

确保主从的IO-RUNNING都是yes的，此步骤在部署时已确认

mysql> show master status\G;

mysql> show slave status\G;

实际环境中，为了主库没有新数据进入，主库需要获取表锁，此时注意，FTWRL的杀伤力很大，总共有三个步骤，上全局锁，全局commit锁，清理表缓存。所以很容易将生产环境hang住。

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

检查是否master所有的binlog已同步到slave，并且slave都已apply了所有的binlog。

mysql> show processlist;

+----+------+--------------------+-------+-------------+--------+---------------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+--------------------+-------+-------------+--------+---------------------------------------------------------------+------------------+

| 3 | rep | 192.168.1.21:36585 | NULL | Binlog Dump | | Master hassent all binlog to slave; waiting for more updates | NULL |

| 6 | root | localhost | mysql | Query | 0 | starting | show processlist |

+----+------+--------------------+-------+-------------+--------+---------------------------------------------------------------+------------------+

2 rows in set (0.00 sec)

mysql> mysql> show processlist;

+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+

| 6 | system user | | NULL | Connect | | Waiting for master to sendevent | NULL |

| 7 | system user | | NULL | Connect | | Slave has read all relaylog; waiting for more updates | NULL |

| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |

+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+

3 rows in set (0.00 sec)

看到Master has sent all binlog to slave; waiting for more updates与Slave has read all relay log; waiting for more updates，代表同步已经完成，没有正在进行中的进程活动。

mysql> stop slave io_thread;

Query OK, 0 rows affected (0.00 sec)

--修改新主库192.168.1.21的配置文件

[root@alice log]# cat /etc/my.cnf

datadir=/mysql/data

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted securityrisks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=1

#relay-log=relay-bin

log-bin=mysql-bin

binlog-do-db=mysql

read-only=0

##关闭中继日志应用，开始二进制应用，关闭只读模式

[root@alice log]# service mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

mysql> stop slave

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.01 sec)

mysql> reset slave all;

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only | OFF |

+---------------+-------+

1 row in set (0.02 sec)

mysql> show master status\G;

*************************** 1. row ***************************

Position: 154

Binlog_Do_DB: mysql

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

No query specified

grant replication client,replication slave on *.* to rep@'192.168.1.20'identified by 'root';

到此新主库的切换完成。

[root@alice data]# cat /etc/my.cnf

socket=/mysql/mysql.sock

datadir=/mysql/data

socket=/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted securityrisks

symbolic-links=0

#skip-grant-tables

server-id=2

#log-bin=mysql-bin

#binlog-do-db=mysql

relay-log=relay-bin

read-only=1

[mysqld_safe]

log-error=/mysql/mysqld.log

pid-file=/mysql/mysqld.pid

[root@alice data]# service mysqld restart

Shutting down MySQL.. [ OK ]

Starting MySQL. [ OK ]

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)

mysql> change master tomaster_host='192.168.1.21',master_user='rep',master_password='root';

Query OK, 0 rows affected, 2 warnings (0.03 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: 192.168.1.21

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Read_Master_Log_Pos: 467

Relay_Log_Pos: 680

Relay_Master_Log_File: mysql-bin.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

到此新从库切换完成。