189 8069 5689
MyISAM 表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。1. 使用直接拷贝数据库备份 典型的如cp、tar或cpio实用程序当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。 如果你不想关闭服务器，要在执行表检查的同时锁定服务器。如果服务器在运行，相同的制约也适用于拷贝文件，应该使用相同的锁定协议让服务器“安静下来”。当你完成了MySQL数据库备份时，需要重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。 要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。2. 使用mysqldump数据库备份mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump程序备份数据库较慢，但它生成的文本文件便于移植。mysqldump 的一些主要参数：1）--compatible=name它告诉 mysqldump，导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。2）--complete-insert，-c导出的数据采用包含字段名的完整 INSERT 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。3）--default-character-set=charset指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。4）--disable-keys告诉mysqldump 在 INSERT 语句的开头和结尾增加 ; 和 ; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。5）--extended-insert = true|false默认情况下，mysqldump 开启 --complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。6）--hex-blob使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。7）--lock-all-tables，-x在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。8）--lock-tables它和--lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 选项。9）--no-create-info，-t只导出数据，而不添加 CREATE TABLE 语句。10）--no-data，-d不导出任何数据，只导出数据库表结构。11）--opt这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 mysqldump 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。12）--quick，-q该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。13）--routines，-R导出存储过程以及自定义函数。14）--single-transaction该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。 本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。 要想导出大表的话，应结合使用 --quick 选项。--triggers同时导出触发器。该选项默认启用，用 --skip-triggers 禁用它。备份：使用mysqldump备份数据库其实就是把数据库转储成一系列CREATE TABLE和INSERT语句，通过这些语句我们就可重新生成数据库。使用mysqldump的方法如下：% mysqldump --opt testdb | gzip /data/backup/testdb.bak#--opt选项会对转储过程进行优化，生成的备份文件会小一点，后的管道操作会进行数据压缩% mysqldump --opt testdb mytable1,mytable2 | gzip /data/backup/testdb_mytable.bak#可在数据库后接数据表名，只导出指定的数据表，多个数据表可用逗号分隔--opt选项还可激活--add-drop-table选项，它将会在备份文件的每条CREATE TABLE前加上一条DROP TABLE IF EXISTS语句。这可方便进行数据表的更新，而不会发生“数据表已存在”的错误。用mysqldump命令还可直接把数据库转移到另外一台服务器上，不用生成备份文件。重复执行可定期更新远程数据库。% mysqladmin -h remote_host create testdb% mysqldump --opt testdb | mysql -h remote_host testdb另外还可通过ssh远程调用服务器上的程序，如：% ssh remote_host mysqladmin create testdb% mysqldump --opt testdb | ssh remote_host mysql testdb 通过直接拷贝系统文件的方式备份数据库，在备份时，要确保没有人对数据库进行修改操作。要做到这点，最好关闭服务器。如果不能关闭的，要以只读方试锁定有关数据表。下面是一些示例：% cp -r db /backup/db #备份db数据库到/backup/db目录% cp table_name.* /backup/db #只备份table_name数据表% scp -r db remotehot:/usr/local/mysql/data #用scp把数据库直接拷贝到远程服务器，在把数据库直接拷贝到远程主机时，应注意两台机器必须有同样的硬件结构，或者将拷贝的数据表全部是可移植数据表类型。或者/usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob -x testdb testdb.sql使用以下 SQL 来备份 Innodb 表：/usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob --single-transaction testdb testdb.sql另外，如果想要实现在线备份，还可以使用 --master-data 参数来实现，如下：/usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --master-data=1 \--single-transaction --flush-logs testdb testdb.sql它只是在一开始的瞬间请求锁表，然后就刷新binlog了，而后在导出的文件中加入CHANGE MASTER 语句来指定当前备份的binlog位置，如果要把这个文件恢复到slave里去，就可以采用这种方法来做。 还原：用mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本，有两种方法可以将数据导入。直接用 mysql 客户端例如：/usr/local/mysql/bin/mysql -uroot -proot testdb testdb.sql用SOURCE 语法其实这不是标准的 SQL 语法，而是 mysql 客户端提供的功能，例如：SOURCE /tmp/testdb.sql;这里需要指定文件的绝对路径，并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。 3. 使用mysqlhotcopy数据库备份 使用mysqlhotcopy工具，它是一个Perl DBI脚本，可在不关闭服务器的情况下备份数据库，mysqlhotcopy 是一个 PERL 程序，最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上。mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在 类Unix 和 NetWare 系统上。它主要的优点是：它直接拷贝文件，所以它比mysqldump快。可自动完成数据锁定工作，备份时不用关闭服务器。能刷新日志，使备份文件和日志文件的检查点能保持同步。备份：mysqlhotcopy 支持一次性拷贝多个数据库，同时还支持正则表达。以下是几个例子：/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb /tmp (把数据库目录 testdb 拷贝到 /tmp 下)/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb_1 testdb_2 testdb_n /tmp/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb./regex/ /tmp 还原：mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：cp -rf testdb /usr/local/mysql/data/chown -R nobody:nobody /usr/local/mysql/data/ (将 testdb 目录的属主改成 mysqld 运行用户) 4. 使用SQL语句数据库备份 BACKUP TABLE 语法其实和 mysqlhotcopy 的工作原理差不多，都是锁表，然后拷贝数据文件。它能实现在线备份，但是效果不理想，因此不推荐使用。它只拷贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。备份：BACK TABLE tbl_test TO '/tmp/testdb/'; #把tbl_test数据库备份到/tmp/testdb/目录里，会自动创建一个testdb目录 为了执行该语句，你必须拥有那些表的FILE权限和SELECT权限，备份目录还必须是服务器可写的。该语句执行时，会先把内存中的数据写入磁盘，再把各个数据表的.frm(表结构定义文件)、.MYD(数据)文件从数据目录拷贝到备份目录。它不拷贝.MYI(索引)文件，因为它能用另外两个文件重建。BACKUP TABLE语句备份时，依次锁定数据表，当同时备份多个数据表时，数据表可能会被修改，所以备份0完成时，备份文件中的数据和现时数据表中的数据可能会有差异，为了消除该差异，我们可用只读方式锁定数据表，在备份完成后再解锁。如：mysql LOCK TABLES tb1 READ,tb2 READ;mysql BACKUP TABLE tb1,tb2 TO 'backup/db';mysql UNLOCK TABLES;使用BACKUP TABLE语句备份的数据表可用RESTORE TABLE重新加载到服务器。注意，必须要有 FILE 权限才能执行本SQL，并且目录 /tmp/testdb/ 必须能被 mysqld 用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件，可以自定义字段间隔的方式，方便处理这些数据。 例子：SELECT * INTO OUTFILE '/tmp/testdb/tbl_test.txt' FROM tbl_test;注意，必须要有 FILE 权限才能执行本SQL，并且文件 /tmp/testdb/tbl_test.txt 必须能被 mysqld 用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。还原：用BACKUP TABLE 方法备份出来的文件，可以运行 RESTORE TABLE 语句来恢复数据表。例子：RESTORE TABLE FROM '/tmp/testdb/';权限要求类似上面所述。用SELECT INTO OUTFILE 方法备份出来的文件，可以运行 LOAD DATA INFILE 语句来恢复数据表。例子：LOAD DATA INFILE '/tmp/testdb/tbl_name.txt' INTO TABLE tbl_name;权限要求类似上面所述。倒入数据之前，数据表要已经存在才行。
我们提供的服务有：成都网站设计、做网站、微信公众号开发、网站优化、网站认证、积石山保安族东乡族ssl等。为上1000家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的积石山保安族东乡族网站制作公司
首先确认一下是否有定期的备份任务，如果没有在考虑下面的方式。
配置参数上，是否开启了bin-log日志？如果开启了并且bin-log日志的周期保留比较长，可以通过重放bin-log日志的方式恢复数据。
因为磁盘空间不足，我的一个虚拟机服务器崩溃了。结果数据库服务器进程无法启动，数据也就无法导出。只能想办法从数据库原始文件 ibdata 和 frm 文件中恢复数据库。
因为没有经验，好不容易才找到了恢复方法。特此记录，以备后用。
磁盘空间不足之后，mysqld 进程无法启动，提示“Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)”。这真是让人无比头大，数据库根本连接不上。
目录 Contents
1. 保存原始数据库文件
2. 恢复方法
3. 参考资料：
1. 保存原始数据库文件¶
好在数据库原始文件还在。在我的系统环境和配置情况下，这些文件位于 /var/lib/mysql/ 文件夹下面。假设数据库名是 test，则这些文件表现为：
--mysql
|--test
|--1.frm
|--2.frm
|...
|--mysql
|...
|--ib_logfile0
|--ib_logfile1
|--ibdata1
|...
这些就是原始数据库文件，可以用来恢复数据库。将这些文件额外保存一份，以防万一。
2. 恢复方法¶
我的原始虚拟机完全没有磁盘空间而无法启动数据库服务器进程。虽然试着删除一些不需要的文件，但是数据库却始终无法连接。于是我新建了一个几乎一样的虚拟机（当然磁盘加大了），试图将这些数据库文件导入并恢复数据库。
在经历了很多错误之后，终于找到了正确的方法：
安装完成新服务器之后，通过命令行新建了与原来一样的数据库：数据库名称、用户名、密码都一样。如果有多个数据库需要恢复，就都给建好。（跟配置新服务器一样，参见安装和配置 MYSQL 数据库服务器。）
停止 mysqld 进程
service mysqld stop
将备份的原始数据库文件中的所有 .frm 文件（保持原来的目录结构）和 ibdata1 文件复制到新服务器的数据库文件目录中（如果新服务器操作系统和配置环境一样，那么目录结构也一样），其它文件不要。
使用 -innodb_force_recovery=6参数启动数据库服务器进程，这里是
/etc/init.d/mysqld start -defaults-file=/etc/my.cnf -standalone -console -innodb_force_recovery=6
OK，数据库恢复完成。
打开mysql的bin log功能：
对于mysql也是支持增量备份，但要打开mysql的bin log功能。
我们修改mysql的配置文件。linux是/etc/my.cnf，windows是mysql的安装目录/my.ini
我们在[mysqld]下面加上log-bin一行代码，如下面。
[mysqld]
log-bin=mysql-bin
复制代码
加完后重起mysql即可。
某客户更新数据的时候，误删了数据库的内容，因为数据库做了主从，但是没有做备份（备份很重要啊！）幸好开启了bin-log，之后只好把整个日志的记录拿回来本地进行恢复。
之后自己也做了一个简单的测试，对数据进行恢复，具体如下：
1、新建一个表
CREATE TABLE `lynn`.`sn_test` ( `name` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL , `age` INT( 3 ) NOT NULL ) ENGINE = MYISAM;
2、插入多条数据
INSERT INTO `lynn`.`sn_test` (`name`, `age`) VALUES ('lynn1', '1');
INSERT INTO `lynn`.`sn_test` (`name`, `age`) VALUES ('lynn2', '2');
INSERT INTO `lynn`.`sn_test` (`name`, `age`) VALUES ('lynn3', '3');
INSERT INTO `lynn`.`sn_test` (`name`, `age`) VALUES ('lynn4', '4');
3、查看数据并删除
mysql select * from sn_test;
+-------+-----+
| name | age |
+---------+---+
| lynn1 | 1 |
| lynn2 | 2 |
| lynn3 | 3 |
| lynn4 | 4 |
+---------+-----+
4 rows in set (0.00 sec)
mysql delete from sn_test;
Query OK, 4 rows affected (0.00 sec)
mysql select * from sn_test;
Empty set (0.00 sec)
4、mysqlbinlog恢复数据
mysqlbinlog mysql-bin.000006 1.sql
查看1.txt里面数据插入的纪录，把删除之前的数据进行恢复
mysqlbinlog mysql-bin.000006 --start-position=2471 --stop-position=2876 | mysql -uroot -p123
重新登录，查看数据，OK，已经成功恢复了
对于数据库操作，应该注意如下问题：
1、要常备份（全备，增量备份），出了问题可以最快恢复数据；
2、操作数据库前，要把需要操作的数据库或者表dump出来；
3、需要把bin-log打开，就算没有做上面的两步，也可以通过日志恢复数据
1：官方百万级别的测试数据库：
官方测试数据库github网址：
下载到目录，解压即可，运行命令：
2：自己创建简单测试数据库：
快速随机生成测试语言的网站：
选择sql和想生成的字段，点击生成Generate!生成即可。
在MySQL输入生成的语句即可。
3：测试备份还原时用到的命令
删库跑路测试（先备份好）
还原后查询库的表数据是否完整。
采用复制整个数据存放目录
1：查看数据库数据存放位置
有两种方法：
1)：在数据库中用命令 show variables like 'datadir'; 查看
2)：在配置文件中查看，配置了 datadir 目录的可查看。没有配置的默认为 /var/lib/mysql/ 位置
Linux中查看配置文件
2：复制目录或者目录下某个数据库名
3：还原时直接复制文件夹到数据库目录即可
mysqldump又可叫做全量备份。
参数 --databases 同 -B ，单独一个库，也可省略。
1、备份命令mysqldump格式
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 database 数据库名 文件名.sql
备份testDatabase数据库
2、备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
3、直接将MySQL数据库压缩备份
备份并压缩
4、备份MySQL数据库某个(些)表
备份testDatabase中的myTable表，不需要用参数 --databases 或者 -B
5、同时备份多个MySQL数据库
同时备份testDatabase和 employees两个库
6、备份服务器上所有数据库
参数 --all-databases 同 -A
7、还原MySQL数据库的命令
1) 不指定数据名还原，默认生成原数据库名称，还原所有数据库。
2) 指定数据名还原，还原指定单个数据库，需在数据库种预先创建一个testDatabase名称。
3) 还原压缩的MySQL数据库
4) 进入数据库用source导入
增量备份是针对于数据库的bin-log日志进行备份的，增量备份是在全量的基础上进行操作的。增量备份主要是靠mysql记录的bin-log日志。
1：查看是否开启bin-log日志
进入mysql输入命令可查看。
显示如下为开启状态，日志文件在/var/lib/mysql/以binlog.00001的格式保存。
如未开启，需要在配置文件种配置
2：查看目前使用的bin-log日志文件
进入mysql查看命令。
显示如下，目前使用的是binlog.000022文件，所有操作都记录在此文件。
查看当前testDatabase的表myTable数据如下，
3：刷新日志，使用新的日志文件（备份）
在命令端执行命令
日志文件从 binlog.000022 变为 binlog.000023
这时相当与已经备份成功，备份文件即为上次的binlog.000022日志文件。
4：删除数量，从日志还原数据
1) 删除ABC行
查询以及没有ABC行列。
2) 恢复数据ABC行
退出mysql，在命令端用mysqlbinlog命令恢复到binlog.000022日志状态。
进入数据库再次查看数据，ABC已经恢复。
增量备份完成。
如果你要恢复的数据库是包含授权表的mysql数据库，您将需要使用--skip赠款-tables选项来运行服务器恢复整个数据库
- 首先，。否则，服务器会抱怨不能找到授权表。恢复表后，执行mysqladmin冲水privileges告诉服务器装载授权表，并用它们来启动。数据库目录到其他地方的原始内容
复制。例如，您可以使用它们在以后的事后分析检查表（验尸）的崩溃。
酒店与最新的数据库备份文件重新加载。如果您打算使用mysqldump的所加载的文件，则需要输入它们作为mysql的。如果您打算使用从数据库文件直接拷贝（例如，使用tar或CP），它会复制他们直接回数据库目录中。然而，在这种情况下，你应该在关闭之前，该服务器复制这些文件，然后重新启动它。在备份过程中
重做日志，然后查询更新数据库表中。对于所有可用的更新日志，你可以使用它作为mysql的输入。指定--one-database选项，因此MySQL只是要还原的数据库执行查询。如果你意识到有必要使用所有的更新日志文件，可以使用包含以下命令日志的目录：
％LS-TRL更新（0-9）* | xargs的猫| mysql--。一个数据库DB_NAME
ls命令生成一个单独的日志文件更新列表，更新日志文件根据服务器（你知道，如果你其中的任何文件，排序顺序生成的顺序进行排序会发生变化，这将导致在更新日志中以错误的顺序使用）。有限公司您可能需要使用一些更新日志。例如，如果日志，因为命名update.392备份，pdate.393等产生的，可以在命令重新运行它们：
％的mysql - 一个数据库DB_NAME 录入。 392
％的mysql - 一个数据库DB_NAME updata.393 ...
如果您正在运行的恢复，并打算使用更新日志恢复由于失误降数据的基础上，DROPTABLE或DELETE语句和丢失的信息时，一定要在开始更新日志删除这些语句。
恢复单个表
恢复单个表是非常困难的。如果是的mysqldump备份文件生成的，它只是不包含数据，您需要一个表，你需要提取的相关线路，并利用它们作为mysql的输入，这部分比较容易。困难的是，提取在该表的更新日志中使用的片段。你会发现：mysql_find_rows工具在这方面有所帮助，它可以提取多行从更新日志查询。另一种可能性是使用另一台服务器
恢复整个数据库，然后将该文件复制到表的原始数据库。这其实很容易！当文件被复制回数据库目录，确保原始数据库服务器关闭。