MySQL 5.6 的MASTER – SLAVE 主从实例配置及切换 (一）

MySQL 5.6 的MASTER– SLAVE 主从实例配置及切换 （一）

1. 任务目标

近期在研究openstack中的trove，涉及到 mysql 的 主从热备的部署和切换，

由于流程复杂，为了更好的理解，直接在Linux上部署，测试一下。

2. 参考

a)MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解2016/5/6

http://m.blog.csdn.net/article/details?id=51331244

b)Windows下搭建MySQLMaster Slave 2013-08-09

http://www.cnblogs.com/gaizai/p/3248207.html

c)SHOWSLAVE STATUS 详解2012-02-16

http://blog.csdn.net/shiqidide/article/details/7263652

3.测试环境

机器1: 10.0.0.213

机器2：10.0.0.195

需要注意是防火墙

使用的linux变种是AmazonLinux AMI

安装mysql：

yuminstall -y mysql56-server mysql

关闭防火墙：

serviceiptables stop

这个变种版本没有配置Selinux

检测两台机器间能否ping通。

4.配置MYSQL

在Linux环境下MySQL的配置文件的位置是在/etc/my.cnf

创建数据库

createdatabase userdb;

4.I配置MASTER

MYSQL的root用户的密码初始为空，需要更改为123456，命令参考如下

[root@ip-10-0-0-213etc]# mysqladmin -u root -p password 123456

Enterpassword:

Warning:Using a password on the command line interface can be insecure.

[root@ip-10-0-0-213etc]#

配置文件内容如下

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-linksis recommended to prevent assorted security risks symbolic-links=0 # Settings user and groupare ignored when systemd is used. # If you need to run mysqldunder a different user or group, # customize your systemdunit file for mysqld according to the # instructions inhttp://fedoraproject.org/wiki/Systemd log-bin=mysql-bin server-id=213 binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=mysql # 指定需要同步的数据库 binlog-do-db=userdb [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

重启服务servicemysqld restart

赋予从库权限帐号

mysql-u root -p123456

mysql>

mysql>GRANT FILE ON *.* TO 'root'@'10.0.0.195' IDENTIFIED BY 'mysqlpassword';

QueryOK, 0 rows affected (0.00 sec)

mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.0.0.195' IDENTIFIED BY'mysql password';

QueryOK, 0 rows affected (0.00 sec)

mysql>FLUSH PRIVILEGES;

QueryOK, 0 rows affected (0.00 sec)

mysql>

重启服务，查看主库信息，参考如下：

mysql>show master status;

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

|File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

|mysql-bin.000002 | 120 | userdb |information_schema,cluster,mysql | |

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

1row in set (0.00 sec)

mysql>

4.II配置SLAVER

配置文件内容

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #Disabling symbolic-links is recommended to prevent assortedsecurity risks symbolic-links=0 #Settings user and group are ignored when systemd is used. # Ifyou need to run mysqld under a different user or group, #customize your systemd unit file for mysqld according to the #instructions in http://fedoraproject.org/wiki/Systemd log-bin=mysql-bin server-id=195 binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=mysql #指定需要同步的数据库 replicate-do-db=userdb replicate-ignore-db=mysql log-slave-updates slave-skip-errors=all slave-net-timeout=60 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

重启服务servicemysqld restart

修改对应的MASTER信息

mysql>show slave status;

Emptyset (0.00 sec)

mysql>

mysql>stop slave; ##关闭Slave

QueryOK, 0 rows affected, 1 warning (0.00 sec)

mysql>change master tomaster_host='10.0.0.213',master_user='root',master_password='mysqlpassword',master_log_file='mysql-bin.000004',master_log_pos=120;##粗体蓝色部分，对应Master输出信息

QueryOK, 0 rows affected, 2 warnings (0.04 sec)

mysql>start slave; ##开启Slave

QueryOK, 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.213 Master_User:root Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000004 Read_Master_Log_Pos:120 Relay_Log_File:mysqld-relay-bin.000002 Relay_Log_Pos:283 Relay_Master_Log_File:mysql-bin.000004 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB:userdb 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:120 Relay_Log_Space:457 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:213 Master_UUID:767e164f-0932-11e7-942a-0e8f186b32da 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 toupdate 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) ERROR: No query specified mysql>

5. 测试

5.I 写MASTER

mysql> use userdb; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create tabletest1( id int, name char(20) ); Query OK, 0 rows affected(0.27 sec) mysql> insert into test1( id, name ) values ( 1, 'unknown'); Query OK, 1 row affected(0.02 sec) mysql>

5.II 读SLAVE

mysql> use userdb; Reading table informationfor completion of table and column names You can turn off thisfeature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_userdb | +------------------+ | test1 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> show columns from test1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null |Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * fromtest1; Empty set (0.00 sec) mysql> select * fromtest1; +------+---------+ | id | name | +------+---------+ | 1 | unknown | +------+---------+ 1 row in set (0.00 sec) mysql>

进一步

a) 没有测试，如果SLAVE中没有创建对应的数据库userdb的情况下，能够自动创建？