MySQL Utilities — The Database Operations


Version 1.5 of the MySQL Utilities are divided into five groups — Database Operations, General Operations, High Availability Operations, Server Operations, and Specialized Operations.

The Database Operations are mysqldbcompare, mysqldbcopy, mysqldbexport, mysqldbimport, and mysqldiff.

mysqldbcopy is used to copy databases between servers (or clone them on the same server. The default is to copy (or clone) everything or the –exclude option to exclude an object, change the storage engine with –new-storage-engine, and locking of the source databse can be turned off with –locking. There is even a –rpl option to pass replication statements so the the new copy a clone of the source server or sync the new copy to the same master as that the slave used for the source database. There is a –drop-first to make a fresh copy, -all to copy all databases

mysqldbcopy --source=root:hidave@localhost --destination=root:hidave@localhost world:world_copy
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_copy
# Copying TABLE world.City
# Copying TABLE world.Country
# Copying TABLE world.CountryLanguage
# Copying data for TABLE world.City
# Copying data for TABLE world.Country
# Copying data for TABLE world.CountryLanguage
#...done.

More details on the man page.

After copying (or cloning) a database it is easy to double check
your work with mysqldbcompare.
mysqldbcompare --server1=root:hidave@localhost world:world_copy --run-all-tests
# server1 on localhost: ... connected.
# Checking databases world and world_copy on server1
#
# Object definitions differ. (--changes-for=server1)
#

--- `world`
+++ `world_copy`
@@ -1 +1 @@
-CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */

# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE City pass pass pass
# TABLE Country pass pass pass
# TABLE CountryLanguage pass pass pass

# Databases are consistent.
#
# ...done

More at the man page.

It is easy to use mysqldiff to check differences between two instances. For demonstration purposes, a dummy column was added to wold_copy.City.
mysqldiff --server1=root:hidave@localhost world:world_copy
# server1 on localhost: ... connected.
# Comparing `world` to `world_copy` [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `world`
+++ `world_copy`
@@ -1 +1 @@
-CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */
Compare failed. One or more differences found.

With version 1.5 id comes the –diff to display the differences.

The mysqldbexport command exports metadata, data, or both. There is a –bulk-insert can be used with –export=DATA to export data from a database for bulk import.
mysqldbexport --server=root:hidave@localhost world
# Source on localhost: ... connected.
SET FOREIGN_KEY_CHECKS=0;
# Exporting metadata from world
DROP DATABASE IF EXISTS `world`;
CREATE DATABASE `world`;
USE `world`;
# TABLE: world.City
CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
# TABLE: world.Country
CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: world.CountryLanguage
CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#...done.
SET FOREIGN_KEY_CHECKS=1;

The man page.

And fianlly mysqldbimport is used to read metadata, data or both. Note that if an object exists on the destination server it will be dropped. If you need to copy only the data after a truncate table, use the –import=data option. See the man page for details.

Next time: The MySQL Utilities General Operations

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s