mysqldump做备份时候可能出现性能抖动，mysqldump是先从buffer中找想要备份的内容，如果buffer没有，就需要访问磁盘中的数据文件，然后把数据调回内存，形成备份文件。当把数据从磁盘调到内存时，有可能把内存里的热数据冲掉，这样就影响了我们对现有业务的访问。因此，备份最好在业务较低时候做。
一台机器安装mysql客户端工具，最好和数据库的版本一致，不然可能存在问题。
获取软件压缩包
使用此mysql客户端工具的mysqldump远程进行备份。具体如下：
需要能远程连接数据库
mysql -h ip -u root -p
一、创建远程用户
mysql -u root -p use mysql create user 'zcbackuser'@'localhost' 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> show grants; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.04 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 ，导入库中应该重新生产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 --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 --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 --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 --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 --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 --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]# tail -20 test1_2022-08-03-015721.sql -- DROP TABLE IF EXISTS `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` -- LOCK TABLES `shop` WRITE; /*!40000 ALTER TABLE `shop` DISABLE KEYS */; INSERT INTO `shop` VALUES (1,'ysl'),(2,'enmo'); /*!40000 ALTER TABLE `shop` ENABLE KEYS */; UNLOCK TABLES;
附录：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
