MySQL

Compiled by Roberto Espinoza at www.cpccci.com

CPC Computer Consultants, Inc.

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 < backupfile.sql

Restoring zipped files

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

Moving database to a new server

mysqldump -uusername -ppassword databasename | mysql –host=ip address -C databasename