14 DAY TRIAL // Like webservers, most MySql servers should remain online 24/7. That is why you should create periodical backups for your database. Because mysql datafiles are stored on the webserver, this makes the backup and restore process very easy to understand.
In bin folder from your mysql install directory you can find mysqldump that can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create and populate the tables. Also, mysqldump can be used to generate files in CSV(Comma-separated values) or XML. Works best for small and medium installations.
To invoke mysqldump, there are 3 options: mysqldump [OPTIONS] database [tables] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] mysqldump [OPTIONS] --all-databases [OPTIONS]
If you use --databases and --al-databases options, entire databases are dumped.
Beginning with MySQL 5.0.40, XML output includes the XML namespace.
The most used function of mysqldump is for making the backup of the entire database: mysqldump db_name > backup-file.sql. To read the dump file back to the server use the following command: mysql db_name < back-file.sql or mysql -e "source /path-to-backup/backup-file.sql"
To dump ultiple database with only one command, use --all-databases option: mysqldump --all-databases > all_databases.sql
You can also use mysqldump to populate databases by copying data from a mysql server to another, use the following command: mysqldump --opt db_name | mysql --host=remote_host -C db_name
If you want to compress your backups directly, try this command: mysqldump --opt -u user --password="password" database | bzip2 -c > database.sql.bz2