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.
