Dumping MySQL Database into File via Command Line

Dumping database in MySQL Database Server can be considered as one mechanism of backup process. Backup process is a necessary thing to have for any services which is considering high availability as its important factor. In this article, there will be a description about steps taken to be able to backup certain database or the database chosen as the target of backup process in MySQL Database Server.

Manually backup database exists in MySQL Database Server can be done in any variant of Linux operating system distribution as long as the service itself can be connected or can be accessed. Below are the steps taken to backup database in MySQL Database Server :

1. Check the service whether it is active and listening for incoming request. The article for this matter can be viewed in ‘Check MySQL Server Status‘.

2. Make sure so that, in the dumping process there is an appropriate account to connect to MySQL Database Server especially for the database which is chosen for backup process or to be dumped. The important thing is the account itself can be used for dumping the selected database in MySQL Database Server. For a reference, it can be viewed in the article ‘Create User for dumping database MySQL‘.

3. Login in MySQL Command Console via command line. In this article, it is used command executed in a command line. The command is actually calling tool which is normally provided on default MySQL Database Server installation. The tool is ‘mysqldump’. Below is the pattern of mysqldump tool :

mysqldump -uuser -p database_name > /home/user/file_dump.sql
Description : 
mysqldump : It is a command which is actually called to dump database 
-u : It is an option used with mysqldump command to specify user
user : It is the value of -u option which is defining the user and in this context the value is user
-p : It is an option used with mysqldump command to interactively display password for user verification
database_name : It is the name of the database used to be dumped. 
> : It is the redirect output sign which is used to redirect any output generated by the command specified before the ">" sign to anything which is specified after the ">" sign. 
/home/user/file_dump.sql : It is the location of the dump file named file_dump.sql. The exact location in the above pattern is in /home/user

For an example :

user@hostname:~$ mysqldump -udb_user -p db_app > /home/user/db_app.sql
Enter password:
user@hostname:~$

The above example is generating file dump named db_app.sql. It is retrieved by dumping from a database named ‘db_app’. If nothing goes wrong and user named ‘db_user’ has the privilege to dump from the database named ‘db_app’, the dump file named db_app.sql will be generated and will be located in /home/user.