[MySQL远程备份策略举例]

mysqldump做备份时候可能出现性能抖动，mysqldump是先从buffer中找想要备份的内容，如果buffer没有，就需要访问磁盘中的数据文件，然后把数据调回内存，形成备份文件。当把数据从磁盘调到内存时，有可能把内存里的热数据冲掉，这样就影响了我们对现有业务的访问。因此，备份最好在业务较低时候做。

一台机器安装mysql客户端工具，最好和数据库的版本一致，不然可能存在问题。

获取软件压缩包： wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-8.0/mysql-8.0.27-el7-x86_64.tar.gz

使用此mysql客户端工具的mysqldump远程进行备份。具体如下：

需要能远程连接数据库

mysql -h ip -u root -p 

一、创建远程用户

mysql -u root -p
use mysql
create user 'zcbackuser'@'%' identified by 'Enmo123';
flush privileges;

根据版本需要做调整。

grant all privileges on *.* to 'zcbackuser'@'%' identified by 'Enmo123' with grant option;
#grant all privileges on dbname.* to 'zcbackuser'@'%' identified by 'Enmo123' with grant option;
flush privileges;

MySQL 5.7
 

MySQL 8.0

//主要问题在于在MySQL 8.0版本，不能够使用grant直接创建用户,需要使用create user先创建用户。
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> create user 'aaaa'@'%' identified by 'Enmo123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'aaaa'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

//而MySQL 5.7版本可以直接grant创建用户

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

mysql> grant all privileges on *.* to 'yslll'@'%' identified by 'Enmo123' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

二、查看权限

mysql> show grants;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.04 sec)

可以去看mysql.user

mysql> use mysql;
Database changed
mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | acbackuser    |
| %         | enmomy1       |
| %         | root          |
| %         | zcbackuser    |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | zcbackuser    |
+-----------+---------------+
8 rows in set (0.00 sec)

如果是 Grants for root@%localhost ，则远程主机不允许除了localhost以外其他登录。需要修改权限

%表示都可以连，可以在这里限制远程ip

grant all on dbname.* to 'username'@'%' identified by 'password';

附：远程修改密码：
mysqladmin -h ip -uroot -p123456 password 密码
远程登录主机的密码是远程root，而不是要登录的远程主机它自己本身的密码，两者不相同。

三、查看数据量

1.进入information_schema，查看所有数据库大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

mysql> use information_schema;
Database changed
mysql>
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
+--------+
| data   |
+--------+
| 2.48MB |
+--------+
1 row in set (0.26 sec)

2.查看指定数据库大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='数据库名';

3.查看指定数据库的某个表的大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='表名';

四、远程备份策略

1.简单远程备份（不加锁,可以在业务运行备份）

./mysqldump -h 172.20.10.8 -u username -p'passwd' --single-transaction --default-character-set=utf8 --set-gtid-purged=off  --skip-opt database1 > /tmp/database_db.sql 2>&1

！对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，请添加 --skip-opt选项，禁用–-opt，避免锁表

--opt
    等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用.

--single-transaction  （为了获取一致性备份）
    该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。
    这个选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。
    要想导出大表的话，应结合使用--quick 选项。

--set-gtid-purged=off 
     MySQL5.6以后，加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。
     可以通过添加--set-gtid-purged=off ，控制不在备份文件中添加SET @@GLOBAL.GTID_PURGED语句，导入库中应该重新生产GTID，而不用原来的。
     如果是备份恢复到新环境，则要关闭。

     当需要构建主从的时候，主库上有许多数据需要先备份出来并恢复到从库上，以此来保持两个库没有差异，
     然后再去配置主从。这种场景下一定要用on——对于想要基于GTID实现主从复制的从库来说，从库是基于
     MASTER_AUTO_POSITION=1自动获取并应用GTID的。因此如果再主库导出的备份文件中没有GTID，
     那么从库无法自动获取并应用GTID。

2.每周天的晚上十点半开始做备份。文件格式为 /tmp/database_db_2022-08-04-001306.sql

30 22 * * 0 /usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off --skip-opt  --default-character-set=utf8 database1 > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql 2>&1

！如果执行备份的时间间隔比较小，可以选择在crontab中加入flock文件锁去限制文件读写冲突，解决脚本重复执行问题。

3.需要压缩如下：

30 22 * * 0 /usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off --skip-opt  --default-character-set=utf8 database1 | gzip > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz 2>&1
#解压命令： gunzip backup.sql.gz

4.使用备份脚本的备份

30 22 * * 0 sh /tmp/back.sh 2>&1

back.sh如下

#！/bin/bash
/usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off --skip-opt  --default-character-set=utf8 database1 > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql 

压缩版 back.sh

#！/bin/bash
/usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off --skip-opt  --default-character-set=utf8 database1  | gzip > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz

5.可以选择加上备份报错日志 --log-error 把报错输出到类似于mysqldump_error_log_2022-08-03-015721.err的日志

./mysqldump -h 172.20.10.2 -u zcbackuser -p'Enmo123' --single-transaction --default-character-set=utf8 --set-gtid-purged=off --skip-opt --log-error=/tmp/mysqldump_error_log_`date +\%Y-\%m-\%d-\%H\%M\%S`.err  test1| gzip > /tmp/test1_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz 2>&1

注：可能会出现如下报错，最好使用同样版本的客户端。
mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.“number-of-buckets-specified”’) FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = ‘test1’ AND TABLE_NAME = ‘shop’;’: Unknown table ‘column_statistics’ in information_schema (1109);

五、远程备份的效果基本如下:

[root@localhost bin]# ./mysqldump -h 172.20.10.2 -u zcbackuser -p'Enmo123' --default-character-set=utf8 --set-gtid-purged=off --skip-opt  --single-transaction --log-error=/tmp/mysqldump_error_log_`date +\%Y-\%m-\%d-\%H\%M\%S`.err  test1| gzip > /tmp/test1_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz 2>&1
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost bin]# cd /tmp
[root@localhost tmp]# ll
total 12
drwxr-xr-x 2 root root  18 Jul  7 20:54 hsperfdata_root
-rw-r--r-- 1 root root  73 Jul  7 19:22 lua_zvpodh
-rw-r--r-- 1 root root 320 Aug  3 01:57 mysqldump_error_log_2022-08-03-015721.err
-rw-r--r-- 1 root root 608 Aug  3 01:57 test1_2022-08-03-015721.sql.gz
[root@localhost tmp]# gunzip test1_2022-08-03-015721.sql.gz 
[root@localhost tmp]# cat test1_2022-08-03-015721.sql 
--
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: 172.20.10.2    Database: test1
-- ------------------------------------------------------
-- Server version       5.7.19
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `shop`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `shop` (
  `id` int(20) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `shop`
--

INSERT INTO `shop` VALUES (1,'ysl');
INSERT INTO `shop` VALUES (2,'enmo');

六、备份的保留及清理策略

此处/enmo_zcloud_mysql_bak/为备份存放的路径

#1.过期备份文件为（超过三天）

find /enmo_zcloud_mysql_bak/ -mtime +3 -type f -exec ls -lt {} \;

#2.超过三天的备份文件的清理

find /enmo_zcloud_mysql_bak/ -mtime +3 -type f -exec rm -f {} \;

#可以把该命令加入到crontab里，实现每周清理一次。在本地保留三次备份。
例如

00 22 * * 0 find /enmo_zcloud_mysql_bak/ -mtime +3 -type f -exec rm -f {} \;

附录：MySQL的mysqldump默认带的参数

这些参数，执行mysqldump 命令的时候默认就会带上的。

 –opt
 –add-drop-table
 –add-locks
 -i,–comments
 -a,–create-options
 -e, –extended-insert
 -l, –lock-tables
 -q, –quick
 -K, –disable-keys
 -Q, –quote-names
 –dump-date
 –ssl
 –triggers
 –tz-utc