mysql基于GTID主從復制+并行復制+增強半同步復制+讀寫分離

GTID即全局事務ID（global transaction identifier），GTID實際上是由UUID+TID組成的。其中UUID是一個MySQL實例的唯一標識。TID代表了該實例上已經提交的事務數量，并且隨著事務提交單調遞增，所以GTID能夠保證每個MySQL實例事務的執行（不會重復執行同一個事務，并且會補全沒有執行的事務）。下面是一個GTID的具體形式：
4e659069-3cd8-11e5-9a49-001c4270714e:1-77
通過GTID來進行主從復制，不用進行二進制日志與master POS號的設定

半同步復制的原理
1、當Slave主機連接到Master時，能夠查看其是否處于半同步復制的機制。
2、當Master上開啟半同步復制的功能時，至少應該有一個Slave開啟其功能。此時，一個線程在Master上提交事務將受到阻塞，直到得知一個已開啟半同步復制功能的Slave已收到此事務的所有事件，或等待超時。
3、當一個事務的事件都已寫入其relay-log中且已刷新到磁盤上，Slave才會告知已收到。
4、如果等待超時，也就是Master沒被告知已收到，此時Master會自動轉換為異步復制的機制。當至少一個半同步的Slave趕上了，Master與其Slave自動轉換為半同步復制的機制。
5、半同步復制的功能要在Master，Slave都開啟，半同步復制才會起作用；否則，只開啟一邊，它依然為異步復制。

同步，異步，半同步復制的對比
同步復制：Master提交事務，直到事務在所有的Slave都已提交，此時才會返回客戶端，事務執行完畢。缺點：完成一個事務可能會有很大的延遲。

異步復制：當Slave準備好才會向Master請求binlog。缺點：不能保證一些事件都能夠被所有的Slave所接收。

半同步復制：半同步復制工作的機制處于同步和異步之間，Master的事務提交阻塞，只要一個Slave已收到該事務的事件且已記錄。它不會等待所有的Slave都告知已收到，且它只是接收，并不用等其完全執行且提交。

master:server1:172.25.11.1
slave:server2:172.25.11.2

server2 stop slave;

server1 添加gtid模式

vim /etc/my.cnf

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id=1
gtid-mode=on
enforce-gtid-consistency=1

grant replication slave on *.* to repl@'172.25.11.%' identified by 'Yakexi+007';

Flush privileges;

show master status;

systemctl restart mysqld

server2

im /etc/my.cnf

server-id=2
gtid-mode=on
enforce-gtid-consistency=1

mysql> change master to master_host='172.25.11.1',
      > master_user='repl',    # 授權用戶
      > master_password='Yakexi+007',   # 授權用戶的密碼
      > master_log_file='mysql-bin.000002', # master的二進制文件
      > master_log_pos=1247;

start slave;

systemctl restart mysqld

下面加入半同步

主從庫都要檢查

show variables like 'have_dynamic_loading';

server1

主庫

install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=ON;
show variables like '%semi%';

server2從庫

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=ON;
show variables like '%semi%';

server1查看

show status like '%rpl_semi_sync%';

on為打開

slave 重起IO

stop slave io_thread;

start slave io_thread;

通過關閉slave io_thread 來進行測試

slave端關閉IO線程

stop slave io_thread;

master端進行測試

show databases;

現在三臺

server4 5 6

4 master

5 6 slave 1 2

slave并行復制

[root@localhost mysql]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=2 ##服務器標識

binlog_format=row

gtid_mode=ON ## 開啟gtid模式

enforce-gtid-consistency=true ## 強制gtid復制

slave-parallel-type=LOGICAL_CLOCK ##開啟邏輯時鐘的復制

slave-parallel-workers=10 ##最大線程10

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

保存退出并重啟mysql

[root@localhost mysql]# systemctl restart mysqld

這時已經有了10個線程

主庫master配置binlog_group_commit

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

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=1 ##服務器標識

binlog_format=row

log-bin=mysql-bin ##開啟二進制日志

gtid_mode=ON ## 開啟gtid模式

