【MySQL 5.7.19】主从复制
1:安装MySQL5.7.19(主从两台节点全部安装mysql数据库)
本测试环境是基于CentOS 7 测试,具体安装步骤,参见:http://www.xxf-home.net/?p=643
2:主从数据库基本信息
主机名 | IP | 端口 |
---|---|---|
msp | 192.168.31.100 | 2587 |
mss | 192.168.31.200 | 2587 |
3:修改主服务器(master)配置参数
1 2 3 4 5 6 |
vi /etc/my.cnf ## 添加如下内容 log-bin = mysql-bin server-id=100 ## 注:log-bin必须启用二进制日志格式;server-id必须唯一,一般可用服务器ip的尾数 |
4:修改从服务器(salave)配置参数
1 2 3 4 5 6 7 8 |
vi /etc/my.cnf ## 添加如下内容 log-bin = mysql-bin server-id=200 ## 注: ## log-bin 启用二进制日志格式,这一步不是必须。如果从服务器下面还有从服务器,那么需要修改为二进制日志格式; ## server-id必须唯一,一般可用服务器ip的尾数 |
5:重启主、从服务器使其修改生效
1 2 3 4 5 6 7 8 9 10 11 |
## 主服务器 [root@msp ~]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@msp ~]# ## 从服务器 [root@msp ~]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@msp ~]# |
6:在主服务器上创建复制账号
1 2 3 4 5 6 |
[root@msp ~]# mysql -uroot -ppassword ## 创建复制账号,一般情况下单独创建为好,并授权slave mysql> grant replication slave on *.* to 'mysync'@'%' identified by 'oracle'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> |
6:在主服务器查询master状态
1 2 3 4 5 6 7 8 9 10 |
## 记录下日志文件名及Position值,记录后,主服务器就不要在做修改操作了,避免相关值发生变化导致复制配置失败 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> |
7:配置、启动从服务器
1 2 3 4 5 6 7 8 9 10 11 |
[root@mss ~]# mysql -uroot -ppassword ## 配置从服务器的相关参数,即为主服务器的IP、端口、复制账号、密码、日志文件及pos mysql> CHANGE MASTER TO MASTER_HOST='192.168.31.100', MASTER_PORT=2587, MASTER_USER='mysync', MASTER_PASSWORD='oracle', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.00 sec) ## 启动复制功能 mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> |
8:查看从服务器复制状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
## 查看一下Master的IP、端口、复制账号等信息是否正确 ## 最重要的是看看Slave_IO_Running、Slave_SQL_Running这两个参数的值是不是YES,是则代表配置成功,否则失败 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.31.100 Master_User: mysync Master_Port: 2587 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 154 Relay_Log_File: mss-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 154 Relay_Log_Space: 525 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: 100 Master_UUID: 74028e73-7df9-11e7-9421-000c2934a592 Master_Info_File: /data/db/mysql/2587/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> |
9:测试复制
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
## 主库,创建两个数据库dc、dm,在dm中创建t表,并插入一条记录 [root@msp ~]# mysql -uroot -ppassword mysql> create database dc; Query OK, 1 row affected (0.00 sec) mysql> create database dm; Query OK, 1 row affected (0.01 sec) mysql> mysql> use dm; Database changed mysql> mysql> show tables; Empty set (0.00 sec) mysql> create table t(x int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t values(1); Query OK, 1 row affected (0.00 sec) mysql> mysql> show tables; +--------------+ | Tables_in_dm | +--------------+ | t | +--------------+ 1 row in set (0.00 sec) mysql> select * from t; +------+ | x | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> ## 从库验证 [root@msp ~]# mysql -uroot -ppassword mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dc | | dm | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> mysql> use dm; 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> mysql> show tables; +--------------+ | Tables_in_dm | +--------------+ | t | +--------------+ 1 row in set (0.00 sec) mysql> mysql> select * from t; +------+ | x | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> |
小结:整个复制过程比较简单,但是在部署的过程中也不能粗心大意,否则也很容易出现问题
如果需要配置“主从从”复制,也就是A->B->C,那么B库需要增加一个参数log_slave_updates = 1
该参数默认值为OFF,也就是说,在数据库作为从库时,不写binary log。所以“主从从”复制的架构,B库必须设置该参数。