一、备份单个数据库
1、备份命令:mysqldump
MySQL数据库自带的一个很好用的备份命令。是逻辑备份,导出 的是SQL语句。也就是把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程。
单实例语法(Syntax): mysqldump -u <username> -p <dbname> > /path/to/***.sql 多实例的备份语法(Syntax): mysqldump -u <username> -p <dbname> -S <sockPath> > /path/to/***.sql
eg: mysqldump -u root -p wordpress > /opt/wordpress_$(date +%F).sql
2、参数解析
View Code
举例使用:
View Code
3、恢复操作
语法(Syntax): mysql -u<username> -p<password> <dbname> < /opt/mytest_bak.sql #库必须保留,空库也可 说明:指定dbname,相当于use <dbname>
4、示例
(1)无参数备份数据库mytest和恢复
(1)备份操作 a、备份 mysqldump -uroot -p‘123456’ mytest > /mnt/mytest_bak_$(date +%F).sql
(2)恢复操作 a、删除student表(库必须要保留,空库都行) mysql -uroot -p'123456' -e "use mytest;drop table student;" b、恢复数据 mysql -uroot -p'123456' mytest < /mnt/mytest_bak.sql c、查看数据 mysql -uroot -p'123456' -e "select * from mytest.student;"
(2)-B参数备份和恢复(建议使用)
(1)备份操作 a、备份 mysqldump -uroot -p'123456' -B mytest > /mnt/mytest_bak_B.sql 说明:加了-B参数后,备份文件中多的Create database和use mytest的命令 加-B参数的好处: 加上-B参数后,导出的数据文件中已存在创建库和使用库的语句,不需要手动在原库是创建库的操作,在恢复过程中不需要手动建库,可以直接还原恢复。 (2)恢复操作 a、删除mytest库 mysql -uroot -p'123456' -e "drop database mytest;" b、恢复数据 (1)使用不带参数的导出文件导入(导入时不指定要恢复的数据库),报错 mysql -uroot - p'123456' < /mnt/mytest_bak.sql ERROR 1046 (3D000) at line 22: No database selected (2)使用带-B参数的导出文件导入(导入时也不指定要恢复的数据库),成功 mysql -uroot -p'123456' < /mnt/mytest_bak_B.sql c、查看数据 mysql -uroot -p'123456' -e "select * from mytest.student;"
(3)–compact参数优化备份文小大小,减少输出注释(一般用于Debug调试)
(1)备份 mysqldump -uroot -p'123456' --compact -B mytest > /mnt/mytest_bak_Compact.sql 说明: 使用--compact参数,可以优化输出内容的大小,让容量更少,适合调试。便会忽略--skip-add-drop-table,--no-set-names,--skip-disable-keys,--skip-add-locks等几个参数的功能。
(4)指定压缩命令来压缩备份文件
(1)备份 mysqldump -uroot -p'123456' -B mytest | gzip > /mnt/mytest_bak_.sql.gz 说明: mysqldump导出的文件是文本文件,压缩效率很高
(5)备份多个数据库
(1)说明 通过-B参数指定相关数据库,每个数据库名之前用空格分格。当使用-B参数后,将所有数据库全部列全,则此时等同于-A参数。 (2)备份 mysqldump -uroot -p'123456' -B mytest wiki | gzip > /mnt/mytestAndWiki_bak.sql.gz
(6)分库备份
分库备份实际上就是执行一个备份语句就备份一个库,有多个库时,就执行多条相同的备份语句,只是备份的库名和备份文件名不同而已。可能通过shell脚本自动生成并执行相应的操作,也可以把所有单个备份语句写在一个shell脚本中,通过cron定时任务来备份。
分库备份的意义是在所有库都备份成一个备份文件时,恢复其中一个库的数据是比较麻烦的,所以分库备份,利于恢复。分库备份脚本如下:
for dbname in ` mysql -uroot -p'123456' -e "show databases;" | grep -Evi "database|infor|perfor"` do mysqldump -uroot -p"123456" --events -B $dbname | gzip > /mnt/${dbname}_bak.sql.gz done
说明:${dbname}_bak,由于要求备份文件名以$dbname_bak.sql.gz格式命令,但系统无法辨别变量是$dbname还是$dbname_bak,所以此时就需要用大括号“{}”将变量括起来,就是${dbname}_bak.sql.gz了。
(7)-d参数,只备份数据库中表结构
mysqldump -uroot -p'123456' -d mytest > /mnt/mytestDesc_bak.sql
(8)-A参数备份全库,并且-F刷新和切换binlog
mysqldump -uroot -p'123456' -A -B -F > /mnt/All_bak.sql
(9)–master-data参数在备份文件中写入当前binlog文件号
mysqldump -uroot -p'123456' --master-data=1 --compact mytest > /mnt/All_bak.sql mysqldump -uroot -p'123456' --master-data=2 --compact mytest > /mnt/All_bak.sql
二、备份单个表
语法(Syntax):不能加-B参数 mysqldump -u<username> -p<password> dbname tablename1 tablename2... > /path/to/***.sql
示例:
示例1:备份mytest库中的student表 mysqldump -uroot -p'123456' mytest student > /mnt/table_bak/student_bak.sql 示例2:备份mytest库中所有表,就是备份mytest库 mysqldump -uroot -p'123456' mytest > /mnt/table_bak/all_bak.sql 示例3:备份mytest库中的student和test表 mysqldump -uroot -p'123456' mytest student test > /mnt/table_bak/two_bak.sql 示例4:-d参数,只备份表结构 mysqldump -uroot -p'123456' -d mytest stusent > /mnt/studentDesc_bak.sql 示例5:-t参数,只备份数据 mysqldump -uroot -p'123456' --compact -t mytest stusent > /mnt/studentData_bak.sql INSERT INTO `student` VALUES (1,'Tom',20,'S11'),(2,'Jary',21,'S12'),(3,'King',25,'S10'),(4,'Smith',19,'S11'),(5,'??',20,'S11'),(6,'张三',20,'S11');
三、企业生产场景不同引擎备份命令参数
1、mysqldump的关键参数
-B:指定多个库,在备份文件中增加建库语句和use语句 --compact:去掉备份文件中的注释,适合调试,生产场景不用 -A:备份所有库 -F:刷新binlog日志 --master-data:在备份文件中增加binlog日志文件名及对应的位置点 -x --lock-all-tables:锁表 -l:只读锁表 -d:只备份表结构 -t:只备份数据 --single-transaction:适合innodb事务数据库的备份 InnoDB表在备份时,通常启用选项--single-transaction来保证备份的一致性,原理是设定本次会话的隔离级别为Repeatable read,来保证本次会话(也就是dump)时,不会看到其它会话已经提交了的数据。
2、不同引擎备份命令参数用法
1)Myisam引擎: mysqldump -uroot -p123456 -A -B --master-data=1 -x| gzip > /data/all_$(date +%F).sql.gz (2)InnoDB引擎: mysqldump -uroot -p123456 -A -B --master-data=1 --single-transaction > /data/bak.sql (3)生产环境DBA给出的命令 a、for MyISAM mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \ --master-data=1 --flush-logs --triggers --routines --events \ --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql b、for InnoDB mysqldump --user=root --all-databases --flush-privileges --single-transaction \ --master-data=1 --flush-logs --triggers --routines --events \ --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql
转载请注明:Adminxe's Blog » MySQL数据库基础备份