mysql备份----XtraBackup备份

最新推荐文章于 2024-06-01 15:29:46 发布

瘋子范ル

最新推荐文章于 2024-06-01 15:29:46 发布

阅读量781

点赞数

本文链接：https://blog.csdn.net/weixin_44006354/article/details/101266708

版权

1.XtraBackup完整备份

备份的权限

使用XtraBack做备份，需要连接到数据库服务器并在服务器上有相关目录操作权限，所以必须要有数据库的相关操作权限以及相关目录执行READ、WRITE以及EXECUTE（执行innobackupex 或xtrabackup 命令）的系统权限。下面我们来简单介绍一下：

数据库所需的一些权限请参考下面表格，细节部分可以参考官方文档。此处不详细阐述！

MySQL 数据库权限	权限描述
RELOAD/ LOCK TABLES	RELOAD权限是管理员级别权限，用来执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables,flush-threads, refresh, reload等命令的权限。XtraBackup在复制文件之前，必须先执行FLUSH TABLES WITH READ LOCK和 FLUSH ENGINE LOGS等
REPLICATION CLIENT	此权限是为了获取二进制日志位置（Position）
CREATE TABLESPACE	此权限是为了导入表
PROCESS	此权限是因为要运行SHOW ENGINE INNODB STATUS命令，以及查看服务器上所有运行的线程。
SUPER	此权限是为了开启、关闭复制环境中的slave threads
CREATE	不是必须，Percon Server数据库才需要的。创建PERCONA_SCHEMA.xtrabackup_history的需要
INSERT	不是必须，跟上面权限一样，需要往PERCONA_SCHEMA.xtrabackup_history插入记录
SELECT	不是必须，跟上面权限一样，需要查询ERCONA_SCHEMA.xtrabackup_history中的记录

当然所需的最小权限如下所示，注意：这个仅仅是最小权限，实际情况中最好根据实际需求授予最小权限。不要放大不必要的权限。

mysql> use mysql;
Database changed
mysql> CREATE USER 'ydt'@'localhost' IDENTIFIED BY 'ydt123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT RELOAD, LOCK TABLES,PROCESS, REPLICATION CLIENT ON *.* TO 'backuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql>

如果上面的权限不够的话，可以使用下面授权语句。

GRANT RELOAD, LOCK TABLES,PROCESS, REPLICATION CLIENT, CREATE TABLESPACE,SUPER ON *.* TO 'backuser'@'localhost';

系统账号授权相关目录的操作权限。如下所示：

# chown -R mysql:mysql /data
# su - mysql
-bash-3.2$

在这里插一句话，或许有的人在设置数据库密码时会出现如下报错

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
ERROR 1819 (HY000): Your password does not satisfy the current policy requiremen

报错显示1819（HY000）：您的密码不符合当前的政策要求

原因是在mysql5.6.6版本之后增加了密码强度验证插件validate_password，相关参数设置的较为严格。
使用了该插件会检查设置的密码是否符合当前设置的强度规则，若不满足则拒绝设置。影响的语句和函数有：create user,grant,set password,password(),old password。

解决办法：
1）查看mysql全局参数配置
该问题其实与mysql的validate_password_policy的值有关。
查看一下msyql密码相关的几个全局参数：

mysql> select @@validate_password_policy;  
+----------------------------+  
| @@validate_password_policy |  
+----------------------------+  
| MEDIUM                     |  
+----------------------------+  
1 row in set (0.00 sec)  
  
  
mysql> SHOW VARIABLES LIKE 'validate_password%';  
+--------------------------------------+--------+  
| Variable_name                        | Value  |  
+--------------------------------------+--------+  
| validate_password_dictionary_file    |        |  
| validate_password_length             | 8      |  
| validate_password_mixed_case_count   | 1      |  
| validate_password_number_count       | 1      |  
| validate_password_policy             | MEDIUM |  
| validate_password_special_char_count | 1      |  
+--------------------------------------+--------+  
6 rows in set (0.08 sec)

2）参数解释

validate_password_dictionary_file
插件用于验证密码强度的字典文件路径。