enforce-gtid-consistency=true ## 強制gtid復制

rpl_semi_sync_master_enabled = 1

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_timeout = 1000 # 1s

##不配置binlog_group_commit從庫無法做到基于事物的并行復制。

binlog_group_commit_sync_delay = 100

binlog_group_commit_sync_no_delay_count = 10

##為了數據安全再配置

sync_binlog=1

innodb_flush_log_at_trx_commit=1

保存退出并重啟mysql

[root@localhost mysql]# systemctl restart mysqld

進入slave1數據庫,查看優化項

mysql> use mysql;

Database changed

mysql> show tables;

|....................................|

| slave_worker_info |

查看4個線程

五、增強半同步

半同步復制

默認情況下，MySQL的復制是異步的，master將新生成的binlog發送給各 slave后，無需等待slave的ack回復(slave將接收到的binlog寫進relay log后才會回復ack)，直接就認為這次DDL/DML成功了。半同步復制(semi-synchronous replication)是指master在將新生成的binlog發送給各slave時，只需等待一個(默認)slave返回的ack信息就返回成功。

MySQL 5.7對半同步復制作了大改進，新增了一個master線程。在MySQL 5.7以前，master上的binlog dump線程負責兩件事：dump日志給slave的io_thread；接收來自slave的ack消息。它們是串行方式工作的。在MySQL 5.7中，新增了一個專門負責接受ack消息的線程ack collector thread。這樣master上有兩個線程獨立工作，可以同時發送binlog到slave和接收slave的ack。還新增了幾個變量，其中最重要的是 rpl_semi_sync_master_wait_point ，它使得MySQL半同步復制有兩種工作模型。解釋如下。

半同步復制的兩種類型

從MySQL 5.7.2開始，MySQL支持兩種類型的半同步復制。這兩種類型由變量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前沒有該變量)控制，它有兩種值：AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之后，默認值為AFTER_SYNC，在此版本之前，等價的類型為AFTER_COMMIT。這個變量控制的是master何時提交、何時接收 ack以及何時回復成功信息給客戶端的時間點。

AFTER_SYNC模式：master將新的事務寫進binlog(buffer)，然后發送給slave，再sync到自己的binlog file(disk)。之后才允許接收slave的ack回復，接收到ack之后才會提交事務，并返回成功信息給客戶端。
AFTER_COMMIT模式：master將新的事務寫進binlog(buffer)，然后發送給slave，再sync到自己的binlog file(disk)，然后直接提交事務。之后才允許接收slave的ack回復，然后再返回成功信息給客戶端。

畫圖理解就很清晰。(前提：已經設置了sync_binlog=1，否則binlog刷盤時間由操作系統決定)

再來分析下這兩種模式的優缺點。

AFTER_SYNC：

- 對于所有客戶端來說，它們看到的數據是一樣的，因為它們看到的數據都是在接收到slave的ack后提交后的數據。
- 這種模式下，如果master突然故障，不會丟失數據，因為所有成功的事務都已經寫進slave的relay log中了，slave的數據是最新的。

AFTER_COMMIT：

- 不同客戶端看到的數據可能是不一樣的。對于發起事務請求的那個客戶端，它只有在master提交事務且收到slave的ack后才能看到提交的數據。但對于那些非本次事務的請求客戶端，它們在master提交后就能看到提交后的數據，這時候master可能還沒收到slave的ack。
- 如果master收到ack回復前，slave和master都故障了，那么將丟失這個事務中的數據。

在MySQL 5.7.2之前，等價的模式是 AFTER_COMMIT ，在此版本之后，默認的模式為 AFTER_SYNC ，該模式能最大程度地保證數據安全性，且性能上并不比 AFTER_COMMIT 差。

master與Slave命令行加載模塊

master加載

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.05 sec)

mysql> show plugins;

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

| Name | Status | Type | Library | License |

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

| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |

| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |

|。。。。。。。。。。。。。。。。。。。。。。。。。。。。。|

| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |

| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |

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

46 rows in set (0.00 sec)

查看加載模塊信息

