DRIZZLEDUMP(1)							      Drizzle							    DRIZZLEDUMP(1)

NAME

       drizzledump - a database backup program

SYNOPSIS

       drizzledump [OPTIONS] database [tables]

       drizzledump [OPTIONS] --databases DB1 [DB2 DB3...]

       drizzledump [OPTIONS] --all-databases

DESCRIPTION

       drizzledump  is	used  for backing up and restoring logical backups of a Drizzle database, as well as for migrating from a more traditional
       MySQL server.

       When connecting to a Drizzle server it will do a plain dump of the server.  When connecting to a MySQL server, it will automatically detect
       this, and will convert the dump of the tables and data into a Drizzle compatible format.

       Any binary data in tables will be converted into hexadecimal output so that it does not corrupt the dump file.

DRIZZLEDUMP OPTIONS

       The drizzledump tool has several available options:

       --all-databases, -A
	      Dumps all databases found on the server apart from information_schema and data_dictionary in Drizzle and information_schema, perfor-
	      mance_schema and mysql in MySQL.

       --force, -f
	      Continue even if a sql-error is received.

       --help Show a message with all the available options.

       --lock-all-tables, -x
	      Locks all the tables for all databases with a global read lock.  The lock is released automatically  when  drizzledump  ends.   Also
	      turns on --single-transaction.

       --single-transaction
	      Creates  a  consistent snapshot by dumping the tables in a single transaction.  During the snapshot no other connected client should
	      use any of the following as this will implicitly commit the transaction and prevent the consistency:

	      ALTER TABLE
	      DROP TABLE
	      RENAME TABLE
	      TRUNCATE TABLE

	      Only works with InnoDB.

       --skip-opt
	      A shortcut for --skip-drop-table, --skip-create, --skip-extended-insert and --skip-disable-keys

       --tables t1 t2 ...
	      Dump a list of tables.

       --skip-drop-table
	      Do not add a 'drop table' before each create.

       --show-progress-size rows (=10000)
	      Show progress of the dump every rows of the dump.  Requires --verbose

       --verbose, -v
	      Sends various verbose information to stderr as the dump progresses.

       --skip-extended-insert
	      Dump every row on an individual line.  For example:

	    INSERT INTO `t1` VALUES (1,'hello');
	    INSERT INTO `t1` VALUES (2,'world');

       This is useful for calculating and storing diffs of dump files.

       --skip-dump-date
	      Do not display the date/time at the end of the dump.

       --no-defaults
	      Do not attempt to read configuration from configuration files.

       --add-drop-database
	      Add DROP DATABASE statements before CREATE DATABASE.

       --compact
	      Gives a more compact output by disabling header/footer comments and enabling --skip-disable-keys.

       --databases, -B
	      Dump several databases.  The databases do not need to follow on after this option, they can be anywhere in the command line.

       --skip-disable-keys, -K
	      Do not dump the statements ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS

       --ignore-table table
	      Do not dump specified table, needs to be in the format database.table.  Can be specified multiple times for multiple tables.

       --insert-ignore
	      Add the IGNORE keyword into every INSERT statement.

       --no-autocommit
	      Make the dump of each table a single transaction by wrapping it in COMMIT statements.

       --no-create-db, -n
	      Do not dump the CREATE DATABASE statements when using --all-databases or --databases.

       --skip-create, -t
	      Do not dump the CREATE TABLE statements.

       --no-data, -d
	      Do not dump the data itself. Used to dump the schemas only.

       --replace
	      Use REPLACE INTO statements instead of INSERT INTO

       --destination-type type (=stdout)
	      Destination of the data.

	      stdout The default.  Output to the command line

	      database Connect to another database and pipe data to that.

	      New in version Drizzle7: 2010-09-27

       --destination-host hostname (=localhost)
	      The hostname for the destination database.  Requires --destination-type = database

	      New in version Drizzle7: 2010-09-27

       --destination-port port (=3306)
	      The port number for the destination database.  Requires --destination-type = database

	      New in version Drizzle7: 2010-09-27

       --destination-user username
	      The username for the destinations database.  Requires --destination-type = database

	      New in version Drizzle7: 2010-09-27

       --destination-password password
	      The password for the destination database.  Requires --destination-type = database

	      New in version Drizzle7: 2010-09-27

       --destination-database database
	      The database for the destination database, for use when only dumping a single database.  Requires --destination-type = database

	      New in version Drizzle7: 2010-09-27

       --my-data-is-mangled
	      If your data is UTF8 but has been stored in a latin1 table using a latin1 connection then corruption is likely  and  drizzledump	by
	      default  will  retrieve  mangled	data.	This  is because MySQL will convert the data to UTF8 on the way out to drizzledump and you
	      effectively get a double-conversion to UTF8.

	      This typically happens with PHP apps that do not use SET NAMES.

	      In these cases setting this option will retrieve the data as you see it in your application.

	      New in version Drizzle7: 2011-01-31

       --host, -h hostname (=localhost)
	      The hostname of the database server.

       --user, -u username
	      The username for the database server.

       --password, -P password
	      The password for the database server.

       --port, -p port (=4427)
	      The port number of the database server.

       --protocol protocol (=mysql)
	      The protocol to use when connecting to the database server.  Options are:

	      mysql The standard MySQL protocol.

	      drizzle The Drizzle protocol.

BACKUPS USING DRIZZLEDUMP

       Backups of a database can be made very simply by running the following:

       $ drizzledump --all-databases > dumpfile.sql

       This can then be re-imported into drizzle at a later date using:

       $ drizzle < dumpfile.sql

MYSQL MIGRATION USING DRIZZLEDUMP

       As of version 2010-09-27 there is the capability to migrate databases from MySQL to Drizzle using drizzledump.

       drizzledump will automatically detect whether it is talking to a MySQL or Drizzle database server.  If it is connected to a MySQL server it
       will automatically convert all the structures and data into a Drizzle compatible format.

       Warning
	      drizzledump will by default try to connect via. port 4427 so to connect to a MySQL server a port (such as 3306) must be specified.

       So, simply connecting to a MySQL server with drizzledump as follows will give you a Drizzle compatible output:

       $ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password > dumpfile.sql

       Additionally drizzledump can now dump from MySQL and import directly into a Drizzle server as follows:

       $ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password --destination-type=database --desination-host=drizzle-host

       Note   Please take special note of old-passwords-label if you have connection issues from drizzledump to your MySQL server.

       Note   If you find your VARCHAR and TEXT data does not look correct in a drizzledump output, it is likely that you have UTF8 data stored in
	      a non-UTF8 table.  In which case please check the --my-data-is-mangled option.

       When you migrate from MySQL to Drizzle, the following conversions are required:

	  o MyISAM -> InnoDB

	  o FullText -> drop it (with stderr warning)

	  o int unsigned -> bigint

	  o tinyint -> int

	  o smallint -> int

	  o mediumint -> int

	  o tinytext -> text

	  o mediumtext -> text

	  o longtext -> text

	  o tinyblob -> blob

	  o mediumblob -> blob

	  o longblob -> blob

	  o year -> int

	  o set -> text [1]

	  o date/datetime default 0000-00-00 -> default NULL [2]

	  o date/datetime NOT NULL columns -> NULL [2]

	  o any date data containing 0000-00-00 -> NULL [2]

	  o time -> int of the number of seconds [3]

	  o enum-> DEFAULT NULL [4]

FOOTNOTES

       [1]  There is currently no good alternative to SET, this is simply to preserve the data in the column.  There is a new alternative  to  SET
	    to be included at a later date.

       [2]  Currently,	ALL  date columns have their DEFAULT set to NULL on migration.	This is so that any rows with 0000-00-00 dates can convert
	    to NULL.

       [3]  This prevents data loss since MySQL's TIME data type has a range of -838:59:59 - 838:59:59, and Drizzle's TIME type  has  a  range	of
	    00:00:00 - 23:59:59.

       [4]  This is so that empty entries such as '' will convert to NULL.

AUTHOR

       Andrew Hutchings

COPYRIGHT

       2010, Drizzle Developers

7								   June 03, 2012						    DRIZZLEDUMP(1)