mysql备份基本参数

-A 全备参数

例子1:

[root@db01 ~]# mkdir -p /data/backup
mysqldump -uroot -p -A >/data/backup/full.sql
Enter password:

mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
# [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
# [root@db01 ~]# mysqldump -uroot -p123 -A    --set-gtid-purged=ON >/backup/full.sql

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

-B 备份单个数据库

说明：生产中需要备份，生产相关的库和MySQL库
例子2 :

mysqldump -B mysql gtid --set-gtid-purged=OFF >/data/backup/b.sql

 

备份单个或多个表

例子3  world数据库下的city,country表

mysqldump -uroot -p world city country >/backup/bak1.sql

以上备份恢复时:必须库事先存在,并且ues才能source恢复 

 

对数据库进行全表备份

mysql -uroot -p123

select concat("mysqldump -uroot -p123 -S /tmp/mysql.sock ",table_schema," ",table_name,">/backup/",table_schema,"_",table_name,".sql") from infomation_schema.tables where table_schema not in('sys','information_schema','performance_schema') into outfile '/tmp/mysqldump.sh

sh /tmp/mysqldump.sh

tar -zcf /mysqlbk/$(date +%T-%Y%m%d).tar.gz /backup/
rm -rf /backup/*