mysql> show variables like '%rpl_semi%';

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_for_slave_count | 1 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_SYNC |

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

6 rows in set (0.01 sec)

mysql> set global rpl_semi_sync_master_enabled=1; ##global全局啟動半同步

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%rpl_semi%';

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_for_slave_count | 1 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_SYNC |

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

6 rows in set (0.01 sec)

修改半同步默認超時時間

mysql> set global rpl_semi_sync_master_timeout = 1000;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%rpl_semi%';

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

| Variable_name | Value |

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

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 1000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_for_slave_count | 1 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_SYNC |

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

6 rows in set (0.01 sec)

########################## 以上的啟動方式是在命令行操作 ########################################

slave1加載模塊

mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so'; ##加載slave模塊

mysql> set global rpl_semi_sync_slave_enabled=1; ##開啟slave模塊

mysql> show variables like '%rpl_semi%'; ##查看信息

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

| Variable_name | Value |

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

| rpl_semi_sync_slave_enabled | ON |

| rpl_semi_sync_slave_trace_level | 32 |

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

2 rows in set (0.01 sec)

mysql> STOP SLAVE IO_THREAD; ##關閉slaveIO線程

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 8

Current database: *** NONE ***

Query OK, 0 rows affected (0.01 sec)

mysql> START SLAVE IO_THREAD; ##開啟slaveIO線程

Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_status | ON |

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

1 row in set (0.00 sec)

########################## 以上的啟動方式是在命令行操作 ########################################

配置文件中加載。

主Master：配置文件里加載semisync_master.so

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

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=1 ##服務器標識

binlog_format=row

log-bin=mysql-bin ##開啟二進制日志

gtid_mode=ON

enforce-gtid-consistency=true

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_master_timeout = 1000 # 1s

保存退出并重啟mysql

[root@localhost mysql]# systemctl restart mysqld

Slave1：配置文件里加載semisync_slave.so，并開啟bin-log日志

[root@localhost mysql]# vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=2 ##服務器標識

binlog_format=row

gtid_mode=ON ## 開啟gtid模式

log-bin=mysql-bin-slave1 ##開啟bin-log日志

log-slave-updates=ON

enforce-gtid-consistency=true ## 強制gtid復制

slave-parallel-type=LOGICAL_CLOCK ##開啟邏輯時鐘的復制

slave-parallel-workers=4 ##最大線程16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

plugin-load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

保存退出并重啟mysql

[root@localhost mysql]# systemctl restart mysqld

授權：

mysql> grant REPLICATION SLAVE ON *.* to otter@'192.168.148.%' identified by '!tp!gNp667aPT';

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

刷新權限

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

再加一臺slave2

slave2：配置server-id，開啟gtid

在slave2上配置server-id，開啟gtid

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

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=3 ##服務器標識

binlog_format=row

gtid_mode=ON ## 開啟gtid模式

enforce-gtid-consistency=true ## 強制gtid復制

保存并退出，重啟mysql。

slave1使用mysqldump全備份數據庫傳給slave2

在slave2上指定master為slave1

mysql>change master to master_host='192.168.148.61',

master_user='otter',

master_password='!tp!gNp667aPT',

master_auto_position=1;

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

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

Slave_IO_State:

Master_Host: 192.168.148.61

Master_User: otter

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

Slave_SQL_Running: Yes

##查看狀態發現有報錯：Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

解決方法：

在slave1上進行mysqldump整庫全備份，mysqldump -uroot -p密碼 -A >all.sql

然后打開all.sql我們可以看到如下語句：

[root@localhost ~]# more all.sql

-- MySQL dump 10.13 Distrib 5.7.22, for Linux (x86_64)

--

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 5.7.22-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

SET @@SESSION.SQL_LOG_BIN= 0;

--

-- GTID state at the beginning of the backup

--

SET @@GLOBAL.GTID_PURGED='4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,

4e552c02-8345-11e8-b571-000c294897b5:1-487';

--

-- Current Database: `mysql`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;

--

-- Table structure for table `columns_priv`

--

DROP TABLE IF EXISTS `columns_priv`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

