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
MySQL-Med 