Coverting MySQL database character set to UTF8

Recently I was engaged to convert the character set for few MySQL databases from latin1 to UTF8 collation utf8_general_ci. This article describes the approach taken for doing the same.

First I will describe various levels at which we can change the character set and collation and then we will see how to convert existing data in a database to required character set.

Backup your database:

Before even thinking about changing character set for your database, take backup of database using whatever backup method that is tested by you – mysqldump, mysql-enterprise-backup, export etc

In case something goes wrong, we can always have data and recreate requried table/database etc.

Make sure your backups and restore methods are proven, meaning that you have sucecssfully done restore of tables/database etc

Setting Character set at various level:

We can see following parameters for character sets

<pre>root [mysql] >show variables like '%character%set%';
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | latin1                                                        |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | latin1                                                        |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.00 sec)

character_set_client – The character set for statements that arrive from the client. If client is not setting any character set while connecting, this character set will be used for statements send by client. Else value set by client during connection will override this value

character_set_connection – The character set used for literals that do not have a character set introducer and for number-to-string conversion.

character_set_database – Character set used by default database. This character set will be used whenever we change database on server and if that database does not have any character set defined.

character_set_filesystem – This character set is used to interpret string literals that refer to file names at file system level, such as in the LOAD DATA INFILE and SELECT … INTO OUTFILE statements.

character_set_results – The character set used for returning query results such as result sets. If client has used character set in its connection, then this value will not be used for returning the result.

character_set_server – Character set defined at the server level. Any new database created will used this character set, unless we are defining character set at database level

character_set_system – The character set used by the server for storing metadata infomration. Example – the return values of the USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE(), VERSION() etc will be retuned in character set assigned to this variable.

character_sets_dir – The directory where all character sets are installed.

We can set character set at following level

Server Level:

We can do this by setting parameter character_set_server in our main my.cnf file. But this needs a bounce. Once MySQL server is bounced it will pick new value of this parameter and new character set will be the one we set for this parameter. But this does not change anything in existing data or objects. Only new database creation will take this effect.

Database Level:

We can alter any database on our MySQL server and change the character set to UTF8. We can use following command:-

ALTER DATABASE <db_name> DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Again, this does not affect existing data or objects. This will only take affect for future objects/tables that we create in this database.

Table Level:

We can use alter table command to set the character set for a table.

ALTER TABLE <table_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

So, if you have many tables in database, you can use following command to dynamically generate a script which can be used to set character set for all required tables in database

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<db_name>';

But this will not change existing columns/data in the table. This will take effect only for new columns getting added to the table.

Changing Character set of existing data:

Above steps are required for setting the character set at various level so that future objects and data will be created in UTF8

Now, for changing character set for existing data, we need to change character set for every text columns of every table in database where we want to change it

We can use multiple approaches for converting character set for existing data and percona blog has provided a very good reference for these methods along with advantages and disadvantages –

Here, I would like to highlight difference between 2 methods that can be used to convert character sets

Doing at table level:

Following command can be used to convert character set at table level

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

This will take care of converting the character set for all columns in table. problem with this approach is that, if you have TEXT columns (TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT, VARCHAR, ENUM), it can end up changing the data type for these columns. Example of the same is giving in above percona blog where TEXT got converted to MEDIUMTEXT

Best and careful way to convert character set is to do it for each text column separately on each table.

You can use following command to change the character set for column

alter table <table_name> change <column_name> <column_name> CHARACTER SET UTF8;

Example:

alter table P1_TABLE change COL1 COL1 TEXT CHARACTER SET UTF8;

In above command/example, we used column name twice. That is required.

But wait, can above approach convert data correctly to required character set?
It may not. Check this article which describes issues we face when we directly try to convert the character set of the column.

Sometimes directly converting can grabble the data. Best way is to convert to binary equivalent and then convert the data type and character set of the column to required once. Following command can be used

alter table P1_TABLE change COL1 COL1 BLOB;
alter table P1_TABLE change COL1 COL1 TEXT CHARACTER SET UTF8;

Since, my column had the text data type, its equivalent is BLOB. Following are the binary equivalent of various text data types

  • CHAR –> BINARY
  • TEXT –> BLOB
  • TINYTEXT –> TINYBLOB
  • MEDIUMTEXT –> MEDIUMBLOB
  • LONGTEXT –> LONGBLOB
  • VARCHAR() –> VARBINARY() (Use same data length)

Automating character set conversion:

You can create simple script with all required commands using following dynamic SQL

Note, that if CHARACTER_SET_NAME is NULL in COLUMNS table for columns, it means that those columns are numbers or binary or of data types which does not need character set conversion

Following dynamic SQL can be used to create automatic script

select concat("alter table ",TABLE_NAME," change ",COLUMN_NAME," ",COLUMN_NAME," BLOB;",
"alter table ",TABLE_NAME," change ",COLUMN_NAME," ",COLUMN_NAME," ",IF(DATA_TYPE in ('varchar','char'),concat(DATA_TYPE,"(",CHARACTER_MAXIMUM_LENGTH,")"),DATA_TYPE)," CHARACTER SET utf8;")
from information_schema.columns
where TABLE_SCHEMA = '<db_name>'
and DATA_TYPE <> 'ENUM'
and CHARACTER_SET_NAME is not null;

ENUM case is different. You need to specify all ENUM value when you convert back to required data type and character set format. Check wordpress codex blog for more details.

Disclaimer: Please do not run above SQLs directly on production without testing. Make sure you write your own SQL as per your need based on database and objects present in your database.

Hope this helps !!

References:

https://codex.wordpress.org/Converting_Database_Character_Sets

Converting Character Sets

http://www.bothernomore.com/2008/12/16/character-encoding-hell/

Advertisements

Pointing MySQL Read-Replica to new master after failover

Here I am describing a simple scenario where we have a read replica (other than slave) which is currently pointing to master mysql instance.
We have another slave which is also pointing to same master. What we are going to see is, how to point read replica to new master after failover.

Environment:

To make you familier with environment, we have 3 instances of mysql

mysql_a -> Current master
mysql_b -> Current slave
mysql_c -> read replica pointing to mysql_a

Technically read replica is same as slave, but in this context we are saying read replica to make you understand that this is additional slave which does not take part in failover scenario.
So excercise is to failover master from mysql_a to mysql_b and point read replica mysql_c to mysql_b

I am using GTID on all 3 instances and both slaves are in sync with master.

On Master (mysql_a):

root [mysql] >show master status \G
*************************** 1. row ***************************
             File: bin_log.000007
         Position: 612
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-91
1 row in set (0.00 sec)

On Slave (mysql_b):

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
               ...
               ...
              Master_Log_File: bin_log.000007
          Read_Master_Log_Pos: 612
               Relay_Log_File: relay_log.000010
                Relay_Log_Pos: 818
        Relay_Master_Log_File: bin_log.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
            ...
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-91
            Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-91
                Auto_Position: 1

On read-replica (mysql_c):

root [mysql] >show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
               ...
               ...
              Master_Log_File: bin_log.000007
          Read_Master_Log_Pos: 612
               Relay_Log_File: relay_log.000005
                Relay_Log_Pos: 442
        Relay_Master_Log_File: bin_log.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
            ...
           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:91
            Executed_Gtid_Set: 6e68fbf2-f9a0-11e4-a3f7-90e2ba6ebdb0:1-7,
f7718b80-c237-11e4-baa8-a0369f370a52:1-91
                Auto_Position: 1

Lagging read-replica:

Before I failover, I want to make read replica lag by couple of transaction so that when we recover and point replica to new master we can clearly see transactions are getting applied from old and new master.
I will stop IO slave on read replica (mysql_c) so that it will stop reading binlog from master (mysql_a)

root [mysql] >stop slave IO_thread;
Query OK, 0 rows affected (0.03 sec)

root [mysql] >show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
               ...
               ...
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

So any change to master will not be replicated to read replica (mysql_c)

Now, lets change few records in master and move forward its GTID

On Master (mysql_a):

root [deo] >show master status \G
*************************** 1. row ***************************
             File: bin_log.000007
         Position: 1220
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-93
1 row in set (0.00 sec)

I did couple of transactions on master (mysql_a) and increased GTID from 1-91 to 1-93.

I see that those trasactions are already applied to slave (mysql_b)

On Slave (mysql_b):

           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-93
            Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-93
                Auto_Position: 1
1 row in set (0.00 sec)

But we didnt get those transaction on read replica (mysql_c) as IO thread is down

On read replica (mysql_c):

           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:91
            Executed_Gtid_Set: 6e68fbf2-f9a0-11e4-a3f7-90e2ba6ebdb0:1-7,
f7718b80-c237-11e4-baa8-a0369f370a52:1-91
                Auto_Position: 1

Failover:

Now, we will failover master to slave (mysql_b)

Failover is simple process. We just have to stop current master and make sure slave is not read only. Plus any other custom configuration that you have done on master, you can need to do the same on slave.

So after failover to mysql_b, my new master looks like below

mysql> show master status \G
*************************** 1. row ***************************
             File: bin_log.000003
         Position: 2495
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-93
1 row in set (0.00 sec)

we can also do reset master, that will flush binlog file and reset GTID to 1. But lets not do that as that is not the objective of our excercise.

Pointing read replica to new master:

Now, we need to point our read replica (which is still pointing to old master (mysql_a)) to new master (mysql_b)
To do that we need to recover the transactions that were missing from old master and continue receving transactions from new master.

When we do new transactions on new master, we get different GTID.
Example, I did 1 transaction after failover on new master and my new master status looks like below

mysql> show master status \G
*************************** 1. row ***************************
             File: bin_log.000003
         Position: 2802
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: c21f28d2-c243-11e4-baf5-2c600c20dba4:1,
f7718b80-c237-11e4-baa8-a0369f370a52:1-93
1 row in set (0.00 sec)
c21f28d2-c243-11e4-baf5-2c600c20dba4:1 is the new GTID

If we just start slave on read replica it will not be able to do fetch any transaction as old master is down. So we need to first point read replica to new master and then start slave which will fetch all old and new transactions

root [mysql] >stop slave;
Query OK, 0 rows affected (0.03 sec)

root [mysql] >change master to master_host='mysql_b.example.com', master_port=3306, master_user='replicate', MASTER_PASSWORD = 'welcome', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

root [mysql] >start slave;
Query OK, 0 rows affected (0.03 sec)

root [mysql] >show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql_b.example.com
                  ...
                  ...
           Retrieved_Gtid_Set: c21f28d2-c243-11e4-baf5-2c600c20dba4:1,
f7718b80-c237-11e4-baa8-a0369f370a52:92-93
            Executed_Gtid_Set: 6e68fbf2-f9a0-11e4-a3f7-90e2ba6ebdb0:1-7,
c21f28d2-c243-11e4-baf5-2c600c20dba4:1,
f7718b80-c237-11e4-baa8-a0369f370a52:1-93
                Auto_Position: 1

In above output of slave status, we can check Retrieved_Gtid_Set and we can clearly see it has retrieved previous master’s 2 transactions f7718b80-c237-11e4-baa8-a0369f370a52:92-93 as well as new transaction that I did after failover – c21f28d2-c243-11e4-baf5-2c600c20dba4:1
In Executed_Gtid_Set we can see it has executed all these transactions – c21f28d2-c243-11e4-baf5-2c600c20dba4:1 and f7718b80-c237-11e4-baa8-a0369f370a52:1-93

Hope this helps !!

“show slave hosts” on master not reporting hostname

If you have multiple MySQL slaves or complex topology, its difficult to remember what slaves are replicating from which master.

We can go to slave servers and check “show slave status” which list master host, but if we want to find out other way, we have can run “show slave hosts” from master.
This will list down all the slaves that are getting replicated from this master.

Only problem I faced was, “show slave hosts” was not reporting hostnames. They were empty.

root [fleetdb] >show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         2 | c21f28d2-c243-11e4-baf5-2c600c20dba4 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.01 sec)

After bit of RTFM, I came to know we need to set a parameter – “report_host” while starting slave. This will register slave hostname while connecting to master (for replication) and same will be shown in “show slave hosts”

This is a read-only parameter meaning, we need to bounce mysql instance after setting this parameter in /etc/my.cnf

So I set the parameter in /etc/my.cnf as below

report_host=slave-host1.mydb.example.com

We can specify any name here and same will be registered in master. But it make sense to provide actual host name.

Once I bounce mysql instance, my slave is started and it connects to master.
Now I check “show slave hosts” and it shows this host.