validate_password_length
密码最小长度，参数默认为8，它有最小值的限制，最小值为：validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)

validate_password_mixed_case_count
密码至少要包含的小写字母个数和大写字母个数。

validate_password_number_count
密码至少要包含的数字个数。

validate_password_policy
密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG。有以下取值：
Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file
默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

validate_password_special_char_count
密码至少要包含的特殊字符数。

3）修改mysql参数配置

mysql> set global validate_password_policy=0;  
Query OK, 0 rows affected (0.05 sec)  
  
mysql>   
mysql>   
mysql> set global validate_password_mixed_case_count=0;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> set global validate_password_number_count=3;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> set global validate_password_special_char_count=0;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> set global validate_password_length=3;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> SHOW VARIABLES LIKE 'validate_password%';  
+--------------------------------------+-------+  
| Variable_name                        | Value |  
+--------------------------------------+-------+  
| validate_password_dictionary_file    |       |  
| validate_password_length             | 3     |  
| validate_password_mixed_case_count   | 0     |  
| validate_password_number_count       | 3     |  
| validate_password_policy             | LOW   |  
| validate_password_special_char_count | 0     |  
+--------------------------------------+-------+  
6 rows in set (0.00 sec)

4）修改简单密码：

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');  
Query OK, 0 rows affected, 1 warning (0.00 sec)

已经成功设置密码，这就解决了密码问题。

2.完整备份

1.常用的完整备份（全备）命令如下所示

$innobackupex  --user=root --password=123456  /var/mysql_backup/

$innobackupex  --user=root --password=123456  /var/mysql_backup/

$innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123456  /var/mysql_backup/

$innobackupex --defaults-file=/etc/mysql/my.cnf --socket=/var/lib/mysql/mysql.sock --user=root --password=123456  /var/mysql_backup/

参数--no-timestamp 表示不生成带时间戳的目录
$innobackupex  --user=root --password=123456  /var/mysql_backup/ --no-timestamp

注意：如果你指定了参数--defaults-file，那么此参数就必须位于第一个，否则就会报“--defaults-file must be specified first on the command line”

2.我们先在测试数据库MyDB创建一个测试表，插入几条数据，后面做还原测试验证使用。

mysql> use MyDB;
Database changed
 
mysql> create table test(id  int primary key);
Query OK, 0 rows affected (0.13 sec)
 
mysql> insert into test
    -> values(1000);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into test
    -> values(2000);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into test
    -> values(3000);
Query OK, 1 row affected (0.01 sec)
 
mysql>

3.我们使用下面命令对MySQL做了一个完整备份，那么默认就会在目录/var/mysql_backup/下生成一个日期时间格式的全备文件2019-09-24_10-44-50

 $ innobackupex  --defaults-file=/etc/mysql/my.cnf --user=root --password=123456  /var/mysql_backup/

在这里插入图片描述 4.那么备份出来的文件以及内容是什么样的呢？如下所示，你可以检查xtrabackup_checkpoints、xtrabackup_binlog_info、xtrabackup_info三个文件了解备份的一些详细信息，例如备份类型、是否压缩等等。

# ls -lrt  (查看文件属性)
# more xtrabackup_checkpoints （查看文件更多内容）
# more xtrabackup_binlog_info
# more xtrabackup_info

xtrabackup_checkpoints ：备份类型信息（完全备份或增量备份）、LSN(日志序列号)范围信息、备份是否压缩；是全量备份还是增量，当前备份集的状态，是否已经prepare过。如果是全量备份，backup_type = full-backuped，如果是增量是backup_type = incremental。备份集的状态在apply log后会改变

xtrabackup_binlog_info ：二进制日志信息,MySQL当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。如果没有开启二进制日志，那么就不会有xtrabackup_binlog_info这个文件。

xtrabackup_info ：xtrabackup工具的版本信息、详细命令、参数以及备份的开始、结束时间。以及备份详细信息等。

backup-my.cnf ：备份命令用到的配置选项信息。

5. 开启binlog

这里我要说一下，或许有的同学会问，为什么我的备份里找不到xtrabackup_binlog_info文件，如果没有xtrabackup_binlog_info文件的，说明没有开启binlog，我们需要手动在配置文件里进行修改。

