Mysql数据库理论基础之十二----备份与还原

一、简介

由MySQL AB公司开发，是最流行的开放源码SQL数据库管理系统，主要特点：

我们提供的服务有：成都网站建设、成都网站制作、微信公众号开发、网站优化、网站认证、开平ssl等。为上1000+企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的开平网站制作公司

1、是一种数据库管理系统
2、是一种关联数据库管理系统
3、是一种开放源码软件，且有大量可用的共享MySQL软件
4、MySQL数据库服务器具有快速、可靠和易于使用的特点
5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中
InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。

二、MySQL备份数据

2.1. 备份类型

热备份：读、写不受影响；

温备份：仅可以执行读操作；

冷备份：离线备份，读、写操作均中止；

2.2. 备份方式

物理备份：复制数据文件，速度快，保存数据库的所有数据结构，如果数据跟数据库关联性强，

导致物理备份可移植性能力不强；

逻辑备份：数据导出至文本文件中速度慢、丢失浮点数精度；方便用文本工具直接处理，可移植能力强；

缺点：浮点数据可能会丢失精度；备份出的数据更占用存储空间；

压缩后可大大节省空间；不适合对大数据库做完全备份。

2.3. 备份策略

2.3.1. 数据库需要备份的内容：数据、配置文件、二进制日志、事务日志

完全备份：备份全部数据；

增量备份：仅备份上次完全备份或者增量备份以后变化的数据；

差异备份：仅备份上次完全备份以后变化的数据；（差异备份比增量备份所需空间大）

Mysql数据库理论基础之十二 ---- 备份与还原

2.4. 备份工具

2.4.1. mysqlhotcopy：物理备份工具、温备份

使用mysqlhotcopy工具快速备份（物理备份工具）

一看名字就知道是热备份。因此,mysqlhotcopy支持不停止MySQL服务器备份;
且mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用;
使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。

备份原理：

先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，
最后，把需要备份的数据库文件复制到目标目录。

命令格式：

   [root@localhost ~]#

dbname：数据库名称；
backupDir：备份到哪个文件夹下；

常用选项：

--help：查看mysqlhotcopy帮助；
--allowold：如果备份目录下存在相同的备份文件，将旧的备份文件加上_old；
--keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧的文件更名；
--flushlog：本次辈分之后，将对数据库的更新记录到日志中；
--noindices：只备份数据文件，不备份索引文件；
--user=用户名：用来指定用户名，可以用-u代替；
--password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p之间没有空格；
--port=端口号：用来指定访问端口，可以用-P代替；
--socket=socket文件：用来指定socket文件，可以用-S代替；

　该工具并非mysql自带，需要安装Perl的数据库接口包；也仅仅能够备份MyISAM类型的表。

mysqlhotcopy下载地址: http://dev.mysql.com/downloads/dbi.html

2.4.2.文件系统工具：

cp: 冷备工具

lv: 逻辑卷的快照功能，几乎热备：

mysql> FLUSH TABLES; 刷新表

mysql>LOCK tables 锁表

创建快照：释放锁，而后复制数据

2.4.3.第三方备份工具：

ibbackup：商业工具

xtrabackup：开源工具

2.4.4. mysqldump：逻辑备份工具、MyISAM(温备份)、InnoDB(热备份)

mysqldump:(备份时需加以下选项)

--databases DB1,DB2,... #备份哪几个数据库

--all-databases #备份所有数据库

MyISAM:温备份（需加选项如下）

--lock-all-tables #备份时锁定所有表

--lock-tables #备份一张表时只锁定该备份的表

InnoDB:热备份（需加选项如下）

--single-transaction #执行热备份时需加该项表示是执行热备份

--flush-logs #备份时先滚动下日志文件

--events #备份事件

--routines #备份存储事件和函数

--triggers #备份触发器

--master-data={0|1|2} 备份后的文件记录了日志位置（一般设置为2）

2.4.4.1.直接复制整个数据库目录

　MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。

备份前，必须先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难停止服务来备份服务器。

　此方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。

2.4.4.2.使用mysqldump命令备份（逻辑备份工具）

mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

工作原理：它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

2.4.4.2.1、备份一个数据库，备份前需先刷新表，并锁表，否则表修改造成备份的不一致性

mysqldump基本语法：

　mysqldump -u username -p dbname table1 table2...-> BackupName.sql

dbname： 表示数据库的名称；
table1和table2： 表示需要备份的表的名称，为空则整个数据库备份；
BackupName.sql： 表示设计备份文件的名称，文件名前面可以加上一个绝对路径，
通常将数据库被分成一个后缀名为sql的文件；

选项：

--master-data={0|1|2}

