MySQL数据库备份与还原

介绍

MySQL是我们开发最为常用的数据库之一，下面简单介绍一下MySQL数据库的备份与还原。

逻辑备份

注意：这种方式适用于比较少量数据的时候，如果大量数据，请物理备份，备份文件尽量含有日期[方便数据的还原与管理]，对于客户的数据库是禁止删除的，因此还原的时候，可以还原到新的数据库。

Windows平台下CMD执行：参数说明-h 10.0.42.180[host数据库服务器IP]，-P 3306[PORT数据库端口，mysql默认3306] gbk[数据库字符集，当前test默认字符集]，test[数据库名称]，D:\test_backup20181123.dump[备份路径以及文件名] ，如果是本机-h 10.0.42.180 -P 3306 部分可以省略

1.cmd执行

D:\> mysqldump -uroot -p -h 10.0.42.180 -P 3306 -c --default-character-set=gbk test > D:\\test_backup20181123.dump

输入密码后回车

在对应路径下就能看到相应文件了【如果2002请看后面的其他情况说明】

其他参数

–lock-all-tables

在mysqldump导出的整个过程中以read方式锁住所有表（锁住方式类似 flush tables with read lock 的全局锁）。
从一开始就对整个mysql实例加global read lock锁。
这整个全局读锁会一直持续到导出结束。
所以在这个过程中，数据库实际严格处于read only状态。
所以导出的数据库在数据一致性上是被严格保证的，也就是数据是一致性的。
由于这个参数会将数据库置于read only状态（也相当于不可使用状态），所以默认不加该参数。
这相当于脱机备份的感觉，所以生产数据库的备份策略上，也很少使用该参数。
使用该参数的话，也会自动将 --single-transaction 及 --lock-tables 参数置于 off 状态。
该参数默认off。

–single-transaction

保证各个表具有数据一致性快照。
指定 --single-transaction 参数，那么导出过程中只能保证每个表的数据一致性（利用多版本特性实现，目前只能针对InnoDB事务表）。
比如有一个大表，mysqldump对该表的导出需要1分钟，那么在这1分钟的过程中，该表时可以被正常访问的。
（正常访问包括增删改查，但是alter table等对表结构发生更改的语句要被挂起。）
mysqldump能够保证从开始对该表进行导出，一直到对该表的导出结束，该表的数据都是开始的一致性数据快照状态。
所以该参数明显不能保证各个表之间的数据一致性（特别是外键约束的父表和子表之间）。
但是该参数能够让数据库处于可使用（就是应用感觉数据库可用）状态，相当于联机备份，所以被经常使用。
该参数默认off。

–lock-tables

保证各个schema具有数据一致性快照。
指定 --lock-tables 参数，那么在导出过程中能够保证各个schema的数据一致性。
该参数只能保证各个schema自己的数据一致性快照。
该参数默认on。

逻辑还原

1.创建数据库，指定字符集

mysql>  CREATE DATABASE test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

2.导入表及数据
cmd下执行下面语句,输入密码后回车

D:\> mysql -uroot -p -h 10.0.42.180 -P 3306 -c --default-character-set=gbk test < D:\\test_backup20181123.dump

其他情况说明

数据库还原的时候，如果提示外键约束，修改dump文件，开头添加，不启用外键

SET FOREIGN_KEY_CHECKS=0;

最后添加

SET FOREIGN_KEY_CHECKS=1;

Linux平台下与windows下面基本一致
如果Linux下提示下面错误

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

套接字错误，选择正确的套接字

[root@localhost gugu]# netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     17512781 /var/lib/mysql/mysql.sock

导出命令中添加socket参数

[root@localhost gugu]# mysqldump -uroot -p --socket=/var/lib/mysql/mysql.sock -c --default-character-set=gbk test > ./test_backup20181123.dump

物理备份

顾名思义，找到物理存储文件进行备份

1.物理备份[InnoDB存储引擎(mysql5.5.5以上版本默认存储引擎)]，test[数据库名称]，
MySQL数据目录下复制test文件夹以及ibdata1文件
如果找不到文件位置

mysql> show global variables like "%datadir%";

就可以找到物理文件位置，以及下面的数据文件

物理还原

1. 停止服务器
   2.将test文件夹以及ibdata1文件夹替换原来数据库数据文件
   3.重新启动数据库
   注意：这种情况会所有数据库都会还原成备份的版本