-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/*