0：不记录二进制日志文件及其位置；
1：以CHANGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；
2：以CHANGE MASTER TO的方式记录位置，但默认为被注释掉；

--lock-all-tables 锁定所有表

--flush-logs 执行日志flush刷新

--single-transaction 若表类型均为InnoDB启动热备，可使用，热备时不要锁表；

mysql> FLUSH TABLES WITH READ LOCK; #锁表

[root@lamp ~]# mysqldump -uroot -p studb > /root/studb0616.sql #备份

mysql> unlock tables; #解锁

　实例：使用root用户备份test数据库步骤：

第一步、先刷新并锁表：

mysql> FLUSH TABLES WITH READ LOCK; #刷新表，并以读锁表

Query OK, 0 rows affected (0.00 sec)

第二步、再通过mysqldump备份表：

[root@lamp ~]# mysqldump -uroot -p test > /root/stu.sql#备份test数据库到/root目录下

Enter password:

[root@lamp ~]# ls /root

anaconda-ks.cfg Public Templates stu.sql

第三步、备份完后再解锁表：

mysql> UNLOCK TABLES; #解锁表

Query OK, 0 rows affected (0.00 sec)

　文件的开头会记录MySQL的版本、备份的主机名和数据库名。

　文件中以“--”开头的都是SQL语言的注释，以"/*!40101"等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比1.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就会被当做注释。

Usage：模拟所有库都坏掉，实现完全备份和及时点还原。

*************************************************************

第一步、先刷新并锁表：

mysql> FLUSH TABLES WITH READ LOCK;

#刷新表，并以读锁表（此步骤也可以结合mysqldump一起使用，不用单独操作）

--flush-logs --lock-all-tables含义一样。

第二步、完全备份所有数据库

[root@lamp ~]# mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/alldatabases.sql

#--master-data=2 以CHANGE MASTER TO的方式记录位置，但默认为被注释掉，

--flush-logs 执行日志flush,日志滚动，

--all-databases 备份所有库，

--lock-all-tables 锁定所有表，备份至/root目录下。（完全备份所有数据库）

--single-transaction 若表类型为InnoDB,此项可启动热备

Enter password:

[root@lamp ~]# less /root/alldatabases.sql #查看完全备份中当前的二进制日志为000020

-- MySQL dump 10.13 Distrib 5.5.28, for Linux (x86_64)

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 5.5.28-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

...

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=107;

-- Current Database: `hellodb`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARAC

...

第三步、模拟删除数据库日志、数值、所有数据

mysql> PURGE BINARY LOGS TO 'mysql-bin.000020';

#模拟删除当前二进制之前的日志信息。(再删除前最好把日志文件保存至其他地方，以备还原至之前状态需要)

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW BINARY LOGS; #查看所有日志信息，只剩下000020

+---------------+---------+

| Log_name | File_size |

+---------------+---------+

| mysql-bin.000020 | 107 |

+---------------+---------+

1 row in set (0.00 sec)

mysql> USE studb;

Database changed

mysql> SELECT * FROM tutors; #查询表tutors的信息

+-----+------------------+--------+-----+

| TID | Tname | Gender | Age |

+-----+------------------+--------+-----+

| 1 | HongQigong | M | 93 |

| 2 | HuangYaoshi | M | 63 |

| 3 | HuangRong | F | 46 |

| 4 | HuYidao | M | 65 |

| 5 | XiaoLongnv | F | 28 |

| 6 | HuFei | M | 45 |

| 7 | GuoXiang | F | 32 |

+-----+------------------+--------+-----+

mysql> DELETE FROM tutors WHERE Age>80; #删除表tutors年龄大于80的行

Query OK, 1 rows affected (0.01 sec)

mysql> SELECT * FROM tutors; #查询表tutors的信息

+-----+------------------+--------+-----+

| TID | Tname | Gender | Age |

+-----+------------------+--------+-----+

| 2 | HuangYaoshi | M | 63 |

| 3 | HuangRong | F | 46 |

| 4 | HuYidao | M | 65 |

| 5 | XiaoLongnv | F | 28 |

| 6 | HuFei | M | 45 |

| 7 | GuoXiang | F | 32 |

+-----+------------------+--------+-----+

mysql> \q

[root@lamp ~]# mysql -uroot -p #登录mysql

Enter password:

mysql> FLUSH LOGS; #刷新滚动日志

Query OK, 0 rows affected (0.01 sec)

mysql> \q

[root@lamp ~]# cd /mydata/data/ #进入到mysql数据文件夹

[root@lamp data]# ls

hellodb ib_logfile0 lamp.err mydb mysql-bin.000020 mysql-bin.index stu

ibdata1 ib_logfile1 lamp.pid mysql mysql-bin.000021 performance_schema test