SET @@GLOBAL.GTID_PURGED='4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,

4e552c02-8345-11e8-b571-000c294897b5:1-487';

此值即為slave1上gtid_executed的值。

在slave2上恢復slave1的備份。

mysql> source all.sql

mysql> reset master; ##在slave上做一下reset master來清除gtid的一些信息。

Query OK, 0 rows affected (0.01 sec)
 
mysql> set global gtid_purged='4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,4e552c02-8345-11e8-b571-000c294897b5:1-487';

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;

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

File: mysql-bini-slave2.000001

Position: 154

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,

4e552c02-8345-11e8-b571-000c294897b5:1-487

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> start slave; ##啟動slave。

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G; ##查看狀態恢復正常。雙YES表示恢復正常。

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.148.61

Master_User: otter

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin-slave1.000001

Read_Master_Log_Pos: 1071

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 435

Relay_Master_Log_File: mysql-bin-slave1.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

master進行一條事務，slave1和slave2同步

master創建個數據庫 aaa

mysql> show databases;

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

| Database |

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

| information_schema |

| kkk |

| mysql |

| performance_schema |

| sys |

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

5 rows in set (0.00 sec)

mysql> create database aaa;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| aaa |

| kkk |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.00 sec)

slave1查看是否同步。

mysql> show databases;

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

| Database |

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

| information_schema |

| aaa |

| kkk |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.01 sec)

slave2查看是否同步。

mysql> show databases;

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

| Database |

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

| information_schema |

| aaa |

| kkk |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.00 sec)

七、讀寫分離

安裝MySQL中間件ProxySQL。

ProxySQL是用C++語言開發的，雖然也是一個輕量級產品，但性能很好(據測試，能處理千億級的數據)，功能也足夠，能滿足中間件所需的絕大多數功能，包括：

最基本的讀/寫分離，且方式有多種。

可定制基于用戶、基于schema、基于語句的規則對SQL語句進行路由。換句話說，規則很靈活。基于schema和與語句級的規則，可以實現簡單的sharding。

可緩存查詢結果。雖然ProxySQL的緩存策略比較簡陋，但實現了基本的緩存功能，絕大多數時候也夠用了。此外，作者已經打算實現更豐富的緩存策略。

監控后端節點。ProxySQL可以監控后端節點的多個指標，包括：ProxySQL和后端的心跳信息，后端節點的read-only/read-write，slave和master的數據同步延遲性(replication lag)。

注意點：slave節點需要設置read_only=1。

# 以下是slave1的配置文件

server-id=2 ##服務器標識

binlog_format=row

log-bin=mysql-bin-slave1

log-slave-updates=ON

gtid_mode=ON ## 開啟gtid模式

enforce-gtid-consistency=true ## 強制gtid復制

slave-parallel-type=LOGICAL_CLOCK ##開啟邏輯時鐘的復制

slave-parallel-workers=4 ##最大線程16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

plugin-load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

read_only=1

# 以下是slave2的配置文件

server-id=3 ##服務器標識

log-bin=mysql-bini-slave2

binlog_format=row

gtid_mode=ON ## 開啟gtid模式

enforce-gtid-consistency=true ## 強制gtid復制

read_only=1

安裝ProxySQL
以 CentOS 7 的 rpm 包為例。

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo

[proxysql_repo] name= ProxySQL

baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever

gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

EOF

然后直接安裝即可。

yum -y install proxysql

[root@localhost ~]# systemctl start proxysql ##啟動 proxysql

[root@localhost ~]# systemctl status proxysql ##查看 proxysql狀態

Active: active (running) since 四 2018-07-19 18:04:48 CST; 40min ago

啟動后會監聽兩個端口，

默認為6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL對外提供服務的端口。

[root@localhost ~]# netstat -tnlp

tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 977/proxysql

tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 977/proxysql

向ProxySQL中添加MySQL節點

然后使用mysql客戶端連接到ProxySQL的管理接口(admin interface)，該接口的默認管理員用戶和密碼都是admin。

