over its data files, which, in an active database, are constantly being updated and searched. If you copy a file thatMySQL 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 lockingthe tables).2. Copy the data files.3. Tell MySQL to allow updates again (by unlockingthe tables).Not performing these steps could result in an incomplete backup.Fortunately MySQL includes two programs to make backups easier.Using mysqldumpThe easiest and most flexible backup program is called mysqldump. It can create a SQL script thatrepresents the structure and data contained in your database. It can also create a CSV, delimited, or XML textfile for your database and data. It isn't the fastest way to back up your database, but it is often plenty fast. Sinceyou 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.sqlEnter password: cold1:~$ At the prompt, type your root password. You'll see no results.
OBSERVE:cold1:~$ mysqldump-u root -p sakila> backup.sqlWe ran the mysqldumpprogram and backed up the sakiladatabase, using the standard Unix outputredirection—>—to save to a backup file named backup.sql.To see the contents of the beginning of the backup file, we'll use the headcommand.Type the following at the Unix prompt:cold1:~$ head backup.sql -n 30OBSERVE:-- 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_TIMESTAMP,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 yourdatabase. Next is the table structure.The first several lines are comments that tell us t he version of mysqldump (10.11), the host anddat abase, the version of the mysql server, and some MySQL-specif ic set t ingsto help load the file intoa different MySQL server. The next lines drop and recreat ethe act ortable.