一、主從復制

redhat6.5
master:server1 172.25.35.52
slave：server3 172.25.35.53

master：

[root@server2 ~]# ls
mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@server2 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server2 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
[root@server2 ~]# grep "temporary password" /var/log/mysqld.log  //查找數據庫原始密碼
2018-08-10T13:21:26.521345Z 1 [Note] A temporary password is generated for root@localhost: EUaF<YSGI2ih
[root@server2 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:  //輸入 EUaF<YSGI2ih

The existing password for the user account root has expired. Please set a new password.

New password:  //輸入用戶密碼，密碼要是字母大小寫+特殊字符+數字

Re-enter new password: 
\The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : 

 ... skipping.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done! 
[root@server2 ~]# vim /etc/my.cnf  //末尾添加
server-id=2  //服務器 id,主從需不同
log-bin=mysql-bin  //日志
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@server2 ~]# mysql -pZhanG@2424
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 4
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 replication slave on *.* to student@'172.25.69.%' identified by 'ZhanG@2424';
Query OK, 0 rows affected, 1 warning (0.17 sec)

mysql> show master status;

slave:

初始化過程同server2：
[root@server3 mysql]# vim /etc/my.cnf
server-id=3
[root@server3 mysql]# /etc/init.d/mysqld restart
[root@server3 mysql]# mysql -pZhanG@2424
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 4
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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='172.25.69.2',master_user='student',master_password='ZhanG@2424',master_log_file='mysql-bin.000001',master_log_pos=450;    //與 master 建立認證
Query OK, 0 rows affected, 2 warnings (0.26 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.69.2
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 450
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  //必須為yes
            Slave_SQL_Running: Yes   //必須為yes
            //這里盡顯示部分status

測試:

master:添加數據庫

mysql> create database haha;
Query OK, 1 row affected (0.21 sec)

mysql> use haha;
Database changed
mysql> create table info(
    -> username varchar(10) not null,
    -> password varchar(10) not null);
Query OK, 0 rows affected (0.93 sec)

mysql> insert into info values('user1','111');
Query OK, 1 row affected (0.16 sec)

mysql> insert into info values('user2','222');
Query OK, 1 row affected (0.42 sec)

mysql> select * from haha.info;

slave：查看到同步過來

mysql> select * from haha.info;

二、Gtid主從復制

master：
[root@server2 ~]# vim /etc/my.cnf  //末尾添加如下內容
gtid_mode=ON
enforce-gtid-consistency=true
[root@server2 ~]# /etc/init.d/mysqld restart


slave：
[root@server3 ~]# vim /etc/my.cnf   //末尾添加如下內容
gtid_mode=ON
enforce-gtid-consistency=true
[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 mysql]# mysql -pZhanG@2424
mysql> stop slave;
Query OK, 0 rows affected (0.11 sec)

mysql> change master to master_host='172.25.69.2' , master_user='student' , master_password='ZhanG@2424' , MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

mysql> start slave;
Query OK, 0 rows affected (0.15 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.69.2
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

測試：

master：給數據庫插入內容

mysql> insert into info values('user3','333');
Query OK, 1 row affected (0.22 sec)

mysql> select * from haha.info;

slave：可以同步過來

mysql> select * from haha.info;

三、半同步復制

master：

mysql>  install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.37 sec)

mysql> show global variables like '%semi%';   //查看參數

timeout 10s，超過該時間會轉為異步復制，不能保證數據完全同步

mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global status like '%semi%';

slave:

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%semi%';

mysql> stop slave io_thread; //重啟io線程
Query OK, 0 rows affected (0.11 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

測試：

master：刪除數據庫內容看slave是否同步

mysql> delete from info where username='user3';
Query OK, 1 row affected (0.18 sec)

mysql> select * from haha.info;

slave：數據庫已經刪除，表示同步過來了

mysql> select * from haha.info;

master：可以看到參數有變化

mysql> show global status like '%semi%';

延時測試：

slave：
mysql> stop slave io_thread;  //關掉io線程
Query OK, 0 rows affected (0.11 sec)

master：
mysql> delete from info where username='user2'; //產生了10秒延時，變成異步傳輸
Query OK, 1 row affected (10.22 sec)  //時間變化10秒左右

slave：
mysql> select * from haha.info;

slave：
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from haha.info;

解決SQL、IO狀態為NO的步驟：
Slave： mysql> stop slave;
mysql> reset master;
mysql> reset slave;
mysql> start slave;
解決數據不同步的步驟：
（1）手動將master、slave的表和庫等復制導入，保證數據同步
（2）Master：mysql> reset master; //重置
Slave： mysql> stop slave;
mysql> reset master;
mysql> reset slave;
mysql> start slave;
mysql> show slave status\G;
***************** 1. row *****************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes //必須是yes
//如果都是 yes,表示從庫的 I/O,Slave_SQL 線程都正確開啟.表明數據庫正在同步

四、數據庫優化

server2為server3的master
server3為server4的master

server3:
[root@server3 mysql]# vim /etc/my.cnf
server-id=3
log-bin=mysql-bin
log-slave-updates
gtid_mode=ON
enforce-gtid-consistency=true
[root@server3 mysql]# /etc/init.d/mysqld restart
[root@server3 mysql]# mysql -pZhanG@2424
mysql> grant replication slave on *.* to student@'172.25.69.%' identified by 'ZhanG@2424';
Query OK, 0 rows affected, 1 warning (0.15 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.69.2
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 952
               Relay_Log_File: server3-relay-bin.000006
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server4:
安裝mysql、安全初始化同server2
[root@server4 ~]# /etc/init.d/mysqld start
[root@server4 ~]# vim /etc/my.cnf
server-id=4
gtid_mode=ON
enforce-gtid-consistency=true
[root@server4 ~]# /etc/init.d/mysqld restart
[root@server4 ~]# mysql -pZhanG@2424
mysql> change master to master_host='172.25.69.3', master_user='student', master_password='ZhanG@2424', master_auto_position=1;   //與server3建立連接
Query OK, 0 rows affected, 2 warnings (0.42 sec)

mysql> start slave;
Query OK, 0 rows affected (0.46 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.69.3
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: server4-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
mysql> show databases;

解決數據不同步：
server3:
[root@server3 ~]# mysqldump -p haha > haha.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don’t want to restore GTIDs, pass –set-gtid-purged=OFF. To make a complete dump, pass –all-databases –triggers –routines –events.
[root@server3 ~]# scp haha.sql server4:.
root@server4’s password:
haha.sql 100% 2051 2.0KB/s 00:00
Server4：
[root@server4 ~]# vim haha.sql

[root@server4 ~]# mysql -p < haha.sql
Enter password:
[root@server4 ~]# mysql -pZhanG@2424
mysql> show databases;

測試：

server2：

mysql> use haha;
mysql> select * from haha.info;

mysql> insert into info values('user2','222');
Query OK, 1 row affected (00.12 sec)

mysql> select * from info;

Server3、server4都同步過來

mysql> select * from haha.info;

注：bin log索引文件: /var/lib/mysql/mysql-bin.index
查看二進制日志：mysqlbinlog -vv –base64-output=DECODE-ROWS mysql-bin.000006 //-vv詳細顯示，–base64查看加密的數據

五、并行復制

server3：

mysql> show processlist;

[root@server3 ~]# vim /etc/my.cnf  //添加如下內容
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# mysql -pZhanG@2424
mysql> show processlist;

六、多組復制

[root@server2 ~]# /etc/init.d/mysqld stop
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="cb59c76b-9cc1-11e8-9524-525400aebb10"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.69.2:24901"
loose-group_replication_group_seeds="172.25.69.2:24901,172.25.69.3:24901,172.25.69.4:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="172.25.69.0/24,127.0.0.1/8"

在server3查看UUID：

[root@server2 mysql]# /etc/init.d/mysqld start
[root@server2 mysql]# grep password /var/log/mysqld.log
2018-08-10T17:26:34.931718Z 1 [Note] A temporary password is generated for root@localhost: Gs8tZdr1=KLU
[root@server2 mysql]# mysql -p
Enter password:   //輸入Gs8tZdr1=KLU
mysql> show databases;  //查看數據庫時會報錯，是因為沒有給超級用戶授權
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by 'ZhanG@2424';
Query OK, 0 rows affected (0.18 sec)

mysql> show databases;

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO student@'%' IDENTIFIED BY 'ZhanG@2424';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.84 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='student', MASTER_PASSWORD='ZhanG@2424' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (1.16 sec)

mysql>  INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.30 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.56 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;

Server3、server4步驟和server2步驟基本相同，差別如下:

1、文件/etc/my.cnf中server_id不同loose-group_replication_local_address后面跟的參數是本機ip
2、在進入數據庫后，加載完插件后，(不執行直接執行SET GLOBAL group_replication_bootstrap_group=ON;）START GROUP_REPLICATION;查看成員信息，全部都是ONLINE的即可

server3:

mysql> select * from performance_schema.replication_group_members;

server4:

mysql> select * from performance_schema.replication_group_members;

測試：

server2:創建數據庫

mysql> show databases;

mysql> create database xixi;
Query OK, 1 row affected (0.95 sec)

mysql> show databases;

Server3、server4:同步到數據庫

mysql> show databases;