rm -rf master bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/master --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/master --core-file --socket=/tmp/mysql_master.sock --port=3333 --log-error=$PWD/master/log.err --log-bin=master-bin --server_id=1 2>&1 & rm -rf slave bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/slave --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/slave --core-file --socket=/tmp/mysql_slave.sock --port=6666 --log-error=$PWD/slave/log.err --log-bin=slave-bin --server_id=2 --skip_slave_start 2>&1 & - Setup replication - master bin/mysql -uroot -S /tmp/mysql_master.sock --prompt='Master>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Master>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.01 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) Master> Master>CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) Master>FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) Master>show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1220 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) - slave bin/mysql -uroot -S /tmp/mysql_slave.sock --prompt='Slave>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Slave>CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=3333, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='master-bin.000001', -> MASTER_LOG_POS=1220; Query OK, 0 rows affected, 2 warnings (0.01 sec) Slave>start slave; Query OK, 0 rows affected (0.00 sec) Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1220 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 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: 1220 Relay_Log_Space: 528 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: 1 Master_UUID: 997d3535-6f5d-11ea-926a-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/GABuilds/mysql-5.7.29/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) - master Master>create database gm_midterm; Query OK, 1 row affected (0.00 sec) Master>use gm_midterm Database changed Master>XA START 'xatest1'; Query OK, 0 rows affected (0.00 sec) Master>drop temporary table if exists gm_midterm.test_temp_table; Query OK, 0 rows affected, 1 warning (0.00 sec) Master>create temporary table gm_midterm.test_temp_table ENGINE = MEMORY as select "a" from dual; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Master>select * from gm_midterm.test_temp_table; +---+ | a | +---+ | a | +---+ 1 row in set (0.00 sec) Master>drop temporary table if exists gm_midterm.test_temp_table; Query OK, 0 rows affected (0.00 sec) Master>XA END 'xatest1'; Query OK, 0 rows affected (0.00 sec) Master>XA PREPARE 'xatest1'; Query OK, 0 rows affected (0.00 sec) Master>XA COMMIT 'xatest1'; Query OK, 0 rows affected (0.00 sec) - slave Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3333 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 2376 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 657 Relay_Master_Log_File: master-bin.000001 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: 3227 Last_Error: Error in Xid_log_event: Commit could not be completed, 'The use of replication filters with XA transactions is not supported, and can lead to an undefined state in the replication slave.' Skip_Counter: 0 Exec_Master_Log_Pos: 1556 Relay_Log_Space: 1684 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: 3227 Last_SQL_Error: Error in Xid_log_event: Commit could not be completed, 'The use of replication filters with XA transactions is not supported, and can lead to an undefined state in the replication slave.' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 997d3535-6f5d-11ea-926a-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/GABuilds/mysql-5.7.29/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: 200326 13:37:52 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) Slave> - extract from slave's error log Version: '5.7.29-log' socket: '/tmp/mysql_slave.sock' port: 6666 MySQL Community Server (GPL) 2020-03-26T12:36:19.395977Z 2 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=hod03-relay-bin' to avoid this problem. 2020-03-26T12:36:19.401253Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='localhost', master_port= 3333, master_log_file='master-bin.000001', master_log_pos= 1220, master_bind=''. 2020-03-26T12:36:22.729437Z 3 [Warning] 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. 2020-03-26T12:36:22.729897Z 4 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0 2020-03-26T12:36:22.729935Z 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'master-bin.000001' at position 1220, relay log './hod03-relay-bin.000001' position: 4 2020-03-26T12:36:22.730371Z 3 [Note] Slave I/O thread for channel '': connected to master 'repl@localhost:3333',replication started in log 'master-bin.000001' at position 1220 2020-03-26T12:37:52.905151Z 4 [ERROR] Slave SQL for channel '': Error in Xid_log_event: Commit could not be completed, 'The use of replication filters with XA transactions is not supported, and can lead to an undefined state in the replication slave.', Error_code: 3227 2020-03-26T12:37:52.905173Z 4 [Warning] Slave: The use of replication filters with XA transactions is not supported, and can lead to an undefined state in the replication slave. Error_code: 3227 2020-03-26T12:37:52.905180Z 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 1556.