mysql 5.7多源複製

主機規劃：

IP地址	主機名	資料庫版本	角色	備註
10.4.132.50	k8s01	mysql-5.7.27	master1
10.4.132.42	k8s02	mysql-5.7.27	master2
10.4.132.66	k8s03	mysql-5.7.27	slave

下載地址：

1.下載安裝mysql(master節點和slave節點)

[root@k8s01 soft]# wget

[root@k8s01 soft]# tar xvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@k8s01 soft]# cd /usr/local/

[root@k8s01 local]# mv mysql-5.7.27-linux-glibc2.12-x86_64/ mysql-5.7.27

[root@k8s01 local]# chown -R root:root mysql-5.7.27/

[root@k8s01 local]# cd mysql-5.7.27/

[root@k8s01 mysql-5.7.27]# mkdir data

[root@k8s01 mysql-5.7.27]# useradd -r -M -s /bin/nologin mysql

[root@k8s01 mysql-5.7.27]# chown -R mysql:mysql data/

[root@k8s01 mysql-5.7.27]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.27 --datadir=/usr/local/mysql-5.7.27/data

2019-11-02T04:24:41.908404Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-11-02T04:24:46.687678Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-02T04:24:47.428823Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-02T04:24:47.487404Z 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: b42cef88-fd28-11e9-a5cc-000c29ee86d5.

2019-11-02T04:24:47.488204Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-02T04:24:47.612739Z 1 [Note] A temporary password is generated for root@localhost: 3m;5yQ_7T#jc --登陸密碼

[root@k8s01 mysql-5.7.27]# cp -a support-files/mysql.server /etc/init.d/mysqld

[root@k8s01 mysql-5.7.27]# chkconfig --add mysqld

[root@k8s01 mysql-5.7.27]# chkconfig mysqld on

[root@k8s01 mysql-5.7.27]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql-5.7.27

datadir=/usr/local/mysql-5.7.27/data

[root@k8s01 mysql-5.7.27]# vim /etc/my.cnf

[mysqld]

basedir=/usr/local/mysql-5.7.27

datadir=/usr/local/mysql-5.7.27/data

socket=/tmp/mysql.sock

symbolic-links=0

[mysqld_safe]

log-error=/usr/local/mysql-5.7.27/data/mariadb.log

pid-file=/usr/local/mysql-5.7.27/data/mariadb.pid

[root@k8s01 mysql-5.7.27]# /etc/init.d/mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.Logging to '/usr/local/mysql-5.7.27/data/mariadb.log'.

... SUCCESS!

[root@k8s01 mysql-5.7.27]# vim /etc/profile

export PATH=$PATH:/usr/local/mysql-5.7.27/bin

[root@k8s01 mysql-5.7.27]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.27

Copyright (c) 2000, 2019, 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> set password=password('System135');

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@k8s01 mysql-5.7.27]# mysql -u root -pSystem135

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql>

2.兩個master節點開啟binlog引數

[root@k8s01 mysql-5.7.27]# vim /etc/my.cnf

[mysqld]

server_id=10       --注意server id兩個master和slave節點不能一樣

binlog_format=ROW

max_binlog_size=2G

sync_binlog=1

binlog_cache_size=64M

log_bin=bin-log

log_bin_index=bin-index

[root@k8s01 mysql-5.7.27]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

[root@k8s01 mysql-5.7.27]# ll data/bin-*

-rw-r----- 1 mysql mysql 34 Nov 2 12:48 data/bin-index.index

-rw-r----- 1 mysql mysql 177 Nov 2 12:48 data/bin-log.000001

-rw-r----- 1 mysql mysql 154 Nov 2 12:48 data/bin-log.000002

[root@k8s01 mysql-5.7.27]#

3.在兩個master節點建立同步使用者

[root@k8s01 mysql-5.7.27]# mysql -u root -pSystem135

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> grant all privileges on *.* to repl@'%' identified by '123456';      --前面*.*可以選擇需要同步的庫和表，後面*可以選擇slave從庫的IP地址

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+----------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+-------------------+

| bin-log.000002 | 885 | | | |

+----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>

4.在slave從庫新增兩個master節點資訊

[root@k8s03 ~]# vim /etc/my.cnf

server_id=30

master-info-repository=TABLE          --從庫必須新增兩個引數，否則有異常

relay-log-info-repository=TABLE

[root@k8s03 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

[root@k8s03 ~]# mysql -u root -pSystem135

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> CHANGE MASTER TO MASTER_HOST='10.4.132.50',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin-log.000002',MASTER_LOG_POS=885 FOR CHANNEL 'k8s01';

Query OK, 0 rows affected, 2 warnings (0.14 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.4.132.42',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin-log.000001',MASTER_LOG_POS=589 FOR CHANNEL 'k8s02';

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave for channel 'k8s01';

Query OK, 0 rows affected (0.07 sec)

mysql> start slave for channel 'k8s02';

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status for channel 'k8s01' \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.4.132.50

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: bin-log.000002

Read_Master_Log_Pos: 885

Relay_Log_File: k8s03-relay-bin-k8s01.000002

Relay_Log_Pos: 318

Relay_Master_Log_File: bin-log.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

mysql> show slave status for channel 'k8s02' \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.4.132.42

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: bin-log.000001

Read_Master_Log_Pos: 2159

Relay_Log_File: k8s03-relay-bin-k8s02.000002

Relay_Log_Pos: 318

Relay_Master_Log_File: bin-log.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

mysql>

5.驗證主從資料是否同步

master節點(k8s01)：

mysql> system hostname

k8s01

mysql> create database test1 charset utf8;

Query OK, 1 row affected (0.41 sec)

mysql> use test1

Database changed

mysql> create table t1 (a int);

Query OK, 0 rows affected (2.11 sec)

mysql> insert into t1 values(1),(2),(3);

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+------+

| a |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

mysql>

master節點(k8s02)：

mysql> system hostname

k8s02

mysql> create database test2 charset utf8;

Query OK, 1 row affected (0.03 sec)

mysql> use test2

Database changed

mysql> create table t2 (a int);

Query OK, 0 rows affected (0.28 sec)

mysql> insert into t2 values(4),(5),(6);

Query OK, 3 rows affected (4.71 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t2;

+------+

| a |

+------+

| 4 |

| 5 |

| 6 |

+------+

3 rows in set (0.00 sec)

mysql>

slave節點：

mysql> system hostname

k8s03

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test1 |

| test2 |

+--------------------+

6 rows in set (0.06 sec)

mysql> select * from test1.t1 ;

+------+

| a |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.04 sec)

mysql> select * from test2.t2;

+------+

| a |

+------+

| 4 |

| 5 |

| 6 |

+------+

3 rows in set (0.00 sec)

mysql>

6.主從狀態系統表（ slave節點）

mysql> use performance_schema

mysql> use mysql