MySQL 备份和恢复策略

作者:课课家教育更新于： 2019-04-04 19:59:56

　　备份策略一、直接拷贝数据库文件

　　直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在备份文件前，执行以下SQL语句：

　　FLUSHTABLESWITHREADLOCK;

　　也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。

　　备份策略二、使用mysqlhotcopy备份数据库

　　mysqlhotcopy是一个PERL程序，最初由TimBunce编写。它使用LOCKTABLES、FLUSHTABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并且mysqlhotcopy只能用于备份MyISAM表。

　　本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份。

　　备份策略布置：

　　（1）、安装DBD-mysqlperl模块，支持mysqlhotcopy脚本连接到MySQL数据库。

　　shell>tar-xzvfDBD-mysql-4.005.tar.gz

　　shell>cdDBD-mysql-4.005

　　shell>unsetLANG

　　shell>perlMakefile.PL-mysql_config=/usr/local/mysql/bin/mysql_config-testuser=root-testpassword=UserPWD

　　shell>make

　　shell>maketest

　　shell>makeinstall

　　（2）、设置crontab任务，每天执行备份脚本

　　shell>crontab-e

　　03***/root/MySQLBackup/mysqlbackup.sh>/dev/null2>&1

　　每天凌晨3:00执行备份脚本。

　　mysqlbackup.sh注释：

　　#!/bin/sh

　　#Name:mysqlbackup.sh

　　#PS:MySQLDataBaseBackup,Usemysqlhotcopyscript.

　　#Writeby:i.Stone

　　#LastModify:2007-11-15

　　#

　　#定义变量，请根据具体情况修改

　　#定义脚本所在目录

　　scriptsDir=`pwd`

　　#数据库的数据目录

　　dataDir=/usr/local/mysql/data/

　　#数据备份目录

　　tmpBackupDir=/tmp/tmpbackup/

　　backupDir=/tmp/mysqlbackup/

　　#用来备份数据库的用户名和密码

　　mysqlUser=root

　　mysqlPWD=111111

　　#定义eMail地址

　　eMail=alter@somode.com

　　#如果临时备份目录存在，清空它，如果不存在则创建它

　　if[[-e$tmpBackupDir]];then

　　rm-rf$tmpBackupDir/*

　　else

　　mkdir$tmpBackupDir

　　fi

　　#如果备份目录不存在则创建它

　　if[[!-e$backupDir]];then

　　mkdir$backupDir

　　fi

　　#清空MySQLBackup.log

　　if[[-sMySQLBackup.log]];then

　　cat/dev/null>MySQLBackup.log

　　fi

　　#得到数据库备份列表，在此可以过滤不想备份的数据库

　　fordatabasesin`find$dataDir-typed|\\

　　sed-e"s/\\/usr\\/local\\/mysql\\/data\\///"|\\

　　sed-e"s/test//"`;do

　　if[[$databases==""]];then

　　continue

　　else

　　#备份数据库

　　/usr/local/mysql/bin/mysqlhotcopy--user=$mysqlUser--password=$mysqlPWD-q"$databases"$tmpBackupDir

　　dateTime=`date"+%Y.%m.%d%H:%M:%S"`

　　echo"$dateTimeDatabase:$databasesbackupsuccess!">>MySQLBackup.log

　　fi

　　done

　　#压缩备份文件

　　date=`date-I`

　　cd$tmpBackupDir

　　tarczf$backupDir/mysql-$date.tar.gz./

　　#发送邮件通知

　　if[[-sMySQLBackup.log]];then

　　catMySQLBackup.log|mail-s"MySQLBackup"$eMail

　　fi

　　#使用smbclientmv.sh脚本上传数据库备份到备份服务器

　　#$scriptsDir/smbclientmv.sh

　　smbclientmv.sh注释

　　#!/bin/sh

　　#Name:smbclientmv.sh

　　#PS:MovethedatatoBackupServer.

　　#Writeby:i.Stone

　　#LastModify:2007-11-15

　　#

　　#定义变量

　　#备份服务器名

　　BackupServer="BackupServerName"

　　#共享文件夹名

　　BackupShare="ShareName"

　　#备份服务器的访问用户名和密码

　　BackupUser="SMBUser"

　　BackupPW="SMBPassword"

　　#定义备份目录

　　BackupDir=/tmp/mysqlbackup

　　date=`date-I`

　　#MovethedatatoBackupServer

　　smbclient//$BackupServer/$BackupShare\\

　　$BackupPW-d0-WWORKGROUP-U$BackupUser\\

　　-c"put$BackupDir/mysql-$date.tar.gz\\

　　mysql-$date.tar.gz"

　　#Deletetempfiles

　　rm-f$BackupDir/mysql-$date.tar.gz

　　(3)、恢复数据库到备份时的状态

　　mysqlhotcopy备份出来的是整个数据库目录，使用时可以直接拷贝到mysqld指定的datadir(在这里是/usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：

　　shell>cp-rfdb_name/usr/local/mysql/data/

　　shell>chown-Rmysql:mysql/usr/local/mysql/data/(将db_name目录的属主改成mysqld运行用户)

　　本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。

　　备份策略一、直接拷贝数据库文件

　　直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在备份文件前，执行以下SQL语句：

　　FLUSHTABLESWITHREADLOCK;

　　也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。

　　备份策略二、使用mysqlhotcopy备份数据库

　　mysqlhotcopy是一个PERL程序，最初由TimBunce编写。它使用LOCKTABLES、FLUSHTABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并且mysqlhotcopy只能用于备份MyISAM表。

　　本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份。

　　备份策略布置：

　　（1）、安装DBD-mysqlperl模块，支持mysqlhotcopy脚本连接到MySQL数据库。

　　shell>tar-xzvfDBD-mysql-4.005.tar.gz

　　shell>cdDBD-mysql-4.005

　　shell>unsetLANG

　　shell>perlMakefile.PL-mysql_config=/usr/local/mysql/bin/mysql_config-testuser=root-testpassword=UserPWD

　　shell>make

　　shell>maketest

　　shell>makeinstall

　　（2）、设置crontab任务，每天执行备份脚本

　　shell>crontab-e

　　03***/root/MySQLBackup/mysqlbackup.sh>/dev/null2>&1

　　每天凌晨3:00执行备份脚本。

　　mysqlbackup.sh注释：

　　#!/bin/sh

　　#Name:mysqlbackup.sh

　　#PS:MySQLDataBaseBackup,Usemysqlhotcopyscript.

　　#Writeby:i.Stone

　　#LastModify:2007-11-15

　　#

　　#定义变量，请根据具体情况修改

　　#定义脚本所在目录

　　scriptsDir=`pwd`

　　#数据库的数据目录

　　dataDir=/usr/local/mysql/data/

　　#数据备份目录

　　tmpBackupDir=/tmp/tmpbackup/

　　backupDir=/tmp/mysqlbackup/

　　#用来备份数据库的用户名和密码

　　mysqlUser=root

　　mysqlPWD=111111

　　#定义eMail地址

　　eMail=alter@somode.com

　　#如果临时备份目录存在，清空它，如果不存在则创建它

　　if[[-e$tmpBackupDir]];then

　　rm-rf$tmpBackupDir/*

　　else

　　mkdir$tmpBackupDir

　　fi

　　#如果备份目录不存在则创建它

　　if[[!-e$backupDir]];then

　　mkdir$backupDir

　　fi

　　#清空MySQLBackup.log

　　if[[-sMySQLBackup.log]];then

　　cat/dev/null>MySQLBackup.log

　　fi

　　#得到数据库备份列表，在此可以过滤不想备份的数据库

　　fordatabasesin`find$dataDir-typed|\\

　　sed-e"s/\\/usr\\/local\\/mysql\\/data\\///"|\\

　　sed-e"s/test//"`;do

　　if[[$databases==""]];then

　　continue

　　else

　　#备份数据库

　　/usr/local/mysql/bin/mysqlhotcopy--user=$mysqlUser--password=$mysqlPWD-q"$databases"$tmpBackupDir

　　dateTime=`date"+%Y.%m.%d%H:%M:%S"`

　　echo"$dateTimeDatabase:$databasesbackupsuccess!">>MySQLBackup.log

　　fi

　　done

　　#压缩备份文件

　　date=`date-I`

　　cd$tmpBackupDir

　　tarczf$backupDir/mysql-$date.tar.gz./

　　#发送邮件通知

　　if[[-sMySQLBackup.log]];then

　　catMySQLBackup.log|mail-s"MySQLBackup"$eMail

　　fi

　　#使用smbclientmv.sh脚本上传数据库备份到备份服务器

　　#$scriptsDir/smbclientmv.sh

　　smbclientmv.sh注释

　　#!/bin/sh

　　#Name:smbclientmv.sh

　　#PS:MovethedatatoBackupServer.

　　#Writeby:i.Stone

　　#LastModify:2007-11-15

　　#

　　#定义变量

　　#备份服务器名

　　BackupServer="BackupServerName"

　　#共享文件夹名

　　BackupShare="ShareName"

　　#备份服务器的访问用户名和密码

　　BackupUser="SMBUser"

　　BackupPW="SMBPassword"

　　#定义备份目录

　　BackupDir=/tmp/mysqlbackup

　　date=`date-I`

　　#MovethedatatoBackupServer

　　smbclient//$BackupServer/$BackupShare\\

　　$BackupPW-d0-WWORKGROUP-U$BackupUser\\

　　-c"put$BackupDir/mysql-$date.tar.gz\\

　　mysql-$date.tar.gz"

　　#Deletetempfiles

　　rm-f$BackupDir/mysql-$date.tar.gz

　　(3)、恢复数据库到备份时的状态

　　mysqlhotcopy备份出来的是整个数据库目录，使用时可以直接拷贝到mysqld指定的datadir(在这里是/usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：

　　shell>cp-rfdb_name/usr/local/mysql/data/

　　shell>chown-Rmysql:mysql/usr/local/mysql/data/(将db_name目录的属主改成mysqld运行用户)