How to Back Up and Restore a MySQL Database

backup-restore-mysqldump-cli.sh

#Back up From the Command Line (using mysqldump)

#If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. 
#This command connects to the MySQL server and creates an SQL dump file. 
#The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:
mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

#For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, 
#you should accomplish this command
mysqldump -u root -p Tutorials > tut_backup.sql
#This command will backup the 'Tutorials' database into a file called tut_backup.sql
#which will contain all the SQL statements needed to re-create the database

#With mysqldump command you can specify certain tables of your database you want to backup.
#For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below.
#Each table name has to be separated by space.
mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

#Sometimes it is necessary to back up more that one database at once. 
#In this case you can use the --database option followed by the list of databases you would like to backup.
#Each database name has to be separated by space
mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

#If you want to back up all the databases in the server at one time you should use the --all-databases option. 
#It tells MySQL to dump all the databases it has in storage.
mysqldump -u root -p --all-databases > alldb_backup.sql

#Back up your MySQL Database with Compress

#If your mysql database is very big, you might want to compress the output of mysqldump.
#Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file
mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

#If you want to extract the .gz file, use the command below
gunzip [backupfile.sql.gz]

#Restoring your MySQL Database

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

#Have a look how you can restore your tut_backup.sql file to the Tutorials database.
mysql -u root -p Tutorials < tut_backup.sql

#To restore compressed backup files you can do the following
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

#If you need to restore a database that already exists, you'll need to use mysqlimport command. 
#The syntax for mysqlimport is as follows:
mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]