How to Backup & Restore MySQL Database

MySQL is one of the most popular open source database tools. This tool is widely used by hosting providers for managing your website database. You can find that easily in your cpanel. MySQL offers you a very easy to use interface to create a database and assign a user to it. Another widely used online SQL tool is phpmyadmin. The best part to use phpmyadmin is customizing creation of each SQL table. Somehow in phpmyadmin a novice can easily backup and restore data. While in Cpanel you get option to restore your entire database back in the public folder. But remember that there are process involved in backup and restore. Do not go for overwriting your files and keep on adding directories of backup which can consume your web space. Here I had listed of the best methods that can be used to backup and restore your Sql database. The entire article is usually based on backing up database of a website. Because you will need the data at anytime. Like if you discontinued your site then you can have a backup for future reference, or for security measure. In my views it is very important that you should keep an incremental backup of your database. The easiest option where no technical interference involved is to go with dedicated support by the hosting providers. Sometime they offer you the service for free under the scheme you purchased or sometime you need to pay additional. And if you do not want to spend more or want a satisfying way of your data backup then you can try reading below to get in detail information about backing up your database with manual and automatic process. Your database is the core of your site. If it is lost then your entire site will be down. Because of day to day rising threats it has become necessary to do this.

There are certain which forces to keep in mind that your database should be backup at a particular time period and kept securely. Some of them important reasons are, like when you are working in development part of your site, you do constant changes. Some wrong changes can bring your entire site down. A backup of database with themes and other file can help you to restore the site as it is. Another reason is that if your site is hacked and the hacker has deleted your database, you can get back some help from the backup. It takes times to make a database but it looses instantly within second due to various issue. To safeguard that backup is a good practice. This guide mainly details with backup solutions for MySQL. But there are other references which are helpful for all. The backup is not just for keeping a copy of your database somewhere else, but it also helps you to common error of database. There are common issues of system crash or any kind of hardware failure. MySQL comes with many features in which backup is vital strategy. It offers a number of methods that you can choose as per your need. I had listed some of the methods below which can be done from MySQL itself.

http://i89.photobucket.com/albums/k214/Deni-/mysql.jpg

What are the types of Backup Methods involved in MySQL

MySQL has a number of backup methods. These methods can be used as per your choice and needs. Anyhow to understand each of them is important. Or else you can miss some important feature of MySQL. I had listed the methods with a short description and the basic method involved.

• Logical Backup: Logical backup consist of backup of logical database structure. Can be done while sever is running.
• Physical Backup: It consists of copy of entire database and files. This is simply the copy of your data folder. Can be done only in offline mode.
• Online Backup: Online backup of MySQL database. Can be done while sever is running.
• Offline Backup: Easy backup procedure to create a copy of database. Can be done only in offline mode.
• Local Backup: Backup is performed from the host machine where the data is stored or MySQL server runs.
• Remote Backup: Backup is performed from distant client via remote access to the host machine where data is stored or MySQL server runs.
• Full Backup: It consists of copy of entire data of MySQL server. It can be done at a frequent period of time.
• Incremental Backup: This is done to update the changes made in the database only. Whenever the database is alter this type of backup updates the alter part.

Above is just a short overview of types of backup involved. You can perform a backup as per your need. To initiate a backup it is necessary to create a backup and recovery strategy. A backup and recovery strategy consists of steps to be taken at the time of any failure. It includes all those points which can be responsible for site down. So you must first decide the best strategy for your server and then choose the right backup method. The same method can be implemented on multiple servers also.

Backup MySQL database with mysqldump

This is one of the easiest manual processes for creating a backup of your MySQL server files. Anyhow you need a bit technical know how about how server works. For that to backup your database you will need to first telnet your server. Then you can use mysqldump command to get a dump of your entire database. It is possible to redirect the backup file to location you want. Other than this you can also have a SQL statement of your backup file which can help you to recreate your tables back again and restore database. For doing this you will need to understand the mysqldump syntax. There are very less commands use there. I had mentioned below the commands and a short description so that you can figure out how this works.

