如何进行数据库备份和恢复 mysql

一、如何进行数据库备份和恢复 mysql

MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。

开始菜单|运行| cmd|利用“cd/Program Files/MySQL/MySQL Server 5.0/bin”命令进入bin文件夹|利用“mysqldump-u用户名-p databasename>exportfilename”导出数据库到文件，如mysqldump-u root-p voice>voice.sql，然后输入密码即可开始导出。

进入MySQL Command Line Client，输入密码，进入到“mysql>”，输入命令"show databases；"，回车，看看有些什么数据库；建立你要还原的数据库，输入"create database voice；"，回车；切换到刚建立的数据库，输入"use voice；"，回车；导入数据，输入"source voice.sql；"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。

[root@localhost~]# cd/var/lib/mysql(进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)

[root@localhost mysql]# mysqldump-u root-p voice>voice.sql，输入密码即可。

[root@localhost~]# mysql-u root-p回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。

[root@localhost~]# cd/var/lib/mysql(进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)

[root@localhost mysql]# mysql-u root-p voice<voice.sql，输入密码即可。

二、如何使用mysqldump命令备份mysql数据库

通用规律只有使用--all-databases(-A)会 ERROR 1356，那就看看他到底备份了什么东西。于是喊上同事一起 less看了下，上下扫了两眼。突然发现：1.备份 SQL文件里 DROP掉了 mysql.proc；2.后CREATE了一个新的 mysql.proc；3. LOCK TABLES和 UNLOCK TABLES中间居然没有备份 CREATE ROUTINE任何数据？这不就是相当于每次导入全备都给我一个没有任何 sys schema routines的全新 mysql.proc表？那这不就异常的尴尬？

---- Table structure for table `proc`--

---- Dumping data for table `proc`-

真相大白在官方文档【sys-schema-usage】官方文档明确的告诉我们不会备份 sys库。但在使用 mysqldump在执行--all-databases会清空 mysql.proc导致 sys无法正常使用；这是一个 BUG，并且只存在于 MySQL 5.7.x！

1、mysql_upgrade install or upgrade sys schema

这个方案适用于 sys库已经因为 mysqldump导入而损坏的情况下使用。

注意：mysql_upgrade在修理 sys库的同时，还修理 mysql库和用户库表（期间加锁且速度一般），有极小可能会误伤；使用 mysql_upgrade的时候要加上--upgrade-system-tables，不然会扫描用户库表。

这个方案适用于需要还原的数据库，sys库也不太正常的情况下使用；在全备后额外再备份一份 sys库用于修复。

注意：不适用于做主从时使用它。

这个方案适用于所有场景的全备需求，100%安全。

如果你的数据库 sys全部中招了，又是生产库。那你只能用这个方法；

mysql-sys：

中记录了 sys库的创建语句将文件下载到本地，然后根据数据库版本，执行以下命令即可。

三、mysql数据使用逻辑备份mysqldump备份出来的文件有多大

有人问mysqldump出来的insert语句，是否可以按每 10 row一条insert语句的形式组织。

思考1：参数--extended-insert回忆过去所学：

表示使用长 INSERT，多 row在合并一起批量 INSERT，提高导入效率

--skip-extended-insert一行一个的短INSERT

均不满足群友需求，无法控制按每 10 row一条 insert语句的形式组织。

思考2：“避免大事务”之前一直没有考虑过这个问题。这个问题的提出，相信主要是为了“避免大事务”。所以满足 insert均为小事务即可。下面，我们来探讨一下以下问题：1.什么是大事务？

2.那么 mysqldump出来的 insert语句可能是大事务吗？

定义：运行时间比较长，操作的数据比较多的事务我们称之为大事务。

∘锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。

∘执行时间长，容易造成主从延迟。

前提，MySQL默认是自提交的，所以如果没有明确地开启事务，一条 SQL语句就是一条事务。在 mysqldump里，就是一条 SQL语句为一条事务。

注意，指的是 mysqldump的参数，而不是 mysqld的参数。官方文档提到: If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.

意思是 mysqldump增大这个值，mysqld也得增大这个值，测试结论是不需要的。怀疑官方文档有误。

不调整的话，会出现以下报错：[root@192-168-199-198~]# mysql-uroot-proot-P3306-h192.168.199.198 test<16M.sqlmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2006(HY000) at line 46: MySQL server has gone away

相关测试最后，我放出我的相关测试步骤mysql> select version();+------------+| version()|+------------+| 5.7.26-log|+------------+1 row in set(0.00 sec)

造100万行数据

create database test;

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t values(1,1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');

insert into t select* from t;#重复执行20次

#直到出现Records: 524288 Duplicates: 0 Warnings: 0

mysql> select count(*) from t;

--net-buffer-length=1M[root@192-168-199-198~]# mysqldump-uroot-proot-S/tmp/mysql3306.sock test t>1M.sql[root@192-168-199-198~]# du-sh 1M.sql225M 1M.sql[root@192-168-199-198~]# cat 1M.sql|grep-i insert|wc-l226

默认--net-buffer-length=1M的情况下，225M的SQL文件里有 226条 insert，平均下来确实就是每条 insert的 SQL大小为 1M。

--net-buffer-length=16M

默认--net-buffer-length=16M的情况下，225M的 SQL文件里有 15条 insert，平均下来确实就是每条 insert的 SQL大小为 16M。所以，这里证明了--net-buffer-length确实可用于拆分 mysqldump备份文件的SQL大小的。性能测试insert次数越多，交互次数就越多，性能越低。但鉴于上面例子的 insert数量差距不大，只有 16倍，性能差距不会很大(实际测试也是如此)。我们直接对比--net-buffer-length=16K和--net-buffer-length=16M的情况，他们insert次数相差了 1024倍。

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16K.sql

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16K.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198~]# mysql-uroot-proot-S/tmp/mysql3306.sock-e"reset master";

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16M.sql

mysql: [Warning] Using a password on the command line interface can be insecure.