一文搞懂│mysql 中的備份恢復、分區分表、主從複製、讀寫分離

目錄

🎈 mysql 的備份和恢復

  • 創建備份管理員
  • 創建備份管理員，並授予管理員相應的許可權
  • 備份所需許可權：select,reload,lock tables,replication client,show view,event,process
# 創建管理員
create user 'backup'@'localhost' identified by '123456';

# 給管理員授權
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';
  • sql 文件恢復之全量恢復
  • 只要將備份的 sql 文件直接導入資料庫即可
mysql -uroot -p 資料庫 < sql文件
  • sql 文件恢復之基於時間點的恢復
# 首先進行一次基於最近一次的全量備份的文件進行一次全量恢復
mysql -uroot -p 資料庫 < sql文件

# 然後查看備份的sql文件的 CHANGE MASTER 值，基於該值進行二進位日誌的還原
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;

# 查看二進位日誌,根據時間點找到誤操作前一段時間的二進位日誌
cd /var/lib/mysql
mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=資料庫名 binlog.000008 | grep -B3 DELETE | more

# 記錄最早刪除記錄的節點值，執行日誌導出
mysqlbinlog --start-position=開始節點 --stop-position=結束節點 --database=資料庫 二進位日誌名 > 導出的sql文件名
mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql

# 對導出的sql文件進行全量的還原
mysql -uroot -p 資料庫 < sql文件
  • mysqldump邏輯備份
  • 指定資料庫多個表進行備份：mysqldump [OPTIONS] database [table]
  • 指定多個資料庫備份：mysqldump [OPTIONS] database [OPTIONS] DB1 DB2
  • 整個資料庫實例進行備份：mysqldump [OPTIONS] --all-database [OPTIONS]
  • 參數 --single-transaction：開啟事務保證備份數據的完整性，innodb 特有
  • 參數 -l或--lock-tables：依次鎖定備份資料庫所有表保證備份數據的完整性
  • 參數 -x或--lock-all-table：一次性鎖定整個資料庫實例所有數據表保證數據完整性
  • 參數 --master-data=[1/2]：CHANGE MASTER TO 語句會被寫成一個 sql 注釋；1不會被寫成注釋，2寫成注釋，默認1
  • 參數 -R或--routines：備份資料庫存儲過程
  • 參數 --triggers：備份資料庫觸發器
  • 參數 -E或--events：備份資料庫調度事件
  • 參數 --hex-blob：16進位導出bit列和blob列數據 避免數據文本不可見
  • 參數 --tab=path：指定路徑下為每個資料庫生成兩個文件：數據結構、數據
  • 參數 -w或--where=過濾條件：過濾指定數據，僅支援單表導出
  • 注意：--single-transaction，--lock-tables 參數是互斥的，所以，如果同一個資料庫下同時存在 innodb 表和myisam表只能使用 --lock-tables 來保證備份數據的一致性，但是 --lock-tables 只能保證某一備份資料庫的完整性，不能保證整個實例備份的完整性
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 資料庫 > 備份文件.sql
  • XtraBackup備份
  • 自定義下載地址：Percona 存儲庫
  • 命令下載：yum install //www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
  • 測試儲存庫安裝是否成功命令：yum list | grep percona
# 下載 libev軟體包
yum install //dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
 
# 安裝 libev軟體包
yum install percona-xtrabackup-24

# 全量備份
innobackupex --user=管理員帳號 --password=密碼 --parallel=2 備份路徑
innobackupex --user=backup --password=Gzjunyu19970925. --parallel=2 /home/db_backup/

# 全量恢復，建議恢復前停止mysql服務，且清空mysql數據文件
innobackupex --datadir=mysql數據路徑 --copy-back 備份路徑
innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/2018-04-21_10-44-22/

# 修改mysql數據路徑的許可權為777
chmod -R 777 /var/lib/mysql

🎈 mysql 的分區分表

  • 查看是否支援分區分表：show plugins;
  • 刪除分區命令：ALTER TABLE 表名 DROP PARTITION 分表名稱;
  • 分區分類：範圍分區、哈希分區、時間分區
