查看帮助
mysqldump --help
-?,--help | 帮助信息 |
-u,--user | 指定连接的用户名 |
-p,--password | 指定用户的密码,可以只指定参数,mysqldump随后将提示输入密码 |
-S,--socket | 指定socket文件连接,本地登录才会使用 |
-h,--host | 指定连接的服务器名 |
-P,--port | 指定连接的服务器端口 |
--default-character-set | 设置字符集,默认UTF8 |
-A,--all-databases | 导出所有数据库(information_schema库不会被导出) |
-B,--databases | 导出指定的某个/或某几个数据库 |
-d,--no-data | 只导出表结构,不包含数据 |
-w,--where | 只导出符合条件的记录 |
-d,--no-create-info | 与-d相反,只导出数据不导出建表语句 |
-f,--force | 即使遇到SQL错误,也继续执行,功能类似oracle exp中的ignore参数 |
-F,--flush-logs | 执行导出前先刷新日志文件,视操作场景,有可能会触发多次刷新日志文件。一般来说,如果是全库导出,建议先刷新日志文件 |
这里要注意一点的是:
mysqldump xxx dbname tablename > dump.sql
mysqldump xxx --databases
dbname tablename > dump.sql
这两个是有略微差别的
前者导出不包含建库语句:
[mysql@rh55 soft]$ more fandb.sql
-- MySQL dump 10.13 Distrib 5.6.24, for Linux (x86_64)
--
-- Host: localhost Database: fandb
-- ------------------------------------------------------
-- Server version 5.6.24-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `csv1`
--
DROP TABLE IF EXISTS `csv1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `csv1` 省略······
后者则包含:
-- MySQL dump 10.13 Distrib 5.6.24, for Linux (i686)
--
-- Host: localhost Database: fandb
-- ------------------------------------------------------
-- Server version 5.6.24-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `fandb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `fandb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `fandb`;
--
-- Table structure for table `csv1`
--
DROP TABLE IF EXISTS `csv1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `csv1` 省略······
还有两个参数
-l,--lock-tables:
锁定读取的表对象,相当常用,想导出一致性备份的话最好启用本参数,其实即使不指定,默认也是在启用状态。如果说影响的话,那就是对象导出期间,其他会话都无法再对表做写入操作了,对于数据量比较大的表对象的导出,锁定时间相对会较长,并不是所有系统都能接受这一点。如果你希望备份过程中其他会话仍能正常执行读写操作,请关注下面的参数。
--single-transaction:
对于支持多版本的存储引擎(说的就是innodb)在导出时会建立一致性的快照,也就是该表对象导出操作将在同一事务中,在保证导出数据的一致性的前提下,又不会堵塞其他会话的读写操作,相比--lock-tables参数来说,锁粒度要低没造成的影响也小很多,遗憾的是它只能支持有限的存储引擎。
另外还需要注意,指定了这个参数后,其他连接不能执行alter table、drop table、rename table、truncate table这类语句
例子:
mysqldump -umysql -p -S /data/mysqldata/3306/mysql.sock fandb
-R --single-transaction
--ignore-table fandb.per2 --ignore-table fandb.per4 | gzip > hehe.sql.gz
ignore多个表要指定多个--ignore-table参数
全库备份
#!/bin/bash
#create by fan
source /data/mysqldata/scripts/mysql_env.ini
data_path=/data/mysqldata/backup/mysql_full
data_file=${data_path}/dbfullbak_`date +%F`.sql.gz
log_file=${data_path}/dbfullbak_`date +%F`.log
mysql_path=/usr/local/mysql/bin
mysql_dump="${mysql_path}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock -A -R --single-transaction --default-character-set=utf8"
if [ ! -d "$data_path" ];then
mkdir -p "$data_path"
fi
echo > $log_file
echo -e "---jobs started at `date +%F' '%T' '%w`---\n" >> $log_file
echo -e "***executed command:${mysql_dump} | gzip > ${data_file}" >> $log_file
${mysql_dump} | gzip > $data_file
echo -e "***executed finished at `date +%F' '%T' '%w`===" >> $log_file
echo -e "***backup file size:`du -sh ${data_file}|awk '{print $1}'`===\n" >> ${log_file}
echo -e "---find expired backup and delete those files---" >> ${log_file}
for tfile in $(/usr/bin/find $data_path/ -mtime +6)
do
if [ -d $tfile ];then
rmdir $tfile
elif [ -f $tfile ];then
rm -f $tfile
fi
echo -e "---delete file $tfile---" >> ${lof_file}
done
echo -e "\n---jobs ended at `date +%F' '%T' '%w`---\n" >> $log_file
分表备份
#!/bin/bash
#create by fan
source /data/mysqldata/scripts/mysql_env.ini
data_path=/data/mysqldata/backup/mysql_full_by_tables
data_file=${data_path}/dbfullbak_by_tables_`date +%F`.sql.gz
log_file=${data_path}/dbfullbak_`date +%F`.log
mysql_path=/usr/local/mysql/bin
mysql_cmd="${mysql_path}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock"
mysql_dump="${mysql_path}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock --single-transaction -l"
if [ ! -d ${data_path} ];then
mkdir -p ${data_path}
fi
if [ ! -f ${log_file} ];then
touch ${log_file}
fi
echo > ${log_file}
echo -e "---jobs started at `date +%F' '%T' '%w` ---\n" >> ${log_file}
for dbs in `${mysql_cmd} -e "show databases" | sed '1d' | egrep -v "information_schema|mysql|performance_schema"`
do
mkdir -p ${data_path}/${dbs}
echo -e "***Database:${dbs} Backup start time:`date +%F' '%T' '%w`***\n" >> ${log_file}
for tbls in `${mysql_cmd} -D ${dbs} -e "show tables" | sed '1d'`
do
echo -e "###Begin ${dbs}.${tbls} Dump start time:`date +%F' '%T' '%w`" >> ${log_file}
echo -e " Execute Command:${mysql_dump} ${dbs} ${tbls} | gzip > ${data_path}/${dbs}/${dbs}_${tbls}.sql.gz" >> ${log_file}
${mysql_dump} ${dbs} ${tbls} | gzip > ${data_path}/${dbs}/${dbs}_${tbls}.sql.gz
echo -e "### End ${dbs}.${tbls} dump stop time:`date +%F' '%T' '%w`\n" >> ${log_file}
echo >> ${log_file}
done
echo -e "***Database: ${dbs} backup stop time `date +%F' '%T' '%w`***" >> ${log_file}
echo -e "***Backup file size: `du -sh ${data_path}/${dbs}`***\n" >> ${log_file}
done
echo -e "---find expired backup and delete those files---" >> ${log_file}
for tfile in $(/usr/bin/find $data_path/ -mtime +6)
do
if [ -d $tfile ];then
rmdir $tfile
elif [ -f $tfile ];then
rm -f $tfile
fi
echo -e "---delete file $tfile---" >> ${lof_file}
done
echo -e "\n---jobs ended at `date +%F' '%T' '%w`---\n" >> $log_file
[mysql@rh55 backup]$ tree
.
|-- mysql_full
| |-- dbfullbak_2015-07-06.log
| `-- dbfullbak_2015-07-06.sql.gz
`-- mysql_full_by_tables
|-- dbfullbak_2015-07-06.log
|-- fandb
| |-- fandb_csv1.sql.gz
| |-- fandb_hehe.sql.gz
| |-- fandb_import1.sql.gz
| |-- fandb_import2.sql.gz
| |-- fandb_import3.sql.gz
| |-- fandb_import4.sql.gz
| |-- fandb_import5.sql.gz
| |-- fandb_import6.sql.gz
| |-- fandb_lock_share.sql.gz
| |-- fandb_per1.sql.gz
| |-- fandb_per2.sql.gz
| |-- fandb_per3.sql.gz
| |-- fandb_per4.sql.gz
| `-- fandb_per5.sql.gz
`-- test