标签：10 -- 备份 Mysql xtrabackup mysql root

Mysql备份笔记

Mysql日志类型

日志分类

• 错误日志 ：启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqld.log

• 通用查询日志：所有的查询都记下来

• 二进制日志：实现备份，增量备份。只记录改变数据，除了select都记

• 中继日志：读取主服务器的binlog，在本地回放。保持一致

• slow log：慢查询日志，指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优建议给开发人员

• DDL log： 定义语句的日志

Error log

 进入主配置文件，观察日志是否启动
 [root@mysql ~] vim /etc/my.cnf
 log-error=/var/log/mysqld.log      该字段，标记是否启动日志，以及日志位置  如果Mysql服务起不来了，就来看这个

Binary Log

默认没有开启,二进制日志

启动二进制日志

 [root@mysql ~]vim /etc/my.cnf
 log_bin    如果有把注释去掉，如果没有  添加该字段。指启动二进制日志
 server-id=2   群集问题，必须指定该主机的序号。数字随意
 [root@mysql ~] systemctl restart mysqld  重启Mysql服务
 ​
 启动之后默认会在/var/lib/mysql/下
 [root@mysql ~] ls /var/lib/mysql/*bin*
 /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index
 ​
 [root@mysql ~] mysqlbinlog -v /var/lib/mysql/mysql-bin.000001   查看二进制日志文件
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 # at 4
 #221020 15:06:23 server id 2  end_log_pos 123 CRC32 0x2e1f293c  Start: binlog v 4, server v 5.7.40-log created 221020 15:06:23 at startup
 # Warning: this binlog is either in use or was not closed properly.
 ROLLBACK/*!*/;
 BINLOG '
 b/NQYw8CAAAAdwAAAHsAAAABAAQANS43LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 AAAAAAAAAAAAAAAAAABv81BjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
 ATwpHy4=
 '/*!*/;
 如下是记录位置
 # at 123  
 #221020 15:06:23 server id 2  end_log_pos 154 CRC32 0x65f98324  Previous-GTIDs
 # [empty]
 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
 DELIMITER ;
 # End of log file
 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 [root@mysql ~]#

Slow Query Log

默认慢查询日志未开启

