There are lots of reasons why you may want backup and restore a MySQL backup using the shell or a cron job. Backing up using the shell or via a cron job is pretty common and fairly well documented. However restoring a database from the shell or a cron is a little more uncommon. I thought I would show you how I do it and explain a usage case I have.
My Usage Case
I have a slightly unusual case where I need to get MySQL data from a third party, I have connectivity to them using an OpenVPN client as they couldn’t create a site to site VPN connection or anything more advanced than giving me a VPN client and a read-only MySQL account. As a VPN connection is generally a two way street, and as I am having to use a VPN client rather than terminating a VPN tunnel on my firewall with some nice rules to prevent the third party accessing my network. I came up with an unusual but safe and effective method of getting the data I needed from their MySQL database.
I added a tiny Linux server into my DMZ network running the OpenVPN connection as daemon on boot. I then added a couple of cron jobs, one to backup the MySQL database from their environment using a read only account they gave me. A second cron job then restores the MySQL backup to a local MySQL server running on the same Linux box. I then created a Microsoft SQL Linked server connection to my MySQL database and pull in all of the required tables into Microsoft SQL on my Internal network. I can then get the MySQL data into my Data Warehouse, Power BI etc.
Backing up a MySQL from the Shell
mysqldump --single-transaction --skip-triggers -h REMOTE_SERVER -uUSER -pPASSWORD DATABASE > /home/db_backup_file.sql
–single-transaction
The user account I was given by my third party did not have the LOCK TABLE permission. The –single-transaction flag will start a transaction before running. Rather than lock the entire database getting around not having the LOCK TABLE permission. If you have the needed permissions you can skip this argument
–skip-triggers
I only need the data so I could skip backing up the triggers. Having the triggers included origionally gave me problems as my local database name was different to theirs. If you need to backup the triggers you can skip this argument
-h REMOTE_SERVER
If you are backing up a local server you can skip this. For a remote server replace REMOTE_SERVER with the IP or name.
-uUSER
Replace USER with the username of an account that has at least access to select/read data from the database. For example if my user name was “readonly” this argument would look like -ureadonly
-pPASSWORD
Replace PASSWORD with the accounts password. For example is the password was “V3ryS3cur3” this argument would look like this -pV3ryS3cur3
DATABASE
Replace with the name of the database we are backing up
/home/db_backup_file.sql
Replace with the location where you want to save the backup. The account you are running the script as will need write access to this location
Restoring a MySQL from the Shell
mysql -uUSER -pPASSWORD DATABASE < /home/db_backup_file.sql
-uUSER
Replace USER with the username of an account that has drop and insert/write access to the destination database. For example if my user name was “fullaccess” this argument would look like -ufullaccess
-pPASSWORD
Replace PASSWORD with the accounts password. For example is the password was “V3ryS3cur3” this argument would look like this -pV3ryS3cur3
DATABASE
Replace with the name of the database we are backing up
/home/db_backup_file.sql
Replace with the location where your backup file is located