mysql数据库备份与还原_使用sql语句备份mysql数据库

一、测试数据库的创建

1：官方百万级别的测试数据库：

官方测试数据库github网址：https://github.com/datacharmer/test_db

下载到目录，解压即可，运行命令：

mysql -u root -p < employees.sql mysql -u root -p < employees_partitioned.sql

2：自己创建简单测试数据库：

快速随机生成测试语言的网站：https://generatedata.com/

选择sql和想生成的字段，点击生成Generate!生成即可。

在MySQL输入生成的语句即可。

#1：创建数据库 create database testDatabase; #2：使用数据库 use testDatabase; #3：创建表 CREATE TABLE `myTable` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(255) default NULL, `phone` varchar(100) default NULL, `country` varchar(100) default NULL, `numberrange` mediumint default NULL, PRIMARY KEY (`id`) ) AUTO_INCREMENT=1; 34;Wade Sykes","1-917-342-3132","Turkey",3), ("Barrett Boyer","1-264-304-0665","Germany",9), ("Alana Kaufman","(213) 254-4997","India",0), ("Emmanuel Lopez","(543) 493-0137","Germany",9), ("Timon Bauer","1-269-448-2772","Pakistan",6);

3：测试备份还原时用到的命令

删库跑路测试（先备份好）

drop database testDatabase;

还原后查询库的表数据是否完整。

select * from testDatabase.myTable +----+----------------+----------------+----------+-------------+ | id | name | phone | country | numberrange | +----+----------------+----------------+----------+-------------+ | 1 | Wade Sykes | 1-917-342-3132 | Turkey | 3 | | 2 | Barrett Boyer | 1-264-304-0665 | Germany | 9 | | 3 | Alana Kaufman | (213) 254-4997 | India | 0 | | 4 | Emmanuel Lopez | (543) 493-0137 | Germany | 9 | | 5 | Timon Bauer | 1-269-448-2772 | Pakistan | 6 | +----+----------------+----------------+----------+-------------+

二、非常规备份方式

采用复制整个数据存放目录

1：查看数据库数据存放位置

有两种方法：

1)：在数据库中用命令 show variables like 'datadir'; 查看

mysql > show variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set Time: 0.031s

2)：在配置文件中查看，配置了 datadir 目录的可查看。没有配置的默认为 /var/lib/mysql/ 位置

Linux中查看配置文件

ubunut cat /etc/mysql/my.cnf

2：复制目录或者目录下某个数据库名

cp -fr /var/lib/mysql/ /backup/

3：还原时直接复制文件夹到数据库目录即可

cp -fr /backup/ /var/lib/mysql/

三、常规备份方式基本命令mysqldump

mysqldump又可叫做全量备份。

参数 –databases 同 -B ，单独一个库，也可省略。

1、备份命令mysqldump格式

格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 database 数据库名 > 文件名.sql

备份testDatabase数据库

sudo mysqldump -u root -p --databases testDatabase > testdatabase.bak.sql

2、备份MySQL数据库为带删除表的格式

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

sudo mysqldump --add-drop-table -u root -p --databases testDatabase > testdatabase.sql

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

备份并压缩

sudo mysqldump -u root -p --databases testDatabase | gzip > testdatabase.sql.gz

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

备份testDatabase中的myTable表，不需要用参数 –databases 或者 -B

sudo mysqldump -u root -p testDatabase myTable > myTable.sql

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

同时备份testDatabase和 employees两个库

sudo mysqldump -u root --databases testDatabase employees > backup.sql

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

参数 –all-databases 同 -A

sudo mysqldump --all-databases > allbackupfile.sql

7、还原MySQL数据库的命令

1) 不指定数据名还原，默认生成原数据库名称，还原所有数据库。

sudo mysql -u root -p < allbackupfile.sql

2) 指定数据名还原，还原指定单个数据库，需在数据库种预先创建一个testDatabase名称。

sudo mysql -u root -p testDatabase < allbackupfile.sql

3) 还原压缩的MySQL数据库

