MySQL Backup and restore common commands

1.3. MySQL Backup and Recovery

1. MySQL Backup

# mysqldump-uroot-pqweasd-s /data/3307/mysql.sock DB8 >/backup/3307-db8.sql

# mysqldump-uroot-pqweasd-s/data/3307/mysql.sock db8 >/backup/3307-db8_ $ (date +%f) . SQL

# mysqldump-uroot-pqweasd-s/data/3307/mysql.sock DB8 |bzip2 >/backup/3307-db8_$ (Date +%f). sql. bz2

# mysqldump-uroot-pqweasd-s/data/3307/mysql.sock DB8 |gzip >/backup/3307-db8_$ (Date +%f). sql. GZ

# MYSQLDUMP-UROOT-PQWEASD --default-character-set=utf8 -s/data/3307/mysql.sock DB8 >/backup/3307-db8 -utf8 _$ (Date +%f). SQL

# mysqldump-uroot-pqweasd--default-character-set=utf8-s/data/3307/mysql.sock DB8 |bzip2 >/backup/3307-db8-utf8_$ (Date +%f). sql. bz2

Backup tables, multiple tables

# MYSQLDUMP-UROOT-PQWEASD--default-character-set=utf8-s/data/3307/mysql.sock MySQL user >/backup/ Mysql_user -utf8_$ (Date +%f). SQL

# mysqldump-uroot-pqweasd--default-character-set=utf8-s/data/3307/mysql.sock MySQL User Host >/backup/mysql _user_host -utf8_$ (Date +%f). SQL

-B recovery creates the database and enters the created database, no-b. If the library to be recovered is deleted, it is not possible to recover the restored library if it needs to be recreated

# MYSQLDUMP-UROOT-PQWEASD--default-character-set=utf8-s/data/3307/mysql.sock - b DB8 >/backup/3307-db8 - b -utf8_$ (Date +%f). SQL

-B Backing up multiple library restores creates a database and enters the created database

# Mysql-uroot- Pqweasd-s/data/3307/mysql.sock-b DB8 DB9 | bzip2 >/backup/3307- DB8-DB9 -B_$ (date +%f). sql.bz2

# MYSQL-UROOT-PQWEASD --default-character-set=utf8 -s/data/3307/mysql.sock-b DB8 db9|bzip2 >/backup/3307-db8-db9-b- UTF8 _$ (Date +%f). sql.bz2

-D backing up the database structure (no data included)

# MYSQLDUMP-UROOT-PQWEASD--default-character-set=utf8-s/data/3307/mysql.sock - D DB8 >/backup/db8 - D -utf8_$ (Date +%f). SQL

--compact make backup the output more concise, less backup results some default options and various comments

# MYSQLDUMP-UROOT-PQWEASD --compact -s/data/3307/mysql.sock DB8 >/backup/3307-db8.sql

# MYSQLDUMP-UROOT-PQWEASD--default-character-set=utf8 --compact -s/data/3307/mysql.sock-d DB8 >/backup/db8-d-utf8_$ (Date +%f). SQL

2. MySQL Recovery

mysql> SOURCE/BACKUP/3307-DB8 - b -utf8_2018-05-12.sql;

When you do not have the use DB Word in the SQL file, you will need to specify the database name when importing

# mysql-uroot-pqweasd-s/data/3307/mysql.sock DB8 </backup/3307-db8_2018-05-12.sql

MySQL Backup and restore common commands