数据库备份笔记

applez0718

13.1               备份前的准备

彻底关停mysql

或者锁定mysql

l         内部锁定（可靠）（只检查或copy表，只读锁定方式足够；如果要修复表，必须采用读/写锁定方式）

l         外部锁定（不可靠，不建议使用）

13.1.1    内部锁定机制介绍

l         基本步骤：

a)         用mysql客户程序连接到MySQL服务器（db管理员账号），并发出LOCK TABLE锁定；

b)        将mysql客户程序闲置在那里，不可退出（否则解除锁），进行其他操作，

c)        操作完成，回到mysql客户程序解除锁定

 

1.         以只读方式锁定

l         以只读方式锁定某个数据表(锁定后，其他客户程序只能read，不可修改、添加、删除)

a)         在A窗口锁定：

%mysql database_name -p -uroot(必须以db的超级管理员身份进行锁定)

Mysql> LOCK TABLES table_name READ, table_name2 READ, table_name3 READ；

Mysql>FLUSH TABLES table_name, table_name2, table_name3；

b)        在B窗口执行其他操作，可以发现有读权限，但是修改、添加、删除无法执行

c)        在A窗口解锁：

Mysql>unlock tables;

但是在B窗口执行的修改、添加、删除在解锁后，自动执行，不管B窗口的客户端程序是否退出。

 

2.         以读/写方式锁定某个数据表（锁定后，其他客户程序不能进行任何操作，？？不生效）

a)         在A窗口锁定：

%mysql database_name -p -uroot(必须以db的超级管理员身份进行锁定)

Mysql> LOCK TABLES table_name write, table_name2 write, table_name3 write；

Mysql>FLUSH TABLES table_name, table_name2, table_name3；

b)        在B窗口执行其他操作，可以发现有读权限，但是修改、添加、删除无法执行

c)        在A窗口解锁：

Mysql>FLUSH TABLES table_name, table_name2, table_name3；

Mysql>unlock tables;

但是在B窗口执行的修改、添加、删除在解锁后，自动执行，不管B窗口的客户端程序是否退出。

 

3.         以只读方式锁定所有数据表

a)         加锁：Mysql>FLUSH TABLES with read lock;

b)        解锁：Mysql>unlock tables; (解锁后，在其它客户端执行的操作，都会自动执行)

 

13.1.2    外部锁定机制介绍（不建议使用，从MySQL4之后，默认禁用）

 

13.2               灾难发生前做好准备P593

13.2.1    充分利用MySQL的自动恢复能力（先采用与往常相同的方式重启MySQL，如果还不能自动修复，可采用如下）

l         InnoDB

1)        前提条件：数据表处理程序被激活

2)        在my.cnf配置文件的“[mysqld]”部分，添加一行内容

Set_variable = innodb_force_recovery=4

l         BDB

l         MyISAM

1)        在my.cnf配置文件的“[mysqld]”部分，添加一行内容

myisam-recover=BACKUP,FORCE （共有四个值可选，DEFAULT、BACKUP、FORCE、QUICK），启动后删除此选项

2)        或者在mysql的启动命令中，加上“-- myisam-recover=BACKUP,FORCE”参数

3)        如果还不能恢复启动，查看错误日志，如果要强行启动并手动恢复，参考13.3.2

 

13.2.2    备份和拷贝db

两种方式：

à       使用mysqldump

优点：生成文本文件（creat表、insert数据的sql语句），容易移植，mysql支持的所有表都可以用此方式备份移植

缺点：速度慢

à       使用mysqlhotcopy、cp、tar、cpio

 

备份原则：

 

1.         mysqldump的方式（适用所有类型的表）

本地转储

mysqldump -p -uroot --opt --flush-logs --lock-tables db_name[|all-databases] > /path/db_name.date

mysqldump -p -uroot --opt --flush-logs --lock-tables db_name | gzip /path/db_name.date.gz

