If you copy a file that MySQL is updating can you be sure that your copy is

If you copy a file that mysql is updating can you be

This preview shows page 71 - 73 out of 100 pages.

over its data files, which, in an active database, are constantly being updated and searched. If you copy a file that MySQL is updating, can you be sure that your copy is valid? In order to copy MySQL's data files, you would need to: 1. Tell MySQL to disallow updates to your tables (by locking the tables). 2. Copy the data files. 3. Tell MySQL to allow updates again (by unlocking the tables). Not performing these steps could result in an incomplete backup. Fortunately MySQL includes two programs to make backups easier. Using mysqldump The easiest and most flexible backup program is called mysqldump . It can create a SQL script that represents the structure and data contained in your database. It can also create a CSV, delimited, or XML text file for your database and data. It isn't the fastest way to back up your database, but it is often plenty fast. Since you are not touching MySQL's data files directly, you don't have to worry about locking or unlocking anything. Let's try it! First, make sure your MySQL server is running. Type the following at the Unix prompt: cold1:~$ mysqldump -u root -p sakila > backup.sql Enter password: cold1:~$ At the prompt, type your root password. You'll see no results.
Image of page 71
OBSERVE: cold1:~$ mysqldump -u root -p sakila > backup.sql We ran the mysqldump program and backed up the sakila database, using the standard Unix output redirection— > —to save to a backup file named backup.sql . To see the contents of the beginning of the backup file, we'll use the head command. Type the following at the Unix prompt: cold1:~$ head backup.sql -n 30 OBSERVE: -- MySQL dump 10.11 -- -- Host: localhost Database: sakila -- ------------------------------------------------------ -- Server version 5.0.41-OREILLY /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @[email protected]@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `actor` -- DROP TABLE IF EXISTS `actor`; CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL auto_increment, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIM ESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; The first few lines are comments and commands that prepare MySQL to use the script to restore your database. Next is the table structure. The first several lines are comments that tell us t he version of mysqldump (10.11) , the host and dat abase , the version of the mysql server , and some MySQL-specif ic set t ings to help load the file into a different MySQL server. The next lines drop and recreat e the act or table.
Image of page 72
Image of page 73

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture