In terms of new features, MTS extrapolate conceptually all the expectations seeing that it is a feature that will elevate (or at leaf will try it) all the stress that we\u2019ve been seeing for years, having a slave server behind master in some way, files or seconds. The first thing that blink on my mid is the red mark I used to have on my personal server\u2019s dashboard control which has called out my attention always when a slave server hangs while the master still working well. Many of time, I\u2019ve seeing IO problems that will make slave hangs within the relay log rotation time or when executing a long report – at least four times a day, I must go and check what\u2019s going on. BTW, due to all of that I believe that the exchange the model of single-thread for that with multiples threads will elevate the problem – I hope to have the slave servers not hanging too much anymore.<\/p>\n
Having said that, I\u2019ve been seeing some cases in which after implementing the MySQL 5.6 in production, properly adjusting the worker threads variable (slave_parallel_wrokers) and starting the slave, not often it\u2019s being worked as promised. The first action when it\u2019s not working properly is to check all the necessary configuration to get it working well, it\u2019s not just raise up the previous mentioned and put it to run – you make sure about some small details:<\/p>\n
There is no problem in using the bin log format as statement or row, both formats will work well since on both one can observe the entries on binary log file of all the necessary information for the workers. Another advice is that, once you\u2019ve started MySQL replicating in a crash-safe mode, it\u2019s not possible to alter dynamically the repositories for relay and master info on runtime for a busy server due to the workers info being stored on tables and if you change this information\u2019s location, workers might be a little bit out of reference.<\/p>\n
First of all, I did a test considering a customer environment where they\u2019re running a MySQL 5.5.37 on all the master and slave servers. the strategy is to replace slave servers until we hit the master, doing finally a switchover to another server in order to upgrade the master server to MySQL 5.6 – a normal and reasonable strategy to avoid risks on production. If you get a error on any project step, time will be welcomed to study a little bit more what\u2019s happening and then, take another step towards another task. Another point to take into account is the old_passwords (removed in 5.7) that is still available in 5.6 and must be used when users in mysql.user table remain using 16-bytes passwords. It\u2019s a good maneuver while updating all the account\u2019s passwords to keep old_passwords=1 configuration to give sometime to map all the accounts used by the applications to avoid problems with access denied (while taking with account\u2019s passwords updates, configure log_warnings=2 to get all the login failed attempts and try to correct it).<\/p>\n
Test Scenario: confirming that mysql 5.5 (master) and mysql 5.6 (slave) does not replicate with mts<\/strong><\/p>\n As this is the scenario I\u2019ve been see as much on many of customers I\u2019ve visited, I decided to spend sometime and stress the possibilities around the MTS replication having a 5.5 master and a new comer, mysql 5.6 as slave. Some discussions on the internet made me believe that at some level of configuration this scenario will become possible – but, it\u2019s not supported. After speaking to guys on MySQL Central, we discussed a lot many scenarios but some high level guys known as developers said that 5.6 used to ship some additional information on binary logs and then, the mts on slave get to know how to proceed splitting up queries (binlog_format=statement) or updates (binlog_format=row) by workers (threads) – this job is done actually by a coordinator that is a thread as well that executes stuff read from the relay logs on slave side. BTW, this all I\u2019ve got to know after testing the environment which I raised up using Vagrant as below.<\/p>\n Files you\u2019ll need to create\/copy\/paste – make sure you have a centos65-x86_64 box added on your vagrant boxes or alter the value of mysql55.vm.box and mysql56.vm.box in Vagrantfile configs.<\/p>\n Vagrantfile, which you can just copy\/paste:<\/p>\n If you get an error like this below, review the boxes you’ve got added to your Vagrant boxes:<\/p>\n Setup scripts that you can use to create others machines:<\/p>\n Following this so is the command vagrant up and machines up & running.<\/p>\n Setting up additional things like classic or GTID replication (where once can do using MySQL Utilities), it’s going to be possible to execute the tests. In addition of regular and initial variables the come mainly with the 5.5 configuration file, on 5.6 I added the server_id, server_id=200, slave_worker_threads = 2, master_info_repository = TABLE, relay_log_info_repository = TABLE and relay_log_recovery = ON, for Crash-Safe Replication configurations as the 5.6 will be the slave.<\/p>\n Checking the replication status:<\/p>\n Now, the test proposed on the Luis’ blog sometime ago to explain MTS is, on the master server:<\/p>\n On slave side, check the content of the table mysql.slave_worker_info – this table will register all the movement around the MTS replication. Below you can see that, as we haven’t executed anything directly on databases yet, threads haven’t worked yet.<\/p>\n Coming back on the master, enter some inserts:<\/p>\n And then, we again go over mysql.slave_worker_info to check if those two listed threads have worked or not:<\/p>\n Just one thread working!! Yes, we confirmed that this does not work when you have a master on 5.5 and slaves on 5.6, regardless of the binlog_format and bla bla bla<\/em>. A good point for a discussion at this point is that MySQL 5.6 has received lots of improvements regarding its engine, many other related with InnoDB and many others on many other things. Maybe it’s a good time to start upgrading 5.5 to 5.6 on slaves until we hit the master and then, upgrade all the database machines, even having MySQL 5.6 MTS disable for this moment.<\/p>\n Test Scenario: confirming that mysql 5.6 (master) and mysql 5.6 (slave) replicates with mts, even with binlog_format=statement<\/strong><\/p>\n To make this new test, we just need to remove the 5.5’s RPM packages and add 5.6 from the repository and then, start slave. The final step is to execute the tests again and check the mysql.slave_worker_info table’s content on slave server.<\/p>\nwagnerbianchi02:mysql55_and_mysql56 root# ls -lh\r\ntotal 24\r\ndrwxr-xr-x 3 root wheel 102B Oct 6 12:53 .vagrant\r\n-rw-r--r-- 1 root wheel 760B Oct 6 12:53 Vagrantfile\r\n-rw-r--r-- 1 root wheel 681B Oct 6 12:52 setup_env55.sh\r\n-rw-r--r-- 1 root wheel 343B Oct 6 12:42 setup_env56.sh<\/pre>\n
# -*- mode: ruby -*-\r\n# vi: set ft=ruby :\r\n\r\nfile_to_disk = '.\/tmp\/galera_disk.vdi'\r\n\r\n# Vagrantfile API\/syntax version. Don't touch unless you know what you're doing!\r\nVAGRANTFILE_API_VERSION = \"2\"\r\n\r\nVagrant.configure(VAGRANTFILE_API_VERSION) do |config|\r\n\r\nconfig.vm.define \"mysql55\" do |mysql55|\r\nmysql55.vm.box = \"centos65-x86_64\"\r\nmysql55.vm.network :\"private_network\", virtualbox__intnet: \"mysql55_and_mysql56\", ip: \"192.168.50.100\"\r\nmysql55.vm.provision \"shell\", path: \"setup_env55.sh\"\r\nend\r\n\r\nconfig.vm.define \"mysql56\" do |mysql56|\r\nmysql56.vm.box = \"centos65-x86_64\"\r\nmysql56.vm.network :\"private_network\", virtualbox__intnet: \"mysql55_and_mysql56\", ip: \"192.168.50.101\"\r\nmysql56.vm.provision \"shell\", path: \"setup_env56.sh\"\r\nend\r\n\r\nend<\/pre>\n
wagnerbianchi01:mysql55_and_mysql56 root# vagrant up\r\nBringing machine 'mysql55' up with 'virtualbox' provider...\r\nBringing machine 'mysql56' up with 'virtualbox' provider...\r\n==> mysql55: Box 'centos65-x86_64' could not be found. Attempting to find and install...\r\nmysql55: Box Provider: virtualbox\r\nmysql55: Box Version: >= 0\r\n==> mysql55: Adding box 'centos65-x86_64' (v0) for provider: virtualbox\r\nmysql55: Downloading: centos65-x86_64\r\nAn error occurred while downloading the remote file. The error\r\nmessage, if any, is reproduced below. Please fix this error and try\r\nagain.\r\n\r\nCouldn't open file \/opt\/vagrant_projects\/mysql55_and_mysql56\/centos65-x86_64<\/pre>\n
#!\/usr\/bin\/env bash
\n#: script name: setup_env55.sh
\n#
\nsudo echo \"nameserver 8.8.8.8\" > \/etc\/resolv.conf
\nsudo echo \"nameserver 8.8.4.4\" >> \/etc\/resolv.conf
\nsudo yum -y install wget vim
\nsudo yum -y remove mysql-libs-5.1.71-1.el6.x86_64
\nsudo rpm -Uvi https:\/\/dev.mysql.com\/get\/mysql-community-release-el6-5.noarch.rpm
\nsudo wget http:\/\/dev.mysql.com\/get\/Downloads\/MySQL-5.5\/MySQL-5.5.39-2.el6.x86_64.rpm-bundle.tar
\nsudo tar xvf MySQL-5.5.39-2.el6.x86_64.rpm-bundle.tar
\nsudo rpm -ivh install MySQL-{server,shared,client}*
\nsudo \/etc\/init.d\/mysql start<\/code><\/p>\n#!\/usr\/bin\/env bash
\n#: script name: setup_env56.sh
\n#
\nsudo echo \"nameserver 8.8.8.8\" > \/etc\/resolv.conf
\nsudo echo \"nameserver 8.8.4.4\" >> \/etc\/resolv.conf
\nsudo yum -y install wget vim
\nsudo rpm -Uvi https:\/\/dev.mysql.com\/get\/mysql-community-release-el6-5.noarch.rpm
\nsudo yum-config-manager --enable mysql56-community
\nsudo yum -y install mysql-server
\nsudo service mysqld start<\/code><\/p>\nwagnerbianchi01:mysql55_and_mysql56 root# vagrant status\r\nCurrent machine states:\r\n\r\nmysql55 running (virtualbox)\r\nmysql56 running (virtualbox)\r\n\r\nThis environment represents multiple VMs. The VMs are all listed\r\nabove with their current state. For more information about a specific\r\nVM, run `vagrant status NAME`.<\/pre>\n
mysql> mysql> select @@server_id, @@slave_parallel_workers, @@master_info_repository,\r\n -> @@relay_log_info_repository, @@master_info_repository, @@relay_log_recovery\\G\r\n*************************** 1. row ***************************\r\n @@server_id: 200\r\n @@slave_parallel_workers: 2\r\n @@master_info_repository: TABLE\r\n@@relay_log_info_repository: TABLE\r\n @@master_info_repository: TABLE\r\n @@relay_log_recovery: 1\r\n1 row in set (0.00 sec)<\/pre>\n
Slave_IO_Running: Yes\r\n Slave_SQL_Running: Yes<\/pre>\n
mysql> create database db1; create database db2;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nQuery OK, 1 row affected (0.00 sec)<\/pre>\n
mysql> select * from mysql.slave_worker_info\\G\r\n*************************** 1. row ***************************\r\n Id: 1\r\n Relay_log_name:\r\n Relay_log_pos: 0\r\n Master_log_name:\r\n Master_log_pos: 0\r\n Checkpoint_relay_log_name:\r\n Checkpoint_relay_log_pos: 0\r\nCheckpoint_master_log_name:\r\n Checkpoint_master_log_pos: 0\r\n Checkpoint_seqno: 0\r\n Checkpoint_group_size: 64\r\n Checkpoint_group_bitmap:\r\n*************************** 2. row ***************************\r\n Id: 2\r\n Relay_log_name:\r\n Relay_log_pos: 0\r\n Master_log_name:\r\n Master_log_pos: 0\r\n Checkpoint_relay_log_name:\r\n Checkpoint_relay_log_pos: 0\r\nCheckpoint_master_log_name:\r\n Checkpoint_master_log_pos: 0\r\n Checkpoint_seqno: 0\r\n Checkpoint_group_size: 64\r\n Checkpoint_group_bitmap:\r\n2 rows in set (0.00 sec)<\/pre>\n
mysql> create table db1.t1(a int); create table db2.t1(a int);\r\nQuery OK, 0 rows affected (0.01 sec)\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> insert into db1.t1 values (1),(2),(3); insert into db2.t1 values (1),(2),(3);\r\nQuery OK, 3 rows affected (0.00 sec)\r\nRecords: 3 Duplicates: 0 Warnings: 0\r\n\r\nQuery OK, 3 rows affected (0.00 sec)\r\nRecords: 3 Duplicates: 0 Warnings: 0<\/pre>\n
mysql> select * from mysql.slave_worker_info\\G\r\n*************************** 1. row ***************************\r\n Id: 1\r\n Relay_log_name: .\/mysqld-relay-bin.000002\r\n Relay_log_pos: 1171\r\n Master_log_name: master-bin.000001\r\n Master_log_pos: 1007\r\n Checkpoint_relay_log_name: .\/mysqld-relay-bin.000002\r\n Checkpoint_relay_log_pos: 797\r\nCheckpoint_master_log_name: master-bin.000001\r\n Checkpoint_master_log_pos: 633\r\n Checkpoint_seqno: 1\r\n Checkpoint_group_size: 64\r\n Checkpoint_group_bitmap:\r\n*************************** 2. row ***************************\r\n Id: 2\r\n Relay_log_name:\r\n Relay_log_pos: 0\r\n Master_log_name:\r\n Master_log_pos: 0\r\n Checkpoint_relay_log_name:\r\n Checkpoint_relay_log_pos: 0\r\nCheckpoint_master_log_name:\r\n Checkpoint_master_log_pos: 0\r\n Checkpoint_seqno: 0\r\n Checkpoint_group_size: 64\r\n Checkpoint_group_bitmap:\r\n2 rows in set (0.00 sec)<\/pre>\n
[root@mysql55 ~]# service mysql stop\r\nShutting down MySQL. SUCCESS!\r\n[root@mysql55 ~]# rpm -e MySQL-shared-5.5.39-2.el6.x86_64 MySQL-client-5.5.39-2.el6.x86_64 \\\r\n> MySQL-shared-compat-5.5.39-2.el6.x86_64 MySQL-server-5.5.39-2.el6.x86_64 \\\r\n> MySQL-shared-compat-5.5.39-2.el6.x86_64\r\n[root@mysql55 ~]# yum -y install mysql-server\r\n[...]\r\nSetting up Install Process\r\n[...]\r\nInstalled size: 329 M\r\nDownloading Packages:\r\n[...]\r\nComplete!\r\n<\/pre>\n