Backup and Restore MySQL Database Using mysqldump
PhpMyAdmin can be used to export or backup MySQL databases easily. However, if the database size is very big, it probably won’t be a good idea. This process slow the exporting process, increase database locking time and thus MySQL unavailability, slow the server and may simply crash the Apache HTTPD server if too many incoming web connections hogging the system’s resources.
The better way to backup and export MySQL database is by doing the task locally on the server, so that the tables’ data can be instantly dumped on the local disk without delay. Thus export speed will be faster and reduce the time MySQL database or table is locked for accessing. This tutorial is the guide on how to backup (export) and restore (import) MySQL database(s) on the database server itself by using the mysqldump and mysql utilities. Below tutorial will concentrate on mysqldump which works for both MyISAM and InnoDB tables.
mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database, execute the *.sql file on destination database.
How to Export or Backup or Dump A MySQL Database
To export a MySQL database into a dump file, simply type the following command syntax in the shell.
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
How to Export A MySQL Database Structures Only
If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures. For example, the syntax is:
mysqldump -u username -ppassword –no-data database_name > dump.sql
How to Restore and Import MySQL Database
mysql -u root -p[root_password] [database_name] < dumpfilename.sql
Some useful backup and restore instructions for a set of databases are:
– Backup all the databases:
# mysqldump -u root -ptmppassword --all-databases > /save/all-database.sql
– Restore all databases :
To restore from .sql file follow this command :
# mysql –user=xxxx –password=xxxx < all_databases_save.sql
The import and export of MySQL database not only is important to recover the data when disaster strikes, but also provides an easy way to migrate or move to another server, such as when switching web hosting providers.
However, do note that one common problem – character set encoding. Newer release of mysqldump uses UTF8 as its default charset if nothing is specified, while older versions (older than 4.1 typically) use Latin1 as default characterset.
If you database charset is Latin1 and dump in UTF8 collation, the data may ends up become simply rubbish, garbled, or unreadable (frequently happen with WordPress blog).
If this case, use –default-character-set=charset_name option to specify the character set or convert the database to UTF8.
Cheers,
