PHP and MySQL Web Development - P58: PHP and MySQL Web Development teaches the reader to develop dynamic, secure, commercial Web sites. Using the same accessible, popular teaching style of the first edition, this best-selling book has been updated to reflect the rapidly changing landscape of MySQL and PHP. | Restoring Your MySQL Database 257 Loading Data from a File One useful feature of MySQL that we have not yet discussed is the LOAD DATA INFILE statement. This can be used to load table data in from a file. It executes very quickly. This is a flexible command with many options but typical usage is something like the following LOAD DATA INFILE INTO TABLE books This will read row data from the file into the table books. By default data fields in the file must be separated by tabs and enclosed in single quotes and each row must be separated by a newline n . Special characters must be escaped out with a slash . All these characteristics are configurable with the various options of the LOAD statement see the MySQL manual for more details. To use the LOAD DATA INFILE statement a user must have the FILE privilege discussed earlier. Backing Up Your MySQL Database In MySQL there are two ways to do a backup. The first way is to lock the tables while you copy the physical files using a LOCK TABLES command. This has the syntax LOCK TABLES table lock_type table lock_type . Each table should be the name of a table and the lock type either READ or WRITE. For a backup you should only need a READ lock. Users and scripts will still be able to run read-only queries while you make your backup. If you have a reasonable volume of queries that alter the database such as customer orders this is not a practical solution. The second and superior method is using the mysql_dump command. Typical usage is something such as mysqldump --opt --all-databases This will dump a set of all the SQL required to reconstruct the database to the file called . You should then stop the mysqld process for a moment and restart it with the --log-update logflle updates stored in the log file will give you the changes made since your dump. Obviously you should back up the log files in any normal file backup. Restoring Your MySQL Database If you need to restore your
