Backing Up and Restoring Data MySQL

Even the best maintained databases occasionally develop problems. Hardware failures, in particular, can really throw a monkey wrench into your web pages. Now that you're using a database, just backing up the files (HTML, PHP, and images) on your web server isn't enough. There's nothing worse than informing your web users that they have to reenter information, such as their accounts, or have to recreate your catalog items. Having a complete backup can make the difference between an hour of down time and having to recreate the wheel. There are a couple of tactics that we'll discuss for backing up your database data.

Copying Database Files

You can also do a simple file backup of your MySQL database's datafiles, in the same way that you can back up your HTML and PHP files. If you can back up files, you can back up the MySQL database files.

We don't recommend this tactic for moving a database from one machine to another server, since different versions of MySQL may expect these files to be in a different format. MySQL stores its datafiles in a special data directory that is usually located in C:\Program Files\MySQL\MySQL Server 4.1\data\[database_name] on Windows and in /var/lib/mysql on Unix variants such as Linux and Mac OS X.

To fully back up and restore a MySQL database using your current datafiles, all the files must be replaced in the same directory from which they were backed up. Then, the database must be restarted.

The mysqldump Command

It's better to use the MySQL command-line tool for making complete database backups. The same tools you'll use to back up and restore can also be used to change platforms or move your database from one server to another; mysqldump creates a text file containing the SQL statements required to rebuild the database objects and insert the data. The mysqldump command is accessible from the command line and takes parameters for backing up a single table, a single database, or everything. The command's syntax is:

mysqldump -u user -p objects_to_backup

The default mode for mysqldump is to export to backup and then to standard output, which is usually the screen.

Backing up

We're going to show you the commands to back up a database called test from the shell prompt.

mysqldump -u root -p test > my_backup.sql

This tells mysqldump to log into the database as the root user with a password of barney, and to back up the test database. The output of the command is saved to a file called my_backup.sql with the help of the redirect character also known as the greater-than symbol >.

To back up only a single table from a database, simply add the table name after the database name. For example, the command below illustrates how to back up only the authors table:

$ mysqldump -u root -p test authors > authors.sql

Most of the time, you'll just want to back up everything in the database. To do this, use the --all-databases command-line switch. The resulting database backup file will contain the commands necessary to create the databases and users, making a complete database restore a snap. Here's how to use this parameter:

$ mysqldump -u root -p --all-databases > my_backup.sql

To create an empty copy of your databasejust the structurefor testing, use the --no-data switch:

$ mysqldump -u root -p --no-data test > structure.sql

You can also do the opposite and just back up the data with the --no-create-info switch like this:

$ mysqldump -u root -p --no-create-info test > data.sql

Of course, having a backup of your database doesn't do you much good if you don't know how to restore the database from it.

Depending on how critical your data is and how often it changes, you can determine how often to back it up. As a rule, weekly, bi-weekly, and monthly are the most common schedules. If your business is completely dependent on your database, you should do a weekly backup schedule, if not backing up daily. Also, keeping a copy of the data in a separate location is a good idea in the event of large scale disasters, such as a fire. A client of ours keeps bi-monthly backups in a fire safe at the office, whereas another client sends the data to a backup service. A backup service can use physical hard drives, tapes, or CDs, or can log into your server and perform the backup electronically.

PHP Functions Php tutorial - imagemagick php.ini Basics PHP Sessions Cookies Versus Sessions PHP Web-Related Variables PHP ERRORS maximum size of a file uploaded Php Image upload php file_get_contents	MySQL Data on the Web What are GET and POST php and pdf $_ENV and $_SERVER PEAR with php SELECTING DATA PHP prevent hijacking with PHP LAMP PHP MySQL Functions PHP Zip File Functions	Substrings PHP PHP Variable names PHP magic methods How to get current session id Add variables into a session $_GET , $_POST,$_COOKIE different tables present in mysql PHP CURL php Sessions page PHP-sorting an array
PHP-count the elements of array Operators for If/Else Statements PHP file uploading code PHP global variables Testing working using phpinfo PHP Code for a Valid Number PHP-Associative Arrays PHP mvc tutorial PHP get_meta_tags-Extracts difference between print and echo	PHP best tutorial-PHP variables Reading DOC file in PHP PHP interview questions convert time PHP PHP implode array elements header function-PHP PHP-Renaming Files Directories PHP Classes in_array function in PHP keep your session secure PHP	Web Application with PHP What is SQL Injection PHP-extract part of a string PHP urlencode PHP- know browser properties PHP- Extracting Substrings Checking Variable Values /Types PHP-best 20 Open Source cms IP AddressPHP PHP-Scope Resolution Operator
how create new instance of object how eliminate an object PHP- ob_start XML file using the DOM API PHP- MVC PHP- CAPTCHA PHP- Position of a Value in an Array PHP-Mail Functions PHP-difference include vs require calculate the sum of values in an array	PHP-total number of rows Show unique records mysql MySQL Triggers MySQL data directory MySQL Subqueries PHP- Networking Functions PHP- Operators Restore database Conditional Functions mysql PHP-function overloading	Friend function mysql_connect /mysql_pconnect PHP-Error Control Operators what is IMAP Apache-Specific Functions Send Email from a PHP Script SQL inherently WAMP, MAMP, LAMP Php tutorial-SYMBOLS Table Types-MySQL
PHP-Encryption data management PHP Array Running MySQL on Windows Maximum Performance MySQL XML-RPC PHP-static variables Advanced Database Techniques FTP Codeigniter Apache Pool Size	Why NoSQL MySQL Server Performance Database software SQL Interview Answers PHP Redirect	PHP Interview Questions with Answers Advanced PHP