MySql备份工具之Mysqldump

一：mysqldump是mysql自带的一个具备简单数据备份和恢复功能的命令，可以实现将MySQL数据的数据备份成SQL文件，然后copy到其他MySQL服务器使用mysqldump命令进行数据还原，适用于备份数据量不是很大的业务场景，例如10G左右的场景，基本的使用方法如下：

1.1：命令的基本使用：

1.1.1：只备份指定的单个库，如果备份全部的库备份时间慢而且导入时间慢，并且另外的服务器可能还用不上：

[root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -S /var/lib/mysql/mysql.sock -uroot -p123456 testdatabase > testdatabase_bak.sql
Warning: Using a password on the command line interface can be insecure.
[root@sql-slave ~]# egrep -v "#|\*|^$" testdatabase_bak.sql #查看备份文件而不显示注释信息

1.1.2：-B：在备份的时候添加-B的参数，是为了增加在在创建数据库和使用数据库的语句，如下：

[root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -S /var/lib/mysql/mysql.sock -B  -uroot -p123456 testdatabase > testdatabas_1.sql

#不加-B的效果：

#加-的效果：

1.1.3：–compact：将备份后的文件注释信息减少的最少，很多注释信息不被写入文件，因此文件大小将减小很多，一般用于测试：

root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -S /var/lib/mysql/mysql.sock -B --compact  -uroot -p123456 testdatabase > testdatabas_2.sql

1.1.4：压缩备份的文件后的sql文件：

[root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -S /var/lib/mysql/mysql.sock -B --compact  -uroot -p123456 testdatabase | gzip  > testdatabas_2.sql.gz

1.1.5：分库备份，数据库有点儿多的话一个个备份太慢，全部备份不好恢复，就写了一个脚本，自动按库名分类备份，这样便于后期只有某个库出现问题的时候恢复数据，而且还不影响其他的库:

#!/bin/sh
#Author:ZhangJie
BAK_TIME=$(date  +%Y-%m-%d_%H:%M:%S)
USER_PSWD=123456
USER_NAME=root
SOCKET="/var/lib/mysql/mysql.sock"
MYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD} -S ${SOCKET}"
DUMP_CMD="/usr/local/mysql/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -S${SOCKET} -B"
DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "_schema|mysql")" #不备份的数据库列表写在这里，自带的mysql、test等可以不备份

for dbname in ${DATABASE}
  do
   MYDIR=/data/backup/${dbname}
   [ ! -d ${MYDIR} ] && mkdir -p ${MYDIR}
 $DUMP_CMD ${dbname}|gzip >${MYDIR}/${dbname}_${BAK_TIME}.sql.gz
done

#备份效果如下：

1.1.6：通过参数备份，可以接收多个不固定的参数，将传递的参数作为数据库进行备份：

#!/bin/sh
#Author:ZhangJie
BAK_TIME=$(date  +%Y-%m-%d_%H:%M:%S)
USER_PSWD=123456
USER_NAME=root
SOCKET="/var/lib/mysql/mysql.sock"
MYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD} -S ${SOCKET}"
DUMP_CMD="/usr/local/mysql/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -S${SOCKET} -B"
DATABASE=$* #接收所有的参数

for dbname in ${DATABASE} #循环参数
  do
    MYDIR=/data/backup/${dbname}
    [ ! -d ${MYDIR} ] && mkdir -p ${MYDIR}
    $DUMP_CMD ${dbname}|gzip >${MYDIR}/${dbname}_${BAK_TIME}.sql.gz
done

#通过传递蚕食备份数据库的备份结果：

1.2：针对表的备份：

1.2.1：备份指定的多个多个表，即不备份一个库里面所有的表：

