"20180402" MySQL Some applications of replication filter and trigger

Requirements Description

1. Intranet server has two sets of master-slave replication environment, one set is based on the traditional replication of 5.6.26, the other set is based on the Gtid 5.7.19 Version of replication. The need for development now is to synchronize the two tables based on traditional replication to the one that is based on Gtid-based replication, and that the two tables that require synchronization have a column value of one table that must be 10 times times the source.

[email protected] 15:51:  [remix_test]> show create table sbtest1 \G*************************** 1. row ***************************       Table: sbtest1Create Table: CREATE TABLE `sbtest1` (  `id` int(11) NOT NULL,  `k` int(11) NOT NULL DEFAULT ‘0‘,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

Table structure as shown above, on the original basis, column K must be multiplied by 10, i.e. New_k = old_k*10

Difficulties

1. How to synchronize the data of the two tables. Here we use the master-slave synchronization scheme, but the master-slave synchronization scheme brings the following problems:

• Based on the traditional copy of the master-slave is based on the Binlog file and binlog position replication, based on the gtid of the master-slave is based on the Gtid to replicate, both the creation of master-slave is based on traditional replication or Gtid replication.
• The master-slave binlog based on Gtid replication is in the row format, and the master-slave Binlog format based on traditional replication is the statement format, where data is synchronized to the master based on Gtid replication from the traditional replicated master. This time based on Gtid's master-slave replication slave Whether the Binlog log can be applied normally, or slave can apply Gtid logs that are not using Binlog.
• Just need to synchronize the two tables, the other library table data do not need to synchronize. So replication filtering is required.

2. How to make changes to the synced data. The scenario we take here is to use a trigger, but the trigger itself consumes resources, because the trigger is based on row row, so if we modify 500 data at a time to spend 1s, trigger trigger modification will trigger 500 times, add 1s per row of data modification, then the trigger will cost 500s.

• During the test, we found that after inserting a row of data in the INSERT, the trigger could not be applied to the update operation on this line of data, and show slave status\g would error:

  Last_SQL_Error: Error ‘Can‘t update table ‘sbtest1‘ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.‘ on query. Default database: ‘remix_test‘. Query: ‘insert into sbtest1(id,k) values(39039,1),(39040,10)‘

Solution Solutions

1. Data synchronization:

• in order for master-based Gtid master-slave replication to be able to apply the Binlog log of master from traditional replication, Sql_mode must be modified on Gtid master-slave copy to change Sql_mode to On_ PERMISSIVE (generates Gtid transactions locally and accepts implicit transactions (i.e. non-gtid transactions):

    mysql> set global sql_mode= ' on_permissive ';  

• As for master-slave slave based on Gtid replication, there is no good solution for the moment, or we haven't experimented yet. But before we forget to make changes locally sql_mode causes the show SLAVE STATUS \g above SLAVE to see the obvious error: error:1236
• Replication filtering: Note that if you just write replication_do_table, although the general DDL,DML operation is not the two tables will not be copied, but when you create a new library (create database schema_name;) The creation of the new library will be synchronized, but it will only synchronize the creation of the new library, and others such as creating a table in the new library will not be synchronized.

    mysql> Change replication filter replicate_do_db= (remix_test), replicate_do_table = (remix_test.sbtest1
  , remix_test.sbtest2);  

2. The creation of the trigger.
   • The creation of the following trigger is successful, but you can trigger the trigger but not execute the insert insert operation based on the traditional copy master, which throws the error above.

     mysql> delimiter || ;mysql> create trigger tr1 after insert on sbtest1-> for each row-> begin->     update sbtest1 set k=new.k*10 where id=new.id;-> end||mysql> delimiter ;

   • In the end, you're going to create a new table, and each time the old table is modified on the new table.

Actual operation:

2. Backup on top of slave based on traditional replicated data

   shell> mysqldump --set-gtid-purged=OFF --single-transaction --dump-slave=2 -uroot -p -t remix_test sbtest1 > sbtest1_20180402.sqlshell> mysqldump --set-gtid-purged=OFF --single-transaction --dump-slave=2 -uroot -p -t remix_test sbtest2 > sbtest2_20180402.sql

4. Restore above master based on Gtid replication

   shell> mysql -S /var/lib/mysql/mysql_3306.sock -uroot -p remix_test< sbtest1_20180402.sqlshell> mysql -S /var/lib/mysql/mysql_3306.sock -uroot -p remix_test<sbtest2_20180402.sql

6. Create a copy account based on a traditional copy of master

   mysql> grant replication slave on ‘slave‘@‘ip_address‘ identified by ‘new_password‘;mysql> flush privileges;

8. Sql_node modification of Master based on Gtid replication

   mysql> set global sql_mode=‘ON_PERMISSIVE‘;

10. Gtid copy-based master builds master-slave (obtains binlog file and position information from Sbtest2_20180402.sql based on back-up)

    mysql> change master  ......

12. Copy filtering based on Gtid replication master

    mysql> change replication replication_do_db=(remix_test),replication_do_table=(remix_test.sbtest1,remix_test.sbtest2);

14. Master-on master-slave replication based on Gtid replication
    
15. Create a new table based on Gtid replication master

    mysql> create table sbtest1_bak like sbtest1;mysql> insert into sbtest1_bak select id,k*10 from sbtest1;

    9. Create a trigger above the master based on Gtid replication

    [email protected] 15:39:  [remix_test]> delimiter ||[email protected] 15:41:  [remix_test]> create trigger tr2 after update on sbtest1-> for each row-> begin->     update sbtest1_bak set k=new.k*10 where id=new.id;-> end||[email protected] 15:48:  [remix_test]> delimiter ||[email protected] 15:48:  [remix_test]> create trigger tr3 after delete on sbtest1-> for each row-> begin->     delete from sbtest1_bak where id=old.id;-> end||Query OK, 0 rows affected (0.11 sec)[email protected] 15:49:  [remix_test]> delimiter ;[email protected] 15:06:  [remix_test]> create trigger tr1 after insert on sbtest1-> for each row-> begin->     insert into sbtest1_bak(id,k) select id,k*10 from sbtest1 where id=new.id;-> end||Query OK, 0 rows affected (0.11 sec)[email protected] 15:08:  [remix_test]> delimiter ;

"20180402" MySQL Some applications for replication filter and trigger