Using mysqldump for backups

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_name

mysqldump --all-databases > dump.sql

mysqldump --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 ot 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.sql

mysqladmin create db2

mysql 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.sql

mysqldump --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