高效能MySQL總結

標籤：資料   準備   測試   不一致   日誌功能   roc   ip地址   b2c   copy   

目錄

1. MySQL主從複製
2. 複製過濾器
3. 複製與監控
4. 主從複製的讀寫分離
5. 備份與恢複
6. 表分區

一、MySQL主從複製

複寫拓撲：

主機	IP	備忘
Mysql-master.linux.com	192.168.239.142	master
Mysql-slave.linux.com	192.168.239.143	slave

1、建立複製帳號

串連主要資料庫伺服器，建立具有replication slave和replication client許可權的帳號。

[[email protected] ~]# mysqlmysql> grant replication slave,replication client on *.* to [email protected]‘192.168.239.%‘ identified by ‘centos‘;

2、配置主從節點

主節點：

[[email protected] ~]# vim /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# 新增加如下兩行log_bin=/data/mysql/logs/mysql-binserver_id=10[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

log_bin指定產生的二進位日子檔案的目錄和檔案名稱
server_id指定唯一標識的資料庫ID
從節點：

[[email protected] ~]# vim /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# 新增如下server_id=20relay_log=/data/mysql/logs/mysql-relay-binread_only=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

relay_log指定中繼日誌的目錄和檔案名稱
最後重啟主從資料庫服務。

3、從節點串連主節點

[[email protected] ~]# mysqlmysql> change master to master_host=‘192.168.239.142‘,master_user=‘replicater‘,master_password=‘centos‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=283;

其中master_log_file和master_log_pos可以根據主節點的show master status語句查詢到。

[[email protected] ~]# mysqlmysql> show slave status\G;

可以看到從節點的IO線程和SQL線程並沒有啟動。那是因為還沒有啟動從節點的複製。

接下啟動從節點的複製,可以看到從節點的IO線程和SQL線程啟動。

mysql> start slave;

到此為止MySQL主從複製已經配置完成，以後只要主機節點的資料發生變化，從節點也會跟著變化。例如：

接著從節點也會相應的產生mydb資料庫。

另外也可以看到讀取二進位檔案的位置也發生了變化。（從283增長為336）

二、複製過濾器1、主庫過濾

在主庫上進行過濾只能針對資料庫進行限制，而無法針對錶進行過濾。
binlog_do_db=指定更新資料寫入二進位記錄檔的資料庫，白名單
binlog_ignore_db=指定更新資料不寫入二進位檔案的資料庫，黑名單

2、從庫過濾

從庫的SQL線程只對關心的資料庫，表進行重放，以此來實現從庫的過濾，可以發現，從庫過濾不僅可是針對資料庫，也可以針對錶。
Replicate_Do_DB=
Replicate_Ignore_DB=

Replicate_Do_Table=
Replicate_Igonre_Table=

Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=

三、複製的監控與維護

隨著時間的推移，資料庫中的記錄檔會日積月累，並且會佔用大量的磁碟空間。因此就需要對一些記錄檔進行管理。

1、清理日誌

對於資料庫的二進位記錄檔，不能簡單地使用rm命令進行刪除，如果直接刪除的話，用於記錄二進位記錄檔的index檔案沒有修改，這會造成資料庫複寫時候的錯誤。因此需要使用專門的工具清理日誌。
命令PURGE：
格式：PURGE MASTER|BINARY LOGS TO ‘log_name‘

2、複製監控

主庫：
?SHOW MASTER STATUS;
?SHOW BINLOG EVENTS;
?SHOW BINARY LOGS;



從庫：
?SHOW SLAVE STATUS\G;

其中的Seconds_Behind_Master參數表示從庫落後於主庫多長時間。解決主從資料不一致的問題可以嘗試重新啟動SQL線程。

四、主從複製的讀寫分離

藉助SQL router（read/write spliter）實現。
目前實現MySQL叢集讀寫分離的中介軟體很多，這裡用ProxySQL來實現讀寫分離。
環境拓撲：

IP地址	角色
192.168.239.129	ProxySQL主機
192.168.239.142	mysql主節點
192.168.239.143	mysql從節點

具體操作：

1、mysql主從節點實現主從複製

這一步驟引用一、MySQL主從複製的例子

2、安裝ProxySQL中介軟體

ProxySQL的為https://github.com/sysown/proxysql/releases
上邊有對應的作業系統的版本，下載即可

