MySQL主從複制
複制過濾器
複制與監控
主從複制的讀寫分離
備份與恢複
表分區
複制拓撲:
主機
IP
備注
Mysql-master.linux.com
192.168.239.142
master
Mysql-slave.linux.com
192.168.239.143
slave
連接配接主資料庫伺服器,建立具有replication slave和replication client權限的賬号。
主節點:
log_bin指定生成的二進制日子檔案的目錄和檔案名
server_id指定唯一辨別的資料庫ID
從節點:
relay_log指定中繼日志的目錄和檔案名
最後重新開機主從資料庫服務。
其中master_log_file和master_log_pos可以根據主節點的show master status語句查詢到。
可以看到從節點的IO線程和SQL線程并沒有啟動。那是因為還沒有啟動從節點的複制。
接下啟動從節點的複制,可以看到從節點的IO線程和SQL線程啟動。
到此為止MySQL主從複制已經配置完成,以後隻要主機節點的資料發生變化,從節點也會跟着變化。例如:
接着從節點也會相應的生成mydb資料庫。
另外也可以看到讀取二進制檔案的位置也發生了變化。(從283增長為336)
在主庫上進行過濾隻能針對資料庫進行限制,而無法針對表進行過濾。
binlog_do_db=指定更新資料寫入二進制日志檔案的資料庫,白名單
binlog_ignore_db=指定更新資料不寫入二進制檔案的資料庫,黑名單
從庫的SQL線程隻對關心的資料庫,表進行重放,以此來實作從庫的過濾,可以發現,從庫過濾不僅可是針對資料庫,也可以針對表。
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Igonre_Table=
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=
随着時間的推移,資料庫中的日志檔案會日積月累,并且會占用大量的磁盤空間。是以就需要對一些日志檔案進行管理。
對于資料庫的二進制日志檔案,不能簡單地使用rm指令進行删除,如果直接删除的話,用于記錄二進制日志檔案的index檔案沒有修改,這會造成資料庫複制時候的錯誤。是以需要使用專門的工具清理日志。
指令PURGE:
格式:PURGE MASTER|BINARY LOGS TO 'log_name'
主庫:
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主機
mysql主節點
mysql從節點
具體操作:
這一步驟引用<code>一、MySQL主從複制</code>的例子
ProxySQL的下載下傳位址為https://github.com/sysown/proxysql/releases
上邊有對應的作業系統的版本,下載下傳即可
ProxySQL提供了如下的檔案。其中的/etc/proxysql.cnf檔案是proxysql服務啟動時候的初始化配置。
下邊的操作可以直接全部在/etc/proxysql.cnf中直接配置,啟動proxysql服務就可以一步到位;當然還可以通過管理端(下邊有提到)連接配接ProxySQL,通過SQL語句一步一步的完成配置。
這裡通過/etc/proxysql.cnf完成。
ProxySQL會啟動兩種類型的端口,一類管理端口,用于管理配置ProxySQL,僅限本地登入;一類用戶端口,用于連接配接後端的mysql。他們預設分别監聽在6032和6033端口。
首先配置ProxySQL的管理端,建立管理賬号。
添加如下内容:
其中
參數
作用
admin_credentials
指定連接配接管理端的使用者和密碼
mysql_ifaces
指定連接配接管理端的IP和端口
相關的配置内容如下:
address 指定後端mysql的IP位址
port 指定後端mysql的port
hostgroup 指定該mysql的組id,這裡規劃的是1組進行寫操作,2組進行讀操作
這一步操作需要在後端的兩台mysql主機分别進行。分别建立連接配接賬戶proxysql,并賦予該賬戶全部權限。并且該賬戶也可以通過登入ProxySQL主機然後連接配接後端mysql。
主節點:
從節點:
然後将連接配接mysql的賬戶proxysql添加到ProxySQL主機,ProxySQL主機就是通過這個賬号連接配接後端mysql并進行操作。在/etc/proxysql.cnf添加如下内容,
default_hostgroup=1表示ProxySQL主機預設将SQL事件排程到1組,一般為進行寫操作的那個組。
在/etc/proxysql.cnf中添加如下内容:
select開頭的查詢語句排程到2組(即讀操作的組),但是特别注意select ... for update之類的語句,也是會修改資料,是以需要将其排程到1組(即寫操作的組),剩下的SQL語句全部排程到default_hostgroup定義的預設組。
關于是否定義監控使用者,簡單起見,這裡沒有在後端mysql中建立,監控使用者可以用來監控後端mysql節點的狀态資訊,并且可以根據後端mysql的read_only參數自動地将其定義為隻讀組。還有因為這裡将讀組和寫組已經定義在mysql_servers =中,是以也無需建立監控使用者。
/etc/proxysql.cnf的其他内容保持預設。然後啟動proxysql服務。
通過proxysql賬戶連接配接ProxySQL主機,分别進行相關的查詢操作和更改資料的操作,
然後通過管理賬戶登入ProxySQL檢視具體的連接配接狀态
可以看到讀操作排程到了2組,寫操作排程了1組。讀寫分離成功。
mysqldump是mysql自帶的用于邏輯備份的工具。
下面是mysqldump工具常用方式的幾個執行個體:
如果恢複mysqldump備份的表出現了錯誤:ERROR 1046 (3D000) at line 22: No database selected,這是因為指令行中沒有指定将表恢複到哪個資料庫中。
首先需要開啟mysql服務的二進制日志功能,在/etc/my.cnf中添加log_bin=/data/mysql/logs/mysql-bin
在某一時候對所有資料庫做全量備份,
檢視全量備份的資料資訊,
現在再次增加新的資料,
因為誤操作導緻删除了所有資料庫,
到這裡,如果僅僅依靠全量備份進行恢複,則mysql隻能回到備份時刻點的資料,之後新增的資料并無法恢複,是以還需要借助二進制檔案生成增量備份。
現在開始做增量備份,需要使用最新生成的二進制檔案。
這樣在/root/backup/目錄下存在兩個sql檔案,一個全量備份,一個增量備份。
現在開始恢複删庫之前的所有資料。
重新開機mysqld服務,
Xtrabackup的下載下傳位址:https://www.percona.com/downloads/XtraBackup/LATEST/
利用Xtrabackup工具中的innobackupex指令進行資料庫的全量備份,完成之後會在指定目錄下生成一個備份目錄
可以發現備份目錄和原來資料庫中的資料相似。
進行準備階段,到了第二個階段後,可以不用連接配接mysql。是以暫停mysql服務。
準備階段的格式:innobackupes --appply-log <生成的全備份目錄>
因為誤操作删除了所有資料庫,
進行恢複,
總之xtrabackup工具進行恢複資料操作經曆三個階段:備份、準備、恢複。當看到completed OK!字樣就表示每個階段執行成功。
完成之後在資料目錄下生成的檔案和目錄的所屬人和所屬組都是root,需要将他們改為mysql
然後啟動mysql,可以看到資料又回來了。
以上例的全量備份為基準,以此來做增量備份,
再次在資料庫中插入新的資料,
再做一次增量備份,這次以上一次的增量備份為基準,
這樣就生成了兩個增量備份檔案。
因為誤操作删除了所有資料庫
現在開始進行恢複操作。首先暫停mysql服務
1、對全量備份進行準備
2、合并增量備份,将兩個增量備份添加到全量備份中
3、對添加了增量備份的全量備份進行準備
4、開始恢複
現在再來檢視回複後的資料庫,可以發現所有的資料都回來了。
注:其中--redo-only 的意義--> 在“準備基本完整備份” 和 “合并所有的增量備份(除了最後一個增備)”時使用此選項。它直接傳遞給xtrabackup的 xtrabackup --apply-log-only 選項,使xtrabackup跳過"undo"階段,隻做"redo"操作。如果後面還有增量備份應用到這個全備,這是必要的。
MySQL支援四種分區類型,分别為:
RANGE分區
LIST分區
HASH分區
KEY分區
該種分區類型是基于連續的值進行分區.
例如建立students表,并且基于id進行分區,id小于101,id小于201,id小于301的三個分區表。
該種分區類型是基于離散的值進行分區。
例如建立classrooms表,其中room-id為11,12,13,14的在pfirst分區表,21,22,23,24的在psecond分區表,31,32,33,34的在pthird分區表。
該種分區類型是基于不同的值進行hash運算,再通過hash值進行分區。hash分區的鍵必須為整數。
例如建立teachers表,其中根據id字段值進行hash運算,文法格式為:partition by hash (expression) partitions num。expresssion表示一個傳回整數的表達式,num表示分區的數量。
該種分區類型也是基于不同的值進行hash運算,和hash分區不同的是,key分區支援非整數的鍵。