Syntax: mysqldump -u [username] -p [password] [nameofdatabase] > [nameofbackup.sql]

You can understand easily what the entire commands says. You will need to provide the database username and password to access the data. The file which is generated is a dump file that has SQL statements in it. When you are restoring the backup you will need to create table for this and then run the backup. If you are restoring then to overwrite old database you can try using --add-drop-table before the -u username. This will remove the method of manually deleting your old database. Anyhow with this method it is also possible to backup table’s specific database also.

Backup MySQL database with cron (Linux)

You can use a service called cron to keep a backup of your database. This is done mostly on the host machine. But this is a nice feature and easy to use which I want to recommend. This is for Linux users only. It is recommended to have a backup of your database everyday. But doing this manually can be annoying. So you can try to set a schedule with cron for this. Cron is a simple scheduling utility that is found in most Unix/Linux os. It runs automatically till the server is on. You can setup a backup task in it. You will need to edit the configuration file of file which is /etc/crontab. Before setting up a backup it is necessary to learn the syntax. To setup the process first create a directory where you want to keep the backup files. Then the process involves of making a script to run the backup. Below is a common sample of a script that can be use for this process.

Example Script:

#! / Bin / sh
mysqldump-uroot-ppwd - opt db1.sql> / nameofdirectory/nameofdatabase.sql
cd / home / user / rnameofdirectory /
tar-zcvf nameofdirectory_ $ (date +% d% m% y). tgz *. sql
find-name '*. tgz'-type f-mtime +2-exec rm-f {} \;

You will need to save the script in nameofscript.sh format and place it in the home directory. Here are some details on the script. The first line #! / Bin / sh shows that it is an executable script. The second link define the mysqldump command which is used for copying the database. Next to it uroot and uppwd needs the user access and password to run the script. in the place of root type the admin account and pwd type the password. Once you are done with changes in the script you will need to open the cron editor to configure the script for automate backup process. In the file type 0 1 *** / home / user / scripts / nameofscript.sh. Look at the syntax properly. The syntax is divided into following which begins from left. Minutes (0-59) hours (0-23) day (1-31) month (1-12) days of week (0-6 with 0 = Sunday) full location off script.

AutoMySQLBackup :

This is another script that can be used with cron to create a database backup as you need. If you do not want to go with manual configuration of cron for settings up backup then you can use AutoMySQLBackup script for the processor. AutoMySQLBackup is another way to backup of MySQL databases into separate files. AutoMySQLBackup has the distinction of performing safety guards on a daily, weekly and monthly through the mysqldump tool, and you can store only a file copy or more separate files for each database that can be compressed (with gzip and bzip2). Installation is very simple, just download the script from SourceForge, modify some parameters depending on your MySQL configuration such as user and password, the location of the backups, etc.

Features :

• Backup multiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database separately)
• Backup all databases to a single backup file or to a separate directory and file for each database.
• Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
• Can backup remote MySQL servers to a central server.
• Runs automatically using cron or can be run manually.
• Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases).
• Can email the compressed database backup files to the specified email address.
• Can specify maximum size backup to email.
• Can be set to run PRE and POST backup commands.
• Choose which day of the week to run weekly backups.

Backup MySQL database in XML File :

XML file is the most common way to import plug-in inside a php site. It is possible to backup your database in the form of xml file so that you can easily import the file wherever needed. This is also one of the manual processes. But you must know how to configure this properly or else you will face errors while restoring your database. To run a backup you will need to run a setup of commands. A sample command is : mysqldump --opt -u joey -p 1234 neon > neon.sql. You can try mysql show databases to get the list of all commands in MySQL.

Backup MySQL database via SSH :

