title: 实践-CentOS7配置MySQL5.7多源复制.md
date: 2019/09/26
tags:
mysql
OS
Kernel
Mysql version
CentOS 7.3
3.10.0
5.7.19
请确保你的机器已经安装好MySQL-5.7.19软件
IP
PORT
BASEDIR
ROLE
10.116.71.209
3331
/opt/app/mysql/3331-mysql
MasterA
10.116.71.209
3332
/opt/app/mysql/3332-mysql
MasterB
10.116.71.209
3333
/opt/app/mysql/3333-mysql
Slave
[root@localhost mysql]# useradd mysql -s /sbin/nologin# mkdir /opt/app/mysql/3331-mysql# mkdir /opt/app/mysql/3331-mysql/data# mkdir /opt/app/mysql/3331-mysql/log# chown -R mysql.mysql /opt/app/mysql/3331-mysql# # /opt/app/mysql/3331-mysql/my.cnf# #### Basic #####' ' # #### Connect ###### #### InnoDB ###### innodb_undo_logs=128# innodb_undo_tablespaces=3# innodb_undo_log_truncate=1# innodb_max_undo_log_size=256M# audit_offsets=7784,7832,3608,4760,456,360,0,32,64,160,536,7948,4336,3640,3648,3652,6032,2048,8,7016,7056,7040# plugin-load=AUDIT=libaudit_plugin.so# audit_json_file=on# ####log###### ####slave###### gtid_mode=on# enforce_gtid_consistency=1' INDEX_SCAN,HASH_SCAN' # ####parallelreplication#####\\ u@\\ h][\\ d]> \\ _
[myisamchk]
key_buffer_size = 256M
# sort_buffer_size = 256M[root@localhost 3331-mysql]# /opt/app/mysql/bin/mysqld --defaults-file=/opt/app/mysql/3331-mysql/my.cnf --initialize-insecure# # /opt/app/mysql/bin/mysqld_safe --defaults-file=/opt/app/mysql/3331-mysql/my.cnf & mysql> create user replication;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on * .* to ' replication' ' %' ' replication' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1510 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> [root@localhost mysql]# # mkdir /opt/app/mysql/3332-mysql# mkdir /opt/app/mysql/3332-mysql/data# mkdir /opt/app/mysql/3332-mysql/log# chown -R mysql.mysql /opt/app/mysql/3332-mysql# # /opt/app/mysql/3332-mysql/my.cnf# #### Basic #####' ' # #### Connect ###### #### InnoDB ###### innodb_undo_logs=128# innodb_undo_tablespaces=3# innodb_undo_log_truncate=1# innodb_max_undo_log_size=256M# audit_offsets=7784,7832,3608,4760,456,360,0,32,64,160,536,7948,4336,3640,3648,3652,6032,2048,8,7016,7056,7040# plugin-load=AUDIT=libaudit_plugin.so# audit_json_file=on# ####log###### ####slave###### gtid_mode=on# enforce_gtid_consistency=1' INDEX_SCAN,HASH_SCAN' # ####parallelreplication#####\\ u@\\ h][\\ d]> \\ _
[myisamchk]
key_buffer_size = 256M
# sort_buffer_size = 256M[root@localhost 3332-mysql]# /opt/app/mysql/bin/mysqld --defaults-file=/opt/app/mysql/3332-mysql/my.cnf --initialize-insecure# # /opt/app/mysql/bin/mysqld_safe --defaults-file=/opt/app/mysql/3332-mysql/my.cnf & mysql> create user replication;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on * .* to ' replication' ' %' ' replication' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 649 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> [root@localhost mysql]# # mkdir /opt/app/mysql/3333-mysql# mkdir /opt/app/mysql/3333-mysql/data# mkdir /opt/app/mysql/3333-mysql/log# chown -R mysql.mysql /opt/app/mysql/3333-mysql# [client]
port = 3333
socket = /opt/app/mysql/3333-mysql/mysql.sock
[mysqld]
# #### Basic #####' ' # #### Connect ###### #### InnoDB ###### innodb_undo_logs=128# innodb_undo_tablespaces=3# innodb_undo_log_truncate=1# innodb_max_undo_log_size=256M# audit_offsets=7784,7832,3608,4760,456,360,0,32,64,160,536,7948,4336,3640,3648,3652,6032,2048,8,7016,7056,7040# plugin-load=AUDIT=libaudit_plugin.so# audit_json_file=on# ####log###### ####slave###### gtid_mode=on# enforce_gtid_consistency=1' INDEX_SCAN,HASH_SCAN' # ####parallelreplication#####\\ u@\\ h][\\ d]> \\ _
[myisamchk]
key_buffer_size = 256M
# sort_buffer_size = 256M [root@localhost 3333-mysql]# /opt/app/mysql/bin/mysqld --defaults-file=/opt/app/mysql/3333-mysql/my.cnf --initialize-insecure# # /opt/app/mysql/bin/mysqld_safe --defaults-file=/opt/app/mysql/3333-mysql/my.cnf &
配置与masterA、masterB的主从复制关系
mysql> change master to master_host = " 10.116.71.209" " replication" " replication" " mysql-bin.000002" for channel " test_3331" ;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> change master to master_host = " 10.116.71.209" " replication" " replication" " mysql-bin.000002" for channel " test_3332" ;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave ;
Query OK, 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.116.71.209
Master_User: replication
Master_Port: 3331
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1510
Relay_Log_File: relay-log-test_3331.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 1510
Relay_Log_Space: 531
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: 712093331
Master_UUID: 02dc5b82-e025-11e9-8d78-5254001cc693
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: test_3331
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.116.71.209
Master_User: replication
Master_Port: 3332
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 649
Relay_Log_File: relay-log-test_3332.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 649
Relay_Log_Space: 531
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: 712093332
Master_UUID: bd5bf27e-e029-11e9-83c0-5254001cc693
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: test_3332
Master_TLS_Version:
2 rows in set (0.01 sec) mysql> create database m1;
Query OK, 1 row affected (0.00 sec)
mysql> use m1
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table tt (id int not null auto_increment primary key,name varchar(32) );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into tt values (1,' admin' ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt;
+----+-------+
| id | name |
+----+-------+
| 1 | admin |
+----+-------+
1 row in set (0.00 sec) mysql> use m2
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table tt (name varchar(32),age int not null );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into tt values (' laughing' ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt;
+----------+-----+
| name | age |
+----------+-----+
| laughing | 18 |
+----------+-----+
1 row in set (0.00 sec)
mysql> mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| m1 |
| m2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from m1.tt;
+----+-------+
| id | name |
+----+-------+
| 1 | admin |
+----+-------+
1 row in set (0.00 sec)
mysql>
mysql> select * from m2.tt;
+----------+-----+
| name | age |
+----------+-----+
| laughing | 18 |
+----------+-----+
1 row in set (0.00 sec)
mysql>