This tutorial assumes you have some experience with Linux administration although I try to hand-hold as much as possible. If you are on a shared hosting solution and don’t have root access, you can easily modify the shell script we will create later to target your user’s home directory on the server.
You also need to know the address of your SQL server. In my case, I am working on a VPS so the SQL server is local (so I just use localhost) but you might need to use a URL or IP where your SQL server is located.
Okay… digging in. Here is the gist’ of it…
1. Create a new user for your MySQL database that will be just for backups
2. Create some folders on your server to store your backups locally
3. Create a script to actually create the database backups, compress them, clean up backups older than 30 days
4. Test the script
5. Setup the script to run as a cron job.
Here we go!
1. New MySQL database user – from the command line
Login to MySQL
#enter your password at the prompt that appears
Some actual examples of this might look like:
mysql -h 45.95.234.5 -u root -p #this is an example with a made-up public IP address
mysql -h external-db.s12345.gridserver.com -u -p #this is an example of what logging into a Media Temple shared hosting grid-server MySQL instance looks like
Once you are in, you will see a prompt that looks like this “mysql>”. You can now enter commands…
create user backup; #this creates a new user named "backup"
grant all on <db_name>.* to 'backup'@'localhost' identified by '<db_password>'; #this gives that user full admin rights on the database and sets a password
exit #this closes the mysql prompt
Okay, because I know how hard it can be to interpret syntax here is an example of that last command for a made up database called “contosodb”
Make sure you get all of the single ‘quote’ marks correct and end each command with a semi-colon.
You can put whatever password you want for that user on that database. Make sure you take note of it. Now we have our backup user all setup lets move on to creating the folder structure for backups.
2. Setting up the Backup Folder Structure
I am using a folder in the root of my server called “/backup” – If you are on shared hosting your folder structure will probably need to look a little different. Like /home/username/backup. You need to figure it out before you proceed. I recommend whenever you create a script to think through when it makes sense to use absolute paths vs relative paths. In this case, I use absolute paths because I like having the versatility of putting my script file wherever I want on my server. If you are lost regarding relative/absolute that is another (short) discussion for another day. You can just try to follow my example.
Okay, so I am working from root and have created the following file structure. I will use this site as an example because I actually host multiple sites on my server. I keep a separate folder of backups for each site. So a file structure for contoso.com might look like this:
/backup
/backup/database
/backup/database/contoso
/backup/database/someothersite
Here is how you can create this structure, you can do this from a non-elevated prompt so it gets your permissions correct for your regular user:
mkdir backup
mkdir /backup/database
mkdir /backup/database/contoso
If you did this as root, and want access to your files from another non-elevated user account, you can change the group ownership of the folders but I won’t go into that now.
3. Create the script.
Okay, this is where I have done the heavy lifting but you will need to make it unique to your environment. Wherever you see the following !some description! you need to fill in your information (i.e. wipe out everything including the exclamation points and put your info in). So copy and paste this into a text editor, modify it, then we can copy and paste it into a new file on the server.
DATE=`date +%Y%m%d`
date
##BACKUP !sitename! database
mysqldump -h !IP or HOSTNAME of SQL SERVER! --opt -u backup --password=!backup user password! !database name! > /backup/database/!sitefoldername!/!sitename!-backup-$DATE.sql
zip /backup/database/!sitefoldername!/!sitename!-backup-$DATE.zip /backup/database/!sitefoldername!/!sitename!-backup-$DATE.sql
rm /backup/database/!sitefoldername!/!sitename!-backup-$DATE.sql
find /backup/database/!sitefoldername!/* -type f -mtime +30 -delete
I know syntax can be a pain in the rear so here is a sample example with all the variables filled in. This is for the contoso.com example site and database above:
DATE=`date +%Y%m%d`
date
##BACKUP CONTOSO.COM DATABASE
mysqldump -h localhost --opt -u backup --password=contpAss12word contosodb > /backup/database/contoso/contoso-backup-$DATE.sql
zip /backup/database/contoso/contoso-backup-$DATE.zip /backup/database/contoso/contoso-backup-$DATE.sql
rm /backup/database/contoso/contoso-backup-$DATE.sql
find /backup/database/contoso/* -type f -mtime +30 -delete
Once you get all of your stuff filled in and looking correct, on your server do the following to create a new file and then cut and paste this info in to the file:
vim db-backup.sh #this will create a new blank file and take you into the VIM editor to edit it
#you will need to hit the "i" key to go into insert mode. then you can just copy the text from your text file into your shell (if you are using SSH which I assume you are...) and then save and close vim.
One other note about my method… I use zip to compress my files as it compresses pretty well and works with windows. This isn’t built-in to most ubuntu installs and you might need to install it with:
Okay, so you should now have a new file called db-backup.sh
We need to make this file executable and then we can test…
chmod +x db-backup.sh
We are now ready to move on with testing our script.
4. Test your script
bash db-backup.sh
If your script executes successfully, you should get something like the following output:
adding: backup/database/contoso/contoso-backup-20140417.sql (deflated 88%)
You should also now be able to sftp into your server and download the zip archive, open it up, and see your database backup inside. You can use this for database restores in the even your site gets royally jacked up.
Now, we want this script to run automatically daily. So assuming all has gone well thus far, we are going to setup a cron job to run this script every day.
5. Setup your script to run as a cron job and output to a log file
Cron is a simple yet powerful task scheduling tool that is part of most linux builds. Cron involves a file for each user and it can execute things as that user. I recommend setting this up as the root user on your box if you can, however you can set it up using a regular user as long as all of your permissions on your folders and script file are correct. I am setting up the script to run daily.
Login and elevate your prompt to root if you need to (skip this if you don’t):
open up cron for the logged in user (root if you elevated) and select your editor.
##if this is your first time using crontab command, it will present you with options for which editor to use for editing the cron file. My preference is usually VIM but you can use whatever you are comfortable with
Now that you are in your editor, go to the bottom and create a new line with the following:
You may need to tweak your line in cron if you are not having much luck getting your script to run. You can adjust the first two numbers to set the minute and hour the script runs to only a minute or two away from the current time of day for easy testing. This is a good discussion regarding some of the idiosyncrasies of getting cron to run scripts: http://askubuntu.com/questions/264607/bash-script-not-executing-from-crontab and here http://askubuntu.com/questions/23009/reasons-why-crontab-does-not-work
If you want to learn how to further customize cron, you can reference the following: https://help.ubuntu.com/community/CronHowto
The above cron command will run the backup script we created at 3:01 AM every day, of every week, of every month. It will also create (the first time) and then append the output of the script to that databasebackup.log file so there is a record of the backups that occur every day.
You will need to change your pathing above if you are aren’t working from root.
Assuming everything went okay you should be golden for database backups. I will eventually create a follow-up article on backing up your webfolder as well at least once a month.
Cheers!
REFERENCES:
http://www.lanexa.net/2011/08/create-a-mysql-database-username-password-and-permissions-from-the-command-line/
http://www.linuxquestions.org/questions/linux-newbie-8/pipe-output-to-append-to-a-text-file-86174/
http://dev.mysql.com/doc/refman/5.0/en/connecting.html
http://askubuntu.com/questions/264607/bash-script-not-executing-from-crontab
http://askubuntu.com/questions/23009/reasons-why-crontab-does-not-work
Excellent blog! Do you have any recommendations for aspiring writers?
I’m planning to start my own website soon but I’m
a little lost on everything. Would you advise starting
with a free platform like WordPress or go for a paid option? There are so many choices out there
that I’m completely overwhelmed .. Any ideas? Cheers!
Also visit my page :: topelevenhack-2.blogspot.com
True blogs, imho, are all about content… content, content, and more content. And not just junk content to load up keywords but actual, useful stuff that people care about. I think blogging can also be a great way to make money, however realistically speaking it is going to take several years and a lot of writing to start making “quit your job” or even “supplemental income” kind of money for most people. If you can go in with the above mindset, you are in a good place to start.
Second thought, you have to be writing about something you really care about, something you find engaging or interesting. If you aren’t, you most likely won’t write. That is why I picked technology and specifically IT. I eat and breathe it all day long. I am constantly reading and researching all the time, just for me… this was well before blogging. So if it is something you are constantly engaged with already, ALL the better.
Regarding a platform… whatever you can easily write on. In my case, I do website development on Drupal. I love Drupal. It is a phenomenally amazing platform for building relatively complex sites. You COULD build a blog in Drupal… However, when I decided to start a blog I went and figured out WordPress. Why? Because wordpress is BUILT for blogging. That is THE #1 use of wordpress and it is the audience they cater to with development. It has all of the bells and whistles you need built right in to make writing and publishing as painless as possible.
Finally, if you have $10… go buy your own domain name (read some good SEO posts on choosing a domain name first so you create something decent in that regard). I am pretty sure you can host your blog at wordpress.com with your own Domain name and you can use their free hosting. If not, will need to pay for hosting (I haven’t researched this at all as I host this on my own VPS with Media Temple) if you want your own domain name. Don’t despair if that is the case though. Figure on about $100 – $120 a YEAR for website hosting. So you are looking at about $130/year for your website at most on a decent shared hosting plan. I would, once again, highly recommend Media Temple however they are on the more expensive side if you aren’t ready for commitment. I have, in the past, hosted with GoDaddy, Dreamhost, and Site5… Site5 was the only one of that lot I would consider going back to but that being said, I wasn’t with them for very long as I just trialed one of their services.
Anyhow, shared hosting is cheap and will be sufficient for most people that just need a blog for quite a long time.
The reason I recommend getting your own domain name and shared hosting (if necessary) is very simple… blogging is like real-estate. The more writing you do, the better the SEO/ranking of your domain. Also, dedicated domains have better page ranking to begin with vs subdomains (which is what a blahblahsite.wordpresss.com domain would be). Back the realestate analogy, if you are going to put the time and money into building a really nice house (i.e. writing lots of content) you might as well do it on your own land (the domain name).
Also, spending a few $$ up front might help you stay committed when the discipline of writing becomes tough 🙂
Anyhow, best of luck and I hope this helps!
Regards,
Nathan