MySQL常用脚本_mysqldump

常用命令

备份整个实例(dump全实例)

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --single-transaction  --column-statistics=0   --skip_add_locks --skip-lock-tables --master-data=2  -A | gzip > /data/mysqlbackup/dboop_dump`date '+%m-%d-%Y'`.sql.gz

备份实例中的用户库(用于实例迁移或升级)

mysql -uroot -p123456 -h127.0.0.1 -P3306 -e "show databases" |grep -Ev "Database|information_schema|mysql|performance_schema"  | xargs mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --single-transaction  --column-statistics=0   --skip_add_locks --skip-lock-tables --master-data=2 --databases > /data/mysqlbackup/dboop_dump0401.sql

-- 此时mysql.user用户也没有迁移过来,如果需要迁移用户,参考:

https://www.dboop.com/mysql/mysql%E5%B8%B8%E7%94%A8%E8%84%9A%E6%9C%AC_%E7%94%A8%E6%88%B7%E7%9B%B8%E5%85%B3/

导出db1、db2两个数据库的所有数据

mysqldump -uroot -p123456 --set-gtid-purged=OFF  --skip_add_locks --skip-lock-tables --databases db1 db2 > /data/mysqlbackup/dboop_dump0401.sql

导出db1中的a1、a2表

mysqldump -uroot -p123456 --set-gtid-purged=OFF  --skip_add_locks --skip-lock-tables --databases db1 --tables a1 a2  > /data/mysqlbackup/dboop_dump0401.sql

导出db1表a1中id=1的数据

mysqldump -uroot -p123456 --set-gtid-purged=OFF  --skip_add_locks --skip-lock-tables  --databases db1 --tables a1 --where='id=1'  > /data/mysqlbackup/dboop_dump0401.sql

只导出表结构不导出数据,–no-data

mysqldump -uroot -p123456 --set-gtid-purged=OFF  --skip_add_locks --skip-lock-tables --no-data  --databases db1 > /data/mysqlbackup/dboop_dump0401.sql

导出指定表数据(不要表结构)

mysqldump -uroot -p123456 –set-gtid-purged=OFF –skip_add_locks –skip-lock-tables –databases db1 –tables a1 >/data/mysqlbackup/dboop_dump0401.sql

常见报错

A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions

mysqldump:Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM..

mysqldump参数

mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1 参数2.... > 备份文件.sql

–all-databases , -A (全部库)

mysqldump -uroot -p123456  --all-databases  > /data/mysqlbackup/dboop_dump0401.sql

mysqldump -uroot -p123456  -A  > /data/mysqlbackup/dboop_dump0401.sql

–add-drop-database (drop库)

–databases, -B (指定库)

mysqldump -uroot -p123456  --databases test1 test2  > /data/mysqlbackup/all2.sql

–no-create-db, -n (不创建库)

mysqldump -uroot -p123456  --host=localhost -A --no-create-db > /data/mysqlbackup/dboop_dump0401.sql

–no-create-info, -t (不创建表)

mysqldump -uroot -p123456  --host=localhost -A --no-create-info > /data/mysqlbackup/dboop_dump0401.sql

–no-data, -d (不导出数据,仅结构)

mysqldump -uroot -p123456  --host=localhost -A --no-data > /data/mysqlbackup/dboop_dump0401.sql

–routines, -R (存储过程/函数)

mysqldump -uroot -p123456  --host=localhost -A --routines > /data/mysqlbackup/dboop_dump0401.sql

–tables (表)

mysqldump -uroot -p123456  --host=localhost --databases test1 --tables hd_acl_entry > /data/mysqlbackup/dboop_dump0401.sql

–triggers (触发器)

mysqldump -uroot -p123456  --host=localhost -A --triggers > /data/mysqlbackup/dboop_dump0401.sql

mysqldump -uroot -p123456  --host=localhost -A -skip-triggers > /data/mysqlbackup/dboop_dump0401.sql

–add-drop-table (drop表)

# 默认添加drop语句
mysqldump -uroot -p123456  -A > /data/mysqlbackup/dboop_dump0401.sql

# 取消drop语句 
mysqldump -uroot -p123456  -A --skip-add-drop-table  > /data/mysqlbackup/dboop_dump0401.sql

–ignore-table (忽略表)

mysqldump -uroot -p123456  --host=localhost -A --ignore-table=test1.a1  --ignore-table=test1.a2 > /data/mysqlbackup/dboop_dump0401.sql

–add-locks (加锁)

# 默认添加LOCK语句
mysqldump -uroot -p123456  -A  > /data/mysqlbackup/dboop_dump0401.sql

# 取消LOCK语句 
mysqldump -uroot -p123456  -A --skip-add-locks   > /data/mysqlbackup/dboop_dump0401.sql

–lock-all-tables, -x (锁表)

mysqldump -uroot -p123456  --host=localhost -A --lock-all-tables > /data/mysqlbackup/dboop_dump0401.sql

–lock-tables, -l (锁表)

mysqldump -uroot -p123456  --host=localhost -A --lock-tables > /data/mysqlbackup/dboop_dump0401.sql

–single-transaction (一致性)

ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
会自动关闭–lock-tables
mysqldump -uroot -p123456  --host=localhost -A --single-transaction > /data/mysqlbackup/dboop_dump0401.sql

–compatible (兼容)

# 要使用几个值,用逗号将它们隔开。
mysqldump -uroot -p123456  -A --compatible=ansi  > /data/mysqlbackup/dboop_dump0401.sql

–default-character-set (字符集)

mysqldump -uroot -p123456  -A --default-character-set=utf8 > /data/mysqlbackup/dboop_dump0401.sql

—master-data (主从信息)

mysqldump -uroot -p123456   -A --master-data=2  > /data/mysqlbackup/dboop_dump0401.sql

–events, -E (事件)

mysqldump -uroot -p123456  -A --events > /data/mysqlbackup/event.sql

–fields-terminated-by (忽略字段)

mysqldump -uroot -p123456  dboop table1 --tab="/data/mysqlbackup/" --fields-terminated-by="nickname"  

–hex-blob (二进制)

mysqldump -uroot -p123456  -A --hex-blob > /data/mysqlbackup/dboop_dump0401.sql

–insert-ignore (重复值)

mysqldump -uroot -p123456  --host=localhost -A --insert-ignore > /data/mysqlbackup/dboop_dump0401.sql

–replace (replace into )

mysqldump -uroot -p123456  --host=localhost -A --replace > /data/mysqlbackup/dboop_dump0401.sql

—extended-insert

mysqldump -uroot -p123456  -A > /data/mysqlbackup/dboop_dump0401.sql

# 取消选项 
mysqldump -uroot -p123456 -A --skip-extended-insert > /data/mysqlbackup/dboop_dump0401.sql

–log-error (输出日志)

mysqldump -uroot -p123456    -A --log-error=/data/mysqlbackup/error_log.err  > /data/mysqlbackup/dboop_dump0401.sql

–max_allowed_packet

mysqldump -uroot -p123456  -A --max_allowed_packet=10240 > /data/mysqlbackup/dboop_dump0401.sql

–set-charset (字符集)

mysqldump -uroot -p123456  --host=localhost -A > /data/mysqlbackup/dboop_dump0401.sql

 
mysqldump -uroot -p123456  --host=localhost -A --skip-set-charset > /data/mysqlbackup/dboop_dump0401.sql

–opt (一些组合)

–add-drop-table
–add-locks
–create-options
–quick
–extended-insert
–lock-tables
–set-charset
–disable-keys

默认开启, 可以用-skip-opt禁用
mysqldump -uroot -p123456  --host=localhost -A --opt > /data/mysqlbackup/dboop_dump0401.sql

mysqldump -uroot -p123456  --host=localhost -A -skip-opt > /data/mysqlbackup/dboop_dump0401.sql

–quick, -q

mysqldump -uroot -p123456  --host=localhost -A > /data/mysqlbackup/dboop_dump0401.sql

 
mysqldump -uroot -p123456  --host=localhost -A --skip-quick > /data/mysqlbackup/dboop_dump0401.sql

–result-file, -r (指定结果文件)

mysqldump -uroot -p123456 --host=localhost -A -result-file=/data/mysqlbackup/result_file.txt > /data/mysqlbackup/dboop_dump0401.sql

–where, -w (指定where)

mysqldump -uroot -p123456  --host=localhost -A --where="id='1'"  > /data/mysqlbackup/dboop_dump0401.sql

已dump完成的文件,分离指定的库表

cd /data/soft
wget  https://www.dboop.com/download/mysqldumpsplitter.sh
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract DB --match_str dboop
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract TABLE --match_str tbtest1
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract ALLDBS

上面的命令将从指定的“dboop_dump0401.sql”mysqldump文件中提取所有数据库,并将其以压缩格式存储到单个以库名命名的gz文件中。

sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract ALLTABLES

经验值

>> Home

51ak

2021/04/04

Categories: mysql 常用脚本 Tags: 基础

《数据库工作笔记》公众号
扫描上面的二维码,关注我的《数据库工作笔记》公众号