MySQL数据库入门之备份数据库操作详解
本文实例讲述了MySQL数据库入门之备份数据库操作。分享给大家供大家参考，具体如下：
接上一次：MySQL数据库入门多实例配置
一提到数据，大家神经都会很紧张，数据的类型有很多种，但是总归一点，数据很重要，非常重要，因此，日常的数据备份工作就成了运维工作的重点中的重点的重点.................
首先来看看数据库中的数据
mysql>select*fromtest; +-----+------+ |id|name| +-----+------+ |1|1| |11|text| |21|abc| |9|bcd| |111|1| |441|text| |41|abc| |999|bcd| +-----+------+ 8rowsinset(0.00sec)
1、单库备份
[root@centos6~]#mysqldump-uroot-ptest>/download/testbak_$(date+%F).sql Enterpassword: [root@centos6~]#ll/download/ total2 -rw-r--r--.1rootroot1888Dec1220:34testbak_2016-12-12.sql
下面我们看看这个备份文件到底是什么内容
[root@centos6~]#egrep-v"^--|\*|^$" /download/testbak_2016-12-12.sql DROPTABLEIFEXISTS`test`; CREATETABLE`test`( `id`int(4)NOTNULL, `name`char(20)NOTNULL )ENGINE=MyISAMDEFAULTCHARSET=latin1; LOCKTABLES`test`WRITE; INSERTINTO`test`VALUES(1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd'); UNLOCKTABLES;
由上的文件内容，可以看出，这个备份实际的过程就是将创建数据库、建表、插入数据的sql语句备份出来，也可以说是将sql语句导出
-B参数
[root@centos6~]#mysqldump-uroot-p-Btest>/download/testbak_$(date+%F)_b.sql Enterpassword: [root@centos6~]#egrep-v"^--|^$"/download/testbak_2016-12-12_b.sql /*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/; /*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/; /*!40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/; /*!40101SETNAMESutf8*/; /*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/; /*!40103SETTIME_ZONE='+00:00'*/; /*!40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/; /*!40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/; /*!40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/; /*!40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/; CREATEDATABASE/*!32312IFNOTEXISTS*/`test`/*!40100DEFAULTCHARACTERSETlatin1*/; USE`test`; DROPTABLEIFEXISTS`test`; /*!40101SET@saved_cs_client=@@character_set_client*/; /*!40101SETcharacter_set_client=utf8*/; CREATETABLE`test`( `id`int(4)NOTNULL, `name`char(20)NOTNULL )ENGINE=MyISAMDEFAULTCHARSET=latin1; /*!40101SETcharacter_set_client=@saved_cs_client*/; LOCKTABLES`test`WRITE; /*!40000ALTERTABLE`test`DISABLEKEYS*/; INSERTINTO`test`VALUES(1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd'); /*!40000ALTERTABLE`test`ENABLEKEYS*/; UNLOCKTABLES; /*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/; /*!40101SETSQL_MODE=@OLD_SQL_MODE*/; /*!40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/; /*!40014SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/; /*!40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/; /*!40101SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/; /*!40101SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/; /*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/;
-B参数的作用一目了然，就是当我们的数据库丢失时，可以直接用此备份文件进行恢复，无需再重新建库、建表，然后再进行数据恢复的操作
2、压缩备份
有时候，数据库的数据比较大，可能会用到压缩后进行备份，节省备份时间与磁盘空间的使用
[root@centos6~]#mysqldump-uroot-p-Btest|gzip>/download/testbak_$(date+%F).sql.gz Enterpassword: [root@centos6~]#ll/download/testbak_2016-12-12.sql.gz -rw-r--r--.1rootroot753Dec1220:49/download/testbak_2016-12-12.sql.gz [root@centos6~]#ll/download/ total14 -rw-r--r--.1rootroot2027Dec1220:41testbak_2016-12-12_b.sql -rw-r--r--.1rootroot1888Dec1220:34testbak_2016-12-12.sql -rw-r--r--.1rootroot753Dec1220:49testbak_2016-12-12.sql.gz
同时也可以看的压缩后的效果
3、多库备份
[root@centos6~]#mysqldump-uroot-p-Btestmysql|gzip>/download/testbak_$(date+%F).sql01.gz Enterpassword: --Warning:Skippingthedataoftablemysql.event.Specifythe--eventsoptionexplicitly. [root@centos6~]#ll/download/testbak_2016-12-12.sql01.gz -rw-r--r--.1rootroot152696Dec1220:52/download/testbak_2016-12-12.sql01.gz
此处有个警告信息，可以忽略也可以备份时加上参数，备份语句如下
[root@centos6~]#mysqldump-uroot-p-B--eventstestmysql|gzip>/download/testbak_$(date+%F).sql02.gz Enterpassword: [root@centos6~]#ll/download/testbak_2016-12-12.sql02.gz -rw-r--r--.1rootroot152749Dec1220:54/download/testbak_2016-12-12.sql02.gz
这样就不会有这为警告信息了
但是这种多库一起备份，就会产生一个问题，如果只是其中一个数据库有问题了，就不好进行单库恢复了，故此备份方法不常用，也不符合实际需求，因此多库备份时就需要进行多次单库备份的操作
[root@centos6~]#mysqldump-uroot-p-Btest|gzip>/download/testbackup_$(date+%F).sql.gz Enterpassword: [root@centos6~]#mysqldump-uroot-p-B--eventsmysql|gzip>/download/mysqlbak_$(date+%F).sql.gz Enterpassword: [root@centos6~]#ll/download/ total80 -rw-r--r--.1rootroot152608Dec1220:58mysqlbak_2016-12-12.sql.gz -rw-r--r--.1rootroot754Dec1220:58testbackup_2016-12-12.sql.gz -rw-r--r--.1rootroot2027Dec1220:41testbak_2016-12-12_b.sql -rw-r--r--.1rootroot1888Dec1220:34testbak_2016-12-12.sql -rw-r--r--.1rootroot152696Dec1220:52testbak_2016-12-12.sql01.gz -rw-r--r--.1rootroot152749Dec1220:54testbak_2016-12-12.sql02.gz -rw-r--r--.1rootroot753Dec1220:49testbak_2016-12-12.sql.gz
4、单表备份
分库备份是为了恢复数据库时方便操作，但是同样面临问题，如果是某个库中的某一个表有损坏，但又不有全库进行恢复，所以实际生产中常用的是分库、分表进行备份，这样数据也备份了，恢复时也好操作
[root@centos6~]#mysqldump-uroot-p-Btesttest>/download/test_testbak_$(date+%F).sql Enterpassword: [root@centos6~]#egrep-v"#|^$|\*"/download/test_testbak_2016-12-12.sql --MySQLdump10.13Distrib5.5.52,forlinux2.6(x86_64) -- --Host:localhostDatabase:test -------------------------------------------------------- --Serverversion5.5.53-log -- --CurrentDatabase:`test` -- USE`test`; -- --Tablestructurefortable`test` -- DROPTABLEIFEXISTS`test`; CREATETABLE`test`( `id`int(4)NOTNULL, `name`char(20)NOTNULL )ENGINE=MyISAMDEFAULTCHARSET=latin1; -- --Dumpingdatafortable`test` -- LOCKTABLES`test`WRITE; INSERTINTO`test`VALUES(1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd'); UNLOCKTABLES; -- --CurrentDatabase:`test` -- USE`test`; -- --Tablestructurefortable`test` -- DROPTABLEIFEXISTS`test`; CREATETABLE`test`( `id`int(4)NOTNULL, `name`char(20)NOTNULL )ENGINE=MyISAMDEFAULTCHARSET=latin1; -- --Dumpingdatafortable`test` -- LOCKTABLES`test`WRITE; INSERTINTO`test`VALUES(1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd'); UNLOCKTABLES; --Dumpcompletedon2016-12-1221:13:16
因此分表备份同分库备份一样，只需要进行多次单表备份的操作，但是有的小伙伴肯定会提出问题了，如果一个库里几千张表，几万张表，这种备份要备到猴年马月吧？？？？，数据量比较大的备份可以使用专业的备份工具，数据量不大或者表不是很多的情况，可以将备份操作写成脚本纳入定时任务，定时执行，只需要检查备份是否成功即可
分享一下民工哥，实际生产环境中一个简单的备份脚本，仅供参考
[root@centos6scripts]#vibak.sh
#!/bin/sh
##########################################
#thisscriptscreatebyrootofmingongge
#createat2016-11-11
#######################################
ip=`grep'IPADDR'/etc/sysconfig/network-scripts/ifcfg-eth0|awk-F"="'{print$2}'`
#定义服务器IP变量
BAKDIR=/backup
#定义备份路径
[!-d$BAKDIR/${ip}]&&mkdir-p$BAKDIR/${ip}
#判断如果不存在这个路径就创建一个，为了服务器多的时候方便看
DB_PWD="mingongge"
DB_USER="root"
MYSQL="/application/mysql/bin/mysql"
MYSQL_DUMP="/application/mysql/bin/mysqldump"
DATA=`date+%F`
####bakdataoftest'sdatabses####
DB_NAME=`$MYSQL-u$DB_USER-p$DB_PWD-e"showdatabases;"|sed'1,5d'`
#定义数据库变量
fornamein$DB_NAME
#for循环语句取库名
do
$MYSQL_DUMP-u$DB_USER-p$DB_PWD-B${name}|gzip>$BAKDIR/${ip}/${name}_$DATA.sql.gz
#全库备份
[!-d$BAKDIR/${ip}/${name}]&&mkdir-p$BAKDIR/${ip}/${name}
#判断这个路径，为了区别哪个库的备份文件
fortablenamein`$MYSQL-u$DB_USER-p$DB_PWD-e"showtablesfrom${name};"|sed'1d'`
#for循环语句取表名
do
$MYSQL_DUMP-u$DB_USER-p$DB_PWD${name}${tablename}|gzip>$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz
#分表备份
done
done
执行的结果如下
[root@ranzhioa~]#tree/backup/ /backup/ 10.1xx.1xx.1xx#服务器IP xxxxxxx#其实是库名 cash_balance_2016-12-15.sql.gz cash_depositor_2016-12-15.sql.gz cash_trade_2016-12-15.sql.gz crm_customer_2016-12-15.sql.gz crm_delivery_2016-12-15.sql.gz crm_order_2016-12-15.sql.gz crm_orderAction_2016-12-15.sql.gz crm_orderField_2016-12-15.sql.gz crm_plan_2016-12-15.sql.gz
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。