It is fairly easy and (unfortunately) common that something will eventually happen to our data. Whether it is a mistake you make by deleting some (or all) of your data, a hacker gains control of your system or your server hard drive dies and all the data with it, you should be concerned with data integrity.  Without proper backups, you could lose some or all of your data which equates to your time being wasted from all the effort you put into the lost data.

Fortunately, MySQL makes it easy for an admin to backup databases. mysqldump is a  utility that comes with MySQL specifically for “dump” databases to a file. Here is how it works:

mysqldump -u [username] -h localhost -p[password] [database]  > file.sql

Replace all of the values in brackets [] with your values. There is no space between -p and [password].

Specific Tables:

mysqldump -u [username] -h localhost -p[password] [database] [tables]  > tables.sql

All Databases

mysqldump -u [username] -h localhost -p[password] --all-databases  > all_databases.sql

Multiple Databases

mysqldump -u [username] -h localhost -p[password] --databases [DB1] [DB2]  > databases.sql

Restoring from a MySQL Backup

Restoring a MySQL dump file is just as easy and dumping the data to a SQL file. You use MySQL command line to do it. You could also restore these dump files via a GUI such as phpMyAdmin (import/export).

mysql -u [username] -h localhost -p[password]  < file.sql

Restoring Specific Tables

mysql -u [username] -h localhost -p[password]  [database] < tables.sql

Restoring All Database Dump

mysql -u [username] -h localhost -p[password] < all_databases.sql.sql

Restoring Multiple Database Dump

mysql -u [username] -h localhost -p[password] < databases.sql.sql

Possibly related posts: (automatically generated)