7.4 Using mysqldump for backups
mysql 官网
感觉这个脚本有点厉害
还有这个逼的博客
a dump file used in several ways
- enable data recovery in case of data loss
- data for setting up replicas
- data for experimentations:
- use without changing the original data
- to test potential upgrade incompatibilities
7.4.1 dumping data in sql format whith mysqldump
mysqldump [arguments] > file_namemysqldump --all-databases > dump.sqlmysqldump --databases db1 db2 db3 > dump.sql
–all-databases and --databases write create database and use statements
–add-drop-database write drop database statement
mysqldump test t1 t3 t7 > dump.sql
dump only specific tables from a database
any set @@global.gtid_purged statement in the second dump file fails.
to avoid this issue, either set option --set-gtid-purged to off
or ccommented to output the second dump file whithout an active set @@global.gtid_purged statement,
or remove the statement manually before replaying the dump file
7.4.2 reloading sql-format bakups
mysql < dump.sql
or enter mysql
mysql shell > source dump.sql
7.4.5 mysqldump tips
中间有2小节我不想看，我不想用这个技术
7.4.5.1 making a copy of a database
mysqldump db1 > dump.sqlmysqladmin create db2mysql db2 < dump.sql
不要使用 --databases sql 脚本会有 use statement
7.4.5.2 copy a database from one server to Another
记得切换 ip
7.4.5.3 dumping stored programs
stored procedures and functions, triggers and events
- –events: event scheduler events
- –routines: stored procedures and functions
- –triggers: triggers for tables
disabled by default
7.4.5.4 dumping table definitions and content separately
mysqldump --no-data test > dump-defs.sqlmysqldump --no-create-info test > dump-data.sql
7.4.5.5 using mysqldump to test for upgrade or downgrade incompatibilities
proc
mysqldump --all-databases --no-data --routines --events > dump-defs.sql
looking for warnings or errors while the dump file is being processed
proc
mysqldump --all-databases --no-create-info > dump-data.sql