It is necessary to take database backups at regular intervals to prevent loss of data. In this article we will see how we can backup mysql database from command line.

Backup MySQL database:

‘mysqldump’ command can be used to create backup of MySQL database as follows:

$ mysqldump -u username -ppassword dbname > dbname.sql

The above command assumes that you are running the ‘mysqldump’ command on the same server on which MySQL is installed.

If your MySQL server is located on another host then you need to provide the hostname. Optionally, if needed you can specify the port also.

$ mysqldump -h hostname  -P portnumber -u username -ppassword dbname > dbname.sql

As a security measure you should avoid specifying the password with the command, just add the ‘-p’ option

$ mysqldump -u username -p dbname > dbname.sql

You will be prompted to enter the password when you run the above command.

If the database is big in size then we can use the ‘gzip’ command to compress it.

$ mysqldump -u username -ppassword dbname | gzip -9 > dbname.sql.gz

When required the .gz file can be extracted using the ‘gunzip’ command

$ gunzip dbname.sql.gz

‘mysqldump’ command can be used to backup some specific table from a database, one or more databases or entire MySQL

1.  Backup some specific tables from a database

$ mysqldump -u username -p dbname tablename1 tablename2> dbname.sql

2.  Backup one or more databases

$ mysqldump -u username -p --databases dbname1 dbname2 > dbname12.sql

2.  Backup entire MySQL

$ mysqldump -u username -p --all-databases > dbname12.sql

‘mysqldump’ command generated SQL statements but it can also generate output in CSV, other delimited text, or XML format.