This article has a same tone with the article published and titled ‘Dumping MySQL Database into file via command line‘. But in this article, there is an interesting addition to the dump file process retrieved. The dumping process is the same but the dump output file is produced with an additional information.
In certain occasion, we need to dump database into a file with an additional information. Generally that information contains date when the file is generated from the database dumping process. So, we can identify when the file was generated by only looking the file name. But, the dumping process itself needs for the appropriate user account to be used for connecting and dumping the target database in MySQL Database Server. Creating user for dumping database purpose in MySQL Database can be read in the following article named ‘Create User for Dumping Database MySQL‘.
Dumping database needs MySQL Database Server’s service to be fully running. Check the status of the service first and this article titled ‘Check MySQL Service Status‘ can be a reference on doing it.
After knowing MySQL Service is active and also the user which is going to be used for dumping process already exists, execute the command below to initiate the process :
mysqldump -uuser -p db_name > /home/user/db_name_$(date +%Y-%m-%d) Description : mysqldump : It is a command used to dump certain database in MySQL Database Server -u : It is a command option used to specify user user : It is the value of the command option used to specify user, in the above example the value is 'user'. It is the user used to connect and dump certain database in MySQL Database Server -p : It is a command option used to specify password db_name : It is the name of the database which is going to be dumped. > : It is a sign which is indicating redirect output. It means that every output generated from before the '>' sign will be directed to anything after the '>' sign. /home/user/db_name_$(date +%Y-%m-%d) : The description can be divided into the following part : /home/user : It is the location of the dump file, in this context it is located in /home/user. db_name_ : It is the beginning part of the dump file name. $_(date +%Y-%m-%d) : It is the ending part of the dump file name which is using the date function. So, if the above command specified is executed successfully, there will be a file for an example in /home/user named db_name_07-10-2016, if it is executed at October 7th 2016.
So, the above command compared with the normal backup or dumping process has a slight difference only on the name of the dump file. In the dump file command execution pattern shown above, there is a date function attached to the file name for an easier identification about when the dump file was generated. For an example in the real execution of the dump command in a bash prompt :
[user@hostname ~]$ mysqldump -uroot -p mydb > /home/java/mydb_dumpfile_$(date + "%Y-%m-%d") date: extra operand `%Y-%m-%d' Try `date --help' for more information. Enter password: [user@hostname ~]$
Pay attention to the format of the date function specified in the ending part of the file. It has to be in the following format :
$(date +%Y-%m-%d)
So, try to re-execute the command as follows in real situation :
[user@hostname ~]$ mysqldump -uroot -p mydb > /home/user/mydb_dumpfile_$(date +%Y-%m-%d).sql Enter password: [user@hostname ~]
After successfully execute the dump process, look for the generated dump file in the directory :
[user@hostname ~]$ ls -al | grep mydb_dumpfile ... -rw-rw-r-- 1 user user 3086662 Oct 10 16:48 mydb_dumpfile_2016-10-10 ... [user@hostname ~]$
Based on the above output, the dump file has already been successfully generated accordingly with the pattern name defined in the dump command execution.