Mysql数据备份与恢复命令

数据备份：

mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1，参数2.... > 备份文件.sql

1、导出整个库

mysqldump -u[username] -p [database_name] > xxx.sql

2、导出某个表

mysqldump -u[username] -p [database_name] [tablename] > xxx.sql

3、导出时忽略某张表（多个表可写多个--ignore-table）

mysqldump -u[username] -p [database_name] --ignore-table=[database_name].[tablename] > xxx.sql

数据恢复：

mysql -h[host] -u[username] -p < xxx.sql

数据量大时备份十分缓慢，可采取以下方式优化：

1、检查配置参数

//binlog机制，一般为0
show variables like 'sync_binlog';
//事务提交机制，一般为1
show variables like 'innodb_flush_log_at_trx_commit';

2、临时调整参数

set global sync_binlog = 2000;
set global innodb_flush_log_at_trx_commit = 2;

3、导入完成后恢复配置

set global sync_binlog = 原值;
set global innodb_flush_log_at_trx_commit = 原值;

其他问题：

1、数据备份导入后可能存在视图权限不足，报错如下

The user specified as a definer (”@’%') does not exist

原因：

创建视图使用的是xff@%用户(目前已经不存在)，然后登录用户使用的是xff@localhost用户，导致mysql认为现在的用户无权限访问该视图。

解决方法：

用alter view 修改definer的值，alter ALGORITHM=UNDEFINED DEFINER=`username(ru :root)`@`192.168.0.%(ip:如10.10.10.10)` SQL SECURITY DEFINER VIEW `view_product` AS 视图选择语句。

2、新旧数据库sql_mode不一致，导致未全量匹配group by字段的sql报错，报错信息如下：

SELECT list is not in GROUP BY clause and contains nonaggregated column

原因：

mysql5.7.5及以上功能依赖检测功能，如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。

可通过如下语句检测:

select @@global.sql_mode;
如返回结果如下，则会强制检测group by映射：
ONLY_FULL_GROUP_BY，STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

解决方法：

1、临时方案，mysql重启后失效

set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

2、永久方案，需重启mysql生效

修改配置文件，在安装目录下找到配置文件mysql/mysql.conf.d/mysqld.cnf

在[mysqld]下面添加

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION