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=3307 --log-error=$PWD/master/log.err --server_id=1 --log_bin 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=3308 --log-error=$PWD/slave/log.err --server_id=2 --log_bin --master-info-repository=TABLE --relay-log-info-repository=TABLE --super_read_only=ON 2>&1 & -- master [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: 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 3 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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.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 | +------------------+----------+--------------+------------------+-------------------+ | hod03-bin.000001 | 1220 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) Master> -- slave [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: 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.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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>show variables like 'super%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | super_read_only | ON | +-----------------+-------+ 1 row in set (0.01 sec) Slave>show variables like '%repository%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | master_info_repository | TABLE | | relay_log_info_repository | TABLE | +---------------------------+-------+ 2 rows in set (0.01 sec) Slave>CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=3307, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='hod03-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: 3307 Connect_Retry: 60 Master_Log_File: hod03-bin.000001 Read_Master_Log_Pos: 1220 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: hod03-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: 527 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: 2fb98ce2-25cd-11e8-9e64-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: 1 row in set (0.00 sec) -- master Master>create database bug89987; Query OK, 1 row affected (0.00 sec) Master>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bug89987 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) -- slave Slave>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bug89987 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (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: 3307 Connect_Retry: 60 Master_Log_File: hod03-bin.000001 Read_Master_Log_Pos: 1391 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 491 Relay_Master_Log_File: hod03-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: 1391 Relay_Log_Space: 698 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: 2fb98ce2-25cd-11e8-9e64-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: 1 row in set (0.00 sec) - kill slave and restart instance [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: ps aux|grep mysql_slave umshastr 16701 0.1 0.0 1431420 172752 pts/3 Sl 09:13 0:00 bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.21 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.21/slave --core-file --socket=/tmp/mysql_slave.sock --port=3308 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.21/slave/log.err --server_id=2 --log_bin --master-info-repository=TABLE --relay-log-info-repository=TABLE --super_read_only=ON umshastr 17085 0.0 0.0 112632 972 pts/8 S+ 09:17 0:00 grep --color=auto mysql_slave [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: kill -9 16701 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: ps aux|grep mysql_slave umshastr 17088 0.0 0.0 112632 972 pts/8 S+ 09:17 0:00 grep --color=auto mysql_slave [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/slave --core-file --socket=/tmp/mysql_slave.sock --port=3308 --log-error=$PWD/slave/log.err --server_id=2 --log_bin --master-info-repository=TABLE --relay-log-info-repository=TABLE --super_read_only=ON 2>&1 & [1] 17090 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.21: 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 4 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: hod03-bin.000001 Read_Master_Log_Pos: 1391 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: hod03-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: 1007 Last_Error: Error 'Can't create database 'bug89987'; database exists' on query. Default database: 'bug89987'. Query: 'create database bug89987' Skip_Counter: 0 Exec_Master_Log_Pos: 1220 Relay_Log_Space: 1396 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: 1007 Last_SQL_Error: Error 'Can't create database 'bug89987'; database exists' on query. Default database: 'bug89987'. Query: 'create database bug89987' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 2fb98ce2-25cd-11e8-9e64-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: 180312 09:17:38 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) --extract from error log 2018-03-12T08:17:38.934001Z 0 [Note] Server socket created on IP: '::'. 2018-03-12T08:17:38.939648Z 0 [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. 2018-03-12T08:17:38.942026Z 1 [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. 2018-03-12T08:17:38.942440Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'hod03-bin.000001' at position 1220, relay log './hod03-relay-bin.000001' position: 4 2018-03-12T08:17:38.942943Z 1 [Note] Slave I/O thread for channel '': connected to master 'repl@localhost:3307',replication started in log 'hod03-bin.000001' at position 1220 2018-03-12T08:17:38.943528Z 2 [ERROR] Slave SQL for channel '': Error 'Can't create database 'bug89987'; database exists' on query. Default database: 'bug89987'. Query: 'create database bug89987', Error_code: 1007 2018-03-12T08:17:38.943566Z 2 [Warning] Slave: Can't create database 'bug89987'; database exists Error_code: 1007 2018-03-12T08:17:38.943571Z 2 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'hod03-bin.000001' position 1220 2018-03-12T08:17:38.947977Z 0 [Note] Event Scheduler: Loaded 0 events 2018-03-12T08:17:38.948219Z 0 [Note] bin/mysqld: ready for connections. Version: '5.7.21-log' socket: '/tmp/mysql_slave.sock' port: 3308 MySQL Community Server (GPL)