关闭

Mysqldump

337人阅读 评论(0) 收藏 举报
分类:

查看帮助

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
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:132586次
    • 积分:2630
    • 等级:
    • 排名:第14355名
    • 原创:130篇
    • 转载:7篇
    • 译文:6篇
    • 评论:32条
    文章分类
    最新评论