當前位置：首頁 > 數據庫 > Mysql > 詳細頁面

腳本之家教小白mysql 中的備份恢復,分區分表,主從復制,讀寫分離

時間：2022-09-11來源：www.farandoo.com作者：電腦系統城

mysql 的備份和恢復
mysql 的分區分表
mysql 的主從復制讀寫分離

mysql 的備份和恢復

創建備份管理員
創建備份管理員，并授予管理員相應的權限
備份所需權限：select,reload,lock tables,replication client,show view,event,process

1
2
3
4
5 # 創建管理員
create user 'backup'@'localhost' identified by '123456';

# 給管理員授權
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';

sql 文件恢復之全量恢復只要將備份的 sql 文件直接導入數據庫即可

1	`mysql -uroot -p 數據庫 < sql文件`

sql 文件恢復之基于時間點的恢復

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 # 首先進行一次基于最近一次的全量備份的文件進行一次全量恢復
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 只能保證某一備份數據庫的完整性，不能保證整個實例備份的完整性

1	`mysqldump -ubackup -p` `--master-data=2 --single-transaction --routines --triggers --events 數據庫 > 備份文件.sql`

XtraBackup備份：

自定義下載地址：Percona 存儲庫
命令下載：yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm?
測試儲存庫安裝是否成功命令：yum list | grep percona

1
2
3
4
5 # 下載 libev軟件包
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

# 安裝 libev軟件包
yum install percona-xtrabackup-24

1
2
3
4
5
6
7
8
9
10 # 全量備份
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 分表名稱;
分區分類：范圍分區、哈希分區、時間分區

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44 # 范圍分區
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 數據庫
新建相關數據庫管理員，授權并開啟遠程訪問權限

1
2
3
4
5
6
7 -- 建議新建一個備份和主從復制的數據庫管理員
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;

1
2
3
4
5
6
7
8 #mysql的bin-log日志配置選項,假設做讀寫（主從），這個選項在從服務器必須關閉
log_bin = binlog
#端口信息，其實可以不寫
port = 3306
#主服務器的id,這id不一定設為1，只要主從不一樣就行
server-id = 1
#要做同步的數據庫名字，可以是多個數據庫，之間用分號分割。
binlog_do_db = test

檢查配置：登錄 mysql 查看 binlog 日志相關參數是否正確

1 2	`show master status;` `show variables` `like` `'log_bin';`

配置mysql從服務器：

主服務器已經配置成功，這時要切換到從服務器開始配置
在從服務器中開啟 binlog 日志和設置要發生主從同步數據庫，使用 vim 打開 /etc/my.cnf 文件，修改配置如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14 #從服務器的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