在数据库表丢失或损坏的情况下,备份你的数据库是很重要的。如果发生系统崩溃,你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。本文主要对MyISAM表做备份恢复。
备份策略一:直接拷贝数据库文件(不推荐)
备份策略二:使用mysqlhotcopy备份数据库(完全备份,适合小型数据库备份)
备份策略三:使用mysqldump备份数据库(完全+增量备份,适合中型数据库备份)
备份策略四:使用主从复制机制(replication)(实现数据库实时备份)
备份策略一、直接拷贝数据库文件
直接拷贝 数据文件最为直接、快速、方便, 但 缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在 备份 文件前,执行以下 SQL 语句:
FLUSH TABLES WITH READ LOCK;
也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。
备份策略二、 使用mysqlhotcopy备份数据库
mysqlhotcopy 是一个 PERL 程序,最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上 ,并且 mysqlhotcopy 只能用于备份 MyISAM 表。
本备份策略适合于小型数据库的备份,数据量不大,可以采用mysqlhotcopy程序每天进行一次完全备份。
备份策略布置:
(1)、安装DBD-mysql perl模块,支持mysqlhotcopy脚本连接到MySQL数据库。
shell> tar -xzvf DBD-mysql-4.005.tar.gz
shell> cd DBD-mysql-4.005
shell> unset LANG
shell> perl Makefile.PL -mysql_config=/usr/local/mysql/bin/mysql_config -testuser=root -testpassword=UserPWD
shell> make
shell> make test
shell> make install
(2)、设置crontab任务,每天执行备份脚本
shell> crontab -e
0 3 * * * /root/MySQLBackup/mysqlbackup.sh >/dev/null 2>&1
每天凌晨3:00执行备份脚本。
mysqlbackup.sh注释:
smbclientmv.sh注释:
(3)、恢复数据库到备份时的状态
mysqlhotcopy 备份出来的是整个数据库目录,使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:
shell> cp -rf db_name /usr/local/mysql/data/
shell> chown -R mysql:mysql /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态,要想在崩溃时丢失的数据尽量少应该更频繁的进行备份,要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。
备份策略三、使用mysqldump备份数据库
备份策略三、使用mysqldump备份数据库 mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。关于mysqldump的更详细解释见最后的附录。 对于中等级别业务量的系统来说,备份策略可以这么定:第一次完全备份,每天一次增量备份,每周再做一次完全备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份。 备份策略布置: (1)、创建备份目录 Shell> mkdir /tmp/mysqlbackup Shell> mkdir /tmp/mysqlbackup/daily (2)、启用二进制日志 采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份。 启用 binlog 时必须要重启 mysqld。首先,关闭 mysqld,打开 /etc/my.cnf,加入以下几行: [mysqld] log-bin 然后启动 mysqld 就可以了。运行过程中会产生 HOSTNAME-bin.000001 以及 HOSTNAME-bin.index,前面的文件是 mysqld 记录所有对数据的更新操作,后面的文件则是所有 binlog 的索引,都不能轻易删除。关于 binlog 的更详细信息请查看手册。 (3)、配置SSH密钥登录,用于将MySQL备份传送到备份服务器(如果备份服务器为Windows,请跳过此部)。 1)、在MySQL所在服务器(192.168.0.20)生成SSH密钥 [root@lab ~]# ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): //直接回车 Enter passphrase (empty for no passphrase): //直接回车,不使用密码 Enter same passphrase again: //直接回车,不使用密码 Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is: c2:96:9f:2d:5a:8e:08:42:43:35:2f:85:5e:72:f8:1c root@lab 2)、在备份服务器(192.168.0.200)上创建目录,修改权限,并传送公钥。 [root@lab ~]# ssh 192.168.0.200 "mkdir .ssh;chmod 0700 .ssh" The authenticity of host '192.168.0.200 (192.168.0.200)' can't be established. RSA key fingerprint is 37:57:55:c1:32:f1:dd:bb:1b:8a:13:6f:89:fb:b8:9d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.200' (RSA) to the list of known hosts. root@192.168.0.200's password: //输入备份服务器的root密码 [root@lab ~]# scp .ssh/id_rsa.pub 192.168.0.200:.ssh/authorized_keys2 root@192.168.0.200's password: id_rsa.pub 100% 218 0.2KB/s 00:00 3)、测试SSH登录 [root@lab ~]# ssh 192.168.0.200 //测试SSH登录 Last login: Fri Nov 16 10:34:02 2007 from 192.168.0.20 [root@lib ~]#
(4)、设置crontab任务,每天执行备份脚本 shell> crontab -e #每个星期日凌晨3:00执行完全备份脚本 0 3 * * 0 /root/MySQLBackup/mysqlFullBackup.sh >/dev/null 2>&1 #周一到周六凌晨3:00做增量备份 0 3 * * 1-6 /root/MySQLBackup/mysqlDailyBackup.sh >/dev/null 2>&1
mysqlFullBackup.sh注释:
(5) 、恢复数据库到备份时的状态 用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,直接用 mysql 客户端导入就可以了。 /usr/local/mysql/bin/mysql -uroot -pUserPWD db_name < db_name.sql 对于任何可适用的更新日志,将它们作为 mysql 的输入: % ls -t -r -1 HOSTNAME-bin* | xargs mysqlbinlog | mysql -uUser -pUserPWD ls 命令生成更新日志文件的一个单列列表,根据服务器产生它们的次序排序(注意:如果你修改任何一个文件,你将改变排序次序,这将导致更新日志以错误的次序被运用。) 本套备份策略只能恢复数据库到最后一次备份时的状态,要想在崩溃时丢失的数据尽量少应该更频繁的进行备份,要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。如果使用本套备份脚本,将日志文件和数据文件放到不同的磁盘上是一个不错的主义,这样不仅可以提高数据写入速度,还能使数据更安全。 |