A MySQL Odyssey: A Blackhole crossover – The technical side of the preso.

Well.. just wanted to share the more technical details I went into when preparing the “A MySQL Odyssey: A Blackhole crossover” for the OpenSUSE Conference in Thessaloniki, July 2013. There’s a youtube version as well, of the actual session, on the opensuse channel. http://youtu.be/Nt2w1UvKEp0. (Audio kicks in at minute 6:40 so enjoy my miming act).

Here goes:

5.6 Enterprise Edition -> edelivery.oracle.com
Win:    mysql-5.6.11-MySQL Installer 5.6.11.0 Package-V38226-01.zip
Linux:  mysql-5.6.11 TAR for Generic Linux (glibc2.5) x86 (32bit)-V38228-01.zip

Windows         192.168.56.101
Oracle Linux    192.168.56.102    o.s. users root & mysql created.
SLES            192.168.56.103    o.s. users root & mysql created.

############################
# Windows                  #
# MySQL Replication Master #
############################

# Default sw install.

C:\my.ini
[mysql]
prompt        = Win \R:\m \d>\_
[mysqld]
basedir    =”C:/Program Files/MySQL/MySQL Server 5.6/”
datadir    =”C:/ProgramData/MySQL/MySQL Server 5.6/data\”

source $SAKILADB_PATH\sakila-schema.sql
source $SAKILADB_PATH\sakila-data.sql

use sakila;

# Master table we’ll be using:
CREATE TABLE `crossover` (
`ID` int(7) NOT NULL AUTO_INCREMENT,
`Name` char(20) NOT NULL DEFAULT ”,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB ;

# Inserting rows… so that we know we have some data:

 delimiter //
 DROP PROCEDURE IF EXISTS BlackholeInsert//
 CREATE PROCEDURE BlackholeInsert (p1 INT)
 BEGIN
     SET @x = 0;
     REPEAT
         INSERT INTO crossover SELECT NULL, '1thousand';
         SET @x = @x + 1;
     UNTIL @x > p1 END REPEAT;
 END
 //
 delimiter ;

Win 16:45 sakila>  call BlackholeInsert (1000);
Query OK, 0 rows affected (0.44 sec)

Win 16:50 sakila> select count(*) from crossover;
+———-+
| count(*) |
+———-+
|     1001 |
+———-+
1 row in set (0.00 sec)

# Ok, so there are 1001 rows (happens if we start from 0..) so…, fire me.

###############################
# Oracle Linux                #
# MySQL Replication BLACKHOLE #
###############################
 OLinuxRel6.3_x86_32_V33415-01_dvd.iso
 /usr/local/mysql-advanced-5.6.11-linux-glibc2.5-i686
 # Disable firewall for this 'staging' env.
 iptables -F
 iptables -L

vi ~mysql/.my.cnf
prompt = ol63 \R:\m \d>\_

# Replication 1st master – slave setup.
# Let’s dump the master database that we want to replicate ‘sakila’.
# 1st: create remote user to mysqldump:
Win 09:48 mysql> grant all on *.* to ‘root’@’192.168.56.102′ identified by ‘oracle';
# Test remote connection, from future slave to master:
ol63# mysql -uroot -poracle -h 192.168.56.101 -P3307 -e ‘status;’
# On slave, we dump the ‘sakila’ db, as it’s all we want to replicate.
# (remember we’ll still be bringing the entire binlog but just replicating the ‘sakila’ db, via ‘replicate-do-db=sakila’ in the my.cnf on all slaves.):
mysqldump -uroot -poracle -h 192.168.56.101 -P3307 -B sakila master-data=2 > dump_sakila.sql
mysql -uroot < dump_sakila.sql

# Starting replication:
change master to master_host=’192.168.56.101′, master_port=3307, master_user=’repl_user’, master_password=’oracle’, master_auto_position=1;
start slave;

# Checking it’s all ok:
ol63 11:00 (none)> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl_user
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: KHOLLMAN-ES-bin.000007
Read_Master_Log_Pos: 328039562
Relay_Log_File: ol63uek01-relay-bin.000002

..
.

ol63 11:00 (none)> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
+——————–+
5 rows in set (0.00 sec)

# Now to make sure we’re only going to replicate data from the ‘sakila’ db:
vi /etc/my.cnf
replicate-do-db =sakila

######################
# BLACKHOLE creation #
######################

# Master server:

ol63 17:25 sakila> select count(*) from crossover;
+———-+
| count(*) |
+———-+
|     1001 |
+———-+
1 row in set (0.00 sec)

ol63 17:25 sakila> show create table crossover;
| crossover | CREATE TABLE `crossover` (
`ID` int(7) NOT NULL AUTO_INCREMENT,
`Name` char(20) NOT NULL DEFAULT ”,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

ol63 17:25 sakila> alter table crossover engine=blackhole;
Query OK, 1001 rows affected (0.16 sec)
Records: 1001  Duplicates: 0  Warnings: 0

ol63 17:25 sakila> show create table crossover;
| crossover | CREATE TABLE `crossover` (
`ID` int(7) NOT NULL AUTO_INCREMENT,
`Name` char(20) NOT NULL DEFAULT ”,
PRIMARY KEY (`ID`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

ol63 17:25 sakila> select count(*) from crossover;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (0.00 sec)

################
# SUSE install #
################

SLES-11-SP2-DVD-i586-GM-DVD1.iso

# Due to Virtual Box ‘Install Guest Additions’ probs:
zypper update
‘shared folders’ now allowed. As well as a smoother mouse input & output.

# Install MySQL
# mysqldump of ol6 “sakila” db.
mysqldump -uroot -poracle -h192.168.56.102 -B sakila > dump_sakila_ol6.sql
# mysql creation of db.
mysql -uroot < dump_sakila_ol6.sql
##
## This will bring the table ‘crossover’ configured in the BLACKHOLE storage engine. We will want to then bring the data from Win and import into either ol63 or SUSE01.

# Setup replication as a slave of the ol6 ‘master’.
mysql -urepl_user -poracle -h192.168.56.102

###############################
# Exercises to test BLACKHOLE #
###############################

#################
# global check: #
#################

# Win Master:
show master status\G
# Will give us the binlog number & GTID set.
Win 17:54 sakila> show master status\G
*************************** 1. row ***************************
File: KHOLLMAN-ES-bin.000023
Position: 191
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 46b7581d-c398-11e2-b8f7-0a002700606f:1-2003246
1 row in set (0.00 sec)

# ol6 slave / master:
show slave status\G
ol63 18:04 sakila> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl_user
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: KHOLLMAN-ES-bin.000023
Read_Master_Log_Pos: 191
Relay_Log_File: ol63uek01-relay-bin.000080
Relay_Log_Pos: 413
Relay_Master_Log_File: KHOLLMAN-ES-bin.000023
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sakila

..
..
Retrieved_Gtid_Set: 46b7581d-c398-11e2-b8f7-0a002700606f:1000222-2003246
Executed_Gtid_Set: 46b7581d-c398-11e2-b8f7-0a002700606f:1-1033876:2000234-2003246,
926ab2f3-c9cb-11e2-a166-080027b43403:1-1019
Auto_Position: 1
# Will give us the slave IO state, Master_Log_File (should match master), Relay_Log_File (ol6’s naming of Win’s binlog), which master log file we have locally, i.e. the relay version “Relay_Master_Log_File”, and if the IO & SQL threads are running or not. Also, last 2 things, are the Retrieved & Executed GTID set.

# Now let’s see how ol6 as a Master is doing:
show master status\G
ol63 18:12 sakila> show master status\G
*************************** 1. row ***************************
File: olinux63-bin.000033
Position: 411
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 46b7581d-c398-11e2-b8f7-0a002700606f:1-1033876:2000234-2003246,
926ab2f3-c9cb-11e2-a166-080027b43403:1-1019
1 row in set (0.00 sec)

# and it’s SUSE01 slave:
show slave status \G
suse01 18:13 sakila> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.102
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: olinux63-bin.000033
Read_Master_Log_Pos: 411
Relay_Log_File: suse01-relay-bin.000050
Relay_Log_Pos: 611
Relay_Master_Log_File: olinux63-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: sakila

..
..
Retrieved_Gtid_Set: 46b7581d-c398-11e2-b8f7-0a002700606f:2000234-2003246,
926ab2f3-c9cb-11e2-a166-080027b43403:6-1019
Executed_Gtid_Set: 2a297856-ca44-11e2-a478-0800276bd7e8:1-1013,
46b7581d-c398-11e2-b8f7-0a002700606f:1-1033876:2000234-2003246,
926ab2f3-c9cb-11e2-a166-080027b43403:1-1019
Auto_Position: 1
1 row in set (0.00 sec)

##########################
# Truncate table on Win. #
##########################

Win 18:10 sakila> select count(*) from crossover;
+———-+
| count(*) |
+———-+
|     1001 |
+———-+
1 row in set (0.00 sec)

Win 18:16 sakila> truncate table crossover;
Query OK, 0 rows affected (0.12 sec)
Win 18:22 sakila> select count(*) from crossover;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (0.00 sec)

# Make sure the replication is done:
flush logs;

# Now take a look at SUSE01:
suse01 18:23 sakila> select count(*) from crossover;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (0.00 sec)

# And do Global Check across 3 servers, making sure all GTID’s are up to date and no errors nor stopped IO SQL threads.

##################################################################
# Insert rows on Win. See SUSE01. “flush logs” just making sure. #
##################################################################

#################################################
# Add a column on Win, trace the DDL to SUSE01. #
#################################################

########################################################
# Insert on ol63 and see that it replicates to SUSE01. #
########################################################
# DELETE & UPDATE don’t work on BLACKHOLE:
— Confirm lack of replication to ol6 & SUSE01.
— Replication breaks until we fix it, i.e. allow the logs to be applied.
# On Win Master:
Win 13:04 sakila> update crossover set name = ‘LessThan1Hundred’ where ID < 100;
Query OK, 99 rows affected (0.13 sec)
Rows matched: 99  Changed: 99  Warnings: 0

ol63 13:05 sakila> select * from crossover where id < 101;
+—–+——————+
| ID  | Name             |
+—–+——————+
|   1 | LessThan1Hundred |
|   2 | LessThan1Hundred |
|   3 | LessThan1Hundred |
|   4 | LessThan1Hundred |

..
|  99 | LessThan1Hundred |
| 100 | 1thousand        |
+—–+——————+
# On Oracle Linux Slave:

– Add a new column (with default non-null, sysdate) after last col. See it replicate to SUSE01.
– Default value for ol63 blackhole col
– insert rows in newcol, see them replicate to SUSE01.
– TESTING RBR & BLACKHOLE: delete a column on Win. & INSERT.

– Monitoring:
Monitoring staging env:
— ol63
– IO here vs IO on SUSE01.
— SUSE01
– IO monitoring. Use of differnet SUSE tools.

#############
# Use cases #
#############

############
# CSV tables
# Be careful of Unicode formats.
# And also, case-sensitive table names, i.e. best to use lower_case_table_names=1 (2 for OSx)
#

(cre_tab_agenda_copy_csv.sql)
create table agenda_copy (
`Title` enum(‘Mr’,’Mrs’,’Miss’,’Ms’,’M.’) not null default ‘M.’,
`First name` varchar(40) not null default ”,
`Middle name` varchar(40) not null default ”,
`Last name` varchar(40) not null default ”,

..
`General phone` char(30) not null default ”,
`General email` varchar(30) not null default ”,
..
) engine=CSV;

# We create the table on Win Master, this is then replicated across, we confirm this, and then change the engine=BLACKHOLE on ol63, which sends the same command to SUSE01.
# Then, on SUSE01 we change it to InnoDB.

## Win 09:54 sakila> load data infile “D:\Agenda_Copy.csv” into table agenda_copy columns terminated by ‘,’
## optionally enclosed by ‘\”‘ escaped by ‘”‘ lines terminated by ‘\n’ ignore 1 lines;
## select replace(replace(`General phone`,left(`General phone`,1),”),'”‘,”) from agenda_copy limit 5;

# Win 13:15 sakila> load data infile “D:\agenda_copy.txt” into table agenda_copy character set ‘utf8′ fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘\r\n’ ignore 1 lines;

# Let’s make a security sensitive column on the Master:
alter table agenda_copy add column SecretCol varchar(19) not null default ‘I am a secret value’ after `x`;
# Watch the column addition to all slaves.
# SUSE01:
suse01 13:52 sakila> select * from agenda_copy where `first name`= ‘Keith';
+——-+————+————-+———–+——–+———–+———+———–+————-+————–+————+———+——-+—————-+—————+—————+————-+——————–+———————+———————-+—————–+——————-+—————-+————————-+————–+————————+————————+————-+————+————+———-+—————–+——————+——————-+————–+—————-+————-+———————-+———–+———————+———————+—————–+—————-+—————-+————–+———————+———————-+———————–+——————+——————–+—————–+————————–+—————+————————-+————————-+—+———————+
| Title | First name | Middle name | Last name | Suffix | Job title | Company | Birthday  | SIP address | Push-to-talk | Share view | User ID | Notes | General mobile | General phone | General email | General fax | General video call | General web address | General VOIP address | General P.O.Box | General extension | General street | General postal/ZIP code | General city | General state/province | General country/region | Home mobile | Home phone | Home email | Home fax | Home video call | Home web address | Home VOIP address | Home P.O.Box | Home extension | Home street | Home postal/ZIP code | Home city | Home state/province | Home country/region | Business mobile | Business phone | Business email | Business fax | Business video call | Business web address | Business VOIP address | Business P.O.Box | Business extension | Business street | Business postal/ZIP code | Business city | Business state/province | Business country/region | x | SecretCol           |

..

# Insert a row:
insert into agenda_copy (`First Name`,`Last Name`) values (‘Keith’, ‘Hollman’);

# Confirm SUSE01 has the SecretCol column and value.
select * from agenda_copy where `first name` like ‘Keith%';

# Delete the SecretCol on ol6 (BLACKHOLE) and watch the column disappear from SUSE01.
alter table agenda_copy drop column secretcol;

# Insert another row on the Master, and make sure it’s got a SecretCol value:
insert into agenda_copy (`First Name`,`Last Name`) values (‘Keith2′, ‘Hollman2′);
select * from agenda_copy where `first name` like ‘Keith%';

# Make sure that SUSE01 only has the required info, i.e. no SecretCol info, and that replication hasn’t broken:
select * from agenda_copy where `first name` like ‘Keith%';
suse01 13:55 sakila> select * from agenda_copy where `first name` like ‘Keith%';
+——-+————+————-+———–+——–+———–+———+———–+————-+————–+————+———+——-+—————-+—————+—————+————-+——————–+———————+———————-+—————–+——————-+—————-+————————-+————–+————————+————————+————-+————+————+———-+—————–+——————+——————-+————–+—————-+————-+———————-+———–+———————+———————+—————–+—————-+—————-+————–+———————+———————-+———————–+——————+——————–+—————–+————————–+—————+————————-+————————-+—+
| Title | First name | Middle name | Last name | Suffix | Job title | Company | Birthday  | SIP address | Push-to-talk | Share view | User ID | Notes | General mobile | General phone | General email | General fax | General video call | General web address | General VOIP address | General P.O.Box | General extension | General street | General postal/ZIP code | General city | General state/province | General country/region | Home mobile | Home phone | Home email | Home fax | Home video call | Home web address | Home VOIP address | Home P.O.Box | Home extension | Home street | Home postal/ZIP code | Home city | Home state/province | Home country/region | Business mobile | Business phone | Business email | Business fax | Business video call | Business web address | Business VOIP address | Business P.O.Box | Business extension | Business street | Business postal/ZIP code | Business city | Business state/province | Business country/region | x |
+——-+————+————-+———–+——–+———–+———+———–+————-+————–+————+———+——-+—————-+—————+—————+————-+——————–+———————+———————-+—————–+——————-+—————-+————————-+————–+————————+————————+————-+————+————+———-+—————–+——————+——————-+————–+—————-+————-+———————-+———–+———————+———————+—————–+—————-+—————-+————–+———————+———————-+———————–+——————+——————–+—————–+————————–+—————+————————-+————————-+—+
| M.    | Keith      |             | Hollman   | M.     |           |         | 01-jan-13 |             |              |            |         |       |                |               |               |             |                    |                     |                      |                 |                   |                |                         |              |                        |                        |             |            |            |          |                 |                  |                   |              |                |             |                      |           |                     |                     |                 |                |                |              |                     |                      |                       |                  |                    |                 |                          |               |                         |                         |   |
| M.    | Keith2     |             | Hollman2  | M.     |           |         | 01-jan-13 |             |              |            |         |       |                |               |               |             |                    |                     |                      |                 |                   |                |                         |              |                        |                        |             |            |            |          |                 |                  |                   |              |                |             |                      |           |                     |                     |                 |                |                |              |                     |                      |                       |                  |                    |                 |                          |               |                         |                         |   |
+——-+————+————-+———–+——–+———–+———+———–+————-+————–+————+———+——-+—————-+—————+—————+————-+——————–+———————+———————-+—————–+——————-+—————-+————————-+————–+————————+————————+————-+————+————+———-+—————–+——————+——————-+————–+—————-+————-+———————-+———–+———————+———————+—————–+—————-+—————-+————–+———————+———————-+———————–+——————+——————–+—————–+————————–+—————+————————-+————————-+—+
2 rows in set (0.00 sec)

###############
# References: #
###############

http://stackoverflow.com/questions/8163320/inserting-1-million-records-into-mysql-database
MySQL Replication Tutorial

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