# 官網(wǎng)
https://www.mysql.com/
# 下載模塊
https://downloads.mysql.com/archives/community/
# 官網(wǎng)下載鏈接
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]下面 添加 跳過登錄校驗(yàn)
# 跳過登錄校驗(yàn)
skip-grant-tables
# 修改mysql服務(wù)端口 也可以不換
port=23306
# 啟動mysql
systemctl start mysqld.service
# 進(jìn)入mysql
mysql
# 設(shè)置登錄密碼
update mysql.user set authentication_string=password('admin123') where user='root';
# 刷新
flush privileges;
# 推出
exit;
?
# 重啟
systemctl restart mysqld.service
# 停止
systemctl stop mysqld.service
# 注釋掉登錄校驗(yàn)
vim /etc/my.cnf
#在 [mysqld]下面 添加 跳過登錄校驗(yàn)
# 跳過登錄校驗(yàn)
# skip-grant-tables
?
# 啟動
systemctl start mysqld.service
?
# 登錄
mysql -h 127.0.0.1 -P 3306 -u root -padmin123
mysql -uroot -padmin123
?
# 設(shè)置密碼的驗(yàn)證強(qiáng)度等級，設(shè)置 validate_password_policy 的全局參數(shù)為 LOW
set global validate_password_policy=LOW;
//設(shè)置最小長度
set global validate_password_length=4;
?
set password=password('admin123');
?
#在 mysql 數(shù)據(jù)庫的 user 表中查看當(dāng)前 root 用戶的相關(guān)信息
select host, user, authentication_string, plugin from user; 
#授權(quán) root 用戶的所有權(quán)限并設(shè)置遠(yuǎn)程訪問
?
?
#刷新權(quán)限列表
flush privileges;
# 增加新用戶 格式：grant 權(quán)限 on 數(shù)據(jù)庫.* to 用戶名@登錄主機(jī) identified by "密碼"
# 如，增加一個(gè)用戶rent密碼為admin123，讓其可以在本機(jī)上登錄， 并對所有數(shù)據(jù)庫有查詢、插入、修改、刪除的權(quán)限。
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

備份方式的二分類維度：狀態(tài)、格式內(nèi)容
備份方式并沒有絕對的好壞，只有不同的用途
1.備份時(shí)數(shù)據(jù)庫的狀態(tài)
2.備份文件的格式
3.備份的內(nèi)容

Hot Backup (熱備)：正常運(yùn)行中直接備份
Cold BackUp (冷備)：完全停止后備份
Warm BackUp （冷備）：數(shù)據(jù)庫只讀

1.邏輯備份：輸出文件或SQL語句
2.物理備份（裸文件）：備份數(shù)據(jù)庫底層文件

1.完全備份: 備份完整數(shù)據(jù)
2.增量備份：備份上次備份的數(shù)據(jù)差異
3.日志備份：備份Binlog

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

1.Mysql原生的SQL指令
2.最原始的邏輯備份方式
3.備份的功能和效果取決于如何寫SQL語句
4.在innoDB事務(wù)下?？梢宰龅揭恢滦栽噲D
5.修改分隔符：fileds terminated by
6.修改換行符： lines terminated by
?
缺點(diǎn)：
1.輸出的文本比較簡略
2.很難進(jìn)行還原，現(xiàn)在往往用來簡單的導(dǎo)出