mysqldump -p -uroot --opt --flush-logs --lock-tables db_name table1 table2 table3 … > /dir/name.sql

(只备份部分表，缺点：日志和备份文件不同步，恢复起来麻烦，需要自己提取)

 

远程转储，

%mysqladmin -h 远程主机 creat db_name

%mysqldump -p -uroot --opt --flush-logs --lock-tables db_name | mysql -h远程主机 --compress db_name

或无访问远程主机的mysql的账号，但有远程主机的ssh账号

%ssh远程主机 mysqladmin create db_name

%mysqldump -p -uroot --opt --flush-logs --lock-tables db_name | ssh远程主机 mysql db_name

 

参数说明：

--opt：可在每条creat table命令之前，加上drop table if exists;

加快备份速度;

自动锁定正在转储的数据表（非所有），防止表在转储中被修改;

--flush-logs：关闭并重新打开变更日志或二进制变更日志，适用于备份整个db，如果只备份部分表不适用

--lock-tables：锁定所有表,推荐使用（经测试不要采用登录到db中，以只读方式锁定所有表，然后再用mysqldump进行备份的方式，会导致备份卡住，只使用此参数即可）

--compress：用于网络传输的数据量压缩，用在mysql命令中

--no-creat-info和--no-data：不转储表结构（creat table语句）和不转储数据（insert语句）

 

2.         以直接copy法来备份和copy数据库 (适用于MYISAM、ISAM类型的表)

前提条件：关闭MySQL或以只读方式锁定所有表13.1

本地copy：Copy 整个DB目录或几个表的所有相关文件 到备份目录即可

远程copy：scp同上

远程copy注意问题：

à       两台机器必须有同样的硬件配置；

à       在两台机器上操作时，都要防止copy时被修改，建议停止mysql服务

à       适用于MYISAM、ISAM类型，InnoDB或BDB参见后面小节

 

3.         mysqlhotcopy工具制作备份（Perl DBI脚本）(适用于MYISAM、ISAM类型的表)

优点：

à       可在mysql服务器运行时进行备份

à       速度比mysqldump快（直接copy方式，所以必须在本机执行，不能远程执行）

à       可自动进行锁定，使用的是内部锁定机制

à       能刷新日志

%mysqlhotcopy --flushlog db_name  /dir  (在/dir目录下创建与db同名的目录，或不加目录，自动在data中创建dbname_copy子目录存放备份)

-n 参数：可以显示命令但不实际执行

备注：如果执行此命令，提示“DBI connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','',...) failed: Access denied for user 'sampadm'@'localhost' (using password: YES) at /usr/bin/mysqlhotcopy line 178”类似的错误。解决如下：

à       如果执行mysqlhotcopy命令的用户主目录下的.my.cnf不存在，在命令行中，必须显式的加上-p 密码 -u root(DB管理员权限，密码也必须要写在命令行中)参数

à       如果文件存在，那么命令行中不要加-p -u参数，如果加了参数，但是参数后的值未设，那么将从.my.conf文件中读入，可能文件中设置的用户与执行此命令行的用户不同，导致错误

执行过程实例：

Using copy suffix '_copy'

Locked 6 tables in 0 seconds.

Flushed tables (`sampdb`.`absence`, `sampdb`.`grade_event`, `sampdb`.`member`, `sampdb`.`president`, `sampdb`.`score`, `sampdb`.`student`) in 0 seconds.

Copying 11 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 6 tables (11 files) in 0 seconds (0 seconds overall).

 

4.         用backup table语句制作备份P600 (仅适用于MYISAM类型的表，用于日常检查发现某个表有问题时使用)

à       Mysql>lock tables table1 read, table2 read,table3 read;

à       Mysql>backup table table1, table2, table3 to ‘/pathdir’;

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

| Table            | Op     | Msg_type | Msg_text |

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

| sampdb.member    | backup | status   | OK       |

| sampdb.president | backup | status   | OK       |

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

