Log in
Registration
- Welcome, visitor !
- |
- Log In | Register
- 05/20 GParted 0.12.1-5
- 05/19 LuninuX 12.00-beta2
- Chris Lawrence: cnlmisc 0.2 for R
- 05/19 Dream Studio 12.04
- Matthew Garrett: Fixing Asus UX21e unexpected power off
- Thorsten Glaser: MirBSD goes webservice – manpages and WTF‽
- Jonathan McDowell: 6 months of GNOME Shell
- Ubuntu developers: Leo Iannacone: Forum ubuntu-it gets a new theme and a new engine
- Richard Hartmann: Mongolia
- Maemo developers: Do Not Track support in Epiphany
- Pietro Abate: how to create VMs with ganeti / xen and dnsmaq
- Disk encryption in ROSA Marathon 2012
- Ubuntu developers: Francisco Molinero: Zentyal on Galapinux
- 05/19 Extix 10
- 05/19 Clonezilla 1.2.12-58
Backup MySQL Schemas to Files
MySQL is a popular and efficient relational database system available for various platforms.This article will introduce a technique how to export the table structure / database schema to a flat file. Generated files are human-readable and they can be utilized to restore databases without any datasets.
The illustrated technique will enable you to store the schema definition of databases to version control systems and you will be able to check database installations for schema modifications.
Contents
The Tool – mysqldump
‘mysqldump‘ is part of virtually every installation of MySQL. It is the command line based backup tool to dump databases or parts of them to files or file streams. It doesn’t matter whether the database system is available at your local host or whether it is reachable via network.
‘mysqldump‘ is able to extract data into text files of the following formats:
- Comma separated values (CSV)
- delimited text (SQL statements)
- XML
If you are doing a backup on the server and your tables all are MyISAM tables, consider using the ‘mysqlhotcopy‘ instead because it can accomplish faster backups and faster restores.
Examples
‘mysqldump‘ is a powerful tool. Here are some examples how to utilize the application and how to redirect the generated data stream to a file.
Backup a Database Schema
# perform a simple dump of the database schema definition without datasets $ mysqldump -d -h hostname -u db_username -p dn_user_password database_name > database_structure.sql # generated data stream: -- MySQL dump 10.13 Distrib 5.1.49, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: database_name -- ------------------------------------------------------ -- Server version 5.1.49-1ubuntu8.1 -- -- Table structure for table `example_table` -- DROP TABLE IF EXISTS `example_table`; CREATE TABLE `example_table` ( `id` int(11) DEFAULT NULL, `note` varchar(35) DEFAULT NULL, `example` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table `test_table` -- DROP TABLE IF EXISTS `test_table`; CREATE TABLE `test_table` ( `id` int(11) DEFAULT NULL, `note` varchar(35) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- Dump completed on 2011-02-24 0:59:31
Backup a Table Definition
# perform a simple dump of the table schema definition without datasets $ mysqldump -d -h hostname -u db_username -p dn_user_password database_name example_table > table_structure.sql # generated data stream: -- MySQL dump 10.13 Distrib 5.1.49, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: database_name -- ------------------------------------------------------ -- Server version 5.1.49-1ubuntu8.1 -- -- Table structure for table `example_table` -- DROP TABLE IF EXISTS `example_table`; CREATE TABLE `example_table` ( `id` int(11) DEFAULT NULL, `note` varchar(35) DEFAULT NULL, `example` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- Dump completed on 2011-02-24 0:59:54
Backup a Table Definition to XML
# perform a simple dump of the table schema definition without datasets $ mysqldump -d --xml -h hostname -u db_username -p dn_user_password database_name example_table > table_structure.sql # generated xml document: <?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="database_name"> <table_structure name="example_table"> <field Field="id" Type="int(11)" Null="YES" Key="" Extra="" /> <field Field="note" Type="varchar(35)" Null="YES" Key="" Extra="" /> <field Field="example" Type="int(11)" Null="YES" Key="" Extra="" /> <options Name="example_table" Engine="MyISAM" Version="10" Row_format="Dynamic" Rows="0" Avg_row_length="0" Data_length="0" Max_data_length="281474976710655" Index_length="1024" Data_free="0" Create_time="2011-02-23 23:58:36" Update_time="2011-02-23 23:58:36" Collation="latin1_swedish_ci" Create_options="" Comment="" /> </table_structure> </database> </mysqldump>