备份数据库oss03
./mysqldump -h localhost -uroot -pmysql --routines --events --default-character-set=utf8 --set-gtid-purged=OFF --lock-tables=false --databases oss03>/opt/bak/oss03.dump
2.采用--single-transaction一致性备份数据库,这样避免锁表
time mysqldump -h node2 -uroot -poracle --default-character-set=gbk --single-transaction --databases db1>/home/mysql/mysqlbak/hxl_bak_20140915.sql
8.导出表数据(需要设置secure_file_priv=目录,同时mysql用户需要对该目录具有修改的权限)
select * from tb_course into outfile 'd:\tb_course.sql';
SELECT * INTO OUTFILE 'd:\tb_course01.sql'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ' '
FROM tb_course;
9.导出数据结果并包含存储过程和函数
mysqldump --no-data -h node2 -uroot -poracle --routines --all-databases>/dbext/mysql/bak/r_no_data_all_db.sql
10.指定字符集导出
mysqldump -h node2 -uroot -poracle --default-character-set=gbk --routines --single-transaction --all-databases|gzip>/dbext/mysql/bak/all_db_r.sql.gz
11.备份表到制定目录，生成表定义文件和表数据文件
mysqldump -h 192.168.56.111 -uroot -p -T /tmp dev tb_class --fields-terminated-by ','
会在指定的目录/tmp下生成两个文件，一个是表定义文件，一个是表数据文件
[mysql@host01 tmp]$ more tb_class.txt
1,class1,2015-01-27,2015-01-27
2,class2,2015-01-27,2015-01-27
3,class3,2015-01-27,2015-01-27
4,class4,2015-01-27,2015-01-27
5,class5,2015-01-27,2015-01-27
6,class6,2015-01-27,2015-01-27
7,class7,2015-01-27,2015-01-27
8,class8,2015-01-27,2015-01-27
9,class9,2015-01-27,2015-01-27
10,class10,2015-01-27,2015-01-27
11,class11,2015-01-27,2015-01-27
12,class12,2015-01-27,2015-01-27
13,class13,2015-01-27,2015-01-27
14,class14,2015-01-27,2015-01-27
12.避免夸时区的备份(skip-tz-utc) mysqldump -h localhost -uroot -ptest oss03 fact_app_info_his --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --where=" inserttime>='2017-03-20 08:00:00' and inserttime<'2017-03-20 08:05:00'">/home/richdm/sbin/fact_app_info_his.sql
13.避免主从设置了gtid的备份(set-gtid-purged=OFF)
mysqldump -h localhost -uroot -ptest oss03 fact_app_info_his --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --where=" inserttime>='2017-03-20 08:00:00' and inserttime<'2017-03-20 08:05:00'">/home/richdm/sbin/fact_app_info_his.sql
14.指定查询条件的备份(--where)
mysqldump -h localhost -uroot -ptest oss03 fact_app_info_his --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --where=" inserttime>='2017-03-20 08:00:00' and inserttime<'2017-03-20 08:05:00'">/home/richdm/sbin/fact_app_info_his.sql
其他条件导出:
mysqldump -h localhost -uroot -pmysql db_paper inoculate_record --default-character-set=utf8 --skip-tz-utc --where=" mod(vcc_id,16)+1 =10">/tmp/inoculate_record_mod.sql
15.只导出表数据，不导出创建表结构(-t –no-create-info)
mysqldump -h localhost -uroot -ploVe*maIl139 -t oss03 fact_app_info --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --where=" inserttime>='2017-03-20 00:00:00' and inserttime<'2017-03-21 00:00:00'">/tmp/fact_app_info.sql
16.导出文件去掉DISABLE KEYS/ENBLE KEYS输出(--skip-disable-keys)
mysqldump -h localhost -uroot -ploVe*maIl139 -t oss03 tmp_hxl_test --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --skip-disable-keys --skip-add-locks>/tmp/tmp_hxl_test.sql
17.导出文件去掉LOCK TABLES/UNLOCK TABLES(--skip-add-locks)
mysqldump -h localhost -uroot -ploVe*maIl139 -t oss03 tmp_hxl_test --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --skip-disable-keys --skip-add-locks>/tmp/tmp_hxl_test.sql
18.不锁表备份(--lock-tables=false)
/home/richdm/mysql/bin/mysqldump --no-data -h 10.217.109.67 -urichdm -pKTm2Lgd --routines --events --default-character-set=utf8 --set-gtid-purged=OFF --lock-tables=false --databases oss03 ngoss_dim monitor_user terminal_etl>/home/richdm/sbin/mydump0425.dump
19.只导出存储过程和函数(不导出表和视图)
/home/richdm/mysql/bin/mysqldump -h localhost -uroot -poxeW4nZ2 -R -ndt --default-character-set=utf8 --lock-tables=false --set-gtid-purged=OFF --databases oss03>/home/richdm/sbin/oss03_proc.dump
20.模糊匹配导出表
./mysqldump -h localhost -uroot -poxeW4nZ2 ngoss_dim $(/home/richdm/mysql/bin/mysql -h localhost -uroot -poxeW4nZ2 -D ngoss_dim01 -Bse "show tables from ngoss_dim where tables_in_ngoss_dim like 'web%'") --default-character-set=utf8 --set-gtid-purged=OFF --skip-disable-keys --skip-add-locks --lock-tables=false>/home/richdm/sbin/web01.sql
$(/home/richdm/mysql/bin/mysql -h localhost -uroot -poxeW4nZ2 -D ngoss_dim01 -Bse "show tables from ngoss_dim where tables_in_ngoss_dim like 'web%'"
21. -e选择项导出一个value写多个值的格式
mysqldump -h localhost -urdcs -prichinfo_data1 rdcs sys_dept -e --max_allowed_packet=1048576 --net_buffer_length=16384>/tmp/a.txt
形式如下：
INSERT INTO `sys_dept` VALUES (2,'井冈山'),(3,'河津'),(1,'系统管理'),(9,'阳西'),(6,'昱光'),(8,'蒲州'),(12,'环保排放'),(13,'井冈山2')
22.导出大对象blob --hex-blob
./mysqldump -h localhost -uroot -pmysql --hex-blob hadoop_terminal example tb_test02>/tmp/example01.dump
其中表example 含有大对象字段
|
mysql> show create table example; | Table | Create Table | example | CREATE TABLE `example` ( `name` varchar(100) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `image` longblob, `Phone` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | |
23. 导出的insert语句带上字段 -c
./mysqldump -h 192.168.56.101 -uroot -pmysql --skip-disable-keys --skip-add-locks --lock-tables=false -c oss03 tb_user_murmur_string_t01>/tmp/tb_user_murmur_string_t01_101.dump
这个在导入到mycat环境比较管用.
24.导出某个数据库下的所有表，文件不包含create database语句
mysqldump -h localhost -uroot -ptestpw zentao $(mysql -h localhost -uroot -ptestpw -P3306 -D zentao -Bse "show tables from zentao") --default-character-set=utf8 --hex-blob >/soft/zentao0523_03.dump
25.加快导出速度(max_allowed_packet + net_buffer_length)
mysqldump -h host01 -udmladmin -pmypwd db_yimiao vcc_inoculate_log --default-character-set=utf8 --max_allowed_packet=16777216 --net_buffer_length=16384 --no-create-info --skip-disable-keys --skip-add-locks --lock-tables=false -e --where=" mod(child_id,64)+1 =1">/home/yeemiao/dumpbak/vcc_inoculate_log_mod1_03.dump
导出完成要是修改导入的表名称，则要替换
sed 's/vcc_inoculate_log/vcc_inoculate_log01/g' vcc_inoculate_log_mod1_05.dump>vcc_inoculate_log_mod1_05.dumplast
经过验证发现这两个参数没有什么效果，而且导出的文件里面会出现多个insert语句
(insert values(),()
insert values(),()
)
26.--skip-extended-insert
mysqldump -h localhost -uroot -pyeemiao1117 forum forum_topic --default-character-set=utf8mb4 --skip-extended-insert --hex-blob >/soft/forum_topic03.sql
该参数导出的文件为多个insert into values的形式出现
如:
insert into tb_test values(x,x,x);
insert into tb_test values(x,x,x);
insert into tb_test values(x,x,x);
mysql -h locahost -uhxl -pxxxxxxxxx --skip-column-names -D db_test -e "SELECT CONCAT(fchildno,',',1000*UNIX_TIMESTAMP(sync_time)) as cdata FROM child WHERE fchildno >1 AND fc
hildno < 100" 2>/dev/null 1>/tmp/dataoutput_data.txt
---------------------------------恢复------------------------------------------
drop database test;
mysql -h hxl -uroot -pmysql < /mysqlbak/all_db_bak.sql
2.恢复单个表
LOAD DATA INFILE 'd:\tb_course.sql' INTO TABLE tb_course;
3.压缩文件恢复和非压缩文件恢复比较
压缩文件恢复数据库
没有关闭日志,all_db_r.sql.gz文件大小是1.8G,解压缩后是23G
[mysql@node2 bak]$ time gunzip -c all_db_r.sql.gz|mysql -h node2 -uroot -p
Enter password:
ERROR 1418 (HY000) at line 24318: This function has none of
DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
real 76m17.424s
user 7m6.085s
sys 1m16.625s
解决办法:
1.mysql> SET GLOBAL log_bin_trust_function_creators = 1;
2.系统启动时(my.cnf) --log-bin-trust-function-creators=1
未压缩文件恢复数据库
[mysql@node2 bak]$ time mysql -h node2 -uroot -p
Enter password:
real 89m47.731s
user 5m50.550s
sys 0m39.742s
4.source方式恢复
set sql_log_bin=0; -- 关闭binlog日志
source /dbext/mysql/bak/all_db_r.sql
set sql_log_bin=1; -- 恢复完成后 开启
5.导入有创建函数的dump文件
source /dbext/mysql/bak/all_db_r.sql
6.导出是用utf8导出的，导入也要采用utf8
./mysql -h localhost -uroot -pmysql --default-character-set=utf8 -D ngoss_dim
7.若导出的文件里含有gtid，则需要加上-f参数导入 ./mysql -h localhost -uroot -pmysql --default-character-set=utf8 -D db_paper -f
8.通过select into生成文本后导入
secure_file_priv='' ##该参数设置可以导入，没有指定目录
./mysql -h localhost -uroot -pmysql --default-character-set=utf8 -D
db_paper -e "SELECT concat('insert into
user_paper_snapshot_record01(child_code,consent_paper_content) values
(','''',child_code,'''',',','UNHEX(','''',hex(consent_paper_content),'''',')',');') FROM user_paper_snapshot_record into outfile '/tmp/g.txt'"
mysql> source /tmp/g.txt;