[root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -uroot -hlocalhost -p123456 -S /var/lib/mysql/mysql.sock    testdatabase testtable testtableN > /opt/test_table_bak.sql

1.2.2：只备份一个数据里面的表结构，可以理解为将创建表的语句备份：

[root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -uroot -hlocalhost -p123456 -S /var/lib/mysql/mysql.sock -B  -d  testdatabase   > /opt/test.sql

#备份文件的 效果：

1.2.3：只备份表中的数据，即不包含表结构：

[root@sql-slave ~]# /usr/local/mysql/bin/mysqldump -uroot -hlocalhost -p123456 -S /var/lib/mysql/mysql.sock  --compact  -B  -t  testdatabase   > /opt/test.sq

#备份的数据结果：:

1.3：记录MSQYL服务的日志文件及POS位置，用于配置Slave同步的时候使用：

[root@sql-master ~]# /usr/local/mysql/bin/mysqldump -uroot -hlocalhost -p123456  --master-data=2  -B   testdatabase   > /opt/test_table_bak.sql

#日志文件和POS位置记录结果如下：

1.4：mysqldump的时候，要去掉drop table的语句，默认导出表的时候会在每个表的前面加上drop table的语句，即导出的sql文件在别的服务器导入时会直接删除该服务器已经存在同名的table而导致会丢失数据，因此在Master或者其他MySQL服务器使用mysqldump导出数据的时候要将默认添加的drop-table去掉，如下：

1.4.1：默认是开启添加drop-table语句的：

[root@sql-master ~]# mysqldump --help | grep  drop-table
  --add-drop-table    Add a DROP TABLE before each create.
                      (Defaults to on; use --skip-add-drop-table to disable.)
                      options --skip-add-drop-table --skip-add-locks
  --opt               Same as --add-drop-table, --add-locks, --create-options,
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
add-drop-table                    TRUE

1.4.2：默认导出的sql文件内容：

[root@sql-master ~]# /usr/local/mysql/bin/mysqldump --single_transaction --flush-logs --master-data=2 --lock-tables > /backup.sql

[root@sql-master ~]# /usr/local/mysql/bin/mysqldump   -uroot -p123456 --all-databases  --single_transaction --flush-logs --master-data=2 --skip-add-drop-table --lock-tables > /backup1.sql

1.4.3：备份所有的数据库：

[root@sql-master ~]#/usr/local/mysql/bin/mysqldump   --all-databases  --single_transaction --flush-logs --master-data=2 --lock-tables > /backup.sql

1.5：推荐使用的命令：

1.5.1：推荐备份的命令：

/usr/local/mysql/bin/mysqldump -u xx  -p xx  -Sxx  -B --master-data=2 --single_transaction --flush-logs --skip-add-drop-table  [要执行的操作]

1.5.2：推荐使用分库备份的脚本：

#!/bin/sh
#Author:ZhangJie
BAK_TIME=$(date  +%Y-%m-%d_%H:%M:%S)
USER_PSWD=123456
USER_NAME=root
SOCKET="/var/lib/mysql/mysql.sock"
MYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD} -S ${SOCKET}"
DUMP_CMD="/usr/local/mysql/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -S${SOCKET} -B --master-data=2 --single_transaction --flush-logs --skip-add-drop-table"
DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "_schema|mysql")"

for dbname in ${DATABASE}
  do
   MYDIR=/data/backup/${dbname}
   [ ! -d ${MYDIR} ] && mkdir -p ${MYDIR}
 $DUMP_CMD ${dbname}|gzip >${MYDIR}/${dbname}_${BAK_TIME}.sql.gz
done

1.6：比较常用的命令：

show processlist; #查看数据库里正在执行的SQL语句，可能无法看全完整的SQL语句。
show full processlist; 	#查看正在执行的完整SQL语句，完整显示。
set global key_buffer_size = 1024*1024*32;	#不重启数据库调整数据库参数，直接生效，重启后失效。
show variables;	#查看数据库的配置参数信息，例如：my.cnf里参数的生效情况。
kill id  杀掉线程的命令，id为线程号（mysql下）
show status  查看当前会话的数据库状态信息。
show global status 查看整个数据库的运行状态信息。很重要，要分析并要做好监控。
show engine innodb status 显示innodb引擎的性能状态