MySQL 5.6 的MASTER – SLAVE 主从实例配置及切换 (一）
MySQL 5.6 的MASTER– SLAVE主从实例配置及切换 （一）1. 任务目标近期在研究openstack中的trove，涉及到mysql 的 主从热备的部署和切换，由于流程复杂，为了更好的理解，直接在Linux上部署，测试一下。2. 参考a)MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解2016/5/6http
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的情况下，能够自动创建？
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)