root [fleetdb] >show slave hosts;
+-----------+------------------------------+------+-----------+--------------------------------------+
| Server_id | Host                         | Port | Master_id | Slave_UUID                           |
+-----------+------------------------------+------+-----------+--------------------------------------+
|         3 | slave-host1.mydb.example.com | 3306 |         2 | c21f28d2-c243-11e4-baf5-2c600c20dba4 |
+-----------+------------------------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

So we can easily know what slaves are connected to this master.

Hope this helps !!

Restoring Slave when GTID is enabled on master

This is a quick post on issues I faced while restoring a slave when GTID was enabled on master.

I have master created few days back and now I am trying to create a slave. I have GTID enabled on master.

Master status:

root [mysql] >show master status \G
*************************** 1. row ***************************
             File: bin_log.000002
         Position: 16682
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-59
1 row in set (0.00 sec)
root [mysql] >show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.00 sec)

Step 1) Take backup from master

mysqldump -u root -p --databases deo fctest nosql --set-gtid-purged=off > /u01/backups/mysql_backup.sql

I used above command to take backup of master.

Note that I used –set-gtid-purged=off. If GTID is enabled on master and we are creating a slave, we want slave to start reading from the point when this backup is created. Because all previous information will be restored and available on slave as part of this backup restore.

If you want slave to start reading from this point and not from the beginning you should NOT set this variable to off. I will explain the significance of this variable later in the post.

Step 2) Install and create MySQL instance

Refer to my previous post https://avdeo.com/2014/02/18/installing-mysql/ for the same

Step 3) SCP backup file to slave server

slave-host.example.com$ scp master-host:/u01/backups/mysql_backup.sql /u01/backups/mysql_backup.sql

Step 4) Import backup into slave

slave-host.example.com$ mysql -u root -p </u01/backups/mysql_backup.sql

Step 5) Start Slave

We have to point slave to our master and then start slave.

When GTID is enabled, you can use MASTER_AUTO_POSITION=1 and slave should automatically start applying from correct GTID

mysql> CHANGE MASTER TO MASTER_HOST = 'master-host.example.com', MASTER_PORT = 3306, MASTER_USER = 'replicate', MASTER_PASSWORD = 'welcome', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-host.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000002
          Read_Master_Log_Pos: 16682
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 547
        Relay_Master_Log_File: bin_log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1133
                   Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'SET PASSWORD FOR 'root'@'master-host.example.com'='*DF216F57F1F2066124E1AA5491D995C3CB57E4C2''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 341
              Relay_Log_Space: 17086
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1133
               Last_SQL_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'SET PASSWORD FOR 'root'@'master-host.example.com'='*DF216F57F1F2066124E1AA5491D995C3CB57E4C2''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: f7718b80-c237-11e4-baa8-a0369f370a52
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 150304 08:03:47
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-59
            Executed_Gtid_Set: c21f28d2-c243-11e4-baf5-2c600c20dba4:1-59,
f7718b80-c237-11e4-baa8-a0369f370a52:1
                Auto_Position: 1
1 row in set (0.00 sec)

Now, if we see above slave status output, its showing error in SQL thread. SQL that is failing is Query: ‘SET PASSWORD FOR ‘root’@’master-host.example.com’=’*DF216F57F1F2066124E1AA5491D995C3CB57E4C2”

This is the first DML that was run on master. Its failing because master-host.example.com entry is not present in mysql.user table.  This is because its a slave on slave host and so root user entry will be present corresponding to slave host only.

Never the less, it should not be running DMLs from the beginning because we have restored everything until GTID 1-59.

Significance of –set-gtid-purged=off

The reason its applying transactions from the beginning is because we used –set-gtid-purged=off while taking backup.

When we are using GTID, we need to know in which GTID is the master and set it on the slave. MySQL keeps two global variables with GTID numbers on it:

  • gtid_executed: it contains a representation of the set of all transaction logged in the binary log
  • gtid_purged: it contains a representation of the set of all transactions deleted from the binary log

So now, the process is the following:

  • take a backup from the master and store the value of gtid_executed
  • restore the backup on the slave and set gtid_purged with the value of gtid_executed from the master

If we don’t use –set-gtid-purged=off in mysqldump, these global variables are automatically set in dump file created by mysqldump. So that when we apply that dump file on slave it will set these variables and slave will start pulling from GTID 60 onwards (in our example).

Fixing the issue

