MySQL数据库备份之mysqldump
创建用户备份的用户
MariaDB [mysql]> create user 'backdata'@'localhost' identified by 'test@123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> grant select,lock tables,replication client,reload,show view,event,trigger on *.* to 'backdata'@'localhost';
Query OK, 0 rows affected (0.00 sec)
在本地电脑上，找一块大的硬盘，然后创建一个目录，专门存放备份
[root@blog ~]# mkdir /opt/mysql_backup
mysqldump参数详解
-u 指定用户
-p 密码
-P 端口
-S socket文件
-h 主机
--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；
-A 备份所有的数据库
-B 数据库 指定要备份的数据库
-d 不备份数据，只备份表结构
-e event 事件
-R 存储过程
--triggers 触发器
--single-transaction
--lock-all-tables
注意:在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io_thread的状态
理解--single-transaction
thread_id: 35
argument: FLUSH TABLES WITH READ LOCK
说明：因为开启了--master-data=2，这时就需要flush tables with read lock锁住全库，记录当时的master_log_file和master_log_pos
thread_id: 35
argument: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
说明：--single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE
READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响
thread_id: 35
argument: START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
这时开启一个事务，并且设置WITH CONSISTENT
SNAPSHOT为快照级别，如果只是可重复读，那么在事务开始时还没dump数据时，这时其他线程修改并提交了数据，那么这时第一次查询得到的结果是其他线程提交后的结果，而WITH
CONSISTENT
SNAPSHOT能够保证在事务开启的时候，第一次查询的结果就是事务开始时的数据A，即使这时其他线程将其数据修改为B，查的结果依然是A
理解--lock-all-tables
thread_id: 120
argument: FLUSH TABLES WITH READ LOCK
这里flush tables with read lock之后就不会主动unlock tables，保证整个dump过程整个db数据不可更改，也没有事务的概念了
一）备份和恢复所有库
[root@blog ~]# mysqldump -S /tmp/mysql.sock -u backdata -p -A >/opt/mysql_backup/alldata_$(date +%F_%H).sql
[root@blog ~]# cat /opt/mysql_backup/alldata_2018-04-27_11.sql---查看是否存在
如下
（插入完毕后，然后执行UNLOCK tables）
- 模拟恢复数据
mysql> drop database louisblog;
Query OK, 12 rows affected (0.09 sec)
[root@blog ~]# mysql -u root -p < /opt/mysql_backup/alldata_2018-04-27_11.sql
Enter password:
(由于备份数据的用户，没有权限create table和create database，已经insert权限，这边需要root用户来操作）
二）备份单个库
[root@blog ~]# mysqldump -S /tmp/mysql.sock -u backdata -p -B louisblog > /opt/mysql_backup/louisblog_$(date +%F_%H).sql
Enter password:
[root@blog ~]# mysqldump -S /tmp/mysql.sock -u backdata -p -B mysql > /opt/mysql_backup/mysql_$(date +%F_%H).sql
Enter password:
[root@blog ~]# cd /opt/mysql_backup/
[root@blog mysql_backup]# ll
total 4900
-rw-r--r-- 1 root root 1669688 Apr 27 11:41 alldata_2018-04-27_11.sql
-rw-r--r-- 1 root root 1669688 Apr 27 12:01 alldata_2018-04-27_12.sql
-rw-r--r-- 1 root root 994517 Apr 27 13:19 louisblog_2018-04-27_13.sql
-rw-r--r-- 1 root root 676300 Apr 27 13:20 mysql_2018-04-27_13.sql
- 还原数据库
mysql> source /opt/mysql_backup/louisblog_2018-04-27_13.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
三） 分库备份脚本
#!/bin/bash
#定义变量
USER="backup"
PASS="Aa123321"
HOST="localhost"
DATE="$(date +%F_%H)"
MYCMD="/usr/local/mysql/bin/mysql -u$USER -p$PASS"
BACKUPDIR="/opt/backup/database/"
MYDUMP="/usr/local/mysql/bin/mysqldump -u$USER -p$PASS -B -R -E --triggers --single-transaction --master-data=2"
DBLIST=`$MYCMD -e "show databases;"|sed 1d|egrep -v "_schema|test"`
#判断备份目录是否存在
[ ! -d ${BACKUPDIR}/${DATE} ] && mkdir -p ${BACKUPDIR}/${DATE}
#刷新生成二进制日志
$MYCMD -e "flush logs;"
#循环备份数据库
for dbname in $DBLIST;do
$MYDUMP $dbname|gzip >${BACKUPDIR}/${DATE}/${dbname}.sql.gz
done
#删除7天前的文件
cd $BACKUPDIR && find . -type d -mtime +7 |xargs rm -f
四）分库分表备份
#!/bin/bash
#description: Use mysqldump to backup mysql.
# GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost'
#编写my.cnf文件,添加
#[client]
#user = backup
#password = Aa123321
#定义变量
HOST="localhost"
DATE="$(date +%F_%H)"
MYCMD="/usr/local/mysql/bin/mysql --defaults-extra-file=/etc/my.cnf"
BACKUPDIR="/opt/backup/database"
MYDUMP="/usr/local/mysql/bin/mysqldump --defaults-extra-file=/etc/my.cnf -R -E --triggers -x --master-data=2"
DBLIST=`$MYCMD -e "show databases;"|sed 1d|egrep -v "_schema|test"`
#判断备份目录是否存在
[ ! -d ${BACKUPDIR}/${DATE} ] && mkdir -p ${BACKUPDIR}/${DATE}
#刷新生成二进制日志
$MYCMD -e "flush logs;"
#循环备份数据库
for dbname in $DBLIST;do
TLIST=`$MYCMD -e "show tables from $dbname;"|sed 1d`
for tname in $TLIST;do
mkdir -p $BACKUPDIR/${DATE}/$dbname
$MYDUMP $dbname $tname|gzip >${BACKUPDIR}/${DATE}/${dbname}/${dbname}_${tname}_${DATE}.sql.gz
done
done