# 範圍分區
CREATE TABLE `表名`(
    -- 數據欄位
)engine=INNODB
PARTITION BY RANGE(`欄位名稱`) (
    PARTITION 分表名稱 VALUES LESS THAN(範圍),
    PARTITION 分表名稱 VALUES LESS THAN(範圍)
);

# 哈希分區
create table `表名`(
    -- 數據欄位
)engine=INNODB
PARTITION BY HASH(UNIX TIMESTAMP(`欄位名稱`)) PATITIONS 4;

# 時間分區
create table `表名`(
    -- 數據欄位
)engine=INNODB
PARTITION BY RANGE(YEAR(`欄位名稱`))(
    PARTITION p0 VALUES LESS THAN(2017),
    PARTITION p1 VALUES LESS THAN(2018),
    PARTITION p2 VALUES LESS THAN(2019)
);

# 查看分區情況
SELECT
    table_name,partition_name,partition_description,table_rows
FROM
    information_schema.`PARTITIONS`
WHERE table_name = '表名';

# 歸檔分區數據，mysql資料庫版本需要大於等於5.7
-- 分區歸檔操作步驟
-- 1.新建和分區表欄位一致的數據表 歸檔表前綴為 arch_
CREATE TABLE `歸檔表表名`(
 
)engine=INNODB
-- 2.進行數據交換 p0為分區名
ALTER TABLE `原數據表表名` exchange PARTITION p0 WITH TABLE `歸檔表表名`;
-- 3.刪除分區，避免對數據的再次寫入
ALTER TABLE `原數據表表名` DROP PARTITION p0;
-- 4.將歸檔表引擎設置為 archive  在檢表語句中mysql引擎必須和原數據表引擎一致，否則無法進行數據交換
ALTER TABLE `歸檔表表名` ENGINE=ARCHIVE;

🎈 mysql 的主從複製讀寫分離

  • 授權遠程訪問 mysql 資料庫
  • 新建相關資料庫管理員，授權並開啟遠程訪問許可權
-- 建議新建一個備份和主從複製的資料庫管理員
CREATE USER 'backup'@'localhost' IDENTIFIED BY '密碼';
-- 分配相關許可權
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';
-- 開啟遠程訪問許可權
GRANT ALL PRIVILEGES ON *.* TO 授權用戶名@被授權伺服器的IP IDENTIFIED BY '授權密碼';
FLUSH PRIVILEGES;
  • 配置mysql主伺服器
  • 需要在 master 伺服器和 slave 伺服器都建立一個同名的數據(備份資料庫)
  • 在主伺服器中開啟 binlog 日誌和設置要發生主從同步資料庫，使用 vim 打開 /etc/my.cnf 文件，修改配置如下
  • 配置完畢後，需要重啟服務：service mysqld restart;
#mysql的bin-log日誌配置選項,假設做讀寫（主從），這個選項在從伺服器必須關閉
log_bin = binlog
#埠資訊，其實可以不寫
port = 3306
#主伺服器的id,這id不一定設為1，只要主從不一樣就行
server-id = 1
#要做同步的資料庫名字，可以是多個資料庫，之間用分號分割。
binlog_do_db = test
  • 檢查配置
  • 登錄 mysql 查看 binlog 日誌相關參數是否正確
show master status;
show variables like 'log_bin';
  • 配置mysql從伺服器
  • 主伺服器已經配置成功，這時要切換到從伺服器開始配置
  • 在從伺服器中開啟 binlog 日誌和設置要發生主從同步資料庫，使用 vim 打開 /etc/my.cnf 文件，修改配置如下
#從伺服器的id,必須與主伺服器的id是不同
server-id = 2
#主伺服器的ip地址
master-host = 192.168.56.2
#grant授權的可複製用戶帳號
master-user = backup
#grant授權的可複製密碼
master-password = 123456
#主伺服器的mysql埠
master-port = 3306
#這個參數是用來設置在和主伺服器連接丟失的時候，重試的時間間隔，默認是60秒
master-connect-retry = 20
#需要同步的主伺服器資料庫
replicate-do-db = test
  • 檢測主從複製配置是否成功
show slave status\G
 
-- 如果結果包含如下參數，則證明主從已經配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Tags:

VirMach 便宜 VPS

QNews

QNews