Backup and Restore Mysql Database

Backup your database

mysqldump -uusername -ppassword databasename > backupfile.sql

Add drop table - This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump –add-drop-table -uusername -ppassword databasename > backupfile.sql

Directly compress data from a database

mysqldump -uusername -ppassword databasename | gzip > backupfile.sql.gz

Back up only specified tables

mysqldump -uusername -ppassword databasename specific_table1 specific_table2 >
backupfile.sql

Backup multiple databases

mysqldump -uusername -ppassword –databases databasename1 databasename2
databasename3 > multibackupfile.sql

Back up only the database structure

mysqldump –no-data –databases databasename1 databasename2 databasename3 >
structurebackupfile.sql

Backup up all databases on a server

mysqldump –all-databases > allbackupfile.sql

Restore database

mysql -uusername -ppassword databasename <>

Restoring zipped files

gunzip <>

Moving database to a new server

mysqldump -uusername -ppassword databasename | mysql –host=66.54.55.56 -C
databasename

Without Shell Access

Use phpMyAdmin

Backup a database

Select the database in the left navigation frame, which should bring up the
database details in the main frame

Select the Export tab

Under Export, select SQL

Select Structure if you’ll be using the data to create new tables

Select Add drop table if the data is intended to completely replace tables

Select Data

Select Save as File

Select your preferred compression format (none, zipped, gzipped, or bzipped)

Click Go (bottom-right)

The mysql data should download to your browser’s default download destination.

Restore a database

Select the SQL tab in the main phpMyAdmin frame

Click Choose File

Locate the SQL file on your computer

Once you’ve selected your import file, click Go

If you get any Table already exists errors while importing data, resave the sql with the Add drop table option checked, or simply delete the conflicting tables and run the import again.

If you encounter any problem, drop a comment and I will try to solve.