备份成功，在指定目录下会生成 表名.frm和表名. MYD文件

如果提示错误信息如“The storage engine for the table doesn't support backup”，是由于有的表本身不存在.MYD .MYI导致的

à       Mysql>unlock tables;

à       备注：backup table语句会把内存中的信息写入要备份的表中；把.frm和.MYD copy到指定目录，.MYI不copy，因为可以重建；

5.         对InnoDB或BDB表进行备份

6.         使用镜像机制帮助制作备份P601

à       对从服务器进行备份；

à       Mysql>slave stop; 挂起从服务器上的镜像机制

à       制作备份（如果用直接copy法，需发出flush tables;）

à       Mysql>slave start; 重启启用镜像

7.         使用备份对DB进行更名

 

13.3              数据表修复和数据恢复

13.3.1     检查和修复数据表(推荐使用前两种方式)

à       Mysql>check table table1,table2;

à       %mysqlcheck -p -uroot sampdb （检查sampdb的所有表）

Usage: mysqlcheck [OPTIONS] database [tables]

OR     mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]

OR     mysqlcheck [OPTIONS] --all-databases

à       %myisamchk(关闭mysql server或内部锁定后再使用)

 

1.         用myisamchk来检查和修复表

1)        进入要检查的db目录；

2)        %myisamchk [--check默认参数] [--medium-check中等] [--extend-check最彻底] 表名或 *.MYI

修复P604

标准修复步骤：P604

进入要检查的db目录；

%myisamchk --recover --quick 表名（不涉及数据文件.MYD），如果不行，执行下面

%myisamchk --recover 表名（涉及数据文件.MYD），如果不行，执行下面

%myisamchk --safe-recover 表名（涉及数据文件.MYD），如果不行，可能.MYI文件损坏或丢失，或提示文件丢失，执行下面

MYI文件修复：

五步

frm文件修复：P605

两种方法

使myisamchk运行更快，通过加上参数来指定某些操作使用的内存大小来实现

2.         用MySQL服务器检查和修复表P606

Mysql>check table

Mysql>repair table

%mysqlcheck -p -uroot sampdb

3.         定期进行预防性维护P607

13.3.2    使用备份恢复数据

1.         恢复整个DB

mysqldump备份恢复的步骤

à       把需要恢复的db的表全部锁定，进行备份（留做以后检查分析），解锁

à       进行恢复前，关闭所有对此db的网络连接

在/etc/my.cnf中添加：

skip-networking(如果用户访问的程序与mysqld在同一台机器上，且程序通过-h localhost方式来访问MySQL，那么数据还是会被更改，所以不保险)

skip-grand-tables（如果恢复的db是包含权限表的，那么此行需要）

重启MySQL服务

à       mysql -p -uroot sampdb < /tmp/sampdb.2011-2-12

 

mysqlbinlog /var/lib/mysql/localhost-bin.000003 | mysql -p -uroot --one-database sampdb（日志恢复）

检查恢复情况

à       恢复/etc/my.cnf的原来配置，重启MySQL服务

 

直接copy法备份恢复的步骤

à       把需要恢复的db的表全部锁定，进行备份（留做以后检查分析），解锁

à       service mysqld stop

à       cp -pf /tmp/sampdb/*  /var/lib/mysql/sampdb/  （也可把源db目录删除再把备份的copy回来）

à       service mysqld start

à       mysqlbinlog /var/lib/mysql/localhost-bin.000005 | mysql -p -uroot --one-database sampdb

2.          

13.4               

13.5               

13.6               

13.7              二进制变更日志的启用

打开 /etc/my.cnf文件，在[mysqld]部分添加如下内容：

log-bin=localhost-bin

log-bin-index=localhost-bin.index

重启MySQL服务

 

日志清理（从1开始计数，确认之前日志永远不再使用的情况下）

删除所有rm -rf localhost-bin.000* ; echo “”>localhost-bin.index

重启MySQL服务