MySQL 备份和恢复速度优化

Mysqldump 备份以及恢复导入数据库很慢的解决办法

# 登录 MySQL
mysql -u root -p'!Fzb_dahua2019'
参看数据库的参数值，使用如下命令；
mysql> show variables like 'max_allowed_packet';
mysql> show variables like 'net_buffer_length';
mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.01 sec)
mysql> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
1 row in set (0.01 sec)
# mysqldump -uroot -p'数据库密码' 原数据库名 -e --max_allowed_packet=67108864 --net_buffer_length=16384 > file.sql
# mysqldump -uroot -p'!Fzb_dahua2019' IFCSDB_NEW -e --max_allowed_packet=67108864 --net_buffer_length=16384 > dump20200520.sql

1.用mysqldump 命令进行备份：

mysqldump -hlocalhost -uroot -proot databaseName > fileName.sql

-h主机名 -u 用户名 -p 密码
databaseName 要备份的数据库 fileName.sql 备份后的文件名
-h,-u的参数名和参数值之间空格可有可无
-p的不能有空格

2.用mysqldump 命令进行恢复：

function importDB(){
newBasePath=/ICC/dahua/fire
DBName=IFCSDB_mysqldump
DBPwd='!Fzb_dahua2019'
bakSQLFile=$newBasePath/bak20200515DB
mysqldump -uroot -p$DBPwd $DBName < $bakSQLFile.sql
}

恢复时，要恢复的数据库必须存在

另外一种方法：

cat test.sql | mysql -u root -p’数据库密码’

mysqlpump与mysqldump及mydumper的备份速度测试

https://blog.csdn.net/zengxuewen2045/article/details/52453135

结论：
mysqlpump的备份效率是最快的，mydumper次之，mysqldump最差

mysqlPWD='!Fzb_dahua2019'
DBName=IFCSDB_NEW
time mysqlpump -uroot -p${mysqlPWD} -h127.0.0.1 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B $DBName

关于mysql还原数据库特别慢的解决办法

原文链接：https://blog.csdn.net/wsfzj123/java/article/details/88909485 还原个8G的数据库还原了一晚上还没跑完，头疼。

• 查看变量 innodb_flush_log_at_trx_commit的值

  SHOW  VARIABLES LIKE 'innodb_flush_log_at_trx_commit' ;

  查询结果如果是 1 的话，将其修改成 0

SET GLOBAL innodb_flush_log_at_trx_commit =0;

• 这时候再 source，会发现速度快了很多，在结束后记得把这个变量重新设置成 **1**

  SET GLOBAL innodb_flush_log_at_trx_commit =1;

用mysqldump备份出来的还原回去很慢解决办法

MySQL导出的SQL语句在导入时有可能会非常非常慢，在处理百万级数据的时候，可能导入要花几小时。在导出
时合理使用几个参数，可以大大加快导 入的速度。

首先确定目标数据库的参数值
mysql> show variables like 'max_allowed_packet';
mysql> show variables like 'net_buffer_length';
根据参数值书写 mysqldump 命令，如：

DBPwd='!Fzb_dahua2019'
DBName=IFCSDB_NEW
bakSQLFile=bakSQL
max_allowed_packet=
net_buffer_length=
-- mysqldump -uroot -p$DBPwd  $DBName -e --max_allowed_packet=$max_allowed_packet --net_buffer_length=$net_buffer_length  > $bakSQLFile.sql
mysqldump -uroot -p$DBPwd  $DBName -e --max_allowed_packet=67108864 --net_buffer_length=16384  > $bakSQLFile.sql

-e 使用包括几个VALUES列表的多行INSERT语法;
—max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
—net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。

注意：max_allowed_packet 和 net_buffer_length 不能比目标数据库的设定数值大，否则可能出错。

Linux下修改Mysql数据库存放路径

原路径：/var/lib/mysql
目标路径：/home/data/mysql

1、home目录下建立data目录
cd /home
mkdir data

2、停止mysql进程
service mysqld stop

3、把 /var/lib/mysql 整个目录保持权限复制到 /home/data
cp -arp /var/lib/mysql　/home/data/
4、找到my.cnf配置文件
如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/找到my-medium*.cnf文件，拷贝到/etc/并改名为my.cnf
命令如下：
[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf

5、编辑/etc/my.cnf
为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。修改
socket=/var/lib/mysql/mysql.sock 为：
socket=/home/data/mysql/mysql.sock
操作如下：
vi 　 my.cnf　 (用vi工具编辑my.cnf文件，找到下列数据修改之)
# The MySQL server[mysqld]　
port　= 3306
#socket　 = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用“#”注释此行）
socket　 = /home/data/mysql/mysql.sock　（加上此行）

6、修改MySQL启动脚本/etc/init.d/mysql
搜索datadir将/var/lib/mysql修改成现在的实际存放路径：home/data/mysql
[root@test1 etc]# vi　/etc/init.d/mysqlget_mysql_option mysqld datadir “/data/mysql”
最后做一个mysql.sock 链接：
ln -s /home/data/mysql/mysql.sock /var/lib/mysql/mysql.sock(需要从/home/data/mysql下复制一份过来)

7、重新启动MySQL服务
service mysqld restart

创建数据库，查看目标目录下是否已经存在