1.找到配置文件my.cnf 文件（因为我用是ubuntu18.04系统，该文件在 /etc/mysql/mysql.conf/下 mysqld.cnf文件）

然后在【mysqld】添加

server_id       = 1918
log_bin         = /var/log/mysql/mysql-bin.log

添加位置如图所示：
在这里插入图片描述
binlog存放的路径最好不要放在root路径下如果放在root下是需要再设置mysql权限

2.修改后保存
重启mysql

service mysql restart

然后进入mysql查看安装好了没有

mysql -u root -p

最后输入查看命令：

show variables like '%log_bin%';

未开启之前显示，log_bin 关闭
在这里插入图片描述
开启好之后显示：

出现如上信息，代表开启成功。

6.使用脚本进行全量备份

#!/bin/sh

backup=/var/mysql_backup
backup_full=/var/mysql_backup/full

today=`date +%Y%m%d`

user=root
pass="123456"
mycnf=/etc/mysql/my.cnf

[ ! -d "$backup_full/" ] && mkdir -p "$backup_full/"

if [ -d "$backup_full" ];then
  cd "$backup"

fi
  innobackupex --defaults-file=$mycnf --user=$user --password=$pass $backup_full

cd $backup
tar -zcvf $today.full.tar.gz ./full

file_name=`find $backup -name *.tar.gz`


if [ $? -eq 0 ];then
    curl 'https://oapi.dingtalk.com/robot/send?access_token=8fbabec6183beafa0bc366a3e06a973c6c89405baaf2202a178b40eafcf2892f' \
        -H 'Content-Type: application/json' \
          -d '
      {"msgtype": "text",
            "text": {
            "content": "全量备份成功"
                }
            }'
          else
              curl 'https://oapi.dingtalk.com/robot/send?access_token=8fbabec6183beafa0bc366a3e06a973c6c89405baaf2202a178b40eafcf2892f' \
                  -H 'Content-Type: application/json' \
              -d '
                {"msgtype": "text",
                "text": {
                      "content": "全量备份失败"
                    }
                      }'
              fi

rm -rf `echo ${file_name##*/}`

3.全备恢复还原

1.准备备份

innobackupex  --defaults-file=/etc/mysql/my.cnf   --apply-log  /var/mysql_backup/full/2019-09-26_17-39-56/

2.关闭MySQL服务，备份数据目录或删除数据目录

/etc/init.d/mysql stop
mv /var/lib/mysql/   /var/lib/mysql_20190926_bak

3.恢复数据，将备份数据文件拷贝到数据目录

 innobackupex  --defaults-file=/etc/mysql/my.cnf  --copy-back  /var/mysql_backup/full/2019-09-26_17-39-56/

如果执行上面命令是在mysql用户下，并且有权限创建对应目录，那么就不需要做任何操作，如果是root账号，那么就必须修改对应目录的Owner，否则启动MySQL服务会报错。

