MySQL/Linux: Creating and restoring database backups using command line

Most of us have worked on different database technologies. MySQL is one amongst them.This tutorial explains the backup/restore process of db's in mysql.

Mysql provides a tool mysqldump which allows the user to take the backup of current state of the databases and restore them at a later point in time,if required.This tool comes in with the default installation of mysql server and can be used on the command line.

In this tutorial i am using mysql-server-5+ and Linux OS command line to explain the usage of this tool.

For a single database

Suppose you need to backup a single database.This command will do your work.

mysqldump -u root -p[root_password] [database_name] >filename.sql

So to backup a database named 'testing',the command would be

mysqldump -u root -p[root_password] testing > testing.sql

Note:The dump file testing.sqlwill be generated and stored in the directory location where you issue this command.However,you can specify your own directory location.Something like this 

mysqldump -u root -p[root_password] [database_name] > /path/to/file

Now comes the restore part.To restore a dump into a database,the syntax is
mysql -u root -p[root_password] [database_name] < filename.sql

where filename.sql is the dump file generated using mysqldump command.

Suppose you need to restore the dump file generated in the previous step to a database named 'testing1'.The command is

mysql -u root -p[root_password] testing1 < testing.sql

Note: The database into which the dump needs to be restored(in this case testing1) should be created in advance (using the Create database) before dumping the content of the dump into file.Not doing so will throw up an error when the restoration is done.

For multiple databases

Sometimes it is necessary to back up more that one database at once. In this case you can use the --databases 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[root_password] --databases db1 db2 > content_backup.sql

The above command will dump two databases db1 and db2 into the content_backup.sql file.

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[root_password] --all-databases > alldb_backup.sql

Selective backup with mysqldump

The mysqldump utility also allows you to take selective backups in a database.You may want to backup one or more tables in database or,backup only selective data or records(using 'where' conditional clause).

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only blog_template1 and blog_template2 tables from the ‘Templates’ database,use the command below. Each table name has to be separated by space.

mysqldump -u root -p[root_password] \blog_template1 blog_template2  > dbtemplate_backup.sql

For backing up or dumping only selective records in tables we can use the --where or -w option.For example,to backup the records from table t1 in the database db1 where vendor is google, the command will be

mysqldump -u root -p[root_password] db1 \t1 --where "vendor='google'" > t1.sql

This completes the tutorial on backing up/restoring data in mysql.Comments and feedbacks will be appreciated.Cheers!!!

Run a MySQL script file in Linux

We commonly use scripts to automate and ease our work. MySQL provides a feature to create schema structure using scripts.

A MySQL script file(.sql) is as good as a normal text file.Just put your queries in a text file and your script is ready to run.In this post i explain the procedure to run a mysql script through the command line as well as mysql shell

The script file

I created my script using vi editor in Linux(you may use any text editor :) )
Here is my script file

I placed my script file in the root directory.The location of the script is important.Why?? We will get to know it soon.

 

Executing the script

We can execute our script in two ways.

• From the Linux/Unix command line with the help of the redirection operator(<)
• From the mysql shell. 

 To do it the first way,from the command-line execute this command
  mysql -u -p < script  

Note: Execute this command from the location where you placed your script.

Replace the with the mysql username and with the password you use to login into mysql.For instance,if the username is root and password is xyz the script filename is scpt the command would be 
mysql -u root -pxyz < scpt

< is the redirection operator in Unix/linux.

If the command execution is successful,you won't get any confirmation message.The control returns to the next line of the shell prompt.

Now to confirm the changes made by the script,login to mysql and use the show databases and show tables command.

Below is an illustration of this method.

             
To execute your script from the mysql shell.Follow these steps

1. Login to mysql with root privileges(Note:Login into mysql from the same directory where you placed your script)     

   2.  Once in the mysql shell,enter this command to execute your script
         source scriptname
        
Replace scriptname with the script you earlier created and want to execute. 

   3.  Once the mysql shell finds your script file,it will execute it and the results can be instantly seen on your screen.

   

   

 




























Now to confirm the changes made by the script, use the show databases and show tables command.

In case the mysql is unable to locate the file,it will throw up an error something like this