應(yīng)用MySQL讀寫(xiě)分離以提高M(jìn)ySQL服務(wù)器的讀寫(xiě)性能
??讀寫(xiě)分離是借助MySQL中間件 ProxySQL 實(shí)現(xiàn)的
?ProxySQL 有兩個(gè)版本：官方版和percona版，percona版是基于官方版基礎(chǔ)上修改C++語(yǔ)言開(kāi)發(fā)，輕量級(jí)但性能優(yōu)異(支持處理千億級(jí)數(shù)據(jù))具有中間件所需的絕大多數(shù)功能，包括：
10年積累的網(wǎng)站設(shè)計(jì)、做網(wǎng)站經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先建設(shè)網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有和平免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
- 多種方式的讀/寫(xiě)分離
- 定制基于用戶、基于schema、基于語(yǔ)句的規(guī)則對(duì)SQL語(yǔ)句進(jìn)行路由
- 緩存查詢結(jié)果
- 后端節(jié)點(diǎn)監(jiān)控
準(zhǔn)備
??實(shí)現(xiàn)讀寫(xiě)分離前，先實(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)行，請(qǐng)關(guān)閉主機(jī)的selinux以及防火墻服務(wù)
開(kāi)始搭建
Master節(jié)點(diǎn)修改數(shù)據(jù)庫(kù)配置文件
[root@Master ~]#cat /etc/my.cnf
[mysqld]
server_id=1 # 為Master節(jié)點(diǎn)設(shè)置一個(gè)全局唯一的ID號(hào)
binlog_format=row # 基于行復(fù)制的數(shù)據(jù)庫(kù)語(yǔ)句
log-bin=/data/bin/mysql-bin # 啟用二進(jìn)制日志重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)
[root@Master ~]#service mysqld restart
Restarting mysqld (via systemctl): [ OK ]Master節(jié)點(diǎn)上創(chuàng)建帶有復(fù)制權(quán)限的用戶賬號(hào)
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號(hào)
read_only # 只讀重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)
[root@Slave-1 ~]#systemctl restart mariadb使用Master創(chuàng)建的復(fù)制權(quán)限的用戶賬號(hào)進(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)啟動(dòng)Slave線程
MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)查看線程是否啟動(dòng)
*************************** 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倉(cāng)庫(kù)
[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啟動(dòng)ProxySQL
[root@ProxySQL ~]#rpm -ql proxysql
/etc/init.d/proxysql
...
由于proxysql啟動(dòng)腳本在init.d文件中，所以需要使用service啟動(dòng)
[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管理的賬號(hào)
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è)置，使其自動(dòng)連接主從節(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)存中生效，并保存到磁盤(pán)中
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)查看讀寫(xiě)組信息
MySQL [(none)]> select *from mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 10 | 20 | test |
+------------------+------------------+---------+
1 row in set (0.00 sec)配置生效并保存到磁盤(pán)
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)啟用一個(gè)客戶端連接ProxySQL進(jìn)行測(cè)試
[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>查看連接到哪個(gè)主機(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)生效并存盤(pán)
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)行讀寫(xiě)分離測(cè)試
[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 |
+-------------+
本文名稱：應(yīng)用MySQL讀寫(xiě)分離以提高M(jìn)ySQL服務(wù)器的讀寫(xiě)性能
網(wǎng)站鏈接：http://bm7419.com/article42/igoiec.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)、網(wǎng)站設(shè)計(jì)公司、品牌網(wǎng)站建設(shè)、靜態(tài)網(wǎng)站、云服務(wù)器、網(wǎng)站內(nèi)鏈
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 手游開(kāi)發(fā)商怎么選擇云服務(wù)器? 2022-10-07
- 營(yíng)銷型網(wǎng)站使用云服務(wù)器有什么好處 2023-04-18
- 便宜的香港云服務(wù)器可以租嗎？ 2022-10-11
- 網(wǎng)站空間虛擬主機(jī)空間虛擬主機(jī)配置域名注冊(cè)云服務(wù)器 2016-11-05
- 為什么云服務(wù)器這么受歡迎？ 2021-02-05
- 云服務(wù)器采用的安全策略都有哪些呢？ 2022-10-12
- 云服務(wù)器好在哪？你知道嗎 2021-03-06
- 不同類型的網(wǎng)站云服務(wù)器配置要怎么選？ 2022-10-08
- 美國(guó)云服務(wù)器購(gòu)買(mǎi)注意事項(xiàng) 2021-02-26
- 選擇海外云服務(wù)器，有什么側(cè)重點(diǎn)? 2022-10-02
- 公司做網(wǎng)站需要買(mǎi)虛擬主機(jī)還是云服務(wù)器呢？ 2021-03-20
- 云服務(wù)器是什么，有什么作用 2021-02-14