Introduction
If you value your data, you’ll want to backup your data (database) often, just in case something bad happens with your data… This article is about running backups/restore for MariaDB.
Install MariaDB
Wherever you use MySQL, you can use MariaDB as it is intended to be fully compatible with MySQL. To install MariaDB on RHEL, you’ll need to add a repository file. First go to: https://downloads.mariadb.org/mariadb/repositories/ and follow the instructions by selecting a Linux Distro and selecting the latest stable version of MariaDB.
Copy the text generated and paste it into the repo file that you will create with vi:
sudo vi /etc/yum.repos.d/MariaDB.repo
Save the file in vi (“:wq!”), then run the following command:
yum install MariaDB-server MariaDB-client
After install, start the service:
sudo service mysql start
Verify it starts correctly. If it doesn’t, try to figure out why before proceeding. Then change the root password to something more secure (especially if this is going to be a production machine). Use the following command, where newpassword is what you want to change the new password to:
sudo mysqladmin -u root password newpassword
Simple Backup
The following example will show a simple backup of a single database. The command mysqldump is used exclusively to dump databases and is fully documented here:
https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html
The documentation is very detailed, but a simple backup of the database called “test” to a file called “backup_test.sql” would be performed like so:
mysqldump -u root -pMypassword345 test > backup_test.sql
In the above command, “-u root” specifies the user as the root user. The “-p” option is followed by the password, there is no space after “-p”.
Backup With Date
We could modify the above command slightly to create the backup that has today’s date. Then the filename can be in the format “Month_Day_ENV.sql”, where “ENV” can be one of “PROD” (for production) or “DEV” (development environment). In Linux, we can use the GNU date command in our script to achieve this like so:
mysqldump -u root -pMypassword345 test > $(date +'%b_%d_%Y'_PROD).sql
If it was done today (November 22, 2016), the resultant file would be “Nov_22_2016_PROD.sql”.
Automating the Backups
The above is all great, but you don’t want to have to log in to the machine and then manually issue that command every day. So let’s use crontab to add a cron job an run a script to do that every weekday at 8 PM. The utility crontab can be run like so:
crontab -e
This opens the default editor, which is normally vi. Then you can press “i” to go to “insert” mode and edit the file. Then press “Esc” to exit the “insert” mode, and then type “:wq!” to quit and save the file. You can always type “crontab -e” again to re-edit the file in case you made a mistake.
You need to create a script file (executable) that contains the above mysqldump command. Probably the best way to do that is using the vi editor. Let’s call the file “backup.sh”, so issue the command “vi backup.sh” in the directory you want to store it. A good place “might” be your home directory (since no one else can access that). Also, you probably want to store your backups somewhere, and your home directory could work for this to. So let’s do that too:
cd ~ mkdir mariadb_backups cd mariadb_backups vi backup.sh
Then using vi edit the backup.sh to contain:
mysqldump -u root -pMypassword345 test > /home/user/backups/$(date +'%b_%d_%Y'_PROD).sql
Note that when your cron job runs, it will place any resultant file in your home directory by default. So in the backup.sh script we’ve specified the full path to the backup directory in the user’s home path, in this case which is “/home/user/mariadb_backups”. Now we can run “crontab -e” and set the above created script to run at 8PM:
00 20 * * 1-5 /home/user/mariadb_backups/backup.sh
You can always use “crontab -l” to list the crontab entry, and it should show the above line that you entered.
Recovery
Should you ever have a disaster and you need to restore your database to a previous state, then you can also use the mysql (Note: it’s mysql) command to recover. Just pipe in the backup file like so:
mysql -u root -p test <Nov_3_PROD.sql
This will prompt you for the root user password, enter that and then it will restore from the file you passed in. Make sure you know what you are doing. You can always open the backup file with an editor to see if it contains the backup data you need.
Hopefully that should help you get started with backup and recovery of your database. Enjoy!
Alvin Bunk 