夏日博客

A-A+

MySQL数据库备份与还原方法总结

2017年10月15日 PHP技术文章 暂无评论 阅读 0 views 次

MySQL数据库备份与还原方法不少因为有命令方式与工具方式还有就是用户自定义的程序备份,下面我来稍加整理一些常用的MySQL数据库备份与还原方法,希望例子能帮助到大家.

备份策略一：直接拷贝数据库文件（不推荐）

备份策略二：使用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数据库。

  1. shell> tar -xzvf  DBD-mysql-4.005.tar.gz   
  2. shell> cd DBD-mysql-4.005   
  3. shell> unset LANG   
  4. shell> perl Makefile.PL -mysql_config=/usr/local/mysql/bin/mysql_config -testuser=root -testpassword=UserPWD   
  5. shell> make   
  6. shell> make test   
  7. shell> make install   

2)、设置crontab任务,每天执行备份脚本

shell> crontab -e

0 3 * * * /root/MySQLBackup/mysqlbackup.sh >/dev/null 2>&1

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

mysqlbackup.sh注释:

  1. #!/bin/sh   
  2. # Name:mysqlbackup.sh   
  3. # PS:MySQL DataBase Backup,Use mysqlhotcopy script.   
  4. # Write by:i.Stone   
  5. # Last Modify:2007-11-15   
  6. #   
  7. # 定义变量，请根据具体情况修改   
  8. # 定义脚本所在目录   
  9. scriptsDir=`pwd`   
  10. # 数据库的数据目录   
  11. dataDir=/usr/local/mysql/data/   
  12. # 数据备份目录   
  13. tmpBackupDir=/tmp/tmpbackup/   
  14. backupDir=/tmp/mysqlbackup/   
  15. # 用来备份数据库的用户名和密码   
  16. mysqlUser=root   
  17. mysqlPWD=111111   
  18. # 定义eMail地址   
  19. eMail=alter@somode.com   
  20. # 如果临时备份目录存在，清空它，如果不存在则创建它   
  21. if [[ -e $tmpBackupDir ]]; then   
  22.   rm -rf $tmpBackupDir/*   
  23. else   
  24.   mkdir $tmpBackupDir   
  25. fi   
  26. # 如果备份目录不存在则创建它   
  27. if [[ ! -e $backupDir ]];then   
  28.   mkdir $backupDir   
  29. fi   
  30. # 清空MySQLBackup.log   
  31. if [[ -s MySQLBackup.log ]]; then   
  32.   cat /dev/null >MySQLBackup.log   
  33. fi   
  34. # 得到数据库备份列表，在此可以过滤不想备份的数据库   
  35. for databases in `find $dataDir -type d | ＼   
  36.   sed -e "s/＼/usr＼/local＼/mysql＼/data＼///" | ＼   
  37.   sed -e "s/test//"`; do   
  38.   if [[ $databases == "" ]]; then   
  39.     continue   
  40.   else   
  41. # 备份数据库   
  42.     /usr/local/mysql/bin/mysqlhotcopy --user=$mysqlUser --password=$mysqlPWD -q "$databases" $tmpBackupDir   
  43.     dateTime=`date "+%Y.%m.%d %H:%M:%S"`   
  44.     echo "$dateTime Database:$databases backup success!" >>MySQLBackup.log   
  45.   fi   
  46. done   
  47. # 压缩备份文件   
  48. date=`date -I`   
  49. cd $tmpBackupDir   
  50. tar czf $backupDir/mysql-$date.tar.gz ./   
  51. # 发送邮件通知   
  52. if [[ -s MySQLBackup.log ]]; then   
  53.   cat MySQLBackup.log | mail -s "MySQL Backup" $eMail   
  54. fi   
  55. # 使用smbclientmv.sh脚本上传数据库备份到备份服务器   
  56. # $scriptsDir/smbclientmv.sh   
  57. smbclientmv.sh注释  
  58.   
  59. #!/bin/sh   
  60. # Name:smbclientmv.sh   
  61. # PS:Move the data to Backup Server.   
  62. # Write by:i.Stone   
  63. # Last Modify:2007-11-15   
  64. #   
  65. # 定义变量   
  66. # 备份服务器名   
  67. BackupServer="BackupServerName"   
  68. # 共享文件夹名   
  69. BackupShare="ShareName"   
  70. # 备份服务器的访问用户名和密码   
  71. BackupUser="SMBUser"   
  72. BackupPW="SMBPassword"   
  73. # 定义备份目录   
  74. BackupDir=/tmp/mysqlbackup   
  75. date=`date -I`   
  76. # Move the data to BackupServer   
  77. smbclient //$BackupServer/$BackupShare ＼   
  78. $BackupPW -d0 -W WORKGROUP -U $BackupUser ＼   
  79. -c "put $BackupDir/mysql-$date.tar.gz ＼  
  80. mysql-$date.tar.gz"//开源代码phpfensi.com   
  81. # Delete temp files   
  82. rm -f $BackupDir/mysql-$date.tar.gz   

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)。

一、Win32系统下MySQL的备份还原方法

备份：在“运行”中输入“cmd ”，利用“cd /Program Files/MySQL/MySQL Server 5.0/bin”进入bin文件夹，输入“mysqldump -u 用户名 -p databasename >exportfilename”导出数据库到文件，如mysqldump -u root -p voice>voice.sql，然后输入密码即可开始导出MYSQL数据，实现备份操作。

还原：进入MySQL Command Line Client，输入密码，进入到“mysql>”，输入命令"show databases；"，回车，看看有些什么数据库；建立你要还原的数据库，输入"create database voice；"，回车；切换到刚建立的数据库，输入"use voice；"，回车；导入数据，输入"source voice.sql；"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。

二、Linux下MySQL的备份与还原方法：

备份：[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)

[root@localhost mysql]# mysqldump -u root -p voice>voice.sql，输入密码即可。

还原：有两种方法可选：

第一种方法：[root@localhost ~]# mysql -u root -p 回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。

第二种方法：

[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)

[root@localhost mysql]# mysql -u root -p voice<voice.sql，输入密码即可。

标签：

给我留言