So at this point we have a backup which does not have GTID information. Lets see how to fix this issue manually.

I tried setting GTID_NEXT to 60 by first executing the transaction on master and make GTID 60 on master.

mysql> set gtid_next = "f7718b80-c237-11e4-baa8-a0369f370a52:60";
Query OK, 0 rows affected (0.00 sec)

mysql> begin; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next = "AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

But this didn’t work.

We know that master is done until GTID 59 and we have those transaction in backup and slave also have them because backup was restored completely.

Lets try to set GTID_PURGED to remove those transactions from BINLOG

mysql> SET GLOBAL gtid_purged="f7718b80-c237-11e4-baa8-a0369f370a52:1:59";
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

Its saying that GTID_EXECUTED should be empty. Lets check whats the current value and try to set to empty

mysql> show global variables like 'gtid_executed';
+---------------+--------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                |
+---------------+--------------------------------------------------------------------------------------+
| gtid_executed | c21f28d2-c243-11e4-baf5-2c600c20dba4:1-59,
f7718b80-c237-11e4-baa8-a0369f370a52:1:59 |
+---------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL gtid_executed = "";
ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable

We cannot assign empty value to gtid_executed variable because its read-only and since we restored the backup gtid_executed is updated till the point this instance has executed the transactions.

Only way to fix this is using reset master on slave

When we issue reset master on slave, it will reset GTID_EXECUTED and make it null. After that we can set GTID_PURGED till 1-59 and slave can start applying from 60

mysql> reset master;
Query OK, 0 rows affected (0.07 sec)

mysql> show global variables like 'GTID_EXECUTED';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global GTID_PURGED="f7718b80-c237-11e4-baa8-a0369f370a52:1-59";
Query OK, 0 rows affected (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-host.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000002
          Read_Master_Log_Pos: 16953
               Relay_Log_File: relay_log.000004
                Relay_Log_Pos: 442
        Relay_Master_Log_File: bin_log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 16953
              Relay_Log_Space: 1202
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: f7718b80-c237-11e4-baa8-a0369f370a52
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-60
            Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-60
                Auto_Position: 1
1 row in set (0.00 sec)

This fixed the issue.

Hope this helps !!

References:

How to create/restore a slave using GTID replication in MySQL 5.6

 

Moving InnoDB Table Between Servers

This is a small article telling how to move a innoDB table from one MySQL server to another.

Moving MyISAM table from one server to another is very straight forward process. MyISAM table generates 3 files at file system level.

  • .frm file contains table structure/definition
  • .MYD file contains table data
  • .MYI file contains index data

We can simply copy these 3 files to another MySQL server installation under desired database and it will show up in “SHOW TABLES” command.

But things are different in InnoDB, because InnoDB also stores metadata in default tablespace represented by ibdata* datafiles in data directory.

InnoDB table has 2 types of files generated

  • .frm file contains table structure/definition
  • .ibd file contains data of table as well as index

Apart from above files, we also have metadata like table ID, database name and transaction information about tables in default tablespace (ibdata1 datafile).

This makes InnoDB table special and, simply copying .frm and .ibd files from source server to destination server will not migrate/copy InnoDB table to different server.

We need to do additional steps to complete the copy/migration. Please follow below steps to copy InnoDB table from one server to another.

 

Step 1) On destination database, create required database if not available.

So lets say, I want to move one table called flavors from fctest database on source server to destination server.

For that to happen, I have to make sure that on destination server, I have fctest database created. If its not created, create the same.

You need to do this, even if you want to move this table to some other existing database on destination server.

We have to first move the table to fctest database on destination (same as source) and then we will rename the same to desired database.

On destination server:

root [deo] >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| deo                |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

I don’t have fctest database. Creating the same.

root [deo] >create database fctest;
Query OK, 1 row affected (0.00 sec)

root [deo] >
root [deo] >use fctest;
Database changed
root [fctest] >

Step 2) Create empty table structure same as source table

Once you create the database fctest, create empty table flavors whose structure/definition is same as source.

You can get the definition from source using “SHOW CREATE TABLE” command

On Source server:

mysql> show create table flavors;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                       |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| flavors | CREATE TABLE `flavors` (
  `flavor_id` int(11) NOT NULL AUTO_INCREMENT,
  `build_type` varchar(255) NOT NULL DEFAULT '',
  `variant` varchar(255) NOT NULL DEFAULT '',
  `threads` varchar(255) NOT NULL DEFAULT '',
  `first_event_id` int(11) NOT NULL DEFAULT '0',
  `last_event_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`flavor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Create the same on destination server

On Destination server:

root [fctest] >CREATE TABLE flavors (
    ->   flavor_id int(11) NOT NULL AUTO_INCREMENT,
    ->   build_type varchar(255) NOT NULL DEFAULT '',
    ->   variant varchar(255) NOT NULL DEFAULT '',
    ->   threads varchar(255) NOT NULL DEFAULT '',
    ->   first_event_id int(11) NOT NULL DEFAULT '0',
    ->   last_event_id int(11) DEFAULT NULL,
    ->   PRIMARY KEY (flavor_id))
    ->  ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
root [fctest] >show tables;
+------------------+
| Tables_in_fctest |
+------------------+
| flavors          |
+------------------+
1 row in set (0.00 sec)

Step 3) Discard tablespace on destination server

This step is required so that MySQL will discard current .ibd file generated for this table.

root [fctest] >ALTER TABLE flavors DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

Step 4) Copy .frm and .ibd file from source server to destination server

At this point copy the .frm and .ibb files from source server to destination server.

Before copying these files, make sure that no transactions are happening on this table (which you are migrating).

Safest way is to use “FLUSH TABLE <table_name> WITH READ LOCK” on source database which will flush table data and takes read only lock so that no DML will happen on the table.

While you keep that session active, copy the .frm and .ibd files to destination server.

Step 5) Import tablespace on destination server

Once you copy .frm and .ibd files to destination server, import the tablespace, which will make required metadata imported into default tablespace and accept the .ibd file.

root [fctest] >ALTER TABLE flavors IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
root [fctest] >show tables;
+------------------+
| Tables_in_fctest |
+------------------+
| flavors          |
+------------------+
1 row in set (0.00 sec)
root [fctest] >select count(1) from flavors;
+----------+
| count(1) |
+----------+
|       29 |
+----------+
1 row in set (0.00 sec)

Step 6) Rename the table to desired database

Since we wanted to move this table to deo database on destination server, we can simply run below rename command and it will move the tables to deo database.

root [fctest] >rename table fctest.flavors to deo.flavors;
Query OK, 0 rows affected (0.00 sec)

you can later drop fctest database on destination server.

Hope this helps !!

Disabling GTID in MySQL replication

There could be situations where we have to disable GTID in our replication setup.
We can follow below steps to do so. No need to change any GTID related parameters. We just have to change slave configuration and bounce slave. No need to touch master.

Step 1) Confirm that you are using GTID

If you check processlist on master, you can see GTID is enabled

master> show processlist \G
*************************** 1. row ***************************
     Id: 51
   User: replicate
   Host: master-1.db.example.com:43420
     db: NULL
Command: Binlog Dump GTID
   Time: 81316
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

Check the “command” above and it says “Binlog Dump GTID

 

Step 2) Stop slave, reset master_auto_position to 0


mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_auto_position=0;
Query OK, 0 rows affected (0.00 sec)

Step 3) Check slave status for master log file and position

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: master-1.db.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: bin_log.000003
          Read_Master_Log_Pos: 191
               Relay_Log_File: relay_log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin_log.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
       ...
       ...
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 8c593930-b4da-11e3-9e2d-b499ba05473c:1-7,
cc4192b6-a60d-11e4-8301-80c16e2272cc:1-168
                Auto_Position: 0
1 row in set (0.01 sec)

 

Step 4) Use change master to point to master log file and position


mysql> change master to master_host='master-1.db.example.com' , master_port = 3306 , master_user = 'replicate' , master_password='welcome' , master_log_file = 'bin_log.000003' , master_log_pos = 191, master_connect_retry = 5;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

Step 5) start slave


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

show slave status will still show GTIDs but its not using GTIDs now

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-1.db.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: bin_log.000003
          Read_Master_Log_Pos: 465
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 586
        Relay_Master_Log_File: bin_log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
              ...
              ...
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 8c593930-b4da-11e3-9e2d-b499ba05473c:8
            Executed_Gtid_Set: 8c593930-b4da-11e3-9e2d-b499ba05473c:1-8,
cc4192b6-a60d-11e4-8301-80c16e2272cc:1-168
                Auto_Position: 0
1 row in set (0.00 sec)

 

We can confirm that GTID is not getting used by checking processlist on master

master>show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: deo
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 2. row ***************************
     Id: 3
   User: replicate
   Host: master-1.db.example.com:59631
     db: NULL
Command: Binlog Dump
   Time: 24
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.00 sec)

If you check “command” for row 2, you will notice GTID is gone.

Hope this helps !!

Semi-synchronous Replication in MySQL

If we talk about MySQL replication, we always default to asynchronous replication. By asynchronous, we mean master will keep doing transactions while they are getting replicated to slave via IO thread asynchronously. In this replication, its possible that slave will fall behind but that does not impact master in anyway.
Problem with this replication is data-loss. Example: In case slave is falling behind and master host crashes, some of the transactions may not get transfered/replicated to slave. Following 2 options exists in this scenario

  1. Wait for master host to come up. Which may take around 20-25 mins
  2. Promote slave to master with some data loss

Nether options sounds good on production database. That’s why we have semi-synchronous replication.

Purpose of semi-synchronous replication is twofold

  1. Reduce replication lag and keep slave in sync with master
  2. Ensure no data-loss in case master crashes

Semi-synchronous replication has been in place from MySQL 5.5 onwards in the form of plugin. We just have to install the plugin and then we can enable the same. But again this semi-synchronous replication available in MySQL 5.5 and 5.6 does not guarantee “no data loss” completely for the reasons we will see later but it keeps slave in sync with master.

MySQL 5.7 Oracle has made some enhanced semi-synchronous replication which they call “loss-less semi-synchronous replication”. This guarantees that there is no data loss in case master crashes.

We will take a look at how semi-synchronous replication works, how to install the plugin and whats the enhancement in MySQL 5.7 to prevent data loss.

How Semi-Synchronous replication works?

As discussed above, there are 2 different algorithm for semi-synchronous replication.

  1. Normal semi-synchronous replication available in mysql 5.5 and 5.6 – does not guarantee “no data loss”
  2. Loss-less semi-synchronous replication available in mysql 5.7 – guarantee “no data loss”

lets take a look at each and how it works

1) Normal semi-synchronous replication in MySQL 5.5 and 5.6

If we talk above true synchronous replication – In truly synchronous replication, when you commit a transaction, the commit does not complete on master until all replicas have also committed successfully and acknowledged to master.

In MySQL’s semi-synchronous replication when transaction is committed on master, its written to binary log and innodb transaction log and commit is complete. At this point the data has not even reached to any of the slave but on master commit is complete. The client issuing the commit is still on hold and is not told that commit has complete.
At this point slave IO thread pulls transaction data from binary logs and updated relay logs, flush relay logs to disk and acknowledges master that slave has received and written the data. Only after receiving ack from at least 1 slave, MySQL confirms the client that transaction is committed.

There is a problem with above functionality. MySQL is only delaying acknowledge of commit to client. In reality its committing the transaction on master way before that (before even sending data to slave). Since committing of transaction is done before waiting for ack from semi-sync slave, so the committed rows are visible from applications, even though semi-sync slaves may not have received the data. If master is crashed and none of the slaves received the data, the data is lost but applications may have seen them. This is called phantom reads, and in many cases it’s problematic.

2) Loss-less semi-synchronous replication in MySQL 5.7

loss-less semi-synchronous replication in MySQL 5.7 answers above problem. Only change that’s done in this semi-synchronous replication is that commit on primary is delayed until master receives acknowledgement from only of the semi-synchronous slave that data is received and written by the slave. So when committed data is visible from applications, one of the semi-sync slaves have received that. Phantom read risk is much smaller: if both master and the latest semi-sync slave are down at the same time, data is lost. But it’s much less likely to happen compared to normal semi-sync.

To avoid data loss and phantom reads, Normal Semi-sync can’t meet your expectations. Using Loss-Less Semi-sync is needed.
With Loss-Less Semi-Synchronous replication, committed data should be on one of the slaves, so you can recover from the latest slave. You can always do fast failover here.

The acknowledgement from the slave means, it has received the transaction and applied it to the relay log and flushed it to disk. It doesn’t mean the data has actually gone into the database.

