备份 · MySQL学习笔记

# MySQL的备份-mysqldump命令的使用 [TOC] >[info] **数据备份的意义[运维的工作]** >1. 保护公司的数据 >2. 网站7*24小时提供服务利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份的文件的过程。 ## 备份数据库的参数 >[danger] **mysqldump参数** **-A**(`--all-databases`) ：备份所有库 **-B**(`--databases`) ：连接多个数据库,备份的数据中增加 `建库和use库`语句 (方便导入数据) **-d**(`--no-data`) ：只导出表结构 **-t**(`--no-create-info`) ：只导出表数据 **-x**(`--lock-all-tables`) ：锁表(无法对数据库进行操作，影响大) **-l**(`--lock-tables`) : 只读锁表 **-F**(`--flush-logs`) : 刷新binlog日志(备份数据时可能会用上)也可以在mysql客户端执行`reset master;` **--master-date=1/2** ：增加binlog日志文件名以及对应的位置点。 **gzip** ：指定gzip进行备份sql压缩 **--single-transaction** ：适合InnoDB事务数据库备份（*InnoDB表在备份时,通常启用该参数来保证备份的一致性，他的工作原理是设定本次回话的隔离级别为: repeatable read,以确保本次回话dump时，不会看到其他会话已经提交了的数据*） **--compact** ：参数优化备份文件大小减少输出注释(debug调试时使用) **--default-chatacter-set=utf8** ：指定默认字符集 mysql数据库自带一个很好用的备份命令**mysqldump**，它的语法为: `mysqldump -u 用户名 -p 密码数据库名 [表名] > 备份的文件名` ### Myisam表常规备份(参数) >[info] `mysqldump -uroot -paaaaaa -A -B -F --flush-privileges --triggers --routines --events --hex-blob --master-data=1 -x|gzip > ~/all.sql.gz` ### InnoDB表常规备份(推荐使用的存储引擎) >[info] `mysqldump -uroot -paaaaaa -A -B -F --flush-privileges --master-data=1 --triggers --routines --events --hex-blob --single-transaction|gzip > ~/all.sql.gz` ## 普通方式备份数据库 ~~~ # 20160108新增一个数据库备份语句(运维推荐这个参数理由:保证数据同步的同时还原操作可控) mysqldum -uroot -paaaaaa --opt DbName >~/dbname.sql ~~~ ~~~ # 多实例指定sock文件 mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock database > ~/database.sql ~~~ ### 多实例指定sock文件备份数据库test并使用gzip压缩 ~~~ mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock test|gzip > ~/test.gz.sql ~~~ ### 使用egrep查看备份的文件的sql内容 `egrep -v "#|\*|--|^$" ~/test.sql` ## **-B参数** 备份多个库 ~~~ [root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock -B test mysql|gzip >~/mutil_db.sql.gz ~~~ ### 备份库下的表 #### 备份库下的某个表 `[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbNAME tableName>~/dbName.sql` #### 备份库下的多个表 `[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbName tableName tableName ..>~/dbName.sql` * * * * * ## **-d参数** 备份表结构 `[root@localhost ~]# mysqldump -uroot -paaaaaa -d -S /data/3306/mysql.sock dbName tableName` ## **-t参数** 备份表数据 `[root@localhost ~]# mysqldump -uroot -paaaaaa -t -S /data/3306/mysql.sock dbName tableName` ## **-F参数** 刷新二进制日志 ## **`--master-data参数`** 备份记录点 |参数|值|例子| |-|-|-| |`--master-data`|1|`CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107`| |`--master-data`|2|`-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107`**(注释change master语句)**| * * * * * # 实例 ## 多实例分库备份 >[info] 思路: 使用for循环查询出数据库中存在的库,然后分别使用mysqldump进行备份 ### 命令行实现备份 ~~~ # 多实例情况(单实例把sock去掉即可) [root@localhost /test]# mysql -uroot -p'aaaaaa' -S /data/3306/mysql.sock -e 'show databases;'|grep -Evi 'database|information_schema|mysql'|sed -r 's#^([a-z].+$)#mysqldump -uroot -p"aaaaaa" -S /data/3306/mysql.sock --events -B \1|gzip>/test/\1.sql.gz #g'|/bin/bash ~~~ ### 脚本实现(多实例情况) `[root@localhost ~] # vim mysql_bakdb.sql` ~~~ #!/bin/bash #filename mysql_bakdb.sh MYUSER=root # 数据库用户 MYPASS=aaaaaa # 用户密码 SOCKET=/data/3306/mysql.sock # 多实例sock文件地址 BAKDIR=~/mysqldatabak/`date +"%Y%m%d"`/ # 保存的文件地址 MYCMD="mysql -u${MYUSER} -p${MYPASS} -S ${SOCKET}" # mysql连接 MYDUMP="mysqldump -u${MYUSER} -p${MYPASS} -S ${SOCKET}" # mysqldump连接 mkdir -p $BAKDIR # 创建文件夹 for database in `${MYCMD} -e "show databases;"|grep -Eiv 'mysql|database|information_schema'` do ${MYDUMP} ${database}|gzip > ${BAKDIR}${database}.sql.gz done ~~~ >[danger] **分库备份的意义** 有时候一个数据库实例中会有多个库,例如(blog,bbs..)但是出问题的时候可能是其中的某一个库，如果在备份时，把所有的库都备份成一个数据文件的话，恢复某一个数据库就比较麻烦 ## 多实例分表备份 **典型备份语句(备份制定库下的表)** ~~~ [root@curder.localhost ~] # mysqldump -uroot -paaaaaa rose student > ~/student.sql ~~~ >[info] 思路：在分库备份下再循环库下`show tables from dbName`找到所有表对表进行备份(**无-B参数**) 使用`mysqldump`拼接库、表进行备份 `[root@localhost ~] # vim mysql_baktable.sh` ~~~ #!/bin/bash #filename mysql_baktable.sh MYUSER=root MYPASS=aaaaaa SOCKET=/data/3306/mysql.sock BAKDIR=~/`date +"%Y%m%d"` MYCMD="mysql -u${MYUSER} -p${MYPASS} -S ${SOCKET}" MYDUMP="mysqldump -u${MYUSER} -p${MYPASS} -S ${SOCKET}" for database in `${MYCMD} -e "show databases;"|grep -Eiv 'mysql|database|information_schema'` do mkdir -p ${BAKDIR}/${database} for table in `${MYCMD} -e "show tables from ${database};"|sed '1d'` # 循环获取当前库下的所有表 do ${MYDUMP} ${database} ${table}|gzip > ${BAKDIR}/${database}/${table}.sql.gz done done ~~~ >[danger] 分表备份缺点:文件多，碎。 1. 备份一个完整全备，再做一个分库分表备份。 2. 脚本批量恢复多个SQL文件。