"MySQL" mysqldump Backup and Recovery

Introduction:
Mysqldump is commonly used in MySQL database logical backups.

Backup operation:

• 1. Back Up all libraries:

  mysqldump -uroot -pyourpass --all-databases > /tmp/all.dump

• 2. Back up a single library:

  mysqldump -uroot -pyourpass test > test.dump

• 3. Backup a single sheet:

  mysqldump -uroot -pyourpass test test_tb > test_tb.sql

• 4. Backup stored procedures:

  mysqldump -uroot -pyourpass -n -d -t -R test > test_procedure.sql

Restore operation:
Ensure that the database to be recovered exists before recovery

• To restore a single library:

  mysql -uroot -pyourpass test < test.dump

  or enter the database to execute the source test.dump;

• To restore a single table from a backup of the whole library:

  cat test.dump | sed -e‘/./{H;$!d;}‘ -e ‘x;/CREATE TABLE `test_tb`/!d;q‘ > /tmp/test_tb.sql  --筛选出建表语句cat test.dump | grep --ignore-case  ‘insert into `test_tb`‘ > /tmp/insert_test_tb.sql  --筛选插入数据的insert语句

  You can then restore a single table by executing the appropriate SQL in the database as needed.

"MySQL" mysqldump Backup and Recovery