[[email protected] src]# pwd/usr/local/src[[email protected] src]# ls proxysql-1.4.10-1-centos67.x86_64.rpm proxysql-1.4.10-1-centos67.x86_64.rpm[[email protected] src]# yum -y install proxysql-1.4.10-1-centos67.x86_64.rpm

ProxySQL提供了如下的檔案。其中的/etc/proxysql.cnf檔案是proxysql服務啟動時候的初始化配置。

下邊的操作可以直接全部在/etc/proxysql.cnf中直接配置，啟動proxysql服務就可以一步到位；當然還可以通過管理端（下邊有提到）串連ProxySQL，通過SQL語句一步一步的完成配置。
這裡通過/etc/proxysql.cnf完成。

3、建立ProxySQL管理帳號

ProxySQL會啟動兩種類型的連接埠，一類管理連接埠，用於管理配置ProxySQL，僅限本地登入；一類用戶端口，用於串連後端的mysql。他們預設分別監聽在6032和6033連接埠。
首先配置ProxySQL的管理端，建立管理帳號。
添加如下內容：

其中

參數	作用
admin_credentials	指定串連管理端的使用者和密碼
mysql_ifaces	指定串連管理端的IP和連接埠

4、配置後端的mysql

相關的配置內容如下：

mysql_servers =(    {        address = "192.168.239.142" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain        port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain        hostgroup = 1           # no default, required        status = "ONLINE"     # default: ONLINE        weight = 1            # default: 1        compression = 0       # default: 0    max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned    },    {        address = "192.168.239.143"        port = 3306        hostgroup = 2        status = "ONLINE"        weight = 1        compression = 0        max_replication_lag = 10    })

address?指定後端mysql的IP地址
port?指定後端mysql的port
hostgroup?指定該mysql的組id，這裡規劃的是1組進行寫操作，2組進行讀操作

5、建立串連mysql的賬戶

這一步操作需要在後端的兩台mysql主機分別進行。分別建立串連賬戶proxysql，並賦予該賬戶全部許可權。並且該賬戶也可以通過登入ProxySQL主機然後串連後端mysql。
主節點:

[[email protected] ~]# mysql ([email protected]) [none]> grant all on *.* to [email protected]‘192.168.239.%‘ identified by ‘proxysql‘;

從節點:

[[email protected] ~]# mysql ([email protected]) [none]> grant all on *.* to [email protected]‘192.168.239.%‘ identified by ‘proxysql‘;

然後將串連mysql的賬戶proxysql添加到ProxySQL主機，ProxySQL主機就是通過這個帳號串連後端mysql並進行操作。在/etc/proxysql.cnf添加如下內容，

mysql_users:(    {        username = "proxysql" # no default , required        password = "proxysql" # default: ‘‘        default_hostgroup = 1 # default: 0        active = 1            # default: 1    })

default_hostgroup=1表示ProxySQL主機預設將SQL事件調度到1組，一般為進行寫操作的那個組。

6、添加規則到ProxySQL主機

在/etc/proxysql.cnf中添加如下內容：

mysql_query_rules:(    {        rule_id=1        active=1        match_pattern="^SELECT .* FOR UPDATE$"        destination_hostgroup=1        apply=1    },    {        rule_id=2        active=1        match_pattern="^SELECT"        destination_hostgroup=2        apply=1    })

select開頭的查詢語句調度到2組（即讀操作的組），但是特別注意select ... for update之類的語句，也是會修改資料，因此需要將其調度到1組（即寫操作的組），剩下的SQL語句全部調度到default_hostgroup定義的預設組。
關於是否定義監控使用者，簡單起見，這裡沒有在後端mysql中建立，監控使用者可以用來監控後端mysql節點的狀態資訊，並且可以根據後端mysql的read_only參數自動地將其定義為唯讀組。還有因為這裡將讀組和寫組已經定義在mysql_servers =中，因此也無需建立監控使用者。
/etc/proxysql.cnf的其他內容保持預設。然後啟動proxysql服務。

7、測試讀寫分離

通過proxysql賬戶串連ProxySQL主機，分別進行相關的查詢操作和更改資料的操作，

[[email protected] ~]# mysql -uproxysql -h127.0.0.1 -P6033 -pproxysql([email protected]) [(none)]> select user,host from mysql.user;([email protected]) [(none)]> create database mydb2;

然後通過管理賬戶登入ProxySQL查看具體的串連狀態

[[email protected] ~]# mysql -uadmin -h127.0.0.1 -P6032 -padmin([email protected]) [(none)]> select * from stats_mysql_query_digest;

可以看到讀操作調度到了2組，寫操作調度了1組。讀寫分離成功。

五、備份與恢複5.1 邏輯備份工具mysqldump

mysqldump是mysql內建的用於邏輯備份的工具。

下面是mysqldump工具常用方式的幾個執行個體：

1. 備份所有資料庫到單個檔案

# 將192.168.239.143主機的mysql上所有庫備份到本地root/backup目錄的dump.sql檔案mysqldump --user=root --host=192.168.239.143 --password=centos --all-databases > /root/backup/dump.sql

2. 備份單個資料庫到單個檔案

# 將192.168.239.143主機的mysql上的mydb庫備份到本地的dump.sql檔案mysqldump --user=root --host=192.168.239.143 --password=centos --databases mydb > /root/backup/dump.sql

3. 備份單個資料表到單個檔案

# 將192.168.239.143主機上的mysql的mydb中的mytbl表備份到本地dump.sql檔案mysqldump --user=root --host=192.168.239.143 --password=centos --databases mydb --tables mytbl > /root/backup/dump.sql# 將備份的單個檔案恢複到mydb庫中mysql -u root mydb < /root/backup/dump.sql

如果恢複mysqldump備份的表出現了錯誤：ERROR 1046 (3D000) at line 22: No database selected，這是因為命令列中沒有指定將表恢複到哪個資料庫中。

4. mysqldump全量備份+mysqlbinlog增量備份

首先需要開啟mysql服務的二進位日誌功能，在/etc/my.cnf中添加log_bin=/data/mysql/logs/mysql-bin

[[email protected] ~]# mkdir -p /data/mysql/logs[[email protected] ~]# chown mysql.mysql /data/mysql/logs

在某一時候對所有資料庫做全量備份,

[[email protected] ~]# mysqldump --single-transaction  --flush-logs --master-data=2 --user=root --host=127.0.0.1 > /root/backup/dump.`date +%F`.sql[[email protected] ~]# cd /root/backup/[[email protected] backup]# lsdump.2018-08-31.sql

查看全量備份的資料資訊，

現在再次增加新的資料，

([email protected]) [(none)]> insert into mydb.mytbl value (6,‘huangzhong‘,90),(7,‘zhugeliang‘,20);

因為誤操作導致刪除了所有資料庫，

[[email protected] ~]# rm -rf /var/lib/mysql/*

到這裡，如果僅僅依靠全量備份進行恢複，則mysql只能回到備份時刻點的資料，之後新增的資料並無法恢複，因此還需要藉助二進位檔案產生增量備份。
現在開始做增量備份,需要使用最新產生的二進位檔案。

[[email protected] ~]# mysqlbinlog /data/mysql/logs/mysql-bin.000001 > /root/backup/mysql-bin.000001.sql

這樣在/root/backup/目錄下存在兩個sql檔案，一個全量備份，一個增量備份。
現在開始恢複刪庫之前的所有資料。
重啟mysqld服務,

[[email protected] backup]# /etc/init.d/mysqld restart[[email protected] backup]# mysql < /data/backup/dump.2018-08-31.sql# 恢複全量備份之後，資料庫中還沒有後來新增的那兩條資料[[email protected] backup]# mysql < /data/backup/mysql-bin.000001.sql# 恢複增量備份，恢複新增的資料

5.2 物理備份工具Xtrabackup

Xtrabackup的：https://www.percona.com/downloads/XtraBackup/LATEST/

1. xtrabackup全量備份與恢複

利用Xtrabackup工具中的innobackupex命令進行資料庫的全量備份，完成之後會在指定目錄下產生一個備份目錄

[[email protected] ~]# innobackupex --user=root /root/backup/[[email protected] ~]# cd /root/backup/[[email protected] backup]# ls2018-08-31_02-17-02

可以發現備份目錄和原來資料庫中的資料相似。

進行準備階段，到了第二個階段後，可以不用串連mysql。因此暫停mysql服務。
準備階段的格式：innobackupes --appply-log <產生的全備份目錄>

[[email protected] 2018-08-31_02-17-02]# /etc/init.d/mysqld stop[[email protected] 2018-08-31_02-17-02]# innobackupex --apply-log /root/backup/2018-08-31_02-17-02/

因為誤操作刪除了所有資料庫，

[[email protected] 2018-08-31_02-17-02]# rm -rf /var/lib/mysql/*

進行恢複，

[[email protected] 2018-08-31_02-17-02]# innobackupex --copy-back /root/backup/2018-08-31_02-17-02/

總之xtrabackup工具進行恢複資料操作經曆三個階段：備份、準備、恢複。當看到completed OK!字樣就表示每個階段執行成功。
完成之後在資料目錄下產生的檔案和目錄的所屬人和所屬組都是root，需要將他們改為mysql

[[email protected] mysql]# chown -R mysql.mysql /var/lib/mysql/*

然後啟動mysql，可以看到資料又回來了。

([email protected]) [(none)]> select * from mydb.mytbl;

2. xtrabackup增量備份與恢複

以上例的全量備份為基準，以此來做增量備份，

[[email protected] ~]# mkdir /root/backup/incremental[[email protected] ~]# innobackupex --user=root  --incremental /root/backup/incremental --incremental-basedir=/root/backup/2018-08-31_02-17-02/

再次在資料庫中插入新的資料，

再做一次增量備份，這次以上一次的增量備份為基準，

[[email protected] incremental]# innobackupex --user=root --incremental /root/backup/incremental --incremental-basedir=/root/backup/incremental/2018-08-31_03-02-49/

這樣就產生了兩個增量備份檔案。

因為誤操作刪除了所有資料庫

[[email protected] ~]# rm -rf /var/lib/mysql/*

現在開始進行恢複操作。首先暫停mysql服務
1、對全量備份進行準備

[[email protected] ~]# innobackupex --apply-log --redo-only /root/backup/2018-08-31_02-17-02

2、合并增量備份，將兩個增量備份添加到全量備份中

# 增量1增加到全量備份[[email protected] ~]# innobackupex --apply-log --redo-only /root/backup/2018-08-31_02-17-02 --incremental-dir=/root/backup/incremental/2018-08-31_03-02-49# 增量2增加到全量備份[[email protected] ~]# innobackupex --apply-log /root/backup/2018-08-31_02-17-02/ --incremental-dir=/root/backup/incremental/2018-08-31_03-10-59/

3、對添加了增量備份的全量備份進行準備

[[email protected] ~]# innobackupex --apply-log /root/backup/2018-08-31_02-17-02/

4、開始恢複

[[email protected] ~]# innobackupex --copy-back /root/backup/2018-08-31_02-17-02/[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql/*

現在再來查看回複後的資料庫，可以發現所有的資料都回來了。

註：其中--redo-only 的意義--> 在“準備基本完整備份” 和 “合并所有的增量備份(除了最後一個增備)”時使用此選項。它直接傳遞給xtrabackup的 xtrabackup --apply-log-only 選項，使xtrabackup跳過"undo"階段，只做"redo"操作。如果後面還有增量備份應用到這個全備,這是必要的。

六、表分區

MySQL支援四種分區類型，分別為：
 RANGE分區
 LIST分區
 HASH分區
 KEY分區

1、range分區類型

該種分區類型是基於連續的值進行分區.
例如建立students表，並且基於id進行分區，id小於101，id小於201，id小於301的三個分區表。

([email protected]) [mydb1]> create table students (id INT,name char(20),grade float(3,1)) partition by range (id) ( partition p0 values less than (101),partition p1 values less than (201),partition p2 values less than (301) );

2、list分區類型

該種分區類型是基於離散的值進行分區。
例如建立cla***ooms表，其中room-id為11,12,13,14的在pfirst分區表，21,22,23,24的在psecond分區表，31,32,33,34的在pthird分區表。

([email protected]) [mydb1]> create table cla***ooms (id INT,leader char(20)) partition by list(id) ( partition pfirst values in (11,12,13,14),partition psecond values in (21,22,23,24),partition pthird values in (31,32,33,34) );

3、hash分區類型

該種分區類型是基於不同的值進行hash運算，再通過hash值進行分區。hash分區的鍵必須為整數。
例如建立teachers表，其中根據id欄位值進行hash運算，文法格式為：partition by hash (expression) partitions num。expresssion表示一個返回整數的運算式，num表示分區的數量。

([email protected]) [mydb1]> create table teachers (id INT,name char(20),age TINYINT) partition by hash (id) partitions 3;

4、key分區類型

該種分區類型也是基於不同的值進行hash運算，和hash分區不同的是，key分區支援非整數的鍵。

([email protected]) [mydb1]> create table employee (name char(20),birthday date,job char(30)) partition by  key (birthday) partitions 3;

高效能MySQL總結