Mysql 数据库常用备份方法和注意事项
程序员文章站
2023-12-15 11:57:58
mysql数据库备份的常用3种方法： 1、直接拷贝(cp、tar,gzip,cpio) 2、mysqldump 3、mysqlhotcopy 1.使用直接拷贝数据库备份 典...
mysql数据库备份的常用3种方法：
1、直接拷贝(cp、tar,gzip,cpio)
2、mysqldump
3、mysqlhotcopy
1.使用直接拷贝数据库备份
典型的如cp、tar或cpio实用程序。
当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。
保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。如果你不想关闭服务器，要在执行表检查的同时锁定服务器。如果服务器在运行，相同的制约也适用于拷贝文件，而且你应该使用相同的锁定协议让服务器“安静下来”。
当你完成了备份时，需要重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。
要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是myiasm格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。
2.mysqldump
基本语法：
shell> mysqldump [options] database [tables] > data_backup.sql （不指定数据库名表示全部备份）
例如：
mysqldump -uroot -p --default-character-set=cp932 --opt --extended-insert=false --hex-blob -r -x mysql > e:\mysql.sql
输出文件的开头看起来象这样：
# mysql dump 6.0
#
# host: localhost database: samp_db
#---------------------------------------
# server version 3.23.2-alpha-log
#
# table structure for table 'absence'
#
create table absence(
student_id int(10) unsigned default '0' not null,
date date default '0000-00-00' not null,
primary key (student_id,date)
);
#
# dumping data for table 'absence'
#
insert into absence values (3,'1999-09-03');
insert into absence values (5,'1999-09-03');
insert into absence values (10,'1999-09-08');
......
文件剩下的部分有更多的insert和create table语句组成。例：
%mysqldump samp_db >/opt/mysqldatabak/samp_db.2006-5-15
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz #产生压缩备份
%mysqldump samp_db student score event absence >grapbook.sql #备份数据库的某些表
%mysqladmin -h boa.snake.net create samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db #直接恢复到另一个服务器上使用--add-drop-table选项告诉服务器将drop table if exists语句写入备份文件，这样当我们以后用来恢复数据库时，如果表已经存在，你不会得到一个错误。
%mysqldump --add-drop-table samp_db | mysql -h boa.snake.net samp_db
mysqldump其它有用的选项包括：
--flush-logs和--lock-tables组合将对你的数据库检查点有帮助。--lock-tables锁定你正在倾倒的所有表，而--flush-logs关闭并重新打开更新日志文件，新的更新日志将只包括从备份点起的修改数据库的查询。这将设置你的更新日志检查点位备份时间。（然而如果你有需要执行个更新的客户，锁定所有表对备份期间的客户访问不是件好事。）
如果你使用--flush-logs设置检查点到备份时，有可能最好是倾倒整个数据库。如果你倾倒单独的文件，较难将更新日志检查点与备份文件同步。在恢复期间，你通常按数据库为基础提取更新日志内容，对单个表没有提取更新的选择，所以你必须自己提取它们。
缺省地，mysqldump在写入前将一个表的整个内容读进内存。这通常确实不必要，并且实际上如果你有一个大表，几乎是失败的。你可用--quick选项告诉mysqldump只要它检索出一行就写出每一行。为了进一步优化倾倒过程，使用--opt而不是--quick。--opt选项打开其它选项，加速数据的倾倒和把它们读回。
用--opt实施备份可能是最常用的方法，因为备份速度上的优势。然而，要警告你，--opt选项确实有代价，--opt优化的是你的备份过程，不是其他客户对数据库的访问。--opt选项通过一次锁定所有表阻止任何人更新你正在倾倒的任何表。你可在一般数据库访问上很容易看到其效果。
一个具有--opt的相反效果的选项是--dedayed。该选项使得mysqldump写出insert delayed语句而不是insert语句。如果你将数据文件装入另一个数据库并且你想使这个操作对可能出现在该数据库中的查询的影响最小，--delayed对此很有帮助。
--compress选项在你拷贝数据库到另一台机器上时很有帮助，因为它减少网络传输字节的数量。下面有一个例子，注意到--compress对与远端主机上的服务器通信的程序才给出，而不是对与本地主机连接的程序：
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump有很多其它选项，主要参数：
--compatible=name
它告诉 mysqldump，导出的数据将和哪种数据库或哪个旧版本的 mysql 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。
--complete-insert，-c
导出的数据采用包含字段名的完整 insert 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。
--extended-insert = true|false
默认情况下，mysqldump 开启 --complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。
--default-character-set=charset
指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
--disable-keys
告诉 mysqldump 在 insert 语句的开头和结尾增加 /*!40000 alter table table disable keys */; 和 /*!40000 alter table table enable keys */; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 myisam 表。
--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 binary、varbinary、blob。
--lock-all-tables，-x
在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。
--lock-tables
它和 --lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 myisam 表，如果是 innodb 表可以用 --single-transaction 选项。
--no-create-info，-t
只导出数据，而不添加 create table 语句。
--no-data，-d
不导出任何数据，只导出数据库表结构。
--opt
这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 mysqldump 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。
--quick，-q
该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
--routines，-r
导出存储过程以及自定义函数。
--single-transaction
该选项在导出数据之前提交一个 begin sql语句，begin 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 innodb 和 bdb。
本选项和 --lock-tables 选项是互斥的，因为 lock tables 会使任何挂起的事务隐含提交。
要想导出大表的话，应结合使用 --quick 选项。
--triggers
同时导出触发器。该选项默认启用，用 --skip-triggers 禁用它。
3.mysqlhotcopy备份
mysqlhotcopy是一个perl脚本，最初由tim bunce编写并提供。它使用lock tables、flush tables和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上。mysqlhotcopy只用于备份myisam。它运行在unix和netware中
使用方法见下面的脚本.加入crotab中吧.
#!/bin/sh
# name:mysqlbackup.sh
# ps:mysql database backup,use mysqlhotcopy script.
# last modify:2008-06-12
# 定义变量，请根据具体情况修改
# 定义脚本所在目录
scriptsdir=`pwd`
# 数据库的数据目录
datadir=/var/lib/mysql
# 数据备份目录
tmpbackupdir=/tmp/mysqlblackup
backupdir=/backup/mysql
# 用来备份数据库的用户名和密码
mysqluser=root
mysqlpwd='you password'
# 如果临时备份目录存在，清空它，如果不存在则创建它
if [[ -e $tmpbackupdir ]]; then
rm -rf $tmpbackupdir/*
else
mkdir $tmpbackupdir
fi
# 如果备份目录不存在则创建它
if [[ ! -e $backupdir ]];then
mkdir $backupdir
fi
# 得到数据库备份列表，在此可以过滤不想备份的数据库
for databases in `find $datadir -type d | \
sed -e "s/\/var\/lib\/mysql\///" | \
sed -e "s/test//"`; do
if [[ $databases == "" ]]; then
continue
else
# 备份数据库
/usr/bin/mysqlhotcopy --user=$mysqluser --password=$mysqlpwd -q "$databases" $tmpbackupdir
datetime=`date "+%y.%m.%d %h:%m:%s"`
echo "$datetime database:$databases backup success!" >>mysqlbackup.log
fi
done
# 压缩备份文件
date=`date -i`
cd $tmpbackupdir
tar czf $backupdir/mysql-$date.tar.gz ./
#end完成
加入到crontab中设置每周5运行
0 0 * * 5 /backup/blackup.sh
注意:恢复数据库到备份时的状态
mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /var/lib/mysql/)目录下即可，同时要注意权限的问题，如下例：
shell> cp -rf db_name /var/lib/mysql/
shell> chown -r mysql:mysql /var/lib/mysql/ (将 db_name 目录的属主改成 mysqld 运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。
小技巧:
不想写密码在shell中的话,可以在root的home目录下建立一个.my.cnf文件，以便让mysqlhotcopy从中读取用户名/密码。
[mysqlhotcopy]
user=root
password=yourpassword
然后安全起见，chmod一下。
chmod 600 ~/.my.cnf
附:mysqlhotcopy常用参数：
· --allowold 如果目标存在不放弃(加上一个_old后缀重新命名它)。
· --checkpoint=db_name.tbl_name 在指定的db_name.tbl_name插入检查点条目。
· ---debug 启用调试输出。
· --dryrun，-n 报告动作而不执行它们。
· --flushlog 所有表锁定后刷新日志。
· --keepold 完成后不删除以前(重新命名的)的目标。
· -- method=command 复制方法(cp或scp)。
· --noindices 备份中不包括全部索引文件。这样使备份更小、更快。可以在以后用myisamchk -rq重新构建索引。
· --password=password，-p password 当连接服务器时使用的密码。请注意该选项的密码值是不可选的，不象其它mysql程序。
· --port=port_num，-p port_num 当连接本地服务器时使用的tcp/ip端口号。
· --quiet，-q 除了出现错误时保持沉默。
· --regexp=expr 复制所有数据库名匹配给出的正则表达式的数据库。
· --socket=path，-s path 用于连接的unix套接字文件。
· --suffix=str 所复制的数据库名的后缀。
· --tmpdir=path 临时目录(代替/tmp)。
· --user=user_name，-u user_name 当连接服务器时使用的mysql用户名。
mysqlhotcopy从选项文件读取[client]和[mysqlhotcopy]选项组。要想执行mysqlhotcopy，你必须可以访问备份的表文件，具有那些表的select权限和reload权限(以便能够执行flush tables)。
1、直接拷贝(cp、tar,gzip,cpio)
2、mysqldump
3、mysqlhotcopy
1.使用直接拷贝数据库备份
典型的如cp、tar或cpio实用程序。
当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。
保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。如果你不想关闭服务器，要在执行表检查的同时锁定服务器。如果服务器在运行，相同的制约也适用于拷贝文件，而且你应该使用相同的锁定协议让服务器“安静下来”。
当你完成了备份时，需要重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。
要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是myiasm格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。
2.mysqldump
基本语法：
shell> mysqldump [options] database [tables] > data_backup.sql （不指定数据库名表示全部备份）
例如：
mysqldump -uroot -p --default-character-set=cp932 --opt --extended-insert=false --hex-blob -r -x mysql > e:\mysql.sql
输出文件的开头看起来象这样：
# mysql dump 6.0
#
# host: localhost database: samp_db
#---------------------------------------
# server version 3.23.2-alpha-log
#
# table structure for table 'absence'
#
create table absence(
student_id int(10) unsigned default '0' not null,
date date default '0000-00-00' not null,
primary key (student_id,date)
);
#
# dumping data for table 'absence'
#
insert into absence values (3,'1999-09-03');
insert into absence values (5,'1999-09-03');
insert into absence values (10,'1999-09-08');
......
文件剩下的部分有更多的insert和create table语句组成。例：
%mysqldump samp_db >/opt/mysqldatabak/samp_db.2006-5-15
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz #产生压缩备份
%mysqldump samp_db student score event absence >grapbook.sql #备份数据库的某些表
%mysqladmin -h boa.snake.net create samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db #直接恢复到另一个服务器上使用--add-drop-table选项告诉服务器将drop table if exists语句写入备份文件，这样当我们以后用来恢复数据库时，如果表已经存在，你不会得到一个错误。
%mysqldump --add-drop-table samp_db | mysql -h boa.snake.net samp_db
mysqldump其它有用的选项包括：
--flush-logs和--lock-tables组合将对你的数据库检查点有帮助。--lock-tables锁定你正在倾倒的所有表，而--flush-logs关闭并重新打开更新日志文件，新的更新日志将只包括从备份点起的修改数据库的查询。这将设置你的更新日志检查点位备份时间。（然而如果你有需要执行个更新的客户，锁定所有表对备份期间的客户访问不是件好事。）
如果你使用--flush-logs设置检查点到备份时，有可能最好是倾倒整个数据库。如果你倾倒单独的文件，较难将更新日志检查点与备份文件同步。在恢复期间，你通常按数据库为基础提取更新日志内容，对单个表没有提取更新的选择，所以你必须自己提取它们。
缺省地，mysqldump在写入前将一个表的整个内容读进内存。这通常确实不必要，并且实际上如果你有一个大表，几乎是失败的。你可用--quick选项告诉mysqldump只要它检索出一行就写出每一行。为了进一步优化倾倒过程，使用--opt而不是--quick。--opt选项打开其它选项，加速数据的倾倒和把它们读回。
用--opt实施备份可能是最常用的方法，因为备份速度上的优势。然而，要警告你，--opt选项确实有代价，--opt优化的是你的备份过程，不是其他客户对数据库的访问。--opt选项通过一次锁定所有表阻止任何人更新你正在倾倒的任何表。你可在一般数据库访问上很容易看到其效果。
一个具有--opt的相反效果的选项是--dedayed。该选项使得mysqldump写出insert delayed语句而不是insert语句。如果你将数据文件装入另一个数据库并且你想使这个操作对可能出现在该数据库中的查询的影响最小，--delayed对此很有帮助。
--compress选项在你拷贝数据库到另一台机器上时很有帮助，因为它减少网络传输字节的数量。下面有一个例子，注意到--compress对与远端主机上的服务器通信的程序才给出，而不是对与本地主机连接的程序：
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump有很多其它选项，主要参数：
--compatible=name
它告诉 mysqldump，导出的数据将和哪种数据库或哪个旧版本的 mysql 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。
--complete-insert，-c
导出的数据采用包含字段名的完整 insert 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。
--extended-insert = true|false
默认情况下，mysqldump 开启 --complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。
--default-character-set=charset
指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
--disable-keys
告诉 mysqldump 在 insert 语句的开头和结尾增加 /*!40000 alter table table disable keys */; 和 /*!40000 alter table table enable keys */; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 myisam 表。
--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 binary、varbinary、blob。
--lock-all-tables，-x
在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。
--lock-tables
它和 --lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 myisam 表，如果是 innodb 表可以用 --single-transaction 选项。
--no-create-info，-t
只导出数据，而不添加 create table 语句。
--no-data，-d
不导出任何数据，只导出数据库表结构。
--opt
这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 mysqldump 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。
--quick，-q
该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
--routines，-r
导出存储过程以及自定义函数。
--single-transaction
该选项在导出数据之前提交一个 begin sql语句，begin 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 innodb 和 bdb。
本选项和 --lock-tables 选项是互斥的，因为 lock tables 会使任何挂起的事务隐含提交。
要想导出大表的话，应结合使用 --quick 选项。
--triggers
同时导出触发器。该选项默认启用，用 --skip-triggers 禁用它。
3.mysqlhotcopy备份
mysqlhotcopy是一个perl脚本，最初由tim bunce编写并提供。它使用lock tables、flush tables和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上。mysqlhotcopy只用于备份myisam。它运行在unix和netware中
使用方法见下面的脚本.加入crotab中吧.
#!/bin/sh
# name:mysqlbackup.sh
# ps:mysql database backup,use mysqlhotcopy script.
# last modify:2008-06-12
# 定义变量，请根据具体情况修改
# 定义脚本所在目录
scriptsdir=`pwd`
# 数据库的数据目录
datadir=/var/lib/mysql
# 数据备份目录
tmpbackupdir=/tmp/mysqlblackup
backupdir=/backup/mysql
# 用来备份数据库的用户名和密码
mysqluser=root
mysqlpwd='you password'
# 如果临时备份目录存在，清空它，如果不存在则创建它
if [[ -e $tmpbackupdir ]]; then
rm -rf $tmpbackupdir/*
else
mkdir $tmpbackupdir
fi
# 如果备份目录不存在则创建它
if [[ ! -e $backupdir ]];then
mkdir $backupdir
fi
# 得到数据库备份列表，在此可以过滤不想备份的数据库
for databases in `find $datadir -type d | \
sed -e "s/\/var\/lib\/mysql\///" | \
sed -e "s/test//"`; do
if [[ $databases == "" ]]; then
continue
else
# 备份数据库
/usr/bin/mysqlhotcopy --user=$mysqluser --password=$mysqlpwd -q "$databases" $tmpbackupdir
datetime=`date "+%y.%m.%d %h:%m:%s"`
echo "$datetime database:$databases backup success!" >>mysqlbackup.log
fi
done
# 压缩备份文件
date=`date -i`
cd $tmpbackupdir
tar czf $backupdir/mysql-$date.tar.gz ./
#end完成
加入到crontab中设置每周5运行
0 0 * * 5 /backup/blackup.sh
注意:恢复数据库到备份时的状态
mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /var/lib/mysql/)目录下即可，同时要注意权限的问题，如下例：
shell> cp -rf db_name /var/lib/mysql/
shell> chown -r mysql:mysql /var/lib/mysql/ (将 db_name 目录的属主改成 mysqld 运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。
小技巧:
不想写密码在shell中的话,可以在root的home目录下建立一个.my.cnf文件，以便让mysqlhotcopy从中读取用户名/密码。
[mysqlhotcopy]
user=root
password=yourpassword
然后安全起见，chmod一下。
chmod 600 ~/.my.cnf
附:mysqlhotcopy常用参数：
· --allowold 如果目标存在不放弃(加上一个_old后缀重新命名它)。
· --checkpoint=db_name.tbl_name 在指定的db_name.tbl_name插入检查点条目。
· ---debug 启用调试输出。
· --dryrun，-n 报告动作而不执行它们。
· --flushlog 所有表锁定后刷新日志。
· --keepold 完成后不删除以前(重新命名的)的目标。
· -- method=command 复制方法(cp或scp)。
· --noindices 备份中不包括全部索引文件。这样使备份更小、更快。可以在以后用myisamchk -rq重新构建索引。
· --password=password，-p password 当连接服务器时使用的密码。请注意该选项的密码值是不可选的，不象其它mysql程序。
· --port=port_num，-p port_num 当连接本地服务器时使用的tcp/ip端口号。
· --quiet，-q 除了出现错误时保持沉默。
· --regexp=expr 复制所有数据库名匹配给出的正则表达式的数据库。
· --socket=path，-s path 用于连接的unix套接字文件。
· --suffix=str 所复制的数据库名的后缀。
· --tmpdir=path 临时目录(代替/tmp)。
· --user=user_name，-u user_name 当连接服务器时使用的mysql用户名。
mysqlhotcopy从选项文件读取[client]和[mysqlhotcopy]选项组。要想执行mysqlhotcopy，你必须可以访问备份的表文件，具有那些表的select权限和reload权限(以便能够执行flush tables)。