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的情况下，能够自动创建？