[root@localhost ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

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 16

Server version: 5.5.30 (ProxySQL Admin Module)

admin> show databases;

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

| seq | name | file |

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

| 0 | main | |

| 2 | disk | /var/lib/proxysql/proxysql.db |

| 3 | stats | |

| 4 | monitor | |

| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |

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

5 rows in set (0.00 sec)

ProxySQL提供了幾個庫，每個庫都有各自的意義。主要修改main和monitor數據庫中的表。

admin> show tables from main;

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

| tables |

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

| global_variables |

| mysql_collations |

| mysql_group_replication_hostgroups |

| mysql_query_rules |

| mysql_query_rules_fast_routing |

| mysql_replication_hostgroups |

| mysql_servers |

| mysql_users |

| proxysql_servers |

| runtime_checksums_values |

| runtime_global_variables |

| runtime_mysql_group_replication_hostgroups |

| runtime_mysql_query_rules |

| runtime_mysql_query_rules_fast_routing |

| runtime_mysql_replication_hostgroups |

| runtime_mysql_servers |

| runtime_mysql_users |

| runtime_proxysql_servers |

| runtime_scheduler |

| scheduler |

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

20 rows in set (0.00 sec)

admin> show tables from monitor;

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

| tables |

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

| mysql_server_connect_log |

| mysql_server_group_replication_log |

| mysql_server_ping_log |

| mysql_server_read_only_log |

| mysql_server_replication_lag_log |

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

5 rows in set (0.00 sec)

runtime_開頭的是運行時的配置，這些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_ 表，修改后必須執行LOAD ... TO RUNTIME才能加載到RUNTIME生效，執行save ... to disk才能將配置持久化保存到磁盤。

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.148.62',3306);

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.148.61',3306);

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.148.64',3306);

#使用insert語句添加主機到mysql_servers表中，其中：hostgroup_id 10 表示寫組，20表示讀組。

查看這3個節點是否插入成功，以及它們的狀態。

admin> select * from mysql_servers\G

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

hostgroup_id: 10

hostname: 192.168.148.62

port: 3306

status: ONLINE

weight: 1

compression: 0

max_connections: 1000

max_replication_lag: 0

use_ssl: 0

max_latency_ms: 0

comment:

*************************** 2. row ***************************

hostgroup_id: 20

hostname: 192.168.148.61

port: 3306

status: ONLINE

weight: 1

compression: 0

max_connections: 1000

max_replication_lag: 0

use_ssl: 0

max_latency_ms: 0

comment:

*************************** 3. row ***************************

hostgroup_id: 20

hostname: 192.168.148.64

port: 3306

status: ONLINE

weight: 1

compression: 0

max_connections: 1000

max_replication_lag: 0

use_ssl: 0

max_latency_ms: 0

comment:

3 rows in set (0.00 sec)

修改后，加載到RUNTIME，并保存到disk。

admin> load mysql servers to runtime; admin> save mysql servers to disk;

監控后端MySQL節點

添加節點之后，還需要監控后端節點。對于后端是主從復制的環境來說，這是必須的，因為ProxySQL需要通過每個節點的read_only值來自動調整它們是屬于讀組還是寫組。

首先在后端master節點上創建一個用于監控的用戶名(只需在master上創建即可，因為會復制到slave上)，這個用戶名只需具有USAGE權限即可。如果還需要監控復制結構中slave是否嚴重延遲于master(這個俗語叫做"拖后腿"，術語叫做"replication lag")，則還需具備replication client權限。這里直接賦予這個權限。

# 在master上執行：

mysql> create user monitor@'192.168.148.%' identified by 'P@ssword1!'; mysql> grant replication client on *.* to monitor@'192.168.148.%';

#然后回到ProxySQL上配置監控。

admin> set mysql-monitor_username='monitor'; admin> set mysql-monitor_password='P@ssword1!';

#修改后，加載到RUNTIME，并保存到disk。

admin> load mysql variables to runtime; admin> save mysql variables to disk;

#驗證監控結果：ProxySQL監控模塊的指標都保存在monitor庫的log表中。

以下是連接是否正常的監控(對connect指標的監控)：(在前面可能會有很多connect_error，這是因為沒有配置監控信息時的錯誤，配置后如果connect_error的結果為NULL則表示正常)

admin> select * from mysql_server_connect_log;

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

| hostname | port | time_start_us | connect_success_time_us | connect_error |

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

| 192.168.148.61 | 3306 | 1532003209576812 | 1113 | NULL |

| 192.168.148.62 | 3306 | 1532003209587641 | 428 | NULL |

| 192.168.148.64 | 3306 | 1532003209598596 | 6290 | NULL |

| 192.168.148.62 | 3306 | 1532003749593625 | 676 | NULL |

| 192.168.148.64 | 3306 | 1532003749604600 | 1459 | NULL |

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

30 rows in set (0.00 sec)

#以下是對心跳信息的監控(對ping指標的監控)：

admin> select * from mysql_server_ping_log;

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

| hostname | port | time_start_us | ping_success_time_us | ping_error |

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

| 192.168.148.61 | 3306 | 1532003210786402 | 717 | NULL |

| 192.168.148.64 | 3306 | 1532003610811775 | 382 | NULL |

| 192.168.148.61 | 3306 | 1532003620807891 | 408 | NULL |

| 192.168.148.62 | 3306 | 1532003620809610 | 140 | NULL |

| 192.168.148.64 | 3306 | 1532003800821971 | 420 | NULL |

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

180 rows in set (0.00 sec)

#但是，read_only和replication_lag的監控日志都為空。

admin> select * from mysql_server_read_only_log;

Empty set (0.00 sec)

admin> select * from mysql_server_replication_lag_log;

Empty set (0.00 sec)

#例如，指定寫組的id為10，讀組的id為20。

admin> insert into mysql_replication_hostgroups values(10,20,1);

在該配置加載到RUNTIME生效之前，先查看下各mysql server所在的組。

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;

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

| hostgroup_id | hostname | port | status | weight |

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

| 10 | 192.168.148.62 | 3306 | ONLINE | 1 |

| 10 | 192.168.148.61 | 3306 | ONLINE | 1 |

| 10 | 192.168.148.64 | 3306 | ONLINE | 1 |

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

3 rows in set (0.00 sec)

#3個節點都在hostgroup_id=10的組中。

現在，將剛才mysql_replication_hostgroups表的修改加載到RUNTIME生效。

admin> load mysql servers to runtime;

admin> save mysql servers to disk;

一加載，Monitor模塊就會開始監控后端的read_only值，當監控到read_only值后，就會按照read_only的值將某些節點自動移動到讀/寫組。

例如，此處所有節點都在id=10的寫組，slave1和slave2都是slave，它們的read_only=1，這兩個節點將會移動到id=20的組。如果一開始這3節點都在id=20的讀組，那么移動的將是Master節點，會移動到id=10的寫組。

#看結果：

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;

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

| hostgroup_id | hostname | port | status | weight |

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

| 10 | 192.168.148.62 | 3306 | ONLINE | 1 |

| 20 | 192.168.148.61 | 3306 | ONLINE | 1 |

| 20 | 192.168.148.64 | 3306 | ONLINE | 1 |

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

3 rows in set (0.00 sec)

admin> select * from mysql_server_read_only_log;

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

| hostname | port | time_start_us | success_time_us | read_only | error |

+----------------+------+------------------+-----------------+-----------+--------+ |

192.168.148.64 | 3306 | 1532003708480193 | 1218 | 1 | NULL |

| 192.168.148.62 | 3306 | 1532003709979095 | 2369 | 0 | NULL |

| 192.168.148.61 | 3306 | 1532003709978587 | 3464 | 1 | NULL |

| 192.168.148.64 | 3306 | 1532003709981780 | 2562 | 1 | NULL |

| 192.168.148.61 | 3306 | 1532003711479179 | 2449 | 1 | NULL |

| 192.168.148.62 | 3306 | 1532003711479965 | 1918 | 0 | NULL |

+----------------+------+------------------+-----------------+-----------+--------+ |

配置mysql_users

上面的所有配置都是關于后端MySQL節點的，現在可以配置關于SQL語句的，包括：發送 SQL語句的用戶、SQL語句的路由規則、SQL查詢的緩存、SQL語句的重寫等等。本小節是SQL請求所使用的用戶配置，例如root用戶。這要求我們需要先在后端MySQL節點添加好相關用戶。這里以root和sqlsender兩個用戶名為例。

#首先，在master節點上執行：(只需master執行即可，會復制給兩個slave)

mysql> grant all on *.* to root@'192.168.148.%' identified by 'passwd';

mysql> grant all on *.* to sqlsender@'192.168.148.%' identified by 'P@ssword1!';

#然后回到ProxySQL，配置mysql_users表，將剛才的兩個用戶添加到該表中。

admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);

admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);

admin> load mysql users to runtime; admin> save mysql users to disk;

mysql_users表有不少字段，最主要的三個字段為username、password和default_hostgroup：

username：前端連接ProxySQL，以及ProxySQL將SQL語句路由給MySQL所使用的用戶名。

password：用戶名對應的密碼。可以是明文密碼，也可以是hash密碼。如果想使用hash密碼，可以先在某個MySQL節點上執行select password(PASSWORD)，然后將加密結果復制到該字段。

default_hostgroup：該用戶名默認的路由目標。例如，指定root用戶的該字段值為10時，則使用root用戶發送的SQL語句默認情況下將路由到hostgroup_id=10組中的某個節點。

admin> select * from mysql_users\G

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

username: root

password: passwd

active: 1

use_ssl: 0

default_hostgroup: 10

default_schema: NULL

schema_locked: 0

transaction_persistent: 1

fast_forward: 0

backend: 1

frontend: 1

max_connections: 10000

*************************** 2. row ***************************

username: sqlsender

password: P@ssword1!

active: 1

use_ssl: 0

default_hostgroup: 10

default_schema: NULL

schema_locked: 0

transaction_persistent: 1

fast_forward: 0

backend: 1

frontend: 1

max_connections: 10000

2 rows in set (0.00 sec)

雖然本文不詳細介紹mysql_users表，但上面標注了"注意本行"的兩個字段必須要引起注意。只有active=1的用戶才是有效的用戶。至于transaction_persistent字段，當它的值為1時，表示事務持久化：當某連接使用該用戶開啟了一個事務后，那么在事務提交/回滾之前，所有的語句都路由到同一個組中，避免語句分散到不同組。在以前的版本中，默認值為0，不知道從哪個版本開始，它的默認值為1。我們期望的值為1，所以在繼續下面的步驟之前，先查看下這個值，如果為0，則執行下面的語句修改為1。

admin> update mysql_users set transaction_persistent=1 where username='root';

admin> update mysql_users set transaction_persistent=1 where username='sqlsender';

admin> load mysql users to runtime;

admin> save mysql users to disk;

然后，另開一個終端，分別使用root用戶和sqlsender用戶測試下它們是否能路由到默認的hostgroup_id=10(它是一個寫組)讀、寫數據。

[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"

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

| @@server_id | +-------------+ | 110 |

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

[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"

[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"

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

| Database |

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

| information_schema | | mysql | | performance_schema | | proxy_test | | sys |

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

[root@s1 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e '\ use proxy_test;\ create table t(id int);'

[root@s1 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'

+-------------------------+ |

Tables_in_proxy_test | +-------------------------+ | t |

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

5讀寫分離：配置路由規則

ProxySQL的路由規則非常靈活，可以基于用戶、基于schema以及基于每個語句實現路由規則的定制。本文作為入門文章，實現一個最簡單的語句級路由規則，從而實現讀寫分離。必須注意，這只是實驗，實際的路由規則絕不應該僅根據所謂的讀、寫操作進行分離，而是從各項指標中找出壓力大、執行頻繁的語句單獨寫規則、做緩存等等。和查詢規則有關的表有兩個：mysql_query_rules和 mysql_query_rules_fast_routing，后者是前者的擴展表，1.4.7之后才支持該快速路由表。本文只介紹第一個表。插入兩個規則，目的是將select語句分離到hostgroup_id=20的讀組，但由于select語句中有一個特殊語句SELECT...FOR UPDATE它會申請寫鎖，所以應該路由到hostgroup_id=10的寫組。

admin>insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);

admin> load mysql query rules to runtime;

admin> save mysql query rules to disk;

select ... for update規則的rule_id必須要小于普通的select規則的rule_id，因為ProxySQL是根據rule_id的順序進行規則匹配的。

#再來測試下，讀操作是否路由給了hostgroup_id=20的讀組。

[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' mysql:

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

+-------------+ | @@server_id | +-------------+ | 120 |

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

[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' mysql:

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

+-------------+ | @@server_id | +-------------+ | 130 |

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

#讀操作已經路由給讀組，再看看寫操作。這里以事務持久化進行測試。

[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e '\ start transaction;\ select @@server_id;\ commit;\ select @@server_id;'

+-------------+ | @@server_id | +-------------+ | 110 | +-------------+ +-------------+ | @@server_id | +-------------+ | 120 |

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

顯然，一切都按照預期進行。最后，如果想查看路由的信息，可查詢stats庫中的stats_mysql_query_digest表。以下是該表的一個輸出格式示例(和本文無關)。

admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

+----+----------+------------+-------------------------------------------------------------+ | hg | sum_time | count_star | digest_text | +----+----------+------------+-------------------------------------------------------------+ | 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? | | 1 | 3142041 | 5001 | COMMIT | | 1 | 2270931 | 5001 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | | 1 | 2021320 | 5003 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? | | 1 | 1768748 | 5001 | UPDATE sbtest1 SET k=k+? WHERE id=? | | 1 | 1697175 | 5003 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? | | 1 | 1346791 | 5001 | UPDATE sbtest1 SET c=? WHERE id=? | | 1 | 1263259 | 5001 | DELETE FROM sbtest1 WHERE id=? | | 1 | 1191760 | 5001 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | | 1 | 875343 | 5005 | BEGIN | +----+----------+------------+-------------------------------------------------------------+

基于GTID主從復制+并行復制+增強半同步復制+讀寫分離結束

本文鏈接：https://blog.csdn.net/m493096871/article/details/87890214

智能推薦

mysql的主從復制，基于gtid的主從復制，半同步復制，組復制

主從復制原理： 1.傳統的實現數據庫同步 # [root@server1 mysql5.7]# grep password /var/log/mysqld.log #獲取初始密碼 2018-08-07T14:06:55.029406Z 1 [Note] A temporary password is generated for root@localhost: aG!a*sUs>3#) #初始...

mysql主從復制、基于GTID的主從、半同步

使用的mysql版本5.7.17 一、主從復制原理：主從復制一共有三個進程，從庫生成兩個線程，一個I/O線程，一個SQL線程； i/o線程去請求主庫的binlog，并將得到的binlog日志寫到relay log（中繼日志）文件中；主庫會生成一個 log dump 線程，用來給從庫 i/o線程傳binlog； SQL 線程，會讀取relay log文件中的日志，并解析成具體操作，來實現主從...

mysql的主從復制、基于gtid的主從復制和半同步復制

主從復制在官網上下載mysql的安裝包mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar 在server1，2中均做以下動作主節點上： mysql -p mysql> grant replication slave on . to repl@’172.25.11.%’ identified by ‘WDd12345.&rs...

mysql的主從復制、基于Gtid的主從復制、半同步復制

1.mysql主從復制的作用：（1）實時災備，用于故障切換（2) 讀寫分離，提供查詢服務 (3) 備份，避免影響業務 2.主從部署必要條件： (1) 主庫開啟binlog日志（設置log-bin參數） (2) 主從server-id不同 (3)從庫服務器能連通主庫 3.主從復制實驗：實驗環境：mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar server2:主...

mysql主從復制及半同步（基于gtid）

1.MySQL主從復制（基于GTID） Mysql主庫（master）：server2 —>172.25.155.2 Mysql從庫（ slave ）：server3 —>172.25.155.3 下載安裝包 mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-...