mysql备份和恢复

环境:

OS:AIX 5.3

DB:MYSQL 5.1

 

-----------------------------备份----------------------------------------

1.dump备份数据库

dump备份db1数据库

#mysqldump -uroot -pmysql -h hxl db1 >/mysqlbak/hxl_db_dump_20121208

备份数据库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

3.备份后压缩

mysqldump -uroot -pmysql -h hxl db1|gzip>/mysqlbak/hxl_db_dump_20121208.gz

 

4.备份数据库中的表,备份hxl数据库中的表authors和books.

mysqldump -uroot -pmysql -h hxl db1 authors books >/mysqlbak/hxl_db_tables.bak

 

5.备份多个数据库

mysqldump -h hxl -uroot -pmysql --databases db1 db2>/mysqlbak/hxl_test_db_bak.sql

 

6.仅仅备份结构

mysqldump --no-data -h hxl -uroot -pmysql --databases db1 db2>/mysqlbak/no_data_hxl_test_db_bak.sql

 

7.备份服务器上的所有数据库

mysqldump -h hxl -uroot -pmysql --all-databases >/mysqlbak/all_db_bak.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 '\n'
  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);

27.不带删除语句导出--skip-add-drop-table

mysqldump -h localhost -uroot -pyeemiao1117 db_admin tb_user_token --skip-add-drop-table=false --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --skip-disable-keys --skip-add-locks>/tmp/tb_user_token01.dump


---------------------------------恢复------------------------------------------

1.还原数据库

drop database hxl;
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文件

set global log_bin_trust_function_creators =1; 

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'"

这里consent_paper_content是longblob类型，导出的时候才要hex 16进制导出,insert导入的时候需要才要unhex导入

mysql> source /tmp/g.txt;