MySQL系列之七：主从复制(转)
一：实验环境
| IP | 操作系统 | mysql版本号 | |
|---|---|---|---|
| master | 192.168.25.11 | CentOS7 | 5.6.35 |
| slave | 192.168.25.10 | win10 | 5.7.18 |
slave版本需要大于等于master，否则master上的一些操作slave不支持时，会导致数据不一致。
Linux下安装mysql，请参考文章：http://blog.csdn.net/lifetragedy/article/details/53333616
Windows下安装mysql，请参考文章：https://jingyan.baidu.com/article/0f5fb0991fef3c6d8334ea23.html
二：实验步骤
2.1：配置master
2.1.1：修改my.cnf文件
在mysql配置文件，默认路径 /etc/my.cnf 加入如下值：
注意：是加入，不是覆盖。
[mysqld]
server-id=1
log-bin=mysql-bin
含义分别为：
【对mysql服务端】
指定唯一的servr ID
打开二进制日志
2.1.2 重启master
[root@CentOS7 local]# service mysql restart
Shutting down MySQL
.. [ OK ]
Starting MySQL..
[ OK ]2.1.3 验证
–查看此时master状态：
[root@CentOS7 local]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)表示：
二进制日志已开启。
2.2：配置slave
2.2.1：修改MySQL配置my.ini文件
在配置文件my.ini（Linux下为my.cnf）加入如下值：
[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=1
# 如果此slave需要作为其他mysql的master，则需要把下边两行注释打开。
# log-bin=mysql-bin
# log_slave_updates=1server_id 是必须的，也是唯一的，不能和master及其他slave一样。
relay_log 配置中继日志。
read_only 它防止改变数据(除了特殊的线程。
slave没有必要开启二进制日志，但是在一些情况下，必须设置。
例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志log-bin，而且显式的命名mysql-bin，mysql会自动添加数字序号后缀.000001(默认名称为hostname.00000N，但是，如果hostname改变则会出现问题)。
log_slave_updates对于这个参数的解释，mySQL官方是如下解释的：当你的Master同时又是其它Master’的slaver 时，你需要设置此参数。
2.2.2 重启slave
[root@CentOS7 local]# service mysql restart
Shutting down MySQL....
[ OK ]
Starting MySQL.
[ OK ]2.3：在master创建复制用户
订正：之前不小心多打了一个‘\’, 另外新版的MySQL对密码复杂度有要求，请自行设置.
mysql> grant replication slave on *.* to 'rep_user' identified by '123456';
Query OK, 0 rows affected (0.02 sec)2.4：slave连接master
2.4.1：记录二进制日志文件名称及Position
在master上：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 322 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)2.4.2 连接master
在slave上执行：
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.25.11',
-> MASTER_USER='rep_user',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=322;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
2.4.3 启动slave复制
在slave上：
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)2.4.4 主从同步状态检查
在slave上：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.11
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 322
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //IO thread 开始工作
Slave_SQL_Running: Yes //SQL thread 开始工作
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: 322
Relay_Log_Space: 456
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: 1
Master_UUID: 6b2de5b2-e4e5-11e4-b3d2-90b11c4b26eb
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 to update 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)其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
2.4.5 验证主从复制效果
1、在master机器上：
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table t_user(id int,name varchar(64), uuid varchar(64), lastime datetime);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_user(id,name,uuid,lastime) values(1,'Moxiao',uuid(),now());
Query OK, 1 row affected (0.01 sec)2、在slave上查看test库中的数据：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t_user;
+------+-------+--------------------------------------+--------------------+
| id | name | uuid | lastime |
+------+-------+--------------------------------------+--------------------+
| 1 | Moxiao|Ef399055-20c2-11e7-a5ef-000c293fed8e |2017-04-14 12:35:18 |
+------+-------+--------------------------------------+--------------------+
1 row in set (0.00 sec)复制成功！
