Support Center

How do I backup my 1and1 database?

Please take caution if you decide to use this guide to backup your 1and1 database. Storing your database password in a file on your web server is extremely dangerous and can leave you vulnerable if you get hacked. Use at your own risk.

In this article, we will go over how to backup your database if you have a 1and1 Web Hosting Package. Please note that you will need SSH access.

Step 1: SSH into your web server

First we will need to SSH into our 1and1 web server. Open up a terminal program (Terminal/iTerm if you are on Mac, Command Line if you are on Windows) and type in the following substituting your 1and1 information in:

ssh <1and1_ssh_username>@<1and1_webserver_hostname>

You will then be asked to enter your password. Enter it and your terminal should look like the following:


 

Step 2: Make sure you can connect to your MySQL server

Now that we are SSH'd into the web server. Let's make sure that we can connect to the MySQL server and perform a test mysqldump. Run the following command, substituting your 1and1 information in.

mysqldump -h <1and1_database_hostname> -u<database_username> -p<database_password> <database_name> > test.sql



Note depending on how large your database is, this may take some time. Once you are able to interact with the shell again and you see no error output, we can check to make sure the dump completed successfully. You can use any unix based text editor or tool such as vim to look at it.

vim test.sql



If it is successful, we can see our mysqldump contents:

Step 3: Create mysqldump shell script

Now that we know we can connect to our database and run mysqldump correctly, we need to write a shell script that will do this for us. First, we will need to make a directory for the mysqldumps to go into. This can be accomplished using the 'mkdir' command:

mkdir <folder_name>



Now that there is a directory for the backups to be stored so we can create the shell script. Using an editor such as vim, open a new shell script as shown below:

vim <script_name>.sh



Once the file is open, we can add the necessary scripts. If you would like to delete backups that are older than seven days in order to save space, you can add the following line:

find <path_to_mysqldumps_directory> -name '<expression_matching_your_mysqldump_files>' -mtime +7 -delete

To add the mysqldump command, we will be using a very similar command to the one we used earlier to test. Be sure to place your specific information in the command:

mysqldump -h <db_hostname> -u<db_username> -p<db_password> <db_name> > <path_to_your_mysqldumps_directory>/<file_name>-$(date + %Y-%m-%d).sql

So for example, here is a command filled with data:

mysqldump -h 1and1host.com -umyusername -pmypassword mydbname > ~/mysqldumps/wordpress-mysqldump-$(date + %Y-%m-%d).sql

As you can see, we included some date formatting so that you can see what date the backup was from. If you have multiple databases, you can add as many mysqldump commands as you would like. Just be sure to change the filename for each one. Also, take note of how since my backups are named wordpress-mysqldump-date, I used '*-mysqldump-*' for the expression in the find command.

This is what your final shell script might look like:

Once you are happy, save and exit from the script. If you are using vim, you can follow the below command:

press ESC to get out of insert mode, then :wq to save and exit

To test that the script works, call the following command and then check in the directory to make sure that the backups are there.

mysqldump.sh



Go into your mysqldumps directory and look for your backup to make sure that the script worked:



Make sure that when you open the file, you see the mysqldump data.

Step 4: Automate the backup

In order to automate the backup, we will have to use a cron job. To access your server's cron jobs, use the following command:

crontab -e



Now we can add a cron job that calls the script automatically for us. Here is a chart taken from the Wikipedia article on cron jobs that details the syntax.

# *    *    *    *    *  command to execute
# ┬    ┬    ┬    ┬    ┬
# │    │    │    │    │
# │    │    │    │    │
# │    │    │    │    └───── day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names)
# │    │    │    └────────── month (1 - 12)
# │    │    └─────────────── day of month (1 - 31)
# │    └──────────────────── hour (0 - 23)
# └───────────────────────── min (0 - 59)

For example, if I wanted to run the backup script every day at 2am, I would use the following command:

0 2 * * * ~/mysqldump.sh



Once you have written your cron job for the interval that you see fit, be sure to save the file and you are done. Now your cron job will run at the specified time and run your backup script.


 

Frequently Asked Questions

What if my database credentials change?

If your database credentials change, you will need to update your shell script to reflect the changes.

Can I add another database to backup?

Yes, you can add another database by adding another mysqldump command to your current shell script. Be sure to use your new database's credentials as well as modify the file that it writes to so that your script won't write to the same file twice.

My mysqldump file is not complete. Why is that?

If your mysqldump file is not complete, it is highly likely that it ran into an error while executing. To test this, run the mysqldump command in your shell outside of the script and see if any errors are returned. Also, it is possible that the mysqldump took longer to complete than the server allows processes to run for.