As the number of users on my systems increase, so do the number of databases. I do a MySQL monthly backup at the first of each month just in case of anything happening that could corrupt the data. Well, this starts to be a time consuming process when the number of databases increases.
So, I thought I would share with everyone a quick solution that I have done for automated monthly backups of the SQL databases.
# Script will backup databases
directory=$(date +%m-%d-%Y)
cd "/home/www/Database Backup"
mkdir $directory
cd $directory
mysqldump –user=USER_HERE –password=PASSWORD_HERE –databases DATABASE_NAME > DATABASE_NAME.sql
So, lets dissect this to make it easy to understand.
This line creates a variable called directory. It will then assign the month, day, and year to this. Since I create a folder each month to hold the backups, this is how I set it up. So, the directory variable will contain "12-01-2008" for December 1st, 2008.
The next couple of lines:
mkdir $directory cd $directory
will move the current directory to where I store the database backups. Then, it will create (mkdir) the new directory name by using "mkdir $directory" as listed above. since "directory" is holding 12-01-2008, this is the same as "mkdir 12-01-2008". By using the variable, however, this will allow the script to change this each time the script is run instead of putting a hard-coded directory. Lastly, it will then go into the new directory that was created.
Lastly, the last bit of code:
mysqldump is a program that comes with the MySQL server that will allow you to dump the contents of a database into a file. This way, if you ever need to restore a database, it is just a matter of dropping all the information in the database and then importing it from this file.
For –user=USER_HERE, you need to put your MySQL username in here. Most likely if you are the owner of the system, you will have to use –user=root because the root user has full access to all the databases on the server. Or, you may have another user that simply has read-only access to all of the databases, such as a backup user. In that case, use something like –user=backup. Again, this all depends upon what username you have.
For –password=PASSWORD_HERE, this is where you will put in the password of the user you just specified beforehand.
For –databases DATABASE_NAME, you will list one or more databases after this. So, you could have –databases database1 database2 and so on. However, I have created one mysqldump line per database that I have, and only listed that database after it – that way there is one SQL dump file per database – instead of all the databases being in one file.
Lastly, the > DATABASE_NAME.sql is the name of the file you wish to save it as. So, lets give a quick example of one if you are using user "backup" and password "testing" with database name of database1:
Now, if you have three databases, database1, database2, and database3 – and you want each of them in an individual file, there are then three of the above lines:
mysqldump –user=backup –password=testing –databases database2 > database2.sql
mysqldump –user=backup –password=testing –databases database3 > database3.sql
So, if you have 20 different databases and want each in an individual file, you will have 20 different lines where the name of the database and the name of the file saved to is different. If you don't mind having all of your databases backed up into one file, the following line will put all three databases (like above) into one file named database-backup.sql: