CentOS7.x 安裝mysql5.7 XtraBackUp備份工具使用命令詳解

# 官網
https://www.mysql.com/
# 下載模組
https://downloads.mysql.com/archives/community/
# 官網下載連結
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar

mkdir /app && mkdir /app/mysql57 && cd /app/mysql57
# lunix下載命令
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
# 解壓
tar -xvf mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar
[root@localhost mysql57]# ls
mysql-5.7.18-1.el7.x86_64.rpm-bundle.tar                 mysql-community-libs-5.7.18-1.el7.x86_64.rpm
mysql-community-client-5.7.18-1.el7.x86_64.rpm           mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm
mysql-community-common-5.7.18-1.el7.x86_64.rpm           mysql-community-minimal-debuginfo-5.7.18-1.el7.x86_64.rpm
mysql-community-devel-5.7.18-1.el7.x86_64.rpm            mysql-community-server-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-5.7.18-1.el7.x86_64.rpm         mysql-community-server-minimal-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.18-1.el7.x86_64.rpm  mysql-community-test-5.7.18-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.18-1.el7.x86_64.rpm
[root@localhost mysql57]# 

# 安裝 community-common
rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm 
​
# 解除安裝  mariadb
rpm -qa | grep  mariadb
​
[root@localhost mysql57]# rpm -qa | grep  mariadb
mariadb-libs-5.5.68-1.el7.x86_64
​
# 解除安裝
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
# 安裝庫 
rpm -ivh --force --nodeps mysql-community-libs-compat-5.7.18-1.el7.x86_64.rpm
# 安裝使用者端
rpm -ivh  --force --nodeps mysql-community-client-5.7.18-1.el7.x86_64.rpm
# 安裝net-tools
yum install net-tools -y
# 安裝server
rpm -ivh  --force --nodeps mysql-community-server-5.7.18-1.el7.x86_64.rpm
# 檢查安裝情況
mysql -uroot -p
# 檢視mysql 安裝目錄
which mysql
/usr/bin/mysql

