Backup Database MySQL Command Line

Posted on

To be able to backup one of the database exists in MySQL database server is quite easy actually. Especially if it is done by using command which is executed in bash prompt.

The command itself not only can be used in a CLI (Command Line Interface) operating system but also in a GUI (Graphical User Interface) operating system as long as MySQL database server is correctly installed and the tool which is going to be used for database backup can be executed in any command line interface available in any kinds of operating system.

The command is actually a tool which is included as part of MySQL database server installation. That command or the tool is known or called ‘mysqldump’.

Any operating system for an example a Linux operating system distribution which is well known for its bash prompt interface and the other operating system distribution such as Windows also have an MS-DOS prompt that can be executed by typing ‘cmd’ in the Ctrl+R or run dialog box.

By using the CLI (Command Line Interface) available in the operating system, we can type the command which is actually called ‘mysqldump’ tool in this context.

Below are the steps which can be followed to get the backup file :

1. Make sure MySQL database server service is active by executing the following command :

service mysql status

Below is the output of the above command execution :

user@hostname:/etc/mysql/conf.d$ service mysql status
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sen 2016-09-12 18:14:18 WIB; 1 day 13h ago
Process: 1252 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Process: 1241 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 1251 (mysqld)
Tasks: 29
Memory: 21.1M
CPU: 40.338s
CGroup: /system.slice/mysql.service
└─1251 /usr/sbin/mysqld

Sep 12 18:14:13 hostname systemd[1]: Starting MySQL Community Server...
Sep 12 18:14:18 hostname systemd[1]: Started MySQL Community Server.
user@hostname:/etc/mysql/conf.d$

2. As soon as MySQL database server has already found that is active, the backup process can be executed by typing the following command. And the command itself can directly executed from the bash prompt without having to enter MySQL command console :

mysqldump -uusername -p databasename > /path_of_dump_file_output/dumpfile_name.sql

Description : 

mysqldump : it is actually a tool installed as part of MySQL database installation and exists as a command which can be typed in the Command Line Interface (CLI) to call or use the tool for database backup.

-u : additional parameter to specify username to access MySQL database server
username : the value of -u parameter and it is the username which is going to be used to access MySQL database sesrver
databasename : the name of the database exists in MySQL database server which is going to be accessed
> : redirect output sign, it will redirect the output from all the command before the sign to the command after the sign. 
/path_of_dump_file_output/dumpfile_name.sql : the folder path and also the file name of MySQL database file dump, for an example : /home/user/mydumpfile.sql

Below is the output display as the command execution performed in Linux bash prompt :

user@hostname:/etc/mysql/conf.d$ mysqldump -uroot -p test > /home/user/test.sql
Enter password: 
user@hostname:/etc/mysql/conf.d$ 

Type the corresponding password for user ‘root’ used as the parameter value of -u which means to connect to MySQL database server as user ‘root’. If the authentication provides by MySQL database server has already passed, there will be a file named test.sql in /home/user.

Leave a Reply