CentOS 系统安装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.准备生产环境事故预案