vim /etc/my.cnf
#在 [mysqld]下面 新增 跳過登入校驗
# 跳過登入校驗
skip-grant-tables
# 修改mysql伺服器埠 也可以不換
port=23306
# 啟動mysql
systemctl start mysqld.service
# 進入mysql
mysql
# 設定登入密碼
update mysql.user set authentication_string=password('admin123') where user='root';
# 重新整理
flush privileges;
# 推出
exit;
​
# 重啟
systemctl restart mysqld.service
# 停止
systemctl stop mysqld.service
# 註釋掉登入校驗
vim /etc/my.cnf
#在 [mysqld]下面 新增 跳過登入校驗
# 跳過登入校驗
# skip-grant-tables
​
# 啟動
systemctl start mysqld.service
​
# 登入
mysql -h 127.0.0.1 -P 3306 -u root -padmin123
mysql -uroot -padmin123
​
# 設定密碼的驗證強度等級，設定 validate_password_policy 的全域性引數為 LOW
set global validate_password_policy=LOW;
//設定最小長度
set global validate_password_length=4;
​
set password=password('admin123');
​
#在 mysql 資料庫的 user 表中檢視當前 root 使用者的相關資訊
select host, user, authentication_string, plugin from user; 
#授權 root 使用者的所有許可權並設定遠端存取
​
​
#重新整理許可權列表
flush privileges;
# 增加新使用者 格式：grant 許可權 on 資料庫.* to 使用者名稱@登入主機 identified by "密碼"
# 如，增加一個使用者rent密碼為admin123，讓其可以在本機上登入， 並對所有資料庫有查詢、插入、修改、刪除的許可權。
grant select,insert,update,delete on *.* to rent@localhost Identified by "admin123" with grant option;
​
grant all privileges on *.* to 'root'@'%' Identified by "admin123" with grant option;
​
flush privileges;
exit;
# 開啟mysql 防火牆 33306 /沒修改埠則是3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent  
# 重新載入
firewall-cmd --reload
#opyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
​
#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
​
[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /app/mysql57/mysql
#log-error  = /var/log/mysql/error.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
​
event_scheduler=ON
max_connections = 2000
max_user_connections = 1900
max_connect_errors = 100000
max_allowed_packet = 50M
lower_case_table_names=1
character_set_server=utf8 
collation-server=utf8_general_ci 
log_timestamps=SYSTEM
default-time-zone = '+8:00' 
[mysqld]
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
wait_timeout=315360
interactive_timeout=31536000
#開啟慢紀錄檔
slow_query_log = ON
slow_query_log_file=/var/log/mysql/error.log
long_query_time=4

備份方式的二分類維度：狀態、格式內容
備份方式並沒有絕對的好壞，只有不同的用途
1.備份時資料庫的狀態
2.備份檔案的格式
3.備份的內容

Hot Backup (熱備)：正常執行中直接備份
Cold BackUp (冷備)：完全停止後備份
Warm BackUp （冷備）：資料庫唯讀

1.邏輯備份：輸出檔案或SQL語句
2.物理備份（裸檔案）：備份資料庫底層檔案

1.完全備份: 備份完整資料
2.增量備份：備份上次備份的資料差異
3.紀錄檔備份：備份Binlog

mysqldump：邏輯、熱、全量備份
xtrabackup: 物理、熱、全量+增量備份

1.Mysql原生的SQL指令
2.最原始的邏輯備份方式
3.備份的功能和效果取決於如何寫SQL語句
4.在innoDB事務下。可以做到一致性試圖
5.修改分隔符：fileds terminated by
6.修改換行符： lines terminated by
​
缺點：
1.輸出的文字比較簡略
2.很難進行還原，現在往往用來簡單的匯出

# mysql 可以操作的資料夾
show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
# 使用into outfile 指令將查詢結果到處至檔案
select * into outfile '/var/lib/mysql-files/out_file_test' from Z;
# 查詢資料庫
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
# 切換庫 
use sys;
# 查詢表
show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
​
# 查詢語句
select * from host_summary;
+-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host        | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| 192.168.1.8 |         50 | 11.16 ms          | 223.24 us             |          24 |        0 | 0 ps            |                   1 |                 7 |            2 | 0 bytes        | 0 bytes                |
| localhost   |        175 | 36.15 ms          | 206.55 us             |           9 |      174 | 13.49 ms        |                   1 |                 2 |            1 | 0 bytes        | 0 bytes                |
+-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
​
# 將查詢的結果放到檔案裡面
select * into outfile '/var/lib/mysql-files/host_summary'  from host_summary;
Query OK, 2 rows affected (0.01 sec)
# 推出到系統中檢視
[root@localhost mysql]# tail -f /var/lib/mysql-files/host_summary
192.168.1.8 50  11.16 ms    223.24 us   24  0   0 ps    1   7   2   0 bytes 0 bytes
localhost   256 55.90 ms    218.36 us   10  176 13.62 ms    1   2   1   0 bytes 0 bytes
# 備份一致性表的內容 開啟事務再匯入  fields terminated by ',' 新增分隔符
begin;
select * into outfile '/var/lib/mysql-files/host_summary3'  fields terminated by ',' from host_summary;
​
[root@localhost mysql]# tail -f /var/lib/mysql-files/host_summary3
192.168.1.8,50,11.16 ms,223.24 us,24,0,0 ps,1,7,2,0 bytes,0 bytes
localhost,526,85.30 ms,162.16 us,14,213,14.04 ms,1,3,1,0 bytes,0 bytes

1.自動發select語句。不需要手動
2.自動開啟事務
3.輸出 inster語句，可以直接用來還原
4.非常常用的mysql邏輯備份工具
5.Mysql server自帶
6.輸出的而備份內容為SQL語句，平衡了閱讀和還原
7.SQL語句佔空間較小
8.mysqldump可以使用以下語句對資料進行備份 SQL_NO_CACHE 查詢出的資料不會進入SQL的快取
select SQL_NO_CACHE FROM t;
9.mysqldump使用以下語句對資料進行備份
mysqldump -uroot -padmin123 --databases d1 --single-transaction > test.sql;
10.直接執行匯出的sql檔案即可進行還原 
source test.sql;

# 新建資料庫 my_db_1 /新建表/新增資料
​
mysql> use my_db_1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
mysql> show tables;
+-------------------+
| Tables_in_my_db_1 |
+-------------------+
| gen_table         |
| gen_table_column  |
| sys_config        |
| sys_dept          |
| sys_dict_data     |
| sys_dict_type     |
| sys_job           |
| sys_job_log       |
| sys_logininfor    |
| sys_menu          |
| sys_notice        |
| sys_oper_log      |
| sys_post          |
| sys_role          |
| sys_role_dept     |
| sys_role_menu     |
| sys_user          |
| sys_user_post     |
| sys_user_role     |
+-------------------+
19 rows in set (0.00 sec)
​
# 推出使用者端 備份my_db_1資料庫
exit;
mysqldump -uroot -padmin123 --databases my_db_1 --single-transaction > test.sql;
​
# 直接輸入mysqldump命令 
[root@localhost mysql]# mysqldump
​
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
​
mysqldump --defaults-file="/etc/my.cnf" -hlocalhost  -uroot -padmin123 --databases my_db_1 --single-transaction > my_db_1.sql
​
[root@localhost back]# mysqldump --defaults-file="/etc/my.cnf" -hlocalhost  -uroot -padmin123 --databases my_db_1 --single-transaction > my_db_1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost back]# ls
my_db_1.sql
# 裡面有建表語句和insert into 語句 鎖表語句
cat my_db_1.sql 

1.--single-transaction: 在RR級別下進行
2.--lock-all-tables: 使用FTWRL鎖所有表（MyISAM）
3.--lock-tables：使用READ LOCAL 鎖當前庫的表（MySAM）
4.--all-database：備份所有庫

1.mysqldump使用簡單、可以熱備
2.sql檔案可以直接執行、佔空間小、可以閱讀
3.備份還原效能不如物理備份

1.binlig中記錄了Mysql資料的變化
2.mysqllddump全量備份之後，可以用binlog作為增量
3.mysqllddump全量備份時，切換新的binlog檔案
4.從零還原時，採用全量還原+binlog還原
# mysql資料路徑 
cd /var/lib/mysql

1.mysqldump使用以下語句對資料進行全量備份
2.--flush-logs備份之後切換binlog檔案
3.-master-data=2 記錄切換後的binlog檔名
4. vim /etc/my.cnf 在[mysqld]標籤下新增: 重啟mysql
log-bin=mysql-bin
server-id=1
# 重啟
systemctl restart mysqld.service
# 執行備份
mysqldump --defaults-file="/etc/my.cnf" -hlocalhost  -uroot -padmin123 --databases my_db_1 --single-transaction --flush-logs --master-data=2  > my_db_1_back2.sql

1.需要增量備份時，切換binlog檔案，會生成一個新的binlog檔案
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
2.
1.mysqldump使用以下語句對資料進行全量備份
mysqldump --defaults-file="/etc/my.cnf" -hlocalhost  -uroot -padmin123 --databases my_db_1 --single-transaction --flush-logs --master-data=2  > my_db_1_back2.sql

source test.sql;
# 然後將binlog增量還原至資料庫
mysqlbinlog mysql-bin.000006 | mysql -uroot -padmin123

1.mysqldump+binlog可以有效對資料進行全量+增量備份
2.兩個元件各司其職，是工程時間中的靜待你作法
3.理論上來說，可以將資料庫恢復至binlog的任意時刻
4.缺點操作起來較為複雜
5.需要執行sql，解析資料，影響資料庫效能

1.先全量備份
mysqldump --defaults-file="/etc/my.cnf" -hlocalhost  -uroot -padmin123 --databases my_db_1 --single-transaction --flush-logs --master-data=2  > my_db_1_back2.sql
2.重新整理binlog
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
3.修改資料重新整理binlog
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
4.修改資料重新整理binlog
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
5.刪庫
DROP DATABASE IF EXISTS my_db_1;
show databases;
6.恢復全量資料
source /var/lib/mysql/my_db_1_back.sql;
7.恢復binlog資料
exit;
mysqlbinlog mysql-bin.000010 | mysql -uroot -padmin123

1.直接備份InnoDB底層資料檔案
2.匯出不需要轉換，速度快
3.工作時對資料庫的壓力較小
4.更容易實現增量備份
5.物理備份是一種高效的備份方式
6.XtraBackup採用了備份ibd——備份期間redo log方式
7.XtraBackUp是最常用的Mysql物理備份工具8
8.缺是不能直接閱讀備份的檔案

1.啟動redo log 監聽執行緒，開始手收集redo log
2.拷貝ibd資料檔案
3.停止收集redo log
4.加FTWRL鎖拷貝後設資料Frm

1.思路：與全量級別相同
2.如何確定增量：根據每個頁的LSN號，確定變化的頁

1.思路：mysqld crash崩潰恢復流程相似
2.還原ibd檔案，重放redo log

1.命名MySQL Enterprise Backup InnoDB官方出品 商業版收費
2.實現了上述的功能，效能優秀

1.Percona公司開發的開源版本，實現ibbackup所有功能
2.XtraBackup 8.0->8.0
3.XtraBackUp 2.4 -> MySql 5.1,5.2,5.5,5.6,5.7

1.官網下載
https://www.percona.com/
2.下載頁
https://www.percona.com/downloads/
3.2.4下載版本選擇頁
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
4.下載連結
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
# 百度網路硬碟
連結: https://pan.baidu.com/s/1nVORg8ox5rgTFsVztCAjHg?pwd=j3kb 
提取碼: j3kb 
5.安裝
rpm -ivh  --force --nodeps percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
6.備份 
innobackupex --user=root --password=admin123 /app/mysql57/back
/ 
7.資料還原 停掉mysqld
systemctl stop mysqld.service
​
8.關閉資料庫
sudo systemctl stop mysqld
​
9.清空現有資料目錄 
sudo rm -r /var/lib/mysql/*
​
ls -lh /var/lib/mysql/
​
10.注意：如果不清空資料目錄，會報如下錯誤，並終止恢復：
Original data directory /var/lib/mysql is not empty!
​
11. 執行恢復資料庫命令 /app/mysql57/back/2022-04-10_00-30-18 備份的位置
innobackupex --defaults-file=/etc/my.cnf --copy-back /app/mysql57/back/2022-04-10_00-30-18
12. 修改資料庫檔案的所有者使用者
sudo chown -R mysql.mysql /var/lib/mysql/*
13.啟動
systemctl start mysqld.service
​
檢視恢復情況
​
14.增量備份方法
innobackupex --user=root --password=admin123 輸出目錄/ --incremental-basedir'/bakdir/XXXX-XX-XX'
15.增量備份合併至全量備份
innobackupex --apply-log bakdir/XXXX-XX-XX/ -incremental-dir=backdir/YYYY-YY-YY/

1.mylvmbackup 備份磁碟
2.物理問唄
3.利用LVM（Logical Volume Manager） 邏輯卷管理器
4.直接備份磁碟資料

1.跟mysqldump類似的工具
2.實現了多執行緒並行的備份還原
3.速度更快
4.對於資料庫效能影響更大，不過影響時間更短

1.功能強大的備份恢復管理工具
2.整合了多種備份工具
3.繼承binlog分析功能

1.給業務應用分配的賬號只給DML許可權
2.開發同學使用唯讀賬號
3.DBA平時使用時使用唯讀賬號，特殊操作時切換賬號
4.DBA在開發環境審計即將上線的SQL語句
5.開發同學修改線上資料，提交給DBA執行
6.inception自動稽核工具
7.刪表之前將表改名，觀察業務是否受影響
8.不直接刪表，給表明加特殊字尾，用指令碼刪除
9.上線之前備份資料
10.準備生產環境事故預案