Learning Mysql
Dumping a Database as SQL Statements
Download 4.24 Mb. View original pdf
|
| Dumping a Database as SQL Statements You can make a database backup by generating a file of all the SQL commands necessary to recreate the existing database structure from scratch, and (if you want) the SQL commands to insert all the data. Note that this is different from exporting table contents using the SELECT INTO OUTFILE syntax that we saw in Writing Data into Comma- 353 Delimited Files in Chapter 8 since we get the actual SQL INSERT statements, rather than just the raw values. SQL statements are an excellent form of backup. One of the easiest ways to safely backup your data is to export it from MySQL, write it to stable media (such as a high-quality recordable CD or DVD, and store it in a safe location. Since the file of SQL statements contains just text, it can be compressed to a fraction of its original size using a compression program. Suitable compression programs on Linux or Mac OS X are gzip , bzip2 , or zip you can also use the StuffIt program under Mac OS X. Under Windows, you can compress a file by right-clicking on the file icon and selecting Send To and then Compressed (zipped) Folder You can also use third-party tools such as WinZip and PKZIP. Let’s try a simple example to backup the music database. To do this, we’ll run the mysqldump utility and save the output to the file music.sql: $ mysqldump --user=root --password=the_mysql_root_password \ --result-file=music.sql music This tries to create the file music.sql in the current directory. If you don’t have permission to write to the current directory, specify a path to another location—for example, /tmp/music.sql under Linux or Mac OS Xor C:\music.sql under Windows. Now open this music.sql file using a text editor if you’re unsure about how to do this, see the instructions in Using a Text Editor In the file, you’ll see something like this- MySQL dump 10.10 -- -- Host localhost Database music- ------------------------------------------------------ -- Server version 5.0.22 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIMEZONE' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `album` -- DROP TABLE IF EXISTS `album`; CREATE TABLE album ( `artist_id` smallint(5) NOT NULL default '0', `album_id` smallint(4) NOT NULL default '0', `album_name` char) default NULL, PRIMARY KEY (`artist_id`,`album_id`) Download 4.24 Mb. Share with your friends:
|