gunzip < testDatabase.sql.gz | mysql -u root -p testDatabase #ubuntu 加sudo sudo gunzip < testDatabase.sql.gz | sudo mysql -u root -p testDatabase

4) 进入数据库用source导入

选择数据库 USE testDatabase; #导入sql文件 SOURCE /home/ubuntu/testDatabase.sql;

四、增量备份mysqladmin，以及还原

增量备份是针对于数据库的bin-log日志进行备份的，增量备份是在全量的基础上进行操作的。增量备份主要是靠mysql记录的bin-log日志。

1：查看是否开启bin-log日志

进入mysql输入命令可查看。

show variables like '%log_bin%';

显示如下为开启状态，日志文件在/var/lib/mysql/以binlog.00001的格式保存。

mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------+ 6 rows in set Time: 0.012s mysql root@(none):testDatabase>

如未开启，需要在配置文件种配置

#binlog setting，开启增量备份的关键 log-bin=/var/lib/mysql/binlog

2：查看目前使用的bin-log日志文件

进入mysql查看命令。

show master status;

显示如下，目前使用的是binlog.000022文件，所有操作都记录在此文件。

mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000022 | 156 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set Time: 0.007s

查看当前testDatabase的表myTable数据如下，

mysql > select * from testDatabase.myTable +----+----------------+----------------+----------+-------------+ | id | name | phone | country | numberrange | +----+----------------+----------------+----------+-------------+ | 1 | Wade Sykes | 1-917-342-3132 | Turkey | 3 | | 2 | Barrett Boyer | 1-264-304-0665 | Germany | 9 | | 3 | Alana Kaufman | (213) 254-4997 | India | 0 | | 4 | Emmanuel Lopez | (543) 493-0137 | Germany | 9 | | 5 | Timon Bauer | 1-269-448-2772 | Pakistan | 6 | | 7 | ABC | 1-917-342-3123 | KHMER | 6 | +----+----------------+----------------+----------+-------------+ 6 rows in set Time: 0.008s

3：刷新日志，使用新的日志文件（备份）

在命令端执行命令

mysqladmin -u root -p flush-logs

日志文件从 binlog.000022 变为 binlog.000023

mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000023 | 841 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set Time: 0.007s

这时相当与已经备份成功，备份文件即为上次的binlog.000022日志文件。

4：删除数量，从日志还原数据

1) 删除ABC行

delete from `myTable` where name = 'ABC';

查询以及没有ABC行列。

mysql> select * from testDatabase.myTable +----+----------------+----------------+----------+-------------+ | id | name | phone | country | numberrange | +----+----------------+----------------+----------+-------------+ | 1 | Wade Sykes | 1-917-342-3132 | Turkey | 3 | | 2 | Barrett Boyer | 1-264-304-0665 | Germany | 9 | | 3 | Alana Kaufman | (213) 254-4997 | India | 0 | | 4 | Emmanuel Lopez | (543) 493-0137 | Germany | 9 | | 5 | Timon Bauer | 1-269-448-2772 | Pakistan | 6 | +----+----------------+----------------+----------+-------------+ 5 rows in set Time: 0.008s

2) 恢复数据ABC行

退出mysql，在命令端用mysqlbinlog命令恢复到binlog.000022日志状态。

sudo mysqlbinlog /var/lib/mysql/binlog.000022 | sudo mysql -u root -p testDatabase;

进入数据库再次查看数据，ABC已经恢复。

mysql> select * from testDatabase.myTable +----+----------------+----------------+----------+-------------+ | id | name | phone | country | numberrange | +----+----------------+----------------+----------+-------------+ | 1 | Wade Sykes | 1-917-342-3132 | Turkey | 3 | | 2 | Barrett Boyer | 1-264-304-0665 | Germany | 9 | | 3 | Alana Kaufman | (213) 254-4997 | India | 0 | | 4 | Emmanuel Lopez | (543) 493-0137 | Germany | 9 | | 5 | Timon Bauer | 1-269-448-2772 | Pakistan | 6 | | 7 | ABC | 1-917-342-3123 | KHMER | 6 | +----+----------------+----------------+----------+-------------+ 6 rows in set Time: 0.008s

增量备份完成。