Backup And Restore DATABASE or TABLE in MySQL

MySQL

In this article, I will guide you through some commands to backup and restore MySQL database or a table in the database.

MySQL – Backup statement

To back up the database or its a table we use the command mysqldump

#Back up the database
$ mysqldump -u [username] -p [dbname]> filename.sql

#Back up a table
$ mysqldump -u [username] -p [dbname] [table "" not found /]

 > filename.sql

#Back up multiple tables
$ mysqldump -u [username] -p [dbname] [table1] [table2]> filename.sql

#Back up the database and compress
$ mysqldump -u [username] -p [dbname] | gzip> filename.sql.gz

For example

Backup database “izzidb” to file web_db_20180727.sql

$ mysqldump -u remoteuser -p izzidb> web_db_20180727.sql
Enter password:

Backup and compress to .gz file

$ mysqldump -u remoteuser -p izzidb | gzip> web_db_20180727.sql.gz
Enter password:

Backup table ‘wp_postmeta’ to file ‘wp_postmeta.sql’

$ mysqldump -u remoteuser -p izzidb wp_postmeta> wp_postmeta.sql
Enter password:

# Backup and compress to gz file
$ mysqldump -u remoteuser -p izzidb wp_postmeta | gzip> wp_postmeta.sql.gz

# Backup multiple tables including wp_postmeta and wp_posts
$ mysqldump -u remoteuser -p izzidb wp_postmeta wp_posts> tables.sql

MySQL – Recovery statement

To recover database or table we use the following command:

# Recover from .sql file using mysql command
$ mysql -u [username] -p [dbname] <filename.sql

# Recover from gz archive
$ gunzip -c filename.sql.gz | mysql -u [username] -p [dbname]

Note:  You avoid confusion between the command mysql and mysqldump

For example

Recover file web_db_20180727.sql into database ‘izzidb’.

$ mysql -u remoteuser -p izzidb <web_db_20180727.sql
Enter password:

# Recover table
$ mysql -u remoteuser -p izzidb <wp_postmeta.sql

Recover archive gzip web_db_20180727.sql.gz into database ‘izzidb’

$ gunzip -c web_db_20180727.sql.gz | mysql -u remoteuser -p izzidb
Enter password:

Good luck.