[root@lamp data]# mysqlbinlog mysql-bin.000020 > /root/mon-incremental.sql

#把mysql的二进制日志文件导出成sql格式数据，（并命名为第一次增量备份）

[root@lamp data]# mysql -uroot -p #登录mysql

Enter password:

mysql> USE studb;

Database changed

mysql> INSERT INTO tutors (Tname) Values ('stu123'); #往表tutors插入一行数据

Query OK, 1 rows affected (0.01 sec)

mysql> \q

[root@lamp data]# ls

hellodb ib_logfile0 lamp.err mydb mysql-bin.000020 mysql-bin.index

ibdata1 ib_logfile1 lamp.pid mysql mysql-bin.000021 performance_schema

[root@lamp data]# cp mysql-bin.000021 /root

#复制最后一次操作的二进制日志文件至数据目录以外的目录/root

[root@lamp data]# rm -rf ./* #删除数据库的数据目录中的所有数据（模拟数据库的所有库数据丢失）

[root@lamp data]# ls #查看当前目录，数据已经被清空

[root@lamp data]# service mysqld stop #由于数据文件都被清空，故此时mysql无法停止，也无法启动

MySQL server PID file could not be found! [FAILED]

第四步、恢复所有数据

[root@lamp data]# killall mysqld #此时只能强制终止mysqld进程

[root@lamp ~]# cd /usr/local/mysql/ #进入mysql的安装目录

[root@lamp mysql]# ls

bin data include lib mysql-test scripts sql-bench

COPYING docs INSTALL-BINARY man README share support-files

[root@lamp mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

#对mysql数据库进行初始化设置，并指定相关的选项，--datadir指定数据库的数据存放目录。

Installing MySQL system tables...

Filling help tables...

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'

./bin/mysqladmin -u root -h lamp password 'new-password'

Alternatively you can run:

./bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

[root@lamp mysql]# service mysqld start

#数据库初始化成功后重新启动数据库，因为已经初始化，所以数据库的密码为空.

Starting MySQL... [ OK ]

[root@lamp mysql]# mysql < /root/alldatabases.sql #先恢复完全备份的内容

[root@lamp mysql]# mysql < /root/mon-incremental.sql #恢复第一次增量备份

[root@lamp mysql]# mysqlbinlog /root/mysql-bin.000021 > /root/temp.sql

#把故障前的最后一次二进制日志导出成sql格式的数据

[root@lamp mysql]# mysql < /root/temp.sql

#把二进制日志还原至数据库至此就手动进行了一次mysql所有库的及时点备份还原恢复,

但是此还原数据库的方法会导致产生很多mysql的二进制日志文件。

[root@lamp mysql]# mysqlbinlog /root/mysql-bin.000021 | mysql -uroot -p

#和以上两步通过temp.sql中转一样的效果

正确的恢复方法是：关闭二进制日志功能后备份，再启用二进制日志功能

恢复数据前，临时关闭mysql记录二进制日志功能，待恢复后再开启记录二进制日志的功能；

mysql> SET sql_log_bin=0; #临时关闭记录二进制日志功能

mysql> SELECT @@sql_log_bin; #确认当前会话的状态

mysql> \. /root/alldatabases.sql

#恢复完全备份的内容（\.表示读取导入后面目录的文件内容，也可以替换为SOURCE命令）

mysql> \. /root/mon-incremental.sql #恢复第一次增量备份

mysql> SET sql_log_bin=1; #数据库恢复完后再打开记录二进制日志功能

********************************************************************************

2.4.4.2.2、备份多个数据库

语法：mysqldump -u username -p --all-databases > /root/back.sql ：备份所有库

mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

加上了--databases选项，然后后面跟多个数据库

mysqldump -u root -p --databases test mysql > /root/backup.sql

--events

--routines

--triggers

2.4.4.2.3、备份所有数据库

mysqldump命令备份所有数据库的语法如下：

mysqldump -u username -p -all-databases > /root/BackupName.sql

示例：

mysqldump -u -root -p -all-databases > /root/all.sql

2.4.4.2.4、备份数据库中的单张表

对于只备份单张表的备份与还原，通过SELECT命令，可以更快速的达到备份和恢复的目的；

以及通过此方法把一个数据库中表的数据，导入至另一数据库的表中去。

2.4.4.2.4.1.备份格式：

SELECT * INTO OUTFILE '/PATH/TO/somefile.txt' FROM table_name [WHERE CLAUSE];

#备份表中的数据或满足WHERE语句的数据到服务器上保存。

table_name：需要备份的表

WHERE：满足的条件，可选项。

/PATH/TO：服务器上的路径目录，且此目录必须是执行SELECT语句的用户有写的权限，否则无法备份。

2.4.4.2.4.2. 还原格式：

LOAD DATA INFILE '/PATH/TO/somefile.txt' INTO TABLE table_name;

table_name：需要还原的表的名称，此表必须先在数据库中存在。

/PATH/TO：备份所存放的路径

mysql> CREATE TABLE tutor LIKE tutors; #仿照tutors表的框架创建一个空表tutor

实例：备份一个数据库表的数据，导入至另一个数据库表的数据；

mysql> USE jiaowu;

Database changed

mysql> SELECT * FROM tutors; #查询表tutors的信息

+-----+--------------+--------+------+

| TID | Tname | Gender | Age |

+-----+--------------+--------+------+

| 1 | HongQigong | M | 93 |

| 3 | Miejueshitai | F | 72 |

| 4 | OuYangfeng | M | 76 |

| 5 | YiDeng | M | 90 |

| 6 | YuCanghai | M | 56 |

| 7 | Jinlunfawang | M | 67 |

| 8 | HuYidao | M | 42 |

| 9 | NingZhongze | F | 49 |

| 10 | stuu01 | M | 30 |

+-----+--------------+--------+------+

9 rows in set (0.00 sec)

mysql> SELECT * INTO OUTFILE '/tmp/tutor.txt' FROM tutors;

Query OK, 9 rows affected (0.01 sec)

mysql> CREATE TABLE tutor LIKE tutors;

Query OK, 0 rows affected (0.03 sec)

mysql> DESC tutor;

+---------+---------------------------+------+-----+---------+------------------+

+---------+---------------------------+------+-----+---------+------------------+

| TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| Gender | enum('F','M') | YES | | M | |

| Age | tinyint(3) unsigned | YES | | NULL | |

+---------+---------------------------+------+-----+---------+------------------+

4 rows in set (0.01 sec)

mysql> SELECT * FROM tutor; #查询表tutor的信息

Empty set (0.04 sec) #暂时无数据

mysql> LOAD DATA INFILE '/tmp/tutor.txt' INTO TABLE tutor;

Query OK, 9 rows affected (0.04 sec)

Records: 9 Deleted: 0 Skipped: 0 Warnings: 0

mysql> SELECT * FROM tutor; #查询表tutor的信息

+-----+--------------+--------+------+

| TID | Tname | Gender | Age |

+-----+--------------+--------+------+

| 1 | HongQigong | M | 93 |

| 3 | Miejueshitai | F | 72 |

| 4 | OuYangfeng | M | 76 |

| 5 | YiDeng | M | 90 |

| 6 | YuCanghai | M | 56 |

| 7 | Jinlunfawang | M | 67 |

| 8 | HuYidao | M | 42 |

| 9 | NingZhongze | F | 49 |

| 10 | stuu01 | M | 30 |

+-----+--------------+--------+------+

9 rows in set (0.00 sec)

三、MySQL数据还原 ------ 经常做消防预演！！！

　3.1.还原使用mysqldump命令备份的数据库的语法如下：

　mysql -u root -p [dbname] < backup.sql

示例：

mysql -u root -p < C:\backup.sql

3.2. 还原直接复制目录的备份

　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。

　3.3.自动备份脚本：

1，每天4点备份mysql数据；

2，为节省空间，删除超过3个月的所有备份数据；
3，删除超过7天的备份数据，保留3个月里的 10号 20号 30号的备份数据；

[root@mysql ~]# vim Full_backup_mysql.sh

#!/bin/bash

cd /root/dbdata/mysqlbak

echo "You are in backup dir : /root/dbdata/mysqlbak"

echo "It is starting backup ..."

mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > ./`date +%Y%m%d`.sql

find /root/dbdata/mysqlbak/ -mtime +7 -name '*[1-9].sql' -exec rm -rf {} \;

find /root/dbdata/mysqlbak/ -mtime +90 -name '*.sql' -exec rm -rf {} \;

#创建定时任务

crontab –e

0 4 * * * /data/dbdata/backup_mysql.sh

mysql> show global variables like '%log%'; #查看日志相关所有全局变量

mysql> show master status; #查看二进制日志信息

mysql> set sql_log_bin=0; #设置不记录二进制日志

附1：mysqldump常用于MySQL数据库逻辑备份

http://www.cnblogs.com/feichexia/p/MysqlDataBackup.html

---end---14--

分享文章：Mysql数据库理论基础之十二----备份与还原
转载注明：http://6mz.cn/article/pppcgd.html

网站建设知识

Mysql数据库理论基础之十二----备份与还原

一、简介

二、MySQL备份数据

三、MySQL数据还原 ------ 经常做消防预演！！！

3.1.还原使用mysqldump命令备份的数据库的语法如下：

其他资讯