[Solved] What is the syntax to run ERPNext database backup from command line?

ERPNext
1

I already know that it is suggested to use the bench command for this, but I have a special need and wish to avoid using bench if at all possible.

I want to be able to specify the output file name and location. This is just not available to me through bench.

So, Looking for someone with the MySQL command experience to point me to the best syntax for this.

Thanks,

BKM

2

mysqldump -u root -p<root_password> [site_database_name] > sql_file.sql

does this work for you ?

there is no space between -p option and the password

1 Like
3

Probably. How do I find the exact database name of my erpnext database?

And I ‘presume’ your reference to root and root_password are actually the username and password for msyql, is that right?

BKM

4

Ok, Yes! Thank you @root13F for the clues to get my process started. I wanted to find a way to generate a reliable backup on the erpnext database in a single command line command. That way I can eventually get it into a cron job.

So for those interested in following along, here is everything explained:

To run a ERPNext database backup that is also relatively portable, you need to be able to generate a compresses backup file. So this explanation will also include how to do that as part of the same single command.

Starting from this clue:

mysqldump -u root -p<root_password> [site_database_name[ > sql_file.sql

  • “mysqldump” is a valid command you can run right from your server user account. You do not need to start the mysql interface to use this command.

  • “-u root” is literally what is required to be typed as part of the command. It is a short way of calling out the MySQL Administrator User as part of a command.

  • “-p<root_password>” represents the way to add the MySQL Administrator password to the command line. The difference is this part of the comand requires there to be NO SPACE between the “-p” and the actual password. For example if your MySQL password is “K3wlK!d” then the password part of the command would look like this " -pK3wlK!d "

  • [site_database_name] represents the actual name of the database your ERPNext site is using to store all of your data. The name is generated as a random name during the install process. To find the correct name type the following command:

cat ~/frappe-bench/sites/site1.local/site_config.json

(If you are on a multi-tenant site replace site1.local with your site name)
This will list the contents of the site_config file. Your database name will look something like this:

“db_name”: “1bd3e1798pr18724”,

Your actual randomized database name is what appears between the quotes after “db_name”
In this example the database name is: 1bd3e1798pr18724

  • " > sql_file.sql" This part of the command would allow you to define a file location and a file name for your sql backup file. So using what we know now, let’s assume the following:

. Your followed the easy setup instructions for creating your ERPNext server on a VPS somewhere.

. During the setup you made your MySQL password: KewlK!d

. You ran the cat command above and found your database name to be: 1bd3e1798pr18724

. Your VPS server login ID is: iamkewl

. You used mkdir to make a /backups directory in your users home account

Then this is what your command would look like to make a backup:

mysqldump -u root -pK3wlK!d 1bd3e1798pr18724 > /home/iamkewl/backups/test.sql

However, this would be a huge file and not very easily moved across servers if you needed to setup a quick emergency backup server. So to get a compressed backup file (like the ones you would find in the ERPNext backups directory) we must replace this part of the command with a pipe to a gzip command that will define the file location and name.

  • So now to generate a much smaller file we use the gzip command as part of the original mysqldump command like this:

mysqldump -u root -pK3wlK!d 1bd3e1798pr18724 | gzip > /home/iamkewl/backups/test.sql.gz

This command will generate a MySQL backup file that is compressed without having to make the intermediate step of first generating the sql file then taking the second step to compress it. This command does it all at once!

  • To take this one step even further and generate compressed backups with the date and time as part of the name, replace the output part of the gzip command:
    “/home/iamkewl/backups/test.sql.gz”
    with the following:

/home/iamkewl/backups/“$(date ‘+%m%d-%H%M’).sql.gz”

If you use the date/time format above, when you look in the backups directory you placed in your host user account you would see something like this:

iamkewl@ubuntu16:~$ ls -lh backups
total 71M
-rw-rw-r-- 1 iamkewl iamkewl 71M Aug 12 17:45 0812-1745.sql.gz

This indicates that you have a backup file named “0812-1745.sql.gz” in your new backups folder.
The “0812-1745” part of the name indicates it was created on Aug 12 at 17:45
The file in this example is only 71Mbytes in size.

If you had NOT used gzip as part of the command, the file size would have been approximately 815Mbytes as a raw sql file. That is a huge difference.

My test system for all the examples above is a KVM type VPS host with Ubuntu 16.04

Anyway… Hope this helps someone else that was stuck trying to figure out how to accomplish their own backup system.

Your mileage may vary… :sunglasses:

BKM

4 Likes