Just to make this statement clearer, it means the transaction was written to the relay log, and the relay log was flushed to disk.
There is still the need for the SQL thread to pick it up and apply it to the database. It’s very unlikely not to complete this task, but that way we’re getting a bit more performance.

Implementing semi-synchronous replication

1) check if semi-synchronous plugin is installed using “show plugins;”


mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
...
...
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.01 sec)

You should be looking for “rpl_semi_sync_master” name.
In my case plugin was not installed.

Optionally you can also check system variables


mysql> show variables like 'rpl_semi_sync%';
Empty set (0.00 sec)

Nothing. That means semi-synchronous functionality is not installed.

2) Install semi-synchronous plugin

You need to install “semisync_master.so” on master and “semisync_slave.so” on slave

on Master:


mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.12 sec)

on Slave:


mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

These .so plugin files are available in <mysql_install_dir>/lib/plugin directory.

“show plugins;” should show the plugin now


mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| mysql_old_password         | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
...
...
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.00 sec)

You should also see the variables


mysql> show variables like 'rpl_semi_syn%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

3) Enable semi-synchronous replication

On master:


mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.03 sec)

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 10000;
Query OK, 0 rows affected (0.00 sec)

rpl_semi_sync_master_timeout is the timeout in millisec. We are setting a timeout of 10 sec.

On slave:


mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

we have to bounce IO_THREAD on slave so that it will connect back to master as semi-synchronous slave.

Monitoring Semi-synchronous replication

We have few status variables that we can monitor to check if semi-synchronous replication is working as expected or not.

If you check status variables on master, it shows following status variables.


mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

On replication slave we have only 1 variable Rpl_semi_sync_slave_status.

Among the variables mentioned above, 3 varaibles are important for us to watch

Rpl_semi_sync_master_status -> Whether semi-synchronous replication currently is operational on the master
Rpl_semi_sync_master_yes_tx -> The number of commits that were acknowledged successfully by a slave
Rpl_semi_sync_master_no_tx  -> The number of commits that were not acknowledged successfully by a slave

Currently we see that Rpl_semi_sync_master_status is ON, that means the semi-synchronous replication is working fine.

Lets create a table in “deo” database and insert few records.

On master:


mysql> use deo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test (col1 smallint not null);
Query OK, 0 rows affected (0.14 sec)

mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
mysql> show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 1356  |
| Rpl_semi_sync_master_net_wait_time         | 4070  |
| Rpl_semi_sync_master_net_waits             | 3     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1379  |
| Rpl_semi_sync_master_tx_wait_time          | 4139  |
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

If you see above, we have 3 transactions done (corrosponding to 3 inserts since autocommit was on) and all are replicated synchronously on slave.

Rpl_semi_sync_master_yes_tx has become 3 and Rpl_semi_sync_master_no_tx is still 0.

Lets stop replication IO thread so that changes in master will not get replicated on slave

on slave:


mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

Now, lets insert a record in test table in master

On master:


mysql> insert into test values (4);
Query OK, 1 row affected (10.01 sec)

mysql> show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 1022  |
| Rpl_semi_sync_master_net_wait_time         | 4090  |
| Rpl_semi_sync_master_net_waits             | 4     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1379  |
| Rpl_semi_sync_master_tx_wait_time          | 4139  |
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

If you see, Rpl_semi_sync_master_no_tx is updated to 1 and Rpl_semi_sync_master_status is updated to off.
It means that replication is changed back to asynchronous.

Also note that insert statement took 10 secs to complete, because semi-sync replication timeout was set to 10 sec (rpl_semi_sync_master_timeout = 10000)

We can now start the slave IO thread and it will be back to semi-synchronous replication

on slave:


mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+
| col1 |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

On master:


mysql> show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 1626  |
| Rpl_semi_sync_master_net_wait_time         | 8131  |
| Rpl_semi_sync_master_net_waits             | 5     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1379  |
| Rpl_semi_sync_master_tx_wait_time          | 4139  |
| Rpl_semi_sync_master_tx_waits              | 3     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 3     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

So after starting slave IO thread, it recovered immediately and semi-synchronous replication enabled automatically.

Reference:

http://yoshinorimatsunobu.blogspot.in/2014/04/semi-synchronous-replication-at-facebook.html
http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html

How Does Semisynchronous MySQL Replication Work?

Hope this help !!