开启慢查询日志功能

 [root@mysql ~] vim /etc/my.cnf
 slow_query_log=1    启动慢查询日志   这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句
 long_query_time=3   当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短
 ​
 [root@mysql ~] ls /var/lib/mysql/*slow*    查看日志文件已经生成
 /var/lib/mysql/mysql-slow.log
 ​
 mysql> SELECT BENCHMARK(500000000,2*3);   基本测试语句，测试一个超长时间的查询
 +--------------------------+
 | BENCHMARK(500000000,2*3) |
 +--------------------------+
 |                        0 |
 +--------------------------+
 1 row in set (8.85 sec)
 ​
 [root@mysql ~] tail /var/lib/mysql/mysql-slow.log  观察长查询日志记录，记录了刚才超长的查询结果,稍后反馈给开发部，进行优化。
 /usr/sbin/mysqld, Version: 5.7.40-log (MySQL Community Server (GPL)). started with:
 Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
 Time                 Id Command    Argument
 # Time: 2022-10-20T07:19:35.989340Z
 # User@Host: root[root] @ localhost []  Id:     3
 # Query_time: 8.850992  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
 SET timestamp=1666250375;
 SELECT BENCHMARK(500000000,2*3);
 [root@mysql ~]#

备份技术

物理备份/冷备份

• 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本

• tar,cp,scp

• 拷贝数据， 优点快，缺点服务停止

逻辑备份/热备份

• 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库

• mysqldump,mydumper

• 效率相对较低

备份种类

• 完全备份

• 增量备份

• 差异备份

完全备份

把数据库全部数据进行一次完整性备份

增量备份：

因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长

差异备份：

占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后 一次的差异版本(包含所有的差异)，恢复速度介于完整备份和增量备份之间

percona-xtrabackup

简介

Logo

它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份； 为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载 percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本--percona Server。并围绕 percona Server还发布了一系列的mysql工具。

 

percone官网

安装

 yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
 //Yum安装
 yum -y install percona-xtrabackup-24.x86_64
 //查询安装结果
 yum list | grep percona
 rpm -ql percona-xtrabackup-24 

完整备份

备份全部选中的文件夹，并不依赖文件的存档属性来确定备份那些文件。在备份过程中，任何现有的标记都被清除，每个文件都被标记为已备份。换言之，清除存档属性。完全备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。实际应用中就是用一盘磁带对整个系统进行完全备份，包括其中的系统和所有数据。这种备份方式最大的好处就是只要用一盘磁带，就可以恢复丢失的数据。因此大大加快了系统或数据的恢复时间。然而它的不足之处在于，各个全备份磁带中的备份数据存在大量的重复信息；另外，由于每次需要备份的数据量相当大，因此备份所需时间较长

完全备份流程

 innobackupex --user=root --password=P@ssw0rd1234 /xtrabackup/full //连接数据库，开始备份
 ​
 [root@mysql home] ls /xtrabackup/full/  //查看数据库备份文件
 2022-10-25_13-39-00
 ​
 [root@mysql home] cat /xtrabackup/full/2022-10-25_13-39-00/xtrabackup_binlog_info  //查看二进制日志文件未知
 mysql-bin.000001  154

完全恢复流程

 systemctl stop mysqld   //停止数据库
 ​
 rm -rf /var/lib/mysql/*   //模拟数据丢失
 rm -rf /var/log/mysqld.log 
 ​
 [root@mysql ~] innobackupex --apply-log /xtrabackup/full/2022-10-25_13-39-00/  //指定备份点
 ​
 [root@mysql ~] innobackupex --copy-back /xtrabackup/full/2022-10-25_13-39-00/   //恢复文件
 ​
 [root@mysql mysql] ls /var/lib/mysql  //查看文件是否恢复
 [root@mysql ~] chown -R mysql.mysql /var/lib/mysql //进行mysql文件授权
 [root@mysql mysql] systemctl restart mysqld   //重启mysql
 [root@mysql mysql] mysql -uroot -pP@ssw0rd1234   //登陆查看是否恢复
 ​
 mysql> show databases;     //可以看到已经恢复了
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 | test               |
 +--------------------+
 5 rows in set (0.00 sec)
 ​

增量备份

备份自上一次备份（包含完全备份、差异备份、增量备份）之后有变化的数据。增量备份过程中，只备份有标记的选中的文件和文件夹，它清除标记，既：备份后标记文件，换言之，清除存档属性。

增量备份备份流程

说明：

这里模拟备份场景周一采用完整备份，周二和周三采用增量备份

 //周一
 [root@mysql mysql] innobackupex --user=root --password=P@ssw0rd1234 /xtrabackup  //完整备份
 ​
 [root@mysql mysql] mysql -uroot -pP@ssw0rd1234 -e 'insert into test.ccc values (1)'   //模拟周二新增数据
 mysql: [Warning] Using a password on the command line interface can be insecure.
 ​
 [root@mysql ~] innobackupex --user=root --password=P@ssw0rd1234 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2022-10-26_21-58-10/   //basedir基于周一的备份，会生成一个今天的
 ​
 [root@mysql xtrabackup] ls
 2022-10-26_21-58-10  2022-10-27_00-02-55
 ​
 [root@mysql /] mysql -uroot -pP@ssw0rd1234 -e 'insert into nsk.ccc values (9)'  //模拟周三新增
 mysql: [Warning] Using a password on the command line interface can be insecure.
 ​
 [root@mysql xtrabackup] innobackupex --user=root  --password=P@ssw0rd1234 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2022-10-27_00-11-47/ basedir基于周2的备份，会生成一个今天的
 ​
 [root@mysql xtrabackup] ls /xtrabackup/
 2022-10-26_21-58-10  2022-10-27_00-11-47  2022-10-28_00-01-20

增量备份恢复流程

 [root@mysql xtrabackup] systemctl stop mysqld   //停止数据库
 [root@mysql xtrabackup] rm -rf /var/lib/mysql/*  //清理数据库数据  这里只是模拟
 [root@mysql xtrabackup] innobackupex --apply-log --redo-only /xtrabackup/2022-10-26_21-58-10/   //回滚合并
 ​
 [root@mysql xtrabackup] innobackupex --apply-log --redo-only /xtrabackup/2022-10-26_21-58-10/ --incremental-dir=/xtrabackup/2022-10-27_00-11-47/    //应用周一全备日志并且增加周二的日志
 ​
 [root@mysql xtrabackup] innobackupex --apply-log --redo-only /xtrabackup/2022-10-26_21-58-10/ --incremental-dir=/xtrabackup/2022-10-28_00-01-20/    //应用周一全备日志并且增加周三的日志
 ​
 [root@mysql xtrabackup] innobackupex --copy-back /xtrabackup/2022-10-26_21-58-10/   //进行恢复周一到周三的数据
 ​
 [root@mysql xtrabackup] chown -R mysql.mysql /var/lib/mysql  //文件夹进行用户授权
 [root@mysql xtrabackup] systemctl restart mysqld   //重启Mysql
 [root@mysql xtrabackup] mysql -uroot -pP@ssw0rd1234 -e 'select * from test.ccc'  //可以看到数据已经恢复了
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    6 |
 |   10 |
 +------+

差异备份

• 操作简单

差异数据库备份，是记录自上次完全数据库备份之后对数据库的更改的数据库备份。

差异备份备份流程

 [root@mysql xtrabackup] innobackupex --user=root --password=P@ssw0rd1234 /xtrabackup/ //首先进行一次完全备份
 ​
 [root@mysql xtrabackup] ls /xtrabackup/
 2022-10-28_01-05-31
 ​
 [root@mysql xtrabackup] innobackupex --user-root --password=P@ssw0rd1234 --incremental /xtrabackup/ --incremental-basedir=2022-10-28_01-05-31/    //进行第一次差异备份
 ​
 [root@mysql xtrabackup] ls /xtrabackup/
 2022-10-28_01-05-31  2022-10-29_00-01-02
 ​
 [root@mysql xtrabackup] innobackupex --user=root --password=P@ssw0rd1234 --incremental /xtrabackup/ --incremental-basedir=2022-10-28_01-05-31/   //进行第二次差异备份，还是基于第一次全备
 ​
 [root@mysql xtrabackup] ls /xtrabackup/
 2022-10-28_01-05-31  2022-10-29_00-01-02  2022-10-29_00-02-51
 ​
 [root@mysql xtrabackup] innobackupex --user=root --password=P@ssw0rd1234 --incremental /xtrabackup/ --incremental-basedir=2022-10-28_01-05-31/  //进行第三次差异备份，还是基于第一次全备
 以此类推…………

差异备份恢复流程

 假设恢复到第三次差异备份的数据
 我们回滚时候只需要回 '滚完全备份包' 和 '第三次差异包' 备份即可
 [root@mysql xtrabackup] innobackupex --apply-log --redo-only /xtrabackup/2022-10-28_01-05-31/ //回滚全量备份包
 ​
 [root@mysql xtrabackup] innobackupex --apply-log --redo-only /xtrabackup/2022-10-28_01-05-31/ --incremental-dir=/xtrabackup/2022-10-29_00-04-05/   //回滚第三次差异备份包
 ​
 [root@mysql xtrabackup] innobackupex --copy-back /xtrabackup/2022-10-28_01-05-31/ //进行恢复，恢复回滚好的，第一次全量包
 ​
 [root@mysql xtrabackup] chown -R mysql.mysql /var/lib/mysql  //文件夹进行对Mysql用户授权
 ​
 [root@mysql xtrabackup] systemctl restart mysqld  //重启Mysql
 ​
 mysql> select * from t1;    //可以看见数据直接恢复到了第三次差异备份的数据
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    4 |
 +------+
 4 rows in set (0.00 sec)
 ​

Mysqldump备份

优势：

• 自动记录日志position位置 （show master status\G;）

• 可用性，一致性 锁表机制保证在书库路进行备份时进行对表只可读不可写的操作，从而达到备份期间数据库仍可使用

语法：

• mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql  // -h是为了实现远程备份，如果本地备份的话可不用写

参数说明：

• 库的范围

  • -A, --all-databases //所有库

  • school //数据库名

  • 例如:school stu_info t1 //是指school数据库的表stu_info、t1

  • -B, --databases bbs test mysql //多个数据库

• --single-transaction //InnoDB 一致性 服务可用性

• --master-data=1|2 //该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释a

高级选项：

• --opt //同时启动各种高级选项

• -R, --routines //备份存储过程和存储函数

• -F, --flush-logs //备份之前刷新日志，截断日志。备份之后新binlog

• --triggers //备份触发器

备份流程：

 [root@mysql ~] mysqldump -uroot -pP@ssw0rd1234 --all-databases --single-transaction --master-data=2 --flush-logs > /backup/`date +%F-%H`_msyql-all.sql     //执行备份  master-data=2 注释掉日志记录
 mysqldump: [Warning] Using a password on the command line interface can be insecure.
 ​
 [root@mysql backup] ls /backup/
 2022-10-30-19_msyql-all.sql
 ​
 [root@mysql backup] vim /backup/2022-10-30-19_msyql-all.sql
 //观察表备份生成表里的内容
 LOCK TABLES `user` WRITE;   //观察各种锁机制，用来保证数据一致性
 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;   //二进制日志截断位置。第22行
 ​
 //那么如果备份完成之后数据又发生变化处理方法，下面是模拟过程
 [root@mysql ~] cp /var/lib/mysql/*bin* ～   //备份二进制日志文件
 [root@mysql ~] ls
 anaconda-ks.cfg    mysql-bin.000001  mysql-bin.000002  mysql-bin.index  xtrabackup_binlog_pos_innodb
  
 [root@mysql ~] systemctl stop mysqld   //停止数据库
 [root@mysql ~] rm -rf /var/lib/mysql/*  //模拟数据丢失
 [root@mysql ~] systemctl start mysqld   //启动数据库
 [root@mysql ~] grep 'password' /var/log/mysqld.log    //找到Mysql最开始的密码
 2022-10-30T13:32:41.511725Z 1 [Note] A temporary password is generated for root@localhost: k:I6d3Ii<fj*  //Mysql密码
 [root@mysql ~] mysqladmin -uroot -p'k:I6d3Ii<fj*' password 'P@ssw0rd123'  //修改Mysql密码为‘P@ssw0rd123’
 ​

恢复流程：

 [root@mysql backup] mysql -uroot -pP@ssw0rd123 < /backup/2022-10-30-19_msyql-all.sql  //进行恢复
 mysql: [Warning] Using a password on the command line interface can be insecure.
 [root@mysql backup] mysql -uroot -pP@ssw0rd123   //登陆mysql
 mysql> flush privileges; //刷新一下
 Query OK, 0 rows affected (0.00 sec)
 //退出mysql 
 [root@mysql backup] mysql -uroot -pP@ssw0rd123   //再次登录提示密码错误
 mysql: [Warning] Using a password on the command line interface can be insecure.
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 //这里原因在于，之前恢复数据的时候是我们‘P@ssw0rd1234'这个密码，数据删除之后我们又重新定义了一个’P@ssw0rd123‘这个密码，那么在我们把之前数据恢复完成之后进行刷新或重启，密码就会变成我们恢复之前的密码

二进制日志恢复

那么mysqldump备份的二进制文件结束之后数据库又恢复了正常使用，又新增了一些新的数据那么在灾难来临之前还没到备份的周期新增数据没有备份，那么我们怎么做到把新增的数据也备份到数据库里呢？下面演示

新增数据舒服

 [root@mysql backup] vim 2022-10-30-19_msyql-all.sql
 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;  //观察第22行 二进制截取记录
 [root@mysql backup] mysqlbinlog /root/mysql-bin.000002 --start-position=154 | mysql -p'P@ssw0rd1234' //注意后续有多少日志，要跟多少日志名字，进行恢复
 mysql: [Warning] Using a password on the command line interface can be insecure.
 //恢复完成进行查看数据库数据已经全部恢复了，包括没有到备份周期的数据

关于数据恢复时的多余日志

每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。

方法一：

 mysql>  set sql_log_bin =0  
 mysql>  source   /backup/*.sql
 执行备份

方法二：

在备份文件中，加入关闭二进制日志

记录的导入导出

导出

 [root@mysql ~] vim /etc/my.cnf   //编辑mysql配置文件
 secure-file-priv=/backup         //添加内容
 [root@mysql ~] chown -R mysql.mysql /backup/    //对目录进行授权
 [root@mysql ~] systemctl restart mysqld       //重启Mysql
 ​
 [root@mysql ~] mysql -uroot -pP@ssw0rd1234 -e 'select * from testdb1.t1' > /backup/testdb1.t1.txt  //导出
 mysql: [Warning] Using a password on the command line interface can be insecure.
 ​
 [root@mysql ~] ls /backup/  //查看目录已经存在  
 2022-10-30-19_msyql-all.sql  testdb1.t1.txt
 [root@mysql ~] cat /backup/testdb1.t1.txt   //查看文件数据是否正确
 id
 1
 2
 3
 4
 不同格式导出
 [root@mysql ~] mysql -uroot -pP@ssw0rd1234 --xml -e 'select * from testdb1.t1' > /backup/t1.xml  //XML格式
 mysql: [Warning] Using a password on the command line interface can be insecure.
 [root@mysql ~] cat /backup/t1.xml    //查看文件
 <?xml version="1.0"?>
 ​
 <resultset statement="select * from testdb1.t1
 " xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <row>
   <field name="id">1</field>
   </row>
 ​
   <row>
   <field name="id">2</field>
   </row>
 ​
   <row>
   <field name="id">3</field>
   </row>
 ​
   <row>
   <field name="id">4</field>
   </row>
 </resultset>
 ​
 [root@mysql ~] mysql -uroot -pP@ssw0rd1234 --html -e 'select * from testdb1.t1' > /backup/t1.html  //HTML格式
 mysql: [Warning] Using a password on the command line interface can be insecure.
 [root@mysql ~] cat /backup/t1.html    //查看文件
 <TABLE BORDER=1><TR><TH>id</TH></TR><TR><TD>1</TD></TR><TR><TD>2</TD></TR><TR><TD>3</TD></TR><TR><TD>4</TD></TR></TABLE>

导入

需要清空想要导入的数据表，这里包括字段及数据类型都需要满足想要导入进去的表数据

 mysql> delete from testdb1.t1;   //清空数据表
 Query OK, 4 rows affected (0.00 sec)
 mysql> desc testdb1.t1;  //查看表结构
 +-------+---------+------+-----+---------+-------+
 | Field | Type    | Null | Key | Default | Extra |
 +-------+---------+------+-----+---------+-------+
 | id    | int(11) | YES  |     | NULL    |       |
 +-------+---------+------+-----+---------+-------+
 1 row in set (0.00 sec)
 ​
 mysql> load data infile '/backup/testdb1.t1.txt' into table testdb1.t1;  //导入
 Query OK, 4 rows affected (0.00 sec)
 Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
 mysql> select * from testdb1.t1;   //查看表数据已经导入进来了
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    4 |
 +------+
 4 rows in set (0.00 sec)

标签：10,--,备份,Mysql,xtrabackup,mysql,root
From： https://www.cnblogs.com/Jqazc/p/16832985.html