chown -R mysql:mysql /var/lib/mysql/*

4.测试数据的完整性

#启动数据库
service mysql start
#进入数据库
mysql -u root -p
#切换数据库
mysql>use ydt; 
mysql>select * from test;

4.XtraBackup增量备份

1.完整备份

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123456  /var/mysql_backup/full

2.增量备份1

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123456 --incremental  --incremental-basedir=/etc/mysql/mysql_backup/full/2019-09-26_09-38-32/  /var/mysql_backup/incr/

3.增量备份2

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123456 --incremental  --incremental-basedir=/var/mysql_backup/full/2019-09-26_09-42-32/   /var/mysql_backup/incr/

注：其实第一次完整备份时，生成了备份目录2019-09-26_09-38-32；第一次增量备份时，生成了增量备份目录2019-09-26_09-42-32，关键在于第二次增量备份，–incremental-basedir这个参数的值不应该是第一次完成备份的目录，而应是增量备份1的备份目录2019-09-26_09-38-32，如果使用了错误的参数，增量备份2其实包含了增量备份1的变化部分。

增量备份注意事项

1：–incremental-basedir
第一次增量备份，–incremental-basedir肯定是全备目录，第二次增量备份，–incremental-basedir为第一次增量备份目录，依此类推！
2：增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
3：应用增量备份的时候只能按照备份的顺序来应用。如果应用顺序错误，那么备份就不可用。如果无法确定顺序，可以查看xtrabackup-checkpoints来确定顺序。

4.使用脚本进行在上一次增量备份的基础上进行增量备份

#! /bin/bash

backup=/var/mysql_backup

backup_full=/var/mysql_backup/full
backup_incr=/var/mysql_backup/incr

today=`date +%Y%m%d`
#日志文件存储位置
tmplog="/tmp/mysql/incr_$today.$$.log"

user=root
pass="123456"

mycnf=/etc/mysql/my.cnf

#查找最新的完整备份
latest_full=`find $backup_full -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
echo "latest_full=$latest_full"

#查找最新的增量备份
latest_incr=`find $backup_incr -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`
echo "latest_incr=$latest_incr"

#如果这是第一个增量，使用完整作为基础。否则，使用最新的增量作为基础。
if [ ! $latest_incr ] ; then
  incrbasedir=$backup_full/$latest_full
else
  incrbasedir=$backup_incr/$latest_incr
fi
echo "Running new incremental backup using $incrbasedir as base."

#将信息以日志文件进行输出保存
innobackupex --defaults-file=$mycnf --user=$user --password=$pass --incremental --incremental-basedir $incrbasedir  $backup_incr > $tmplog 2>&1

#将文件进行打包
cd $backup
tar -zcvf $today.incr.tar.gz ./incr
file=`find $backup -name *.tar.gz`

#使用钉钉进行消息提醒
if [ $? -eq 0 ];then
  curl 'https://oapi.dingtalk.com/robot/send?access_token=8fbabec6183beafa0bc366a3e06a973c6c89405baaf2202a178b40eafcf2892f' \
  -H 'Content-Type: application/json' \
  -d '
  {"msgtype": "text",
     "text": {
  "content": "增量备份成功"
                }
            }'
else
  curl 'https://oapi.dingtalk.com/robot/send?access_token=8fbabec6183beafa0bc366a3e06a973c6c89405baaf2202a178b40eafcf2892f' \
  -H 'Content-Type: application/json' \
  -d '
  {"msgtype": "text",
  "text": {
  "content": "增量备份失败"
                }
            }'
fi

#rm -rf `echo ${file##*/}`

5.恢复备份

1.恢复完全备份

innobackupex --defaults-file=/etc/mysql/my.cnf  --apply-log --redo-only /var/mysql_backup/full/2019-09-26_09-38-32/

注：注意如果后续必须应用增量备份，则必须加上–redo-only. 否则后续不能应用增量备份。只有当最后一个增量备份时，才不需要–redo-only，这个如果对SQL Server备份还原熟悉的话，原理也是如此。

2.增量备份还原

innobackupex --apply-log --redo-only --incremental /var/mysql_backup/full/2019-09-26_09-38-32/   --incremental-dir=/var/mysql_backup/incr/2019-09-26_09-42-32/

注：增量备份必须按一定顺序Apply，否则后面的增量备份都无效了！

3. 关闭MySQL服务并移除数据目录

service mysql stop

4.将备份文件拷贝回MySQL数据目录

innobackupex  --defaults-file=/etc/mysql/my.cnf  --copy-back /var/mysql_backup/full/2019-09-26_09-38-32/

chown -R mysql:mysql /var/lib/mysql/*

5.检查验证数据数据恢复情况

service mysql start

mysql> select * from t1;

瘋子范ル

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql备份----XtraBackup备份

1.XtraBackup完整备份备份的权限使用XtraBack做备份，需要连接到数据库服务器并在服务器上有相关目录操作权限，所以必须要有数据库的相关操作权限以及相关目录执行READ、WRITE以及EXECUTE（执行innobackupex 或xtrabackup 命令）的系统权限。下面我们来简单介绍一下：数据库所需的一些权限请参考下面表格，细节部分可以参考官方文档。此处不详细阐述！...
复制链接

扫一扫