drizzle to mysql, stress free migration
TRANSCRIPT
Blue Lines and Gradients
MySQL to Drizzle, stress free migration
Andrew HutchingsSoftware DeveloperRackspace
[email protected]://www.linuxjedi.co.uk/
What is Drizzle?
KDE3 -> 4
New Frameworks
Easier for Developers
Improved Usability
MySQL to Drizzle, stress free migration
Picture by K Latham under a CC by NC SA 2.0 license
Installing
Installing in Ubuntu
Natty:sudo apt-get install drizzle
Lucid & Maverick:sudo apt-get install python-software-propertiessudo apt-add-repository ppa:drizzle-developers/ppasudo apt-get updatesudo apt-get install drizzle
(These instructions are also on http://docs.drizzle.org/)
Installing on Fedora
Create a /etc/yum.repos.d/drizzle.repo:[drizzle]name=drizzlebaseurl=http://rpm.drizzle.org/7-dev/fedora/$releasever/$basearch/enabled=1gpgcheck=0
[drizzle-src]name=drizzle-srcbaseurl=http://rpm.drizzle.org/7-dev/fedora/$releasever/SRPMSenabled=1Gpgcheck=0
Then:su -c yum install drizzle7-server drizzle7-client
(These instructions are also on http://docs.drizzle.org/)
Not Supported
Not Supported (yet)
Stored Procedures
Triggers
Views
SET data type
Partitions
Not Supported (at all)
MyISAM (as a main table)
Memory/Heap (as a main table)
FULLTEXT indexes
DELETE/UPDATE using multiple tables
Multiple timezones (only UTC)
Cartesian Joins
Protocol
Picture by Derek Kaczmarczyk under a CC by 2.0 license
Protocol
ExistingApplicationlibmysqlclientDrizzleNewApplicationlibdrizzle
Port 3306Port 4427UnixSocket
Settings
Picture by Travel Aficionado under a CC by NC 2.0 license
Settings
Configuration can be multiple files
Just use --config-dir=/path/to/files
By default no configuration is needed
Settings
Example config file 1:user=testmax-heap-table-size=32M
Settings
Example config file 2:innodb.buffer-pool-size=256Mmysql-protocol.max-connection=2000
Settings
Example config file 3:[innodb]buffer-pool-size=256M[mysql-protocol]max-connections=32
Authentication
Auth_allAuth_fileAuth_httpAuth_ldapAuth_pamAuth plugin system
Replication
Picture by Kristina Alexanderson under a CC by NC SA 2.0 license
MySQL Replication
SlaveMasterBinary LogIO threadSQL threadTransaction
InnoDB
Drizzle Replication
SlaveMasterInnoDBReplicationReaderReplicationApplierTransaction
Drizzle Replication
Start Master:$ drizzled --innodb.replication-log
Slave config (slave.cfg):master-host=my_hostmaster-port=3306master-user=my_usermaster-password=my_password
Start Slave:$ drizzled --plugin-add=slave --slave.config-file=/path/to/slave.cfg
Drizzle Replication
Disabling replication for a database or table:
drizzle> create table t2 (a int) replicate=FALSE;Query OK, 0 rows affected (0.087416 sec)
drizzle> create database test2 replicate=FALSE;Query OK, 1 row affected (0.280803 sec)
Schemas Removed Types
TINY/SMALL/MEDIUM INT
TINY/MEDIUM/LONG BLOB/TEXT
YEAR
SET
CHAR/BINARY
Schemas Changed Types
Time
In MySQL range is -839:59:59 to 838:59:59
In Drizzle range is 00:00:00 to 23:59:59
Schemas Changed Types
Date/Datetime
First valid date in MySQL 0000-00-00
First valid date in Drizzle 0001-01-01
Schemas Changed Types
INT UNSIGNED
UNSIGNED is only a constraint in Drizzle
For extended values use BIGINT
Schemas Changed Types
INT(n)
The braces are not used and ignored.
Schemas New Types
TIMESTAMP(6)
TIMESTAMP(6) stores microseconds
drizzle> select now();+----------------------------+| now() |+----------------------------+| 2011-04-14 00:17:14.638962 | +----------------------------+1 row in set (0.000396 sec)
Schemas New Types
UUID
Stores universally unique values
drizzle> select * from t1;+------+--------------------------------------+| a | b |+------+--------------------------------------+| 1 | 22681f76-41fa-4245-8461-96dba0961675 | +------+--------------------------------------+1 row in set (0.000409 sec)
Schemas New Types
BOOLEAN
Native BOOLEAN, stores in a single bit where engine supports it.
drizzle> select * from t1;+------+------+| a | b |+------+------+| 1 | TRUE | +------+------+1 row in set (0.000408 sec)
Schemas - Strictness
Erroneous ENUM actually errors
Divide by zero is an error
Not NULL actually means Not NULL
Character Sets
Drizzle supports 2 character sets:
UTF-8 and Binary
Information Schema
Now follows the SQL standard
Everything else is in DATA_DICTIONARY
Drizzledump Migration
Picture by Gianluca Neri under a CC by NC ND 2.0 license
Drizzledump Migration
History...
August 2010 in Seattle, the Developers meeting
Drizzledump Migration
DatabaseAre youMySQL?ConvertDataOutput
YesNo
Drizzledump Migration
MySQLdrizzledumpDrizzlestdout
Drizzledump Migration
MyISAM -> InnoDB
FullText Dropped
INT UNSIGNED -> BIGINT
TINY/SMALL/MEDIUM INT -> INT
TINY/MEDIUM/LONG BLOB -> BLOB
TINY/MEDIUM/LONG TEXT -> TEXT
YEAR -> INT
TIME -> INT
Drizzledump Migration
ENUM has DEFAULT NULL and convert '' to NULL
DATE/DATETIME DEFAULT 0000-00-00 -> DEFAULT NULL
DATE/DATETIME NOT NULL -> NULL
DATE/DATETIME 0000-00-00 data -> NULL
SET -> TEXT
Drizzledump Migration
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password > dumpfile.sql
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password --destination-type=database --desination-host=drizzle-host
Drizzledump Migration
Mysqldump:
CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` char(50) DEFAULT NULL, `c` datetime DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `t1` ( `a` INT NOT NULL AUTO_INCREMENT, `b` VARCHAR(50) COLLATE utf8_general_ci DEFAULT NULL, `c` DATETIME DEFAULT NULL, PRIMARY KEY (`a`)) ENGINE='InnoDB' AUTO_INCREMENT=1 COLLATE='utf8_general_ci';
Drizzledump:
Drizzledump Migration
INSERT INTO `t1` VALUES (1,'Hello','0000-00-00 00:00:00'),(2,'World','2011-04-14 06:50:52');
INSERT INTO `t1` VALUES (1,'Hello',NULL),(2,'World','2011-04-14 06:50:52');
Mysqldump:
Drizzledump:
My Data is Mangled!
Picture by Remi under a CC by NC ND 2.0 license
My Data is Mangled!
Whilst migrating...
''
Became
''
My Data is Mangled!
UTF8DataPHP AppMySQLServerUTF8TableDrizzledump
Latin1
UTF8
My Data is Mangled!
Solution?
--my-data-is-mangled
Drizzledump doesn't set MySQL connection character set.
Wordpress 3.1 Migration
Drizzledump Migration
Change 0000-00-00 to 0001-01-01 in code
Clint Byrum from Canonical is working on a plugin for this
The Future
Drizzledump From File
MySQLServermysqldumpMySQL dumpfileDrizzledump?DrizzleServerDrizzle dumpfile
MySQL to Drizzle Replication
Drizzle ServerMySQLServerBinlogReaderTransactionApplier
Questions?
Photo by under CC by NA SA 2.0 license
Drizzle Developer Day
Friday 15th April 9:30 16:00
Hilton Hotel (across the road)Coastal Ballroom