Multi Source Replication MySQL 5.6 to 5.7 GTID Auto Position Issues [SOLVED] -

i have 3 master servers, different dbs, trying replicate single server. having hard time getting them setup , current. have duplicate entry errors on 3 channels. skipping them manually painful least. there way auto sync correct position? under impression easy pie gtid.

i used:

dump:

mysqldump --databases profiles --single-transaction --triggers --routines --host=10.10.10.10 --port=3306 --user=user --password=pass > ~/dump.sql 

initialize:

change master master_host="10.10.10.10", master_port=3306, master_user="user", master_password="pass", master_auto_position=1 channel "channel1"; 

master my.cnf:

gtid_mode       = on enforce_gtid_consistency        = true log_bin = /var/log/mysql/bin_log.index log_slave_updates       = true server-id       = 2061 

slave my.cnf:

  [client] port            = 3306 socket          = /var/run/mysqld/mysqld.sock  [mysqld_safe] pid-file        = /var/run/mysqld/mysqld.pid socket          = /var/run/mysqld/mysqld.sock nice            = 0  [mysqld] user            = mysql pid-file        = /var/run/mysqld/mysqld.pid socket          = /var/run/mysqld/mysqld.sock port            = 3306 basedir         = /usr datadir         = /var/lib/mysql tmpdir          = /tmp lc-messages-dir = /usr/share/mysql server-id       = 10001 explicit_defaults_for_timestamp  gtid_mode=on enforce_gtid_consistency=true log_bin=/var/log/mysql/bin_log.index log_slave_updates=true master_info_repository=table relay_log_info_repository=table   # instead of skip-networking default listen on # localhost more compatible , not less secure. #bind-address   = 127.0.0.1  log-error       = /var/log/mysql/error.log  # disabling symbolic-links recommended prevent assorted security risks symbolic-links=0 

am missing something? appreciated.

the problem global.gtid_purged had registered 1 of master databases, sets proper position continue replication process. other databases starting beginning of time essentially. improper positioning , duplicate record errors receiving.

so solution:

mysql multi-source gtid replication guide me:

re/install mysql server:

after securing current data performed clean install of mysql server 5.7.7-rc onto slave server. mysql server can removed using following:

$ sudo apt-get --purge remove mysql-client mysql-server mysql-common $ sudo apt-get autoremove 

and selecting yes @ prompt remove "data" directory (this permanently delete of databases, configurations, etc.). if have custom configurations, time backup /etc/mysql/my.cnf file.

reinstall mysql server 5.7 ubuntu 14-lts

$ sudo apt-get install mysql-server-5.7 

if don't have 5.7 on system use this guide

backup live master databases:

i created current mysql dump of 3 live master databases. each of databases have different name e.g. db01, db02, db03 , being saved directly onto slave server.

in case each database on own server, ran few times changing ip , database, , filename.

$ mysqldump -u username -p -h 10.10.10.10 --skip-lock-tables --single-transaction --triggers --routines --databases db01 > ~/dumpdb01.sql 

once complete, need gtid_purged data each dump , save later:

$ grep purged ~/dumpdb01.sql set @@global.gtid_purged='d23dceda-08a4-11e5-85e4-005056a2431f:1-10073'; 

you need entire string: d23dceda-08a4-11e5-85e4-005056a2431f:1-10073

mysql slave server configuration:

now decided configure mysql before ever imported data , explain why shortly.

edit my.cnf:

sudo vi /etc/mysql/my.cnf  gtid_mode                             =on enforce_gtid_consistency              =true log_bin                               =/var/log/mysql/bin_log.index log_slave_updates                     =true master_info_repository                =table relay_log_info_repository             =table server-id                             =1001  # instead of skip-networking default listen on # localhost more compatible , not less secure. #bind-address   = 127.0.0.1 

you need save configuration file , create bin_log.index file, otherwise server not start.

$ sudo touch /var/log/mysql/bin_log.index $ sudo chown mysql:mysql /var/log/mysql/bin_log.index $ sudo service mysql restart 

import / configure data:

while importing dumps, first 1 register gtid_purged automatically this:

set @@global.gtid_purged='d23dceda-08a4-11e5-85e4-005056a2431f:1-10073'; 

once happens gtid_executed set same data, , each subsequent import produce errors this:

error 1840 (hy000) @ line 24: @@global.gtid_purged can set when @@global.gtid_executed empty. 

these errors can ignored on import $ mysql -u username -p -f < ~/dumpdb02.sql going modify gtid_purged manually after import process. if have forced imports , have seen error can clear both gtid variables executing mysql > reset master;

from mysql console run following:

mysql> reset master;  

you need 3 gtids dumps , comma separate them in assignment below.

mysql> set @@global.gtid_purged='d23dceda-08a4-11e5-85e4-005056a2431f:1-10073,d23dceda-08a4-11e5-85e4-005056a2431f:1-10073,d23dceda-08a4-11e5-85e4-005056a2431f:1-10073'; 

you can initialize , start replication:

for each database (in case changed ip , channel string of choice. use channel access slave data):

mysql> change master master_host="10.10.10.10", master_port=3306, master_user="username", master_password="password", master_auto_position=1 channel "db01"; 

then start each slave:

mysql> start slave channel "db01"; mysql> show slave status channel "db01"\g 

and success!

i have of data, no errors, , date master servers