RailsPlayground
How To Automatically Backup Your MySQL Databases

It is very important to regularly backup your important MySQL databases. We have found a script that will allow you to easily backup your MySQL databases.

• Login to your account via ssh and download the backup script
  

  wget http://worldcommunity.com/opensource/utilities/mysql_backup.txt
  

• Now rename the file and change the permissions
  

  mv mysql_backup.txt mysql_backup.cgi
  chmod 755 mysql_backup.cgi
  

• Next, you will need to edit this file either with your ftp client or with vi on the shell. This script allows for a lot of customization, the following are the essential items to configure.
  • If you would like each backup to be emailed to set the following.
    

    $email_backup                = 'yes';
    $admin_email_to              = "you\@yourdomain.com";
    $admin_email_from            = "webmaster\@yourdomain.com";
    

  • Enter the databases you would like to backup, separated by spaces.
    

    @selected_databases  = qw[cpanelid_dbname1 cpnaleid_dbname2];
    

  • Set process all databases to no.
    

    $process_all_databases       = 'no';
    

  • Set the password location to this_file
    

    $password_location           = 'this_file';
    

  • Now lets setup your database login information. The username and password are the same as when you created your MySQL database and users from cpanel.
    

    $user                        = 'mysqldb_id';
    $password                    = 'mysqldb_password';
    
    

  • You will also need to setup the directory to save the backups to
    

    $mysql_backup_dir            = '/home/cpanelid/mysql_backups';
    

• This is the minimum that you will need to configure, if you have an ftp account on another server, you can also have the program automatically ftp the backup to that account.
• The next step is to create the backup directory.

  • mkdir mysql_backups

• Now test the script and make sure it works.

  • ./mysql_backup.cgi

    • If you get an error, feel free to email support@railsplayground.com for help.

• If the script runs without any problems, we now need to setup a cron job to run this script automatically.
  • Login to cPanel
  • Click on Cron Jobs
  • Click on the Standard Button
  • For the command to run enter the location of the script.
    

    /home/cpanelid/mysql_backup.cgi

  • You now need to decided how often to run this backup. If you have a very active database then you might want to consider once an hour. If you have a moderately active database then once a day should be fine. If you need help in this area, please contact us.
  • After you have setup the schedule, click on save crontab.

———

if it helps anyone, it’s really a lot easier than all this:

ssh user@domain.com “mysqldump—user=user—password=password example_database” > /Users/user/backups/my_website_db.sql

that gets your database in one line from your own computer. just put it on cron on your computer. if you wanted to make copies on your webserver itself, just drop the ssh part and the double quotes. in either case, set the output path you want and put in your username and password and database name.

edit: some spaces appear to have been dropped by the wiki software. put spaces between each part.

——-

Can someone please write about how to restore a database using the .txt archive provided by this script? Thanks.

——-

To restore an entire db from this .txt archive… I’m doing this on a local machine and using CocoaMysql to import into my railsplayground account.

• First, make sure you have the path to the directory with the mysql command included in your $PATH variable.
• Using terminal, cd to the directory with all the .txt files in it.
• Type this…
  

   cat *.txt | mysql [your_db_name] -u [username] -p 

• Be proud. You saved the day again.

I’m no expert, but I hope this helps.

———-