-- 8.0.19 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 --mysqlx=0 --log-slave-updates=1 --binlog_format=MIXED --log-error-verbosity=3 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=9999 --log-error=$PWD/slave/log.err --log-bin=slave-bin --server_id=2 --skip_slave_start --mysqlx=0 --log-slave-updates=1 --binlog_format=MIXED --log-error-verbosity=3 2>&1 & -- 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 6 Server version: 8.0.19 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> Master>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 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 | 1420 | | | | +-------------------+----------+--------------+------------------+-------------------+ 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 6 Server version: 8.0.19 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=1420, -> GET_MASTER_PUBLIC_KEY=1; 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: 1420 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 323 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: 1420 Relay_Log_Space: 531 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: 8f879283-52e2-11ea-abec-0010e05f3e06 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 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: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) -- master Master>create database test; Query OK, 1 row affected (0.00 sec) Master>use test Database changed Master>DROP FUNCTION IF EXISTS temp_func; DELIMITER // CREATE FUNCTION temp_func() RETURNS INTEGER READS SQL DATA BEGIN RETURN 3; END // DELIMITER ; DROP PROCEDURE IF EXISTS temp_proc; DELIMITER // CREATE PROCEDURE temp_proc() BEGIN CREATE TEMPORARY TABLE temp_table AS SELECT 1; INSERT INTO temp_table SELECT temp_func(); END // DELIMITER ;Query OK, 0 rows affected, 1 warning (0.01 sec) Master>DELIMITER // Master>CREATE FUNCTION temp_func() -> RETURNS INTEGER READS SQL DATA -> BEGIN -> RETURN 3; -> END // Query OK, 0 rows affected (0.00 sec) Master>DELIMITER ; Master> Master>DROP PROCEDURE IF EXISTS temp_proc; Query OK, 0 rows affected, 1 warning (0.00 sec) Master>DELIMITER // Master>CREATE PROCEDURE temp_proc() -> BEGIN -> CREATE TEMPORARY TABLE temp_table AS SELECT 1; -> -> INSERT INTO temp_table SELECT temp_func(); -> END // Query OK, 0 rows affected (0.00 sec) Master>DELIMITER ; Master>CALL temp_proc(); Query OK, 1 row 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: 2940 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 1502 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: 1146 Last_Error: Error 'Table 'test.temp_table' doesn't exist' on query. Default database: 'test'. Query: 'INSERT INTO temp_table SELECT temp_func()' Skip_Counter: 0 Exec_Master_Log_Pos: 2599 Relay_Log_Space: 2051 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: 1146 Last_SQL_Error: Error 'Table 'test.temp_table' doesn't exist' on query. Default database: 'test'. Query: 'INSERT INTO temp_table SELECT temp_func()' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 8f879283-52e2-11ea-abec-0010e05f3e06 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: 200219 07:43:36 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) Slave>system cat slave/log.err 2020-02-19T06:41:44.110437Z 0 [Note] [MY-010098] [Server] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2020-02-19T06:41:44.110548Z 0 [Note] [MY-010949] [Server] Basedir set to /export/umesh/server/binaries/GABuilds/mysql-8.0.19/. 2020-02-19T06:41:44.110563Z 0 [System] [MY-010116] [Server] /export/umesh/server/binaries/GABuilds/mysql-8.0.19/bin/mysqld (mysqld 8.0.19) starting as process 25670 2020-02-19T06:41:44.120497Z 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO 2020-02-19T06:41:44.120803Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled. 2020-02-19T06:41:44.120876Z 0 [Note] [MY-010747] [Server] Plugin 'mysqlx' is disabled. 2020-02-19T06:41:44.123577Z 1 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available 2020-02-19T06:41:44.123666Z 1 [Note] [MY-012943] [InnoDB] Mutexes and rw_locks use GCC atomic builtins 2020-02-19T06:41:44.123716Z 1 [Note] [MY-012944] [InnoDB] Uses event mutexes 2020-02-19T06:41:44.123761Z 1 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier 2020-02-19T06:41:44.123823Z 1 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.11 2020-02-19T06:41:44.128788Z 1 [Note] [MY-013251] [InnoDB] Number of pools: 1 2020-02-19T06:41:44.129023Z 1 [Note] [MY-012951] [InnoDB] Using CPU crc32 instructions 2020-02-19T06:41:44.129712Z 1 [Note] [MY-012203] [InnoDB] Directories to scan './' 2020-02-19T06:41:44.129857Z 1 [Note] [MY-012204] [InnoDB] Scanning './' 2020-02-19T06:41:44.130949Z 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 4 files. 2020-02-19T06:41:44.131934Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 2020-02-19T06:41:44.140449Z 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool 2020-02-19T06:41:44.141916Z 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-02-19T06:41:44.221369Z 1 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 19562526, whereas checkpoint_lsn = 19562526 2020-02-19T06:41:44.222538Z 1 [Note] [MY-013083] [InnoDB] Log background threads are being started... 2020-02-19T06:41:44.222927Z 1 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ... 2020-02-19T06:41:44.223022Z 1 [Note] [MY-012535] [InnoDB] Apply batch completed! 2020-02-19T06:41:44.223241Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'. 2020-02-19T06:41:44.223610Z 1 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'. 2020-02-19T06:41:44.227239Z 1 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces. 2020-02-19T06:41:44.227371Z 1 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 1242 2020-02-19T06:41:44.238487Z 1 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables 2020-02-19T06:41:44.238697Z 1 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-02-19T06:41:44.254419Z 1 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB. 2020-02-19T06:41:44.254760Z 1 [Note] [MY-011825] [InnoDB] Scanning temp tablespace dir:'./#innodb_temp/' 2020-02-19T06:41:44.289225Z 1 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active. 2020-02-19T06:41:44.289733Z 1 [Note] [MY-012976] [InnoDB] 8.0.19 started; log sequence number 19562526 2020-02-19T06:41:44.299104Z 1 [Note] [MY-011089] [Server] Data dictionary restarting version '80017'. 2020-02-19T06:41:44.404076Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files 2020-02-19T06:41:44.405347Z 1 [Note] [MY-012356] [InnoDB] Validated 6/6 tablespaces 2020-02-19T06:41:44.423768Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80017'. 2020-02-19T06:41:44.514325Z 0 [Note] [MY-010902] [Server] Thread priority attribute setting in Resource Group SQL shall be ignored due to unsupported platform or insufficient privilege. 2020-02-19T06:41:44.533078Z 0 [Note] [MY-012487] [InnoDB] DDL log recovery : begin 2020-02-19T06:41:44.533376Z 0 [Note] [MY-012488] [InnoDB] DDL log recovery : end 2020-02-19T06:41:44.534048Z 0 [Note] [MY-011946] [InnoDB] Loading buffer pool(s) from /export/umesh/server/binaries/GABuilds/mysql-8.0.19/slave/ib_buffer_pool 2020-02-19T06:41:44.534988Z 0 [Note] [MY-011946] [InnoDB] Buffer pool(s) load completed at 200219 7:41:44 2020-02-19T06:41:44.540302Z 0 [Note] [MY-012922] [InnoDB] Waiting for purge to start 2020-02-19T06:41:44.592760Z 0 [Note] [MY-010182] [Server] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2020-02-19T06:41:44.592946Z 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory. 2020-02-19T06:41:44.594662Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-02-19T06:41:44.594877Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory. 2020-02-19T06:41:44.595056Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory. 2020-02-19T06:41:44.595544Z 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 9999 2020-02-19T06:41:44.595772Z 0 [Note] [MY-010253] [Server] IPv6 is available. 2020-02-19T06:41:44.595979Z 0 [Note] [MY-010264] [Server] - '::' resolves to '::'; 2020-02-19T06:41:44.596208Z 0 [Note] [MY-010251] [Server] Server socket created on IP: '::'. 2020-02-19T06:41:44.621286Z 4 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 4 2020-02-19T06:41:44.621641Z 0 [System] [MY-010931] [Server] /export/umesh/server/binaries/GABuilds/mysql-8.0.19/bin/mysqld: ready for connections. Version: '8.0.19' socket: '/tmp/mysql_slave.sock' port: 9999 MySQL Community Server - GPL. 2020-02-19T06:42:50.157598Z 6 [Warning] [MY-010604] [Repl] 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-02-19T06:42:50.163252Z 6 [System] [MY-010597] [Repl] '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= 1420, master_bind=''. 2020-02-19T06:42:54.088988Z 7 [Warning] [MY-010897] [Repl] 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-02-19T06:42:54.090327Z 8 [Note] [MY-010581] [Repl] Slave SQL thread for channel '' initialized, starting replication in log 'master-bin.000001' at position 1420, relay log './hod03-relay-bin.000001' position: 4 2020-02-19T06:42:54.096264Z 7 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl@localhost:3333',replication started in log 'master-bin.000001' at position 1420 2020-02-19T06:43:36.937019Z 8 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Table 'test.temp_table' doesn't exist' on query. Default database: 'test'. Query: 'INSERT INTO temp_table SELECT temp_func()', Error_code: MY-001146 2020-02-19T06:43:36.937074Z 8 [Warning] [MY-010584] [Repl] Slave: Table 'test.temp_table' doesn't exist Error_code: MY-001146 2020-02-19T06:43:36.937085Z 8 [ERROR] [MY-010586] [Repl] 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 2599 Slave>