溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

應(yīng)用MySQL讀寫分離以提高M(jìn)ySQL服務(wù)器的讀寫性能

發(fā)布時間：2020-07-19 16:29:26 來源：網(wǎng)絡(luò) 閱讀：871 作者：Darius_D 欄目：MySQL數(shù)據(jù)庫

??讀寫分離是借助MySQL中間件 ProxySQL 實(shí)現(xiàn)的
??ProxySQL 有兩個版本：官方版和percona版，percona版是基于官方版基礎(chǔ)上修改C++語言開發(fā)，輕量級但性能優(yōu)異(支持處理千億級數(shù)據(jù))具有中間件所需的絕大多數(shù)功能，包括：

  1. 多種方式的讀/寫分離
  2. 定制基于用戶、基于schema、基于語句的規(guī)則對SQL語句進(jìn)行路由
  3. 緩存查詢結(jié)果
  4. 后端節(jié)點(diǎn)監(jiān)控

準(zhǔn)備

??實(shí)現(xiàn)讀寫分離前，先實(shí)現(xiàn)主從復(fù)制

??注意：slave節(jié)點(diǎn)需要設(shè)置read_only=1
主機(jī) IP地址 類型
CentOS7.6 192.168.36.101 Master
CentOS7.6 192.168.36.103 Slave
CentOS7.6 192.168.36.104 ProxySQL
注：實(shí)驗(yàn)之前為保障實(shí)驗(yàn)順利進(jìn)行，請關(guān)閉主機(jī)的selinux以及防火墻服務(wù)

開始搭建

Master節(jié)點(diǎn)修改數(shù)據(jù)庫配置文件

[root@Master ~]#cat /etc/my.cnf
[mysqld]
server_id=1     # 為Master節(jié)點(diǎn)設(shè)置一個全局唯一的ID號
binlog_format=row       # 基于行復(fù)制的數(shù)據(jù)庫語句
log-bin=/data/bin/mysql-bin     # 啟用二進(jìn)制日志

重新啟動數(shù)據(jù)庫服務(wù)

[root@Master ~]#service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

Master節(jié)點(diǎn)上創(chuàng)建帶有復(fù)制權(quán)限的用戶賬號

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

查看Master的日志位置信息

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    912372 |
+------------------+-----------+
1 row in set (0.00 sec)

Slave節(jié)點(diǎn)修改配置文件

[root@Slave-1 ~]#cat /etc/my.cnf
[mysqld]
server_id=2         # Slave節(jié)點(diǎn)設(shè)置全局唯一的ID號
read_only           # 只讀

重新啟動數(shù)據(jù)庫服務(wù)

[root@Slave-1 ~]#systemctl restart mariadb

使用Master創(chuàng)建的復(fù)制權(quán)限的用戶賬號進(jìn)行同步

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

啟動Slave線程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

查看線程是否啟動

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.101
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7389
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 7673
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes      # 從節(jié)點(diǎn)的IO線程
            Slave_SQL_Running: Yes      # 從節(jié)點(diǎn)的SQL線程
....
        Seconds_Behind_Master: 0        # Master與SLave服務(wù)器差別延遲
.....
             Master_Server_Id: 1
1 row in set (0.00 sec)

檢查數(shù)據(jù)同步情況

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

配置ProxySQL的YUM倉庫

[root@ProxySQL ~]#cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name= ProxySQL YUM repository
> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
> gpgcheck=1
> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
> EOF
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

安裝ProxySQL

[root@ProxySQL ~]#yum install -y proxysql mariadb

啟動ProxySQL

[root@ProxySQL ~]#rpm -ql proxysql
/etc/init.d/proxysql
...

由于proxysql啟動腳本在init.d文件中，所以需要使用service啟動
[root@ProxySQL ~]#service proxysql start
Starting ProxySQL: 2019-05-08 17:58:16 [INFO] Using config file /etc/proxysql.cnf
DONE!

端口查看

[root@ProxySQL ~]#netstat -at
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:mysql           0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN
# 6033端口是接收遠(yuǎn)程用戶的連接、6032端口是連接，管理接口

連接proxysql管理接口

[root@ProxySQL ~]#mysql -uadmin -padmin -P6032 -h227.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>
ProxySQL相當(dāng)于小型的MySQL,自帶：用戶名admin、密碼admin

添加主從節(jié)點(diǎn)的地址

# 添加Master節(jié)點(diǎn)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.36.101',3306);
Query OK, 1 row affected (0.00 sec)

# 添加Slave節(jié)點(diǎn)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.36.103',3306);
Query OK, 1 row affected (0.00 sec)

# 查看添加信息
MySQL [(none)]> select *from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.36.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         |
| 10           | 192.168.36.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

在Master上創(chuàng)建ProxySQL管理的賬號

MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.36.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)

ProxySQL配置監(jiān)控的用戶名和密碼設(shè)置，使其自動連接主從節(jié)點(diǎn)進(jìn)行調(diào)整

MySQL [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> set mysql-monitor_password='magedu';
Query OK, 1 row affected (0.00 sec)

使配置加載到內(nèi)存中生效，并保存到磁盤中

MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql variables to disk;
Query OK, 97 rows affected (0.01 sec)

查看監(jiān)控連接是否正常

MySQL [(none)]> select *from mysql_server_connect_log;
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                           |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| 192.168.36.101 | 3306 | 1557312316296707 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
| 192.168.36.101 | 3306 | 1557312557263893 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
| 192.168.36.101 | 3306 | 1557312616308042 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
| 192.168.36.103 | 3306 | 1557312617121004 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
| 192.168.36.103 | 3306 | 1557312676308396 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
| 192.168.36.101 | 3306 | 1557312676936371 | 0                       | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
| 192.168.36.101 | 3306 | 1557312694163848 | 2228                    | NULL                                                                    |
| 192.168.36.103 | 3306 | 1557312695077512 | 4613                    | NULL                                                                    |
| 192.168.36.103 | 3306 | 1557312754164398 | 1370                    | NULL                                                                    |
| 192.168.36.103 | 3306 | 1557312874168899 | 2204                    | NULL                                                                    |
| 192.168.36.101 | 3306 | 1557312874890981 | 2939                    | NULL                                                                    |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
22 rows in set (0.00 sec)

設(shè)置分組信息

MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");
Query OK, 1 row affected (0.00 sec)

查看讀寫組信息

MySQL [(none)]> select *from mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 10               | 20               | test    |
+------------------+------------------+---------+
1 row in set (0.00 sec)

配置生效并保存到磁盤

MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

查看信息

MySQL [(none)]> select *from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.36.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         |
| 20           | 192.168.36.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0  |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

在master上進(jìn)行創(chuàng)建設(shè)置賬戶，讓客戶端連接中間的調(diào)度器

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.36.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)

ProxySQL添加記錄，將用戶添加到mysql_users表中

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);
Query OK, 1 row affected (0.00 sec)

查看是否添加成功

MySQL [(none)]> select *from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections|
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sqluser  | magedu   | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000|
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

保存配置

MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)

啟用一個客戶端連接ProxySQL進(jìn)行測試

[root@CentOS6 ~]# mysql -usqluser -pmagedu -h292.168.36.104 -P6033
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2013, 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>

查看連接到哪個主機(jī)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

ProxySQL上定義調(diào)度規(guī)則

MySQL [(none)]> insert into mysql_query_rules
    -> (rule_id,active,match_digest,destination_hostgroup,apply) values
    -> (1,1,'^select.*from update$',10,1),(2,1,'^select',20,1);
Query OK, 2 rows affected (0.00 sec)

生效并存盤

MySQL [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)

客戶端進(jìn)行讀寫分離測試

[root@CentOS6 ~]# mysql -usqluser -pmagedu -h292.168.36.104 -P6033 -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

[root@CentOS6 ~]# mysql -usqluser -pmagedu -h292.168.36.104 -P6033 -e 'begin;use db1;insert t1 values(1);select @@server_id;'
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
完工
向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手