MySQL5.7-OCP-配置主从复制(Configuring Replication)
通过配置多实现来做来实验的平台，在一台服务器上，配置4个MySQL实例
1.将现在的MySQL服务器停止
[root@node232 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
2.查看多实例repl.cnf文件
[root@node232 replication]# cat repl.cnf
[mysqld1]
datadir=/var/lib/mysql1
port=3311
socket=/var/lib/mysql1/mysql.sock
server-id=1
user=mysql
log-bin=mysql1-bin
relay-log=mysql1-relay-bin
log-slave-updates=ON
log-error=mysql1
report-host=localhost
report-port=3311
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# gtid-mode=ON
# enforce-gtid-consistency
[mysqld2]
datadir=/var/lib/mysql2
port=3312
socket=/var/lib/mysql2/mysql.sock
server-id=2
user=mysql
log-bin=mysql2-bin
relay-log=mysql2-relay-bin
log-slave-updates=ON
log-error=mysql2
report-host=localhost
report-port=3312
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# gtid-mode=ON
# enforce-gtid-consistency
[mysqld3]
datadir=/var/lib/mysql3
port=3313
socket=/var/lib/mysql3/mysql.sock
server-id=3
user=mysql
log-bin=mysql3-bin
relay-log=mysql3-relay-bin
log-slave-updates=ON
log-error=mysql3
report-host=localhost
report-port=3313
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# gtid-mode=ON
# enforce-gtid-consistency
[mysqld4]
datadir=/var/lib/mysql4
port=3314
socket=/var/lib/mysql4/mysql.sock
server-id=4
user=mysql
log-bin=mysql4-bin
relay-log=mysql4-relay-bin
log-slave-updates=ON
log-error=mysql4
report-host=localhost
report-port=3314
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
# gtid-mode=ON
# enforce-gtid-consistency
3.创建对应的数据库目录：
[root@node232 ~]# mkdir -p /var/lib/mysql{1..4}
[root@node232 lib]# ls -dl mysql*
drwx------. 5 mysql mysql 4096 6月 14 2020 mysql
drwxr-xr-x 2 root root 4096 7月 10 05:06 mysql1
drwxr-xr-x 2 root root 4096 7月 10 05:06 mysql2
drwxr-xr-x 2 root root 4096 7月 10 05:06 mysql3
drwxr-xr-x 2 root root 4096 7月 10 05:06 mysql4
4.对以上4个新数据库目录进行初始化：
[root@node232 lib]# mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql1
2021-07-09T21:10:06.058194Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-07-09T21:10:06.444214Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-09T21:10:06.497889Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-09T21:10:06.510217Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0929a492-e0fa-11eb-969c-000c29cb49a4.
2021-07-09T21:10:06.514130Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-09T21:10:06.515103Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@node232 lib]# mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql2
2021-07-09T21:10:30.037627Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-07-09T21:10:30.623783Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-09T21:10:30.775041Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-09T21:10:30.807214Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 17a51221-e0fa-11eb-9769-000c29cb49a4.
2021-07-09T21:10:30.810015Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-09T21:10:30.811916Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@node232 lib]# mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql3
2021-07-09T21:10:35.549413Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-07-09T21:10:36.321306Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-09T21:10:36.489047Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-09T21:10:36.512264Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1b0b97a2-e0fa-11eb-9869-000c29cb49a4.
2021-07-09T21:10:36.513904Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-09T21:10:36.555212Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@node232 lib]# mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql4
2021-07-09T21:10:42.640914Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-07-09T21:10:43.312039Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-09T21:10:43.581879Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-09T21:10:43.606521Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1f46170f-e0fa-11eb-997a-000c29cb49a4.
2021-07-09T21:10:43.611499Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-09T21:10:43.616400Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
4.1通过mysqld_multi命令，启动以上4个数据库实例：
[root@node232 replication]# mysqld_multi --defaults-file=/server/shell_scripts/replication/repl.cnf start 1-4
4.2.查看多实例的进程：
[root@node232 replication]# ps -ef | grep mysql
mysql 2431 1 71 05:14 pts/1 00:00:05 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql1 --port=3311 --socket=/var/lib/mysql1/mysql.sock --server-id=1 --user=mysql --log-bin=mysql1-bin --relay-log=mysql1-relay-bin --log-slave-updates=ON --log-error=mysql1 --report-host=localhost --report-port=3311 --relay-log-recovery=1 --master-info-repository=TABLE --relay-log-info-repository=TABLE
mysql 2434 1 76 05:14 pts/1 00:00:05 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql2 --port=3312 --socket=/var/lib/mysql2/mysql.sock --server-id=2 --user=mysql --log-bin=mysql2-bin --relay-log=mysql2-relay-bin --log-slave-updates=ON --log-error=mysql2 --report-host=localhost --report-port=3312 --relay-log-recovery=1 --master-info-repository=TABLE --relay-log-info-repository=TABLE
mysql 2437 1 74 05:14 pts/1 00:00:05 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql3 --port=3313 --socket=/var/lib/mysql3/mysql.sock --server-id=3 --user=mysql --log-bin=mysql3-bin --relay-log=mysql3-relay-bin --log-slave-updates=ON --log-error=mysql3 --report-host=localhost --report-port=3313 --relay-log-recovery=1 --master-info-repository=TABLE --relay-log-info-repository=TABLE
mysql 2440 1 73 05:14 pts/1 00:00:05 /usr/local/mysql/bin/mysqld --datadir=/var/lib/mysql4 --port=3314 --socket=/var/lib/mysql4/mysql.sock --server-id=4 --user=mysql --log-bin=mysql4-bin --relay-log=mysql4-relay-bin --log-slave-updates=ON --log-error=mysql4 --report-host=localhost --report-port=3314 --relay-log-recovery=1 --master-info-repository=TABLE --relay-log-info-repository=TABLE
4.3查看对应的端口：
[root@node232 replication]# lsof -i:3311
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2431 mysql 23u IPv4 12878 0t0 TCP *:mcns-tel-ret (LISTEN)
[root@node232 replication]# lsof -i:3312
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2434 mysql 23u IPv4 12883 0t0 TCP *:appman-server (LISTEN)
[root@node232 replication]# lsof -i:3313
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2437 mysql 23u IPv4 12881 0t0 TCP *:uorb (LISTEN)
[root@node232 replication]# lsof -i:3314
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2440 mysql 23u IPv4 12877 0t0 TCP *:uohost (LISTEN)
5.对端口为3311的实例配置终端提示符，方便区别不同的实例，端口为3311的实例做为主从复制的master角色
[root@node232 replication]# mysql -uroot -h127.0.0.1 -P3311 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
root@localhost[(none)]>show variables like '%port%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_support_xa | ON |
| large_files_support | ON |
| port | 3311 |
| report_host | localhost |
| report_password | |
| report_port | 3311 |
| report_user | |
| require_secure_transport | OFF |
+--------------------------+-----------+
8 rows in set (0.01 sec)
root@localhost[(none)]>PROMPT 3311>;
3311>
6.查看3311实例时的show master status的值：
3311>show master status\G;
*************************** 1. row ***************************
File: mysql1-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
7.创建用于复制的repl用户并配置密码和权限：
3311>create user 'repl'@'127.0.0.1' identified by 'Rscpass123.';
Query OK, 0 rows affected (0.01 sec)
3311>grant replication slave on *.* to 'repl'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)
8.在master上创建world数据库并导入数据:
3311>create database world;
Query OK, 1 row affected (0.01 sec)
3311>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
3311>source /server/shell_scripts/replication/world.sql
3311>set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
注意：禁用自动提交选项可加快导入操作。如果每句语句都在 脚本自动提交，导入需要几分钟
9.开启另一个终端，启动端口为3312的实例，做为slave服务器：
[root@node232 ~]# mysql -uroot -h127.0.0.1 -P3312 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
root@localhost[(none)]>prompt 3312>
PROMPT set to '3312>'
3312>
10.在3312端口的数据库实例上，配置从库的复制命令：
3312>change master to master_host='127.0.0.1',master_port=3311,master_log_file='mysql1-bin.000001',master_log_pos=154,master_user='repl',master_password='Rscpass123.';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
3312>show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)
ERROR:
No query specified
11.查看3312数据库是否已自动创建了world数据库：
3312>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
12.开启3312从库的slave线程：
3312>start slave;
Query OK, 0 rows affected (0.01 sec)
13.查看数据库进程列表：
3312>show processlist;
+----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
| 3 | root | localhost:59072 | NULL | Query | 0 | starting | show processlist |
| 4 | system user | | NULL | Connect | 32 | Waiting for master to send event | NULL |
| 5 | system user | | NULL | Connect | 607 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------------+------+---------+------+--------------------------------------------------------+------------------+
14.
过一段时间后，查看从库的world数据库是否创建：
3312>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.01 sec)
到此，一主一从的主从复制配置已完成
Adding a New Slave-配置级联复制
配置级联复制
3311->3312->3313
1.通过mysqldump备份端口为3312从训实例的数据库的所有数据,包含创建复制时的命令
[root@node232 ~]# mysqldump -uroot -p -h127.0.0.1 -P3312 --master-data=2 -B world >/tmp/server2.sql
Enter password:
2.编辑server2.sql备份后的文本文件，补充创建从库复制的命令:
[root@node232 ~]# vim /tmp/server2.sql
备份的原始命令
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql2-bin.000001', MASTER_LOG_POS=1707978;
修改后的命令：
CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3312,MASTER_USER='repl',MASTER_PASSWORD='Rscpass123. ',MASTER_LOG_FILE='mysql2-bin.000001', MASTER_LOG_POS=1707978 ;
3.开启另一个终端，进入端口号为3313的数据库实例，这个实例将把3312做为主库，跟3312建立主从关系
[root@node232 ~]# mysql -uroot -p -h127.0.0.1 -P3313
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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.
root@localhost[(none)]>prompt 3313>
PROMPT set to '3>'
3313>
4.在3313数据库实例中导入上面备份的数据：
3313>source /tmp/server2.sql
5.开启3313数据库实例的复制进程,并查看复制状态：
3313>start slave;
Query OK, 0 rows affected (0.02 sec)
3313>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 3312
Connect_Retry: 60
Master_Log_File: mysql2-bin.000001
Read_Master_Log_Pos: 1707978
Relay_Log_File: mysql3-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: mysql2-bin.000001
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: 1707978
Relay_Log_Space: 529
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: 2
Master_UUID: 17a51221-e0fa-11eb-9769-000c29cb49a4
Master_Info_File: mysql.slave_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)
ERROR:
No query specified
到些，3312与3313的主从复制已创建完成
6.实验：
在3311的主库的world库的city表中删除id>4070的记录,查看3312，3313实例数据同步情况：
在3311数据库主库上
3311>use world;
Database changed
3311>show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City |
| Country |
| CountryLanguage |
+-----------------+
3 rows in set (0.00 sec)
3311>select * from world.City order by id desc limit 10;
+------+--------------+-------------+------------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------+-------------+------------+------------+
| 4079 | Rafah | PSE | Rafah | 92020 |
| 4078 | Nablus | PSE | Nablus | 100231 |
| 4077 | Jabaliya | PSE | North Gaza | 113901 |
| 4076 | Hebron | PSE | Hebron | 119401 |
| 4075 | Khan Yunis | PSE | Khan Yunis | 123175 |
| 4074 | Gaza | PSE | Gaza | 353632 |
| 4073 | Gweru | ZWE | Midlands | 128037 |
| 4072 | Mutare | ZWE | Manicaland | 131367 |
| 4071 | Mount Darwin | ZWE | Harare | 164362 |
| 4070 | Chitungwiza | ZWE | Harare | 274912 |
+------+--------------+-------------+------------+------------+
10 rows in set (0.00 sec)
3312>select * from world.City order by id desc limit 10;
+------+--------------+-------------+------------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------+-------------+------------+------------+
| 4079 | Rafah | PSE | Rafah | 92020 |
| 4078 | Nablus | PSE | Nablus | 100231 |
| 4077 | Jabaliya | PSE | North Gaza | 113901 |
| 4076 | Hebron | PSE | Hebron | 119401 |
| 4075 | Khan Yunis | PSE | Khan Yunis | 123175 |
| 4074 | Gaza | PSE | Gaza | 353632 |
| 4073 | Gweru | ZWE | Midlands | 128037 |
| 4072 | Mutare | ZWE | Manicaland | 131367 |
| 4071 | Mount Darwin | ZWE | Harare | 164362 |
| 4070 | Chitungwiza | ZWE | Harare | 274912 |
+------+--------------+-------------+------------+------------+
10 rows in set (0.00 sec)
3313>select * from world.City order by id desc limit 10;
+------+--------------+-------------+------------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------+-------------+------------+------------+
| 4079 | Rafah | PSE | Rafah | 92020 |
| 4078 | Nablus | PSE | Nablus | 100231 |
| 4077 | Jabaliya | PSE | North Gaza | 113901 |
| 4076 | Hebron | PSE | Hebron | 119401 |
| 4075 | Khan Yunis | PSE | Khan Yunis | 123175 |
| 4074 | Gaza | PSE | Gaza | 353632 |
| 4073 | Gweru | ZWE | Midlands | 128037 |
| 4072 | Mutare | ZWE | Manicaland | 131367 |
| 4071 | Mount Darwin | ZWE | Harare | 164362 |
| 4070 | Chitungwiza | ZWE | Harare | 274912 |
+------+--------------+-------------+------------+------------+
10 rows in set (0.00 sec)
在3311主库上，删除world.city 的ID>4070的记录：
3311>delete from world.City where id>4070;
Query OK, 9 rows affected (0.01 sec)
在3311,3312,3313实例上查看数据同步情况：
3311>select * from world.City order by id desc limit 5;
+------+------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------+-------------+----------------+------------+
| 4070 | Chitungwiza | ZWE | Harare | 274912 |
| 4069 | Bulawayo | ZWE | Bulawayo | 621742 |
| 4068 | Harare | ZWE | Harare | 1410000 |
| 4067 | Charlotte Amalie | VIR | St Thomas | 13000 |
| 4066 | Charleston | USA | South Carolina | 89063 |
+------+------------------+-------------+----------------+------------+
5 rows in set (0.00 sec)
3312>select * from world.City order by id desc limit 5;
+------+------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------+-------------+----------------+------------+
| 4070 | Chitungwiza | ZWE | Harare | 274912 |
| 4069 | Bulawayo | ZWE | Bulawayo | 621742 |
| 4068 | Harare | ZWE | Harare | 1410000 |
| 4067 | Charlotte Amalie | VIR | St Thomas | 13000 |
| 4066 | Charleston | USA | South Carolina | 89063 |
+------+------------------+-------------+----------------+------------+
5 rows in set (0.00 sec)
3313>select * from world.City order by id desc limit 5;
+------+------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------+-------------+----------------+------------+
| 4070 | Chitungwiza | ZWE | Harare | 274912 |
| 4069 | Bulawayo | ZWE | Bulawayo | 621742 |
| 4068 | Harare | ZWE | Harare | 1410000 |
| 4067 | Charlotte Amalie | VIR | St Thomas | 13000 |
| 4066 | Charleston | USA | South Carolina | 89063 |
+------+------------------+-------------+----------------+------------+
5 rows in set (0.00 sec)
以上实例说明，配置数据库级联复制已成功，数据同步正常
视频讲解地址：https://mp.weixin.qq.com/s/MfXWoPo3-5R7vnZ61krY1g
