版本: 8.0.20 操作: Centos 7 Linux

未介紹針對資料庫的詳細操作，如有需求請前往

第一章 MySQL的介紹及安裝

1、介紹

1.1 資料庫管理系統(DBMS)
RDBMS : Oracle SQLServer DB2 MySQL
NoSQL : Redis MongoDB ES
NewSQL: PolarDB OceanBase TDB TiDB

1.2 排名
db-engines.com/ranking

1.3 MySQL 介紹

1.3.1 如何獲取軟體
www.mysql.com

1.3.2 企業版本選擇.6
5.7
8.0

GA 6-12月版本,大約20個版本左右,可以測驗使用了. 從邊緣業務開始,做好前期的測驗

1.3.3 分支
Oracle MySQL
Percona
MariaDB
RDS
TDB

2、MySQL的安裝配置

2.1 上傳并解壓軟體
[root@db01 local]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.ta

**2.2 軟連接 **

[root@db01 local]# mv mysql-8.0.20-linux-glibc2.12-x86_64 mysq

**2.3 環境變數 **

[root@db01 bin]# vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

生效配置

source /etc/profile

[root@db01 bin]# mysql -V
mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

**2.4 建用戶 **

[root@db01 bin]# useradd mysql

2.5 創建所需目錄
[root@db01 ~]# mkdir -p /data/3306/data
[root@db01 ~]# chown -R mysql.mysql /data

2.6 資料初始化
[root@db01 ~]# mv /etc/my.cnf /tmp
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

2.7 組態檔

[root@db01 ~]# vim /etc/my.cnf

[mysql]
socket=/tmp/mysql.sock
[mysqld]
server_id=10
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock

**2.8 準備啟動腳本 **
[root@db01 ~]# cd /usr/local/mysql/support-files/
[root@db01 support-files]# \cp mysql.server /etc/init.d/mysqld
[root@db01 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
. SUCCESS!

第二章體系結構及基礎管理

1、C/S

1.1 Unix Socket
1.2 網路socket(TCP/IP)
1.3 連接方法
自帶的客戶端程式: mysql mysqldump
開發工具: navicat sqlyog workbench
程式連接: pymysql

2、體系結構

2.1 server
2.1.1 連接層
協議
驗證
執行緒

2.1.2 SQL層
語法,語意,權限檢查
決議,生成決議樹
優化,生成執行計劃
執行,得出執行結果.

2.2 engine
從磁盤中段 , 區(簇,64個PAGE,默認1M) ,頁(16KB)

3、用戶管理

3.1 定義
oldguo@'白名單'

白名單:
%
10.0.0.%
10.0.0.5%
10.0.0.1
10.0.0.0/255.255.254.0
localhost

**3.2 用戶管理 **
注意:

1. 8.0之后,必須先建立用戶再授權,不能通過grant創建用戶和修改密碼.
 2. 8.0之后,加密插件從mysql_native_password 改為了 caching_sha2_password

(2) 創建用戶
mysql> create user oldguo@'10.0.0.%' identified with mysql_native_password by '123';
mysql> create user oldboy@'10.0.0.%' identified by '123';

(3) 修改

mysql> alter user root@'localhost' identified with mysql_native_password by '123';
mysql> alter user oldguo@'10.0.0.%' account lock;
mysql> alter user oldguo@'10.0.0.%' account unlock;

(4) 洗掉
drop user oldguo@'10.0.0.%';

4、權限管理

**4.1 權限的定義 **
mysql> show privileges;

8.0 加入了新特性role . approle
all
Grant option

**4.2 授權和回收 **
grant 權限 on 權限范圍 to 用戶

權限范圍:
.
app.*
app.t1
mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on . to root@'10.0.0.%' with grant option;

revoke
mysql> revoke drop on . from root@'10.0.0.%';
mysql> show grants for root@'10.0.0.%';

4.3 授權表
例如: users db tables_priv

**4.4 本地管理員密碼忘記 **

[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[root@db01 ~]# mysql
mysql> flush privileges ;
mysql> alter user root@'localhost' identified with mysql_native_password by '123';
[root@db01 ~]# /etc/init.d/mysqld restart

5、資料庫的啟動和關閉

5.1 mysqld 程式啟動
/usr/local/mysql/bin/mysqld &
直接啟動資料庫服務,并且列印所有日志到螢屏上.
一般是在需要啟動除錯時使用.

5.2 mysqld_safe 程式啟動
mysqld_safe ----> mysqld
一般是在需要啟動除錯時使用.例如,可以在啟動時,臨時加引數,--skip-grant-tables --skip-networking

**5.3 sys-v ---> /etc/init.d/ **

[root@db01 ~]# service mysqld stop
[root@db01 ~]# service mysqld strt
[root@db01 ~]# service mysqld restart

**5.4 systemd **
[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# systemctl restart mysqld

**5.5 關閉 **
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

6、組態檔

**6.1 默認讀取位置 **
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

6.2 改變默認組態檔位置
mysqld
mysqld_safe
--defaults-file=/opt/aa.txt

6.3 組態檔結構

[標簽] ----> 作用: 表示下面的配置影響了什么程式
配置

型別:
客戶端標簽: 影響本地登錄 ---> [mysql] [mysqldump]
服務端標簽: 影響資料庫啟動 ---> [mysqld] [mysqld_safe]

[mysql]
socket=/tmp/mysql.sock

[mysqld]
server_id=10
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock

7、連接管理

**7.1 本地客戶端工具連接 **
(1) Socket檔案方式
前提: 必須提前創建白名單為localhost用戶,并授權.例如:oldguo@'localhost'
[root@db01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock

(2) tcpip方式 :
前提: 必須提前創建白名單為遠程登錄用戶,并授權.例如:oldboy@'10.0.0.%'
[root@db01 ~]# mysql -uoldboy -p123 -h10.0.0.51 -P3306

**7.2 開發工具 **

第三章節 SQL 規范

去掉不必要的括號
如： ((a AND b) AND c OR (((a AND b) AND (c AND d))))
修改成 (a AND b AND c) OR (a AND b AND c AND d)
去掉重疊條件
如： (a<b AND b=c) AND a=5
修改成 b>5 AND b=c AND a=5
如： (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
修改成 B=5 OR B=6
避免使用not in、not exists 、<>、like %%
多表連接，小表驅動大表
減少臨時表應用，優化order by 、group by、uninon、distinct、join等
減少陳述句查詢范圍，精確查詢條件
多條件，符合聯合索引最左原則
查詢條件減少使用函式、拼接字符等條件、條件隱式轉換
union all 替代 union

10.減少having子句使用

11.如非必須不使用 for update陳述句

12.update和delete，開啟安全更新引數
mysql> set global sql_safe_updates=1;

13.減少inset ... select陳述句應用
如果是歸檔表,pt-archiver

14.使用load 替代insert錄入大資料

匯入大量資料時，可以禁用索引、增大緩沖區(innodb_buffer_pool_size)、增大redo(innodb_log_file_size)檔案和 redo buffer()、關閉autocommit、RC級別可以提高效率.

16.優化limit，最好業務邏輯中先獲取主鍵ID，再基于ID進行查詢
limit 5000000,10

DDL執行前要審核
8.0之前: 如果緊急,pt-osc,gh-ost
多表連接陳述句執行前要看執行計劃

欄位定義規范

每個表建議在30個欄位以內，
需要存盤emoji字符的，則選擇utf8mb4字符集，
機密資料，加密后存盤，
整型資料，默認加上UNSIGNED，
存盤IPV4地址建議用INT UNSIGNE，查詢時再利用INET_ATON()、INET_NTOA()函式轉換，
如果遇到BLOB、TEXT大欄位單獨存盤表或者附件形式存盤，
選擇盡可能小的資料型別，用于節省磁盤和記憶體空間，
存盤浮點數，可以放大倍數存盤， 3.14
每個表必須有主鍵，INT/BIGINT并且自增做為主鍵，分布式架構使用sequence序列生成器保存，
每個列使用not null，或增加默認值，
表名和業務有關,不使用大寫字母
列名要有意義,表名_列名
表名不要數字開頭,不要使用內置字符.
每個列要有注釋.

第四章索引及執行計劃

1、索引型別

Btree 索引
Hash 索引
FULLTEXT

2、Btree演算法

b-tree、b+tree、b*tree：請看博主關于mysql的應用詳解鏈接

3、MySQL 如何應用BTREE

3.1 聚簇(區)索引

IOT組織表: 索引組織表.聚簇索引組織表.資料行是按照聚簇索引順序組織存盤的.

前提:
1. 如果有主鍵,主鍵就是聚簇索引
2. 沒有主鍵,選擇第一個不為空的唯一鍵的列
3. 都沒有,生成隱藏列

3.2 輔助索引

**3.3 關于索引自由化能力 **

AHI : 自適應Hash索引
索引的索引. latch 閂鎖.
自動評估記憶體索引頁訪問頻率,將熱點記憶體頁生成hash串列,快速鎖定記憶體地址目的.

Change buffer(Insert buffer)
主要影響輔助索引的修改.
對于輔助索引的更新,不會立即進行,會將更新緩沖到change buffer區域中.

ICP 索引下推
(a,b,c)

MRR

3.4 索引優化

非唯一索引按照“i_欄位名稱_欄位名稱[_欄位名]”進行命名，
唯一索引按照“u_欄位名稱_欄位名稱[_欄位名]”進行命名，
索引名稱使用小寫，
索引中的欄位數不超過5個，
唯一鍵由3個以下欄位組成，并且欄位都是整形時，使用唯一鍵作為主鍵，
沒有唯一鍵或者唯一鍵不符合5中的條件時，使用自增id作為主鍵，
唯一鍵不和主鍵重復，
索引選擇度高的列作為聯合索引最左條件
ORDER BY，GROUP BY，DISTINCT的欄位需要添加在索引的后面， where a order by b
單張表的索引數量控制在5個以內，若單張表多個欄位在查詢需求上都要單獨用到索引，需要經過DBA評估，查詢性能問題無法解決的，應從產品設計上進行重構，
使用EXPLAIN判斷SQL陳述句是否合理使用索引，盡量避免extra列出現：Using File Sort，Using Temporary，
UPDATE、DELETE陳述句需要根據WHERE條件添加索引，
對長度大于50的VARCHAR欄位建立索引時，按需求恰當的使用前綴索引，或使用其他方法，
下面的表增加一列url_crc32，然后對url_crc32建立索引，減少索引欄位的長度，提高效率，
CREATE TABLE all_url(ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
url VARCHAR(255) NOT NULL DEFAULT 0,
url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,
index idx_url(url_crc32));
合理創建聯合索引（避免冗余），(a,b,c) 相當于 (a) 、(a,b) 、(a,b,c)，
合理利用覆寫索引，減少回表，
減少冗余索引和使用率較低的索引

第四章索引及執行計劃-下

1、作用

優化器選擇完的執行計劃.可以通過分析執行計劃,評估查詢陳述句的執行效率.

2、獲取執行計劃

3、分析執行計劃

table
type
possible_keys
key
key_len
rows
Extra

4、type

ALL 全表掃描
index 全索引掃描

range 索引范圍掃描

< >= <= like or in

**說明: **
in or 有些情境下可以改寫為union all ,主要看一下重復值多少,如果太多不建議調整.

select * from city where countrycode in ('CHN','USA');
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='world'
--query="select * from city where countrycode in ('CHN','USA')" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='world'
--query="select * from city where countrycode='CHN' union all select * from city where countrycode='USA'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

ref 輔助索引等值查詢

eq_ref 多表連接中的被驅動表的關聯條件是主鍵或唯一鍵

const(system) 主鍵或唯一鍵等值查詢

5、key_len

判斷聯合索引覆寫長度. 索引最大預估位元組長度.
a,b,c

a keylen 63
b keylen 21
c keylen 15

             not null    無not null

tinyint 1 1 1+1
int 4 4 4+1

utf8 not null 無 not null

char(10) 310 310+1

varchar(10) 310+2 310+2+1

utf8mb4 not null 無 not null

char(10) 410 410+1

varchar(10) 410+2 410+2+1

6、聯合索引應用原則:

i_a_b_c(a,b,c)

最左原則:
建索引,最左列唯一值多的
查詢條件必須包含最左列
完全覆寫
where a= and b= and c=
where a= and c= and b=
where a and b order c
部分覆寫
where a= and c=
where a= and c=
where a= and b < > and c=
不覆寫
where b= and c=

7、Extra

using filesort 額外排序 : order by group by distinct union
using temp 使用了臨時表 :

第五章節存盤引擎

1、介紹

mysql> show engines;
FEDERATED
MEMORY
InnoDB
PERFORMANCE_SCHEMA
MyISAM
MRG_MYISAM
BLACKHOLE
CSV
ARCHIVE

2、InnoDB 核心特性

? 事務
? 行鎖
? MVCC
? 多緩沖區
? 外鍵
? 熱備
? 自動故障恢復(Crash Recovery)
? DWB(double write buffer)

3、事務(Transaction "交易")

3.1 ACID特性
原子性 :
一致性 :
隔離性 :
持久性 :

**3.2 生命周期管理 **
(1) 標準事務控制陳述句
begin;/start transaction;
commit;
rollback;

(2) 自動提交機制
沒有使用顯示啟動事務時
begin;/start transaction;
對每一個DML陳述句,都會自動提交

(3) 隱式提交

**3.3 事務的隔離級別 **
RU : 臟讀不可重復讀幻讀
RC : 不可重復讀幻讀
RR : 可以配合GAP Next lock 解決幻讀
SE :

4、ACID 如何保證的?

4.1 redo 日志
WAL : 日志先行.

redo log file
redo log buffer

主要功能是實作ACID中的D的特性,同時還實作了AC.

**4.2 undo 日志 **

存盤資料行修改的前鏡像.
主要功能實作ACID中的A特性.CI的特性有一定影響

**4.3 I特性 **
隔離級別 : 讀
鎖 : 寫
MVCC : 利用多個版本readview ---> undo

4.4 C 特性
CR : crash recovery ---> redo undo
DWB: double write buffer

第六章節: 日志管理

1、general_log

**1.1 作用: **
記錄所有操作日志.

**1.2 配置 **
mysql> set global general_log=0;

2、log_error

mysql>
mysql> show variables like '%log_error%';

log_error | ./db01.err
log_error_verbosity | 2

2.1 作用
記錄資料運行期間,狀態警告報錯

2.2 配置
mysql> set global log_error_verbosity=3;

**2.3 查看 **

Z 0 [ERROR] [MY-000067] [Server] unknown variable 'defualts-file=/opt/aa.txt'.
2020-10-09T03:35:59.905745Z 0 [ERROR] [MY-010119] [Server] Aborting

3、慢日志slow_log

**3.1 作用: **
記錄資料庫中運行較慢的陳述句

3.2 配置
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> select @@slow_query_log_file;
+-------------------------------+
| @@slow_query_log_file |
+-------------------------------+
| /data/3306/data/db01-slow.log |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.01 sec)

mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> set glo
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time=0.1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

**3.3 分析 **
[root@db01 data]# mysqldumpslow -s c -t 3 db01-slow.log

4、binlog 二進制日志

4.1 作用
記錄MySQL發生過的修改類的操作日志.以二進制事件的方式記錄到日志檔案中.
可以用來做資料恢復,可以用來做主從復制.

**4.2 配置 **
8.0版本默認開啟,在資料目錄下存盤.

mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)

mysql> select @@log_bin_basename;
+------------------------+
| @@log_bin_basename |
+------------------------+
| /data/3306/data/binlog |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |

**二進制日志格式? **
row : 記錄航變化,記錄精準,日志量比較大.

update t1 set name='a' where id<1000; 記錄999行變化日志.

statement : SQL 資料操作時,如果有函式操作,例如亂數函式.
update t1 set name='a' where id<1000; 記錄SQL陳述句本身.

miexd: 混合模式.

4.3 管理操作

mysql> show binary logs;
mysql> show master status ;
mysql> show binlog events in 'binlog.000014';
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| binlog.000014 | 4 | Format_desc | 10 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000014 | 125 | Previous_gtids | 10 | 156 | |
| binlog.000014 | 156 | Anonymous_Gtid | 10 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000014 | 233 | Query | 10 | 347 | create database oldguo /* xid=62898 */ |
+---------------+-----+----------------+-----------+-------------+---------------------------------------

[root@db01 data]# mysqlbinlog --start-position=233 --stop-position=347 /data/3306/data/binlog.000014 >/tmp/bin.sql

mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;
mysql> flush logs;

mysql> select @@binlog_expire_logs_seconds;
mysql> PURGE BINARY LOGS TO 'binlog.000010';
mysql> reset master;

4.4 binlog2sql 閃回DML操作

yum install python3
pip3 install -r requirements.txt
pip3 show pymysql
pip3 install --upgrade PyMySQL

決議日志事件SQL

a. 單獨過濾某張表的binlog

[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d binlog -t t1 --start-file='binlog.000001'
INSERT INTO test1.t1(id) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid
INSERT INTO test1.t1(id) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid
INSERT INTO test1.t1(id) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid
INSERT INTO test1.t1(id) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid

b. 單獨過濾某些型別的binlog

[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=delete --start-file='mysql-bin.000003'
DELETE FROM test1.t1 WHERE id=3 LIMIT 1; #start 5172 end 5416 time 2020-09-18 09:17:48 gtid
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=update --start-file='mysql-bin.000003'
UPDATE test1.t1 SET id=10 WHERE id=1 LIMIT 1; #start 4882 end 5141 time 2020-09-18 09:17:35 gtid
[root@db01 binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=insert --start-file='mysql-bin.000003'
INSERT INTO test1.t1(id) VALUES (1); #start 1647 end 1891 time 2020-09-18 08:46:53 gtid
INSERT INTO test1.t1(id) VALUES (2); #start 1922 end 2166 time 2020-09-18 08:46:54 gtid
INSERT INTO test1.t1(id) VALUES (3); #start 3125 end 3369 time 2020-09-18 08:47:50 gtid
INSERT INTO test1.t1(id) VALUES (4); #start 3400 end 3644 time 2020-09-18 08:47:53 gtid
[root@db01 binlog2sql-master]#

c. 生成指定事件回滾陳述句
應用場景： 3000萬資料，誤刪10行資料，怎么恢復？

[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B

[root@db01 binlog2sql]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1 --start-file='mysql-bin.000003' --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql

第七章備份恢復

1、mysqldump

1.1 介紹
邏輯備份工具.備份的是SQL陳述句(Create database create table insert into)
比較適合于: 資料量小,大表比較少情景下使用.

1.2 使用

(1) 連接引數
-u -p -h -P -S ...
(2) -A 全備引數
mysqldump -uroot -p123 -S /tmp/mysql.sock -A >/tmp/full.sql

(3) -B 單庫\多庫備份
mysqldump -uroot -p123 -S /tmp/mysql.sock -B oldguo test >/tmp/db.sql

(4) 單表多表備份

mysqldump -uroot -p123 -S /tmp/mysql.sock world city country >/tmp/tb.sql

(5) --master-data=https://www.cnblogs.com/wylshkjj/p/2

1. 自動記錄備份時binlog name pos 
2. FTWRL(flush table with read lock)

(6) --single-transaction
基于一致性快照備份InnoDB的資料行.

(7) -R -E --triggers 特殊物件備份

source /tmp/full.sql

2、Percona Xtrabackup (PXB)

2.1 介紹
物理備份工具.拷貝表空間資料檔案. 在備份同時,產生的新的資料變化通過redo的方式備份走.
恢復時,需要先將備份進行prepare(CR--->redo undo),然后進行恢復.
自帶增量備份功能.

2.2 安裝
8.0.18以后版本,需要使用 pxb 8.0.13 版本.

yum install percona-xtrabackup*.rpm

注意：
對于MySQL 8.0.20版本，需要使用PXB 8.0.12+以上版本，MysQL：8.0.11 ~ 8.0.19  使用PXB 8.0正式版本，

MySQL 8.0 之前（5.6，5.7 ）的版本： PXB 2.4

2.3 全量備份

1.全量備份
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --target-dir=/data/backup/full


2.資料恢復：

2.0 搞破壞 

[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/3306/data/*
[root@db01 ~]# \rm -rf /data/3306/logs/*
[root@db01 ~]# \rm -rf /data/3306/binlog/*


2.1 準備：
xtrabackup   --prepare --target-dir=/data/backup/full

說明： 模擬CR程序，將redo前滾，undo回滾，讓備份資料是一致狀態


2.2 拷回資料：
xtrabackup  --copy-back --target-dir=/data/backup/full

2.3 修改權限并啟動資料庫
[root@db01 data]# chown -R mysql.mysql /data/*
[root@db01 data]# /etc/init.d/mysqld start

2.4 增量備份

**介紹 **

增量備份，是基于上一次備份LSN變化過的資料頁進行備份，在備份同時產生的新變更，會將redo備份，
第一次增量是依賴于全備的，將來的恢復也要合并到全備中，再進行統一恢復，


增量備份演練

全量備份的目錄為： mkdir  -p /data/backup/full
增量備份的目錄為： mkdir  -p /data/backup/inc

1.備份操作：
1.1.全量備份：
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/data/backup/full

mysql> create database pxb;
mysql> use pxb
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;


增量備份：
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=123 --port=3306 --backup --parallel=4 --target-dir=/data/backup/inc  --incremental-basedir=/data/backup/full



# 模擬損壞  
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# rm -rf /data/3306/logs/*
[root@db01 ~]# rm -rf /data/3306/binlog/*


恢復操作：
2.1 準備全備份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
2.2 準備增量備份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full  --incremental-dir=/data/backup/inc


全備份準備：
# xtrabackup --prepare --target-dir=/data/backup/full


拷回資料：
xtrabackup    --copy-back --target-dir=/data/backup/full

修改資料目錄的權限和屬性：
chown -R mysql:mysql /data/*



3. clone plugin

3、Clone Plugin介紹

8.0.17版本出現的功能.

本地克隆：
啟動克隆操作的MySQL服務器實體中的資料，克隆到同服務器或同節點上的一個目錄里

遠程克隆：
默認情況下，遠程克隆操作會洗掉接受者(recipient)資料目錄中的資料，并將其替換為捐贈者(donor)的克隆資料，您也可以將資料克隆到接受者的其他目錄，以避免洗掉現有資料，(可選)

原理

PAGE COPY

這里有兩個動作
開啟redo archiving功能，從當前點開始存盤新增的redo log，這樣從當前點開始所有的增量修改都不會丟失，同時上一步在page track的page被發送到目標端，確保當前點之前所做的變更一定發送到目標端，
關于redo archiving，實際上這是官方早就存在的功能，主要用于官方的企業級備份工具，但這里clone利用了該特性來維持增量修改產生的redo，
在開始前會做一次checkpoint，開啟一個后臺執行緒log_archiver_thread()來做日志歸檔，當有新的寫入時(notify_about_advanced_write_lsn)也會通知他去archive，當arch_log_sys處于活躍狀態時，他會控制日志寫入以避免未歸檔的日志被覆寫(log_writer_wait_on_archiver), 注意如果log_writer等待時間過長的話， archive任務會被中斷掉.

Redo Copy

停止Redo Archiving", 所有歸檔的日志被發送到目標端，這些日志包含了從page copy階段開始到現在的所有日志，另外可能還需要記下當前的復制點，例如最后一個事務提交時的binlog位點或者gtid資訊，在系統頁中可以找到，

Done

目標端重啟實體，通過crash recovery將redo log應用上去，

限制

*官方檔案列出的一些限制：
The clone plugin is subject to these limitations:

DDL, is not permitted during a cloning operation. This limitation should be considered when selecting data sources. A workaround is to use dedicated donor instances, which can accommodate DDL operations being blocked while data is cloned. Concurrent DML is permitted.
An instance cannot be cloned from a different MySQL server version. The donor and recipient must have the same MySQL server version. For example, you cannot clone between MySQL 5.7 and MySQL 8.0. The clone plugin is only supported in MySQL 8.0.17 and higher.
Only a single MySQL instance can be cloned at a time. Cloning multiple MySQL instances in a single cloning operation is not supported.
The X Protocol port specified byis not supported for remote cloning operations
The clone plugin does not support cloning of MySQL server configurations.
The clone plugin does not support cloning of binary logs.
The clone plugin only clones data stored in InnoDB. Other storage engine data is not cloned.
Connecting to the donor MySQL server instance through MySQL Router is not supported.
Local cloning operations do not support cloning of general tablespaces that were created with an absolute path. A cloned tablespace file with the same path as the source tablespace file would cause a conflict.

4、應用

4.1 本地
4.1.1 加載插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

4.1.2 創建克隆專用用戶
CREATE USER clone_user@'%' IDENTIFIED by 'password';
GRANT BACKUP_ADMIN ON . TO 'clone_user';

BACKUP_ADMIN是MySQL8.0 才有的備份鎖的權限

4.1.3 本地克隆
[root@db01 3306]# mkdir -p /data/test/
[root@db01 3306]# chown -R mysql.mysql /data/

mysql -uclone_user -ppassword
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

觀測狀態

日志觀測：
set global log_error_verbosity=3;
tail -f db01.err
CLONE LOCAL DATA DIRECTORY = '/data/test/3308';

4.1.4 啟動新實體
[root@db01 clonedir]# mysqld_safe --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &

4.2 遠程clone

**4.2.1 作用 **
a. 遠程的快速熱遷移，
b. 快速創建復制關系

4.2.2 環境準備
a. 克隆1臺虛擬機db02
b. 捐贈者： db01 接受者： db02
c. 接收者(db02)，搭建空的資料庫實體，
pkill mysqld
\rm -rf /data/3306/data/*
\rm -rf /data/3306/binlog/*
\rm -rf /data/3306/logs/*
mysqld --initialize-insecure --user=mysql --basedir=/user/local/mysql --datadir=/data/3306/data
/etc/init.d/mysqld restart

4.2.3 加載各個節點插件

INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

4.2.4 創建遠程clone用戶

捐贈者授權（db01）

create user test_jz@'%' identified by '123';
grant backup_admin on . to test_jz@'%';

接受者授權(db02)

create user test_js@'%' identified by '123';
grant clone_admin on . to test_js@'%';

4.2.5 遠程clone(db02)

開始克隆

mysql -e "SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';"
mysql -utest_js -p123 -h10.0.0.52 -P3306 -e "CLONE INSTANCE FROM test_jz@'10.0.0.51':3306 IDENTIFIED BY '123';"

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/213793.html

標籤：MySQL

上一篇：Android WebView

下一篇：Redis學習(一)——初識Redis

MySQL-8.0.20

版本: 8.0.20 操作: Centos 7 Linux

第一章 MySQL的介紹及安裝

1、介紹

2、MySQL的安裝配置

生效配置

第二章 體系結構及基礎管理

1、C/S

2、體系結構

3、用戶管理

4、權限管理

5、資料庫的啟動和關閉

6、組態檔

7、連接管理

第三章節 SQL 規范

欄位定義規范

第四章 索引及執行計劃

1、索引型別

2、Btree演算法

3、MySQL 如何應用BTREE

第四章 索引及執行計劃-下

1、作用

2、獲取執行計劃

3、分析執行計劃

4、type

5、key_len

6、聯合索引應用原則:

7、Extra

第五章節 存盤引擎

1、介紹

2、InnoDB 核心特性

3、事務(Transaction "交易")

4、ACID 如何保證的?

第六章節: 日志管理

1、general_log

2、log_error

3、慢日志slow_log

4、binlog 二進制日志

決議日志事件SQL

第七章 備份恢復

1、mysqldump

2、Percona Xtrabackup (PXB)

3、Clone Plugin介紹

限制

4、應用

第二章體系結構及基礎管理

第四章索引及執行計劃

第四章索引及執行計劃-下

第五章節存盤引擎

第七章備份恢復