Has MySQL Grown Up?

Mark Stanton grude

A brief history

A brief history1995 Named after Monty’s daughter (My)

2000 Open sourced

2001 3.23

2003 4.0

2005 5.0 Oracle buys the company behind InnoDB

2006 MySQL hits 33% market share, Oracle tries to buy

2008 5.1 Sun acquires MySQL for $1 billion, Monty leaves

2010 5.5 Oracle buys Sun for $7.4 billion

Where are we now?

• MySQL is growing up

• Oracle are showing love

• Facebook, Google & others are contributing

• Make sure you are running:

• 5.5

• recent version of 5.1

The wonderful world of Storage Engines

Storage Engines

• Storage engines are pluggable table types

• You can have multiple storage engines within each a DB

• Transparent to clients (mostly)MyISAM

InnoDB

MyISAM

• Simple, light weight

• File based

• FULLTEXT indexes

• Table level locking

InnoDB

• Hot backup

• Active development

• Optimised for multi-core

• Default from 5.5 on

• No FULLTEXT (until 5.6)

• ACID compliant

• Crash safe

• Foreign keys

• Row-level locking

• Table compression

NDB - MySQL Cluster

• High Availability

• Tables clustered across nodes

• Auto fail & recover

• Auto partitioning

• No FK, limited transactions

• Poor multi-table joins

• Pretends to be MySQL, really whole other product

Other Storage Engines

• MERGE

• MEMORY

• BLACKHOLE

• CSV (CREATE TABLE export ENGINE=CSV SELECT foo, bar FROM table)

Storage Engines:Conclusion

• InnoDB is the new default & new direction

• MyISAM is the past

• If you are still on MyISAM, think of moving

• NDB is very interesting for special cases

Replication

Why Replicate?

• Scale out

• High availability

• Geographic

• Backup

• Analytics / Business Intelligence

Under the Hood:Logs

Step 1

Step 2 Step 3

Step 4

IO Thread

SQL Thread

Under the Hood:Format

• Statement: UPDATE table..

• Records 1100101100111

Under the Hood:Semi-sync

Step 1

Step 1 Step 1

Step 2

Configuration: Master-Slave

• Master my.cnf:server-id = 10log-bin=mysql-M1-bin

• Master DB:mysql> CREATE USER 'repl';mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl';

• Slave my.cnf:server-id = 11

• Slave DB:mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_PORT=3310, MASTER_USER='repl';

Name Server-Id Port

M1 10 3310

M1-S1 11 3311

SHOW SLAVE STATUSmysql> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Server_Id: 10 Master_Host: localhost Master_Port: 3310 Master_User: repl Master_Log_File: mysql-M1-bin.000017 Read_Master_Log_Pos: 107 Exec_Master_Log_Pos: 107 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Seconds_Behind_Master: 0 Master_Server_Id: 10

In Practice

Multi-Master

M1

M1#S1

M2

M2#S1

Why Multi-Master?

• Active/Passive sites

• High Availability

• Geographically dispersed writes

Multi-Master: Considerations

Multi-Master replication needs at least one of:

• Conflict Avoidance

• Conflict Resolution

• Hope

Multi-Master: Considerations

• Conflict Avoidance

• UUIDs

• Auto increment hacksauto_increment_increment = 2auto_increment_offset = 1

• Semi-sync

• Active/Passive

• Ticket server

Multi-Master: Considerations

• Conflict Resolution

• Cluster/NDB

• Galera

• or roll your own...

Configuration: Multi-Master

• M2 as slave of M1

• M1 as slave of M2

• M2-S1 as slave of M2

Name Server-Id Port

M1 10 3310

M1-S1 11 3311

M2 20 3320

M2-S1 21 3321 M1

M1#S1

M2

M2#S1

Review of Topologies

Master

Slave

Master

Slave

Slave

Mul$%Slaves

Master Master Master

Mul)*Slaves

Mul)*Slaves

Master

Slave

Master

Slave

Master Master

Connector/J

• MySQL’s Connector/J supports:

• Failover

• Load Balancing

• (Replication)

JDBC Connection Syntax

jdbc:mysql://hostname:port/dbname?property=value

jdbc:mysql://localhost:3306/test?username=root

JDBC Failover

jdbc:mysql://host1:port1,host2:port2/dbname

JDBC Load Balancing

jdbc:mysql:loadbalance://host1:port1,host2:port2,host3:port3/dbname

Warning

• Failover doesn’t attempt to be transparent

• ColdFusion doesn’t allow you full control

• Check/upgrade your Connector/J version

• Set connectionTimeout, socketTimeout and blacklistTimeout carefully

MySQL Enterprise Monitor

MySQL Enterprise Monitor

• Heat maps

• Charts

• Query analyser

• Replication status

• Configurable advisors & email alerts

• Event logs

MySQL Enterprise Monitor

• Server with agents for each mysqld

• Query analyser can have performance hit

• Non-free

Backing Up MySQL

• Several approaches:

• Copy DB files

• mysqldump

• binlog

• Backup to slave

• mysqlbackup

Backup: File copy

• Enable read lock:mysql> LOCK TABLES READ;mysql> FLUSH TABLES;

• Run file copy:$ cp -R ./myDB /mybackups/myDB

• Unlock tables:mysql> UNLOCK TABLES;

Backup: mysqldump

• Creates an sql script containing commands to recreate database

• Run from command-line:$ mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

Backup: binlog

• The binary log:

• records statements

• can be replayed from arbitrary point

• need to know where to replay from

• Rotate binlog mysql> FLUSH LOGS;

• Then copy relevant files

Backup: Slave

• Setup dedicated slave for backup purposes

• Shutdown slave

• Run backup

• Restart slave

Backup: mysqlbackup

• Non-free enterprise backup tool

• Features:

• Hot

• Incremental

• Compressed

• Verification

Summary of Backup Options

copy DB files mysqldump binlog backup to slave mysqlbackup

Hot/Read-Lock Lock Lock (MyISAM)Hot* (InnoDB) Hot Hot Lock (MyISAM)

Hot (InnoDB)

Incremental No No Yes No Yes

InnoDB Support No Yes Yes Yes Yes

Restore Speed Fast OK ** OK Fast Fast

* use --single-transaction for InnoDB** disable for autocommit/FK checks in InnoDB

Questions?

• Mark Stanton

• http://blog.gruden.com

• mark@gruden.com

• @MarkStanto