The easier and quicker way to backup a database is using mysqldump command.
The syntax is as follows:
backup:
mysqldump –u [uname] –p [pass] [dbname] > [backupfile.sql]
restore:
mysql –u [uname] –p [pass] [dbname] < [backupfile.sql]
[uname] database username
[pass] database password
[dbname] database name
[backupfile.sql] dump file name
It’s not recommended typing the password when using this command because it’ll be stored in the history. Just type –p and the next time you issue the command, you will be asked for the password.
In case you have more than one database and want to backup all the databases:
mysqldump –u root –p –all-databases > [backupfile.sql]
If you want to backup a specific table:
mysqldump –u root –p [dbname] [tablename] > [backupfile.sql]
To restore simply run mysql; to restore a remote database, first you have to create the database and then import the backup file:
mysql –u root –p mysql> create database [dbname] mysql –u root –p [dbname] < [backupfile.sql]
If the database already exists and you just want to import a backup:
mysqlimport –u root –p [dbname] [backupfile.sql]
Another useful command is tar (warning: by default tar DOES NOT COMPRESS anything but only creates an archive!):
tar –czvf [archive_name].tar.gz [backupfile.sql]
-c create new archive
-z use gzip compression; for a bzip2 compression we have to add -j
-v verbose
-f specify the file name
To decompress:
tar –xvfz [archive_name].tar.gz
-x decompress an archive
-z decompressing a gzip archive; for a bzip2 you have to type -j