You can also take a MySQL database backup via SSH. This is a fantastic tool that kills the browser virtually all tasks related to its own database. If you go to his web space has an SSH root access or a server running MySQL and does not want to have accessible from the outside. This can be used when you are accessing your database from any other system. Should you have times the problem is that your MySQL dump for phpmyadmin is too big then the only cure is SSH access database backup instructions!: best created a folder: mkdir sql_backup cd sql_backup now a new MySQL backup create: mysqldump -h HOSTNAME -u USERNAME -p PASSWORD DATABASENAME > backup.sql. the password must be directly on-p are Importing MySQL dump into a new database. Ex. mysql -h HOSTNAME -u USERNAME -p PASSWORD DATABASENAME < backup.sql now you should have created a perfect MySQL Dump.

Tools to Backup MySQL Database

There are free and paid tools on web that can help you to backup your database. They are awesome and easy to use. I had tried to find the list of most server friendly tools you can use without hassle. Always whenever you are going for some advance options do not forget to read the documentation. The documentation guides you about the common methods and causes of backup and restore. Also there are detail steps mentioned in performing those steps easily.

phpMyBackupPro :

This is one of the easiest tools you can use. It is a web based backup tool that can be used to backup MySQL database. It is a free tool. This tool allows you to backup and manage your database. If you want you can download or mail the database also. The tool offers you web based or ftp backup solution. You need to provide the configuration settings in the tool itself and you can manage it from web. There are fewer options here which does confuse a user. There are some features which I had listed below.

Major features:

• backup of one or several databases with or without data, table structure, ...
• three types of compression (no compression, gzip or zip)
• scheduled backups (by a small PHP script which must be included in an existing PHP script)
• interface for management of the backups (viewing, restoring, downloading, deleting)
• backup directly onto FTP server and sending of backups by email
• platform independent - only webserver and PHP needed to run e.g. on MS Windows, Linux or Macshell mode (to use manually or by cron script)
• backup of whole file directories (on a FTP server).
• backup databases from different accounts on several MySQL servers
• one installation can be used for all MySQL users of one MySQL server (used by webhosters)
• highest security through two alternative login methods (HTTP or HTML authentication)
• easy to use interface and very easy to install
• several language packages available
• online help (included in pMBP) and FAQ on this website

phpmyadmin :

phpmyadmin is another easiest way to backup your sql database. It offers you a simple import and export button by which you can save the SQL file in text format. Later on you can import that file in your server. But remember that there is a size limit to upload a file in sql. You will need to set the setting limit to higher to upload a large database. phpmyadmin offers you a number of options while taking the database backup. Like :

• Structure only
• Structure and data
• Data only
• Export to XML format
• LaTex

You can choose the best one with this. The tool offers you easy restoration within minutes. There are no automated support available here by which you can schedule your backup. Mostly backup works manually.

Amazon S3 :

Amazon Simple Storage Service ( Amazon S3 ) is a web service that allows anyone with storage needs of data to exploit the technological infrastructure and knowledge gained in the field of high reliability, scalability and security of the giant world of online shopping . Compared to other online storage and backup solutions, the main advantage of Amazon S3 is the cost calculated based on the actual use of resources, rather than a fixed periodic fee. In addition to archiving and backup, the nominal price per gigabyte (GB) of storage and traffic actually used and the ability to access resources via HTTP Amazon S3 make an ideal solution for webmasters who are faced occasional or unexpected traffic surges and have a need to distribute the workload of the web server without resorting to expensive hardware upgrades or incurring overage charges imposed by the provider. To use this you will need to use a script which can connect your MySQL server with Amazon S3. There are ample of free resources available for that on web. To use the web service you must register on the portal Amazon Web Services Store.

Amazon S3 does not work like other online storage space. This is a web service offering unlimited storage, while a total abstraction of the physical layer. You do not worry about the address of the server, its connectivity, issues related to geographic location, or disk space. The system stores arbitrary objects up to 5 GB, organized into "buckets", sort of files online. You can interact with your records and objects using the REST or SOAP protocols. Individuals can use it, even if the API intends it more for professional use. You remain completely free of the type of files you want to store photos, videos, files, etc.