# mysql 可以操作的文件夾
show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
# 使用into outfile 指令將查詢結(jié)果到處至文件
select * into outfile '/var/lib/mysql-files/out_file_test' from Z;
# 查詢數(shù)據(jù)庫
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                |
+-------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
?
# 將查詢的結(jié)果放到文件里面
select * into outfile '/var/lib/mysql-files/host_summary'  from host_summary;
Query OK, 2 rows affected (0.01 sec)
# 推出到系統(tǒng)中查看
[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
# 備份一致性表的內(nèi)容 開啟事務(wù)再導(dǎo)入  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.自動發(fā)select語句。不需要手動
2.自動開啟事務(wù)
3.輸出 inster語句，可以直接用來還原
4.非常常用的mysql邏輯備份工具
5.Mysql server自帶
6.輸出的而備份內(nèi)容為SQL語句，平衡了閱讀和還原
7.SQL語句占空間較小
8.mysqldump可以使用以下語句對數(shù)據(jù)進(jìn)行備份 SQL_NO_CACHE 查詢出的數(shù)據(jù)不會進(jìn)入SQL的緩存
select SQL_NO_CACHE FROM t;
9.mysqldump使用以下語句對數(shù)據(jù)進(jìn)行備份
mysqldump -uroot -padmin123 --databases d1 --single-transaction > test.sql;
10.直接執(zhí)行導(dǎo)出的sql文件即可進(jìn)行還原 
source test.sql;

# 新建數(shù)據(jù)庫 my_db_1 /新建表/添加數(shù)據(jù)
?
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數(shù)據(jù)庫
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級別下進(jìn)行
2.--lock-all-tables: 使用FTWRL鎖所有表（MyISAM）
3.--lock-tables：使用READ LOCAL 鎖當(dāng)前庫的表（MySAM）
4.--all-database：備份所有庫

1.mysqldump使用簡單、可以熱備
2.sql文件可以直接執(zhí)行、占空間小、可以閱讀
3.備份還原性能不如物理備份

1.binlig中記錄了Mysql數(shù)據(jù)的變化
2.mysqllddump全量備份之后，可以用binlog作為增量
3.mysqllddump全量備份時(shí)，切換新的binlog文件
4.從零還原時(shí)，采用全量還原+binlog還原
# mysql數(shù)據(jù)路徑 
cd /var/lib/mysql

1.mysqldump使用以下語句對數(shù)據(jù)進(jìn)行全量備份
2.--flush-logs備份之后切換binlog文件
3.-master-data=2 記錄切換后的binlog文件名
4. vim /etc/my.cnf 在[mysqld]標(biāo)簽下添加: 重啟mysql
log-bin=mysql-bin
server-id=1
# 重啟
systemctl restart mysqld.service
# 執(zhí)行備份
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.需要增量備份時(shí)，切換binlog文件，會生成一個(gè)新的binlog文件
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
2.
1.mysqldump使用以下語句對數(shù)據(jù)進(jìn)行全量備份
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增量還原至數(shù)據(jù)庫
mysqlbinlog mysql-bin.000006 | mysql -uroot -padmin123

1.mysqldump+binlog可以有效對數(shù)據(jù)進(jìn)行全量+增量備份
2.兩個(gè)組件各司其職，是工程時(shí)間中的靜待你作法
3.理論上來說，可以將數(shù)據(jù)庫恢復(fù)至binlog的任意時(shí)刻
4.缺點(diǎn)操作起來較為復(fù)雜
5.需要執(zhí)行sql，解析數(shù)據(jù)，影響數(shù)據(jù)庫性能

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.修改數(shù)據(jù)刷新binlog
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
4.修改數(shù)據(jù)刷新binlog
mysqladmin -hlocalhost  -uroot -padmin123  flush-logs
5.刪庫
DROP DATABASE IF EXISTS my_db_1;
show databases;
6.恢復(fù)全量數(shù)據(jù)
source /var/lib/mysql/my_db_1_back.sql;
7.恢復(fù)binlog數(shù)據(jù)
exit;
mysqlbinlog mysql-bin.000010 | mysql -uroot -padmin123

1.直接備份InnoDB底層數(shù)據(jù)文件
2.導(dǎo)出不需要轉(zhuǎn)換，速度快
3.工作時(shí)對數(shù)據(jù)庫的壓力較小
4.更容易實(shí)現(xiàn)增量備份
5.物理備份是一種高效的備份方式
6.XtraBackup采用了備份ibd——備份期間redo log方式
7.XtraBackUp是最常用的Mysql物理備份工具8
8.缺是不能直接閱讀備份的文件

1.啟動redo log 監(jiān)聽線程，開始手收集redo log
2.拷貝ibd數(shù)據(jù)文件
3.停止收集redo log
4.加FTWRL鎖拷貝元數(shù)據(jù)Frm

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

1.思路：mysqld crash崩潰恢復(fù)流程相似
2.還原ibd文件，重放redo log

1.命名MySQL Enterprise Backup InnoDB官方出品 商業(yè)版收費(fèi)
2.實(shí)現(xiàn)了上述的功能，性能優(yōu)秀

1.Percona公司開發(fā)的開源版本，實(shí)現(xiàn)ibbackup所有功能
2.XtraBackup 8.0->8.0
3.XtraBackUp 2.4 -> MySql 5.1,5.2,5.5,5.6,5.7

1.官網(wǎng)下載
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
# 百度網(wǎng)盤
鏈接: 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.數(shù)據(jù)還原 停掉mysqld
systemctl stop mysqld.service
?
8.關(guān)閉數(shù)據(jù)庫
sudo systemctl stop mysqld
?
9.清空現(xiàn)有數(shù)據(jù)目錄 
sudo rm -r /var/lib/mysql/*
?
ls -lh /var/lib/mysql/
?
10.注意：如果不清空數(shù)據(jù)目錄，會報(bào)如下錯(cuò)誤，并終止恢復(fù)：
Original data directory /var/lib/mysql is not empty!
?
11. 執(zhí)行恢復(fù)數(shù)據(jù)庫命令 /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. 修改數(shù)據(jù)庫文件的所有者用戶
sudo chown -R mysql.mysql /var/lib/mysql/*
13.啟動
systemctl start mysqld.service
?
查看恢復(fù)情況
?
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.直接備份磁盤數(shù)據(jù)

1.跟mysqldump類似的工具
2.實(shí)現(xiàn)了多線程并發(fā)的備份還原
3.速度更快
4.對于數(shù)據(jù)庫性能影響更大，不過影響時(shí)間更短

1.功能強(qiáng)大的備份恢復(fù)管理工具
2.集成了多種備份工具
3.繼承binlog分析功能

1.給業(yè)務(wù)應(yīng)用分配的賬號只給DML權(quán)限
2.開發(fā)同學(xué)使用只讀賬號
3.DBA平時(shí)使用時(shí)使用只讀賬號，特殊操作時(shí)切換賬號
4.DBA在開發(fā)環(huán)境審計(jì)即將上線的SQL語句
5.開發(fā)同學(xué)修改在線數(shù)據(jù)，提交給DBA執(zhí)行
6.inception自動審核工具
7.刪表之前將表改名，觀察業(yè)務(wù)是否受影響
8.不直接刪表，給表明加特殊后綴，用腳本刪除
9.上線之前備份數(shù)據(jù)
10.準(zhǔn)備生產(chǎn)環(huán)境事故預(yù)案