mysql的备份还原

1、在备份前，需查看数据库的大小，以确认备份盘的大小

一、查询所有数据库的总大小

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_mb from TABLES;

二、查询每个数据库的大小

mysql> use information_schema;
mysql> SELECT table_schema,CONCAT(ROUND(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024,2),'MB') AS total_mb FROM TABLES GROUP BY table_schema;  

三、查询各数据库容量

select
    table_schema as '数据库',
    sum(table_rows) as '记录数',
    sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema

四、查询各数据表容量大小

select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

2、确认大小后，我们就通过挂载备份盘到系统中，具体的挂载命令可参考这个地址的内容：https://www.cnblogs.com/lxhaaron/articles/14024238.html

3、备份数据库，以下为备份数据库的命令介绍：

一、备份MySQL指定数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

二、备份MySQL数据库为带删除表的格式，备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

三、直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

四、备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

五、同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

六、仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

七、备份服务器上所有数据库

mysqldump –-all-databases > allbackupfile.sql

八、还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

九、还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

十、将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

 参考资料地址：https://www.jb51.net/article/74613.htm

错误处理：

1、在备份时出现以下错误：

mysqldump: Got error: 1556: You can't use locks with log tables. when doing LOCK TABLES

原因:

mysqldump备份的时候不能锁mysql.general_log,mysql.slow_log这两张表。

解决办法有四种：

1.不备份mysql这个schema

2.备份时排除这两张表'mysql.general_log' 'mysql.slow_log'

3.不备份CSV引擎的表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN('CSV');

4.mysqldump备份时增加参数--lock-tables=0

所以备份SQL为以下：

mysqldump -uroot -p –-all-databases --lock-tables=0 > /mnt/cddisk/ydtdw/allbackupfile.sql

2、在还原时出现以下这样的错误，原因是这个参数有问题 

 

 即：ERROR 2006(HY000)  at line 92791:MySQL server has gone away

原因是max_allowed_packet参数有问题，太小了，可以调整大些就OK了，具体参考地址处理：https://www.cnblogs.com/lxhaaron/articles/14154108.html 处理

 

mysqldump的参数的详细使用，可参考地址：

https://www.cnblogs.com/linuxk/p/9371475.html

https://www.cnblogs.com/dannylinux/p/9529271.html