mysql dump 使用示例

原创
2018/06/14 18:04
阅读数 376

1、mysql 备份

Usage: mysqldump [OPTIONS] database [tables]

OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

--no-create-info 只导数据

--default-character-set=utf8 指定字符集

--single-transaction innodb一致性备份

--opt Same as --add-drop-table, --add-locks, --create-options,

--quick, --extended-insert, --lock-tables, --set-charset,

and --disable-keys. Enabled by default, disable with --skip-opt.

备份test 数据库

mysqldump -udevuser -p123456 test > backup.sql;

 

只导出表结构

mysqldump -udevuser -p123456 --no-data test > backup.sql;

mysqldump --opt -d ota -u root -p > ota.sql

 

mysqldump默认是会lock-tables的, 所以生产环境切记.

/usr/local/mysql/bin/mysqldump --single-transaction -ubackup -pbackuptomcat -h127.0.0.1 -R -B bayg | zip >/home/mysqlbackup/mysqldump/batdb`date

+%F`ALL.SQL.zip

mysqldump --single-transaction -uroot -pxxx -h127.0.0.1 -R -B bigdata > bigdata.sql

 

先drop DB语句,接着create DB --add-drop-database

先drop table, 接着create table --add-drop-table 

导出并压缩

time mysqldump store | xz >store_2014-7-4.xz 

time mysqldump store | gzip >store_2014-7-4.gz 

time mysqldump store | zip >store_2014-7-4.zip

mysqldump -udevuser -p123456 --no-create-info test | gzip> backup.gz;

mysqldump -udevuser -p123456 --no-create-info  --default-character-set=utf8  swadmin > swadmin.sql

 

解压gz

gzip -d backup1.gz

unxz -c database.sql.xz | mysql  database   #解压缩,并导入到数据库

automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.

mysqldump --all-databases --master-data >dbdump.db

 

只导出数据库里的一张表

mysqldump  payment t_payment_trade_record > backup_t_payment_trade_record.sql

只导出数据库里的两张表

mysqldump -udevuser -p123456  payment t_payment_user_account t_payment_trade_record > trade_record.sql

 

完全备份所有数据库中的所有InnoDB表:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

 

压缩并导入到别的数据库

mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db

亿级的数据导入mysql

 

本地数据库复制,并排除一张表

mysqldump store  --ignore-table=store.t_widget_download_log | mysql store_skyworth

单事务无锁导出表

/usr/local/mysql/bin/mysqldump --single-transaction -ubackup -pbackuptomcat -h127.0.0.1 -R -B bayg --ignore-table=bayg.MESSAGE_RECEIVER --ignore-table=bayg.MESSAGE_RECEIVER_BACKUP |zip >/home/mysqlbackup/mysqldump/batdb_20180109.sql.zip &

 

/usr/local/mysql/bin/mysqldump --single-transaction -ubackup -pbackuptomcat -h127.0.0.1 -R -B bayg |zip >/home/mysqlbackup/mysqldump/batdb `date +%F`ALL.SQL.zip && find /home/mysqlbackup/mysqldump -ctime +6 -type f -name "*SQL.zip" -exec rm -rf {} \;

 

排除多张表

mysqldump wear --ignore-table=wear.t_video_file --ignore-table=wear.t_wear_active --ignore-table=wear.t_wear_location > test.sql

导出数据时,insert后面出现所有列名

mysqldump --complete-insert test > /tmp/test.sql

备份多个数据库

mysqldump  -uroot -p123456 --databases admin skymall --complete-insert --add-drop-database --add-drop-table |zip > skymall_and_admin_2014-12-17.sql.zip

mysqldump -udevuser -h192.168.52.17 -p123456 --databases sw2_admin sw2_qmz --complete-insert --add-drop-database --add-drop-table |zip > qmz_admin_2016-7-22.sql.zip

 

导出某张表的部分记录

select * from temp2 into outfile 'c:\\temp2.sql' fields terminated by ',' lines terminated by '\n';

导出表的部分字段,要确保对目标目录的访问权限:

SELECT id,name,version,filePath INTO OUTFILE '/usr/local/dev/ota_part.txt' FIELDS TERMINATED BY ',' FROM t_sw_phonesw;

 

mysql -uroot -p -e "show databases" | grep -Ev "mysql|test|Database|information_schema|performance_schema"| xargs mysqldump -uroot -pmipt12345 --databases > database_20151120.sql

 

sql语句导出

和load data 相反的语句

select * 

into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n'

from test_info where id < 1000;    

 

数据库迁移

mysqldump db1 -u root -ppassword --add-drop-table | mysql newdb -u root -ppassword

 

使用mysqldump来搭建slave环境

https://www.2cto.com/database/201510/447516.html

 

--dump-slave /master-data

=1 自动执行 =2 手工执行, 默认等于1

 

--master-data 用于在master端dump数据,用于建立slave

--dump-slave 用户在slave端dump数据,建立新的slave,至少是第2个slave

--apply-slave-statements 在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。

--include-master-host-port

 

zlib_decompress

https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/zlib-decompress.html

4.8.5 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output

The zlib_decompress utility decompresses mysqlpump output that was created using ZLIB compression. zlib_decompress was added in MySQL 5.7.10.

Invoke zlib_decompress like this:

shell> zlib_decompress input_file output_file

Example:

shell> mysqlpump --compress-output=ZLIB > dump.zlib shell> zlib_decompress dump.zlib dump.txt

To see a help message, invoke zlib_decompress with no arguments.

To decompress mysqlpump LZ4-compressed output, use lz4_decompress. See Section 4.8.1, “lz4_decompress — Decompress mysqlpump LZ4-Compressed Output”.

 

 

 

 

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部