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

About these ads

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: