对MySQL数据库执行非热备操作的要点

mysql备份恢复的场景一：

数据库数据比较小的情况下，使用mysqldump做完全备份，使用mysqlbinlog做增量备份。

完全备份，示例：

# mysqldump -A --lock-all-tables --routines --triggers --master-data=2 > /backup/all-`date +%F`.sql

增量备份，示例：

# mysqlbinlog /mydata/data/mysql-bin.0000## > /backup/incre-`date +%F`.sql

恢复的过程：

1、关闭数据库：

# service mysqld stop

2、修改配置文件，使得启动数据库也不对外提供服务：

# vim /etc/my.cnf   #在mysqld字段添加如下内容：
skip-networking

还Mysql使用的套接字文件的位置，避免其他人使用数据库

socket=/home/USERNAME/mysql.sock   #在修改配置文件中生成mysql.sock文件的位置的时候，客户端和服务器端的配置都需要改动为一致的mysql.sock文件路径及名称。这样其他的客户端在连接的时候由于配置的不一致而无法连接到数据库，而修复数据库的管理员是可以的。

3、启动数据库，并尝试恢复：

# service mysqld start

这个时候一定要记得如果在命令行下直接输入# mysql < /path/from/file.sql的方式恢复数据库，那么新生成的数据库中会多出来很多的二进制日志记录。所以这种恢复的方式是不太好的，或者说习惯是不好的，备份的时候使用命令行，这种习惯是好的，恢复的时候要连入数据库，先将环境变量设置为不记录二进制日志，然后再恢复，这样可以避免很多不必要的日志记录，免去磁盘空间的浪费。

连入数据库：

# mysql 
> SET session sql_log_bin=0;
> SOURCE /path/from/file.sql;
> SOURCE /path/from/incre.sql;

将所有的数据都恢复回来以后，开启二进制日志的记录功能。

> SET session sql_log_bin=1;
>\q

停止Mysql服务

# service mysqld stop

编辑配置文件，将服务的权限放开，删除掉skip-networking选项，修改套接字文件生成的目录位置至原来定义的位置。：

# vim /etc/my.cnf

以完全访问的模式重启服务器。

# service mysqld start

mysql备份恢复场景二

基于LVM快照的方式备份：

连入mysql数据库，对数据库的所有表施加共享锁：

# mysql
> FLUSH TABLES WITH READ LOCK;

在实际的生产环境中这条命令可能会请求很长时间长能完成对所有表施加锁，

施加锁成功后，需要记录一下此时使用的二进制日志文件及日志所在的位置信息，要将这个信息保存做好记录并备份。

> SHOW MASTER STATUS;

此时，可以另起一个终端，进行快照的创建，简单的示例如下：

# lvcreate -s -L SIZE -p r -n NAME /dev/VG_NAME/LV_NAME

创建好快照以后，在前一个终端执行一下日志滚动的功能：

> FLUSH LOGS  这样操作的目的是生成新的二进制日志记录文件，之后的二进制日志都会被存放进这个文件中，备份数据库的操作理解起来比较容易，只是习惯很多时候更为重要，也是避免数据有损失的很重要的保证，之前的备份方式中，mysqldump命令可以做全部备份，其带有的--master-data选项可以记录二进制日志文件的名称及位置，我个人觉得在使用mysqldump做备份时滚动日志并不利于日后做增量备份时查看之前备份的位置，为了避免操作上的重复查看日志内容，也避免在习惯上会遗漏，有时滚动，有时忘记滚动日志，就会对备份的数据造成影响。而使用基于快照的方式备份时，建议使用FLUSH LOGS，这样生成新的日志文件，便于快照备份的文件与数据库中文件进行比较查看，所以，在这种场景下最好是使用这个命令。如果数据库数据很小，我觉得还是做每次做完全备份来得简单，而且恢复数据也方便。使用一个完全备份的脚本基本可以解决问题。

滚动日志之后释放锁：

> UNLOCK TABLES;

之后进行快照内容的复制和归档备份的操作。

使用LVM方式备份的数据库恢复操作：

这个过程其实比mysqldump命令的恢复方式基本一致，只是将完全备份的数据库内容复制到数据库的数据目录下，然后如果有其他的增量部分的数据恢复就需要使用二进制日志文件做增量部分的数据恢复操作。不过具体的操作过程也是要按照前一种恢复的方式，恢复的时候启动服务也不要对外提供服务，而且要关闭二进制日志记录的功能（当有增量数据的情况下需要恢复时），避免产生不必要的二进制日志记录。

mysql备份工作中要备份很关键的是二进制日志文件，二进制日志文件与数据库的数据目录不能在一个磁盘上，这是最基本的要求，另外，还要备份配置文件，这些都是数据库配置工作中重要的基础性工作。