rm -rf master/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/master/ bin/mysqld --no-defaults --log-bin --server_id=1 --basedir=$PWD --datadir=$PWD/master/ --core-file --socket=/tmp/mysql_master.sock --port=3306 --log-error=$PWD/master/log.err 2>&1 & rm -rf slave/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/slave/ bin/mysqld --no-defaults --server_id=2 --basedir=$PWD --datadir=$PWD/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=$PWD/slave/log.err 2>&1 & -- Master [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.18: 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.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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) -- Slave [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.18: 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 3 Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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=3306, -> 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: 3306 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: 0fdf259d-c390-11e7-a8d1-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/GABuilds/mysql-5.7.18/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) ## Create required table on master -- master Master>show collation where `Default`='Yes' and Charset='utf8mb4'; +--------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------+---------+----+---------+----------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | +--------------------+---------+----+---------+----------+---------+ 1 row in set (0.00 sec) Master>create database test; Query OK, 1 row affected (0.00 sec) Master>use test Database changed Master>create database if not exists test; Query OK, 1 row affected, 1 warning (0.00 sec) Master>use test; Database changed Master>drop table `foobar`; ERROR 1051 (42S02): Unknown table 'test.foobar' Master>CREATE TABLE `foobar` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `foo` varchar(100) CHARACTER SET utf8mb4 NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY `id_foo` (`foo`) -> ) ENGINE=InnoDB AUTO_INCREMENT=12345 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) Master>select 0x666F6F626172406578616D706C652E636F6D; +----------------------------------------+ | 0x666F6F626172406578616D706C652E636F6D | +----------------------------------------+ | foobar@example.com | +----------------------------------------+ 1 row in set (0.00 sec) Master>INSERT INTO foobar(foo) VALUES(0x666F6F626172406578616D706C652E636F6D),(0x666F6F626172406578616D706C652E636F6DD98B); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 Master>select * from foobar; +-------+----------------------+ | id | foo | +-------+----------------------+ | 12345 | foobar@example.com | | 12346 | foobar@example.comً | +-------+----------------------+ 2 rows in set (0.00 sec) -- slave Slave>select * from foobar; +-------+----------------------+ | id | foo | +-------+----------------------+ | 12345 | foobar@example.com | | 12346 | foobar@example.comً | +-------+----------------------+ 2 rows in set (0.00 sec) Slave>\q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.18: bin/mysqladmin -uroot -S /tmp/mysql_slave.sock shutdown [1]+ Done bin/mysqld --no-defaults --server_id=2 --basedir=$PWD --datadir=$PWD/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=$PWD/slave/log.err 2>&1 -- Upgrade slave to 8.0.3 bin/mysqld --no-defaults --server_id=2 --basedir=$PWD --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.18/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.18/slave/log.err 2>&1 & [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.3: bin/mysqld --no-defaults --server_id=2 --basedir=$PWD --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.18/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.18/slave/log.err 2>&1 & [1] 31916 [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.3: bin/mysql -uroot -S /tmp/mysql_slave.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.3-rc-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2017, 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. mysql> show collation where `Default`='Yes' and Charset='utf8mb4'; +--------------------+---------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +--------------------+---------+-----+---------+----------+---------+---------------+ | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | +--------------------+---------+-----+---------+----------+---------+---------------+ 1 row in set (0.00 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.3: bin/mysql_upgrade -uroot -S /tmp/mysql_slave.sock Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_ddl_log OK mysql.innodb_dynamic_metadata OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases. sys.sys_config OK test.foobar OK Upgrade process completed successfully. Checking if update is needed. [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.3: bin/mysqladmin -uroot -S /tmp/mysql_slave.sock shutdown [1]+ Done bin/mysqld --no-defaults --server_id=2 --basedir=$PWD --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.18/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.18/slave/log.err 2>&1 [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.3: [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-8.0.3: bin/mysql -uroot -S /tmp/mysql_slave.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.3-rc-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2017, 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. mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: hod03-bin.000001 Read_Master_Log_Pos: 2233 Relay_Log_File: hod03-relay-bin.000006 Relay_Log_Pos: 321 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: 2233 Relay_Log_Space: 529 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: 0fdf259d-c390-11e7-a8d1-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) mysql> -- master Master>ALTER TABLE `foobar` CHANGE `foo` `old_foo` varchar(100) CHARACTER SET utf8mb4 NOT NULL; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 -- slave mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: hod03-bin.000001 Read_Master_Log_Pos: 2459 Relay_Log_File: hod03-relay-bin.000006 Relay_Log_Pos: 321 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: 1062 Last_Error: Error 'Duplicate entry 'foobar@example.comً' for key 'id_foo'' on query. Default database: 'test'. Query: 'ALTER TABLE `foobar` CHANGE `foo` `old_foo` varchar(100) CHARACTER SET utf8mb4 NOT NULL' Skip_Counter: 0 Exec_Master_Log_Pos: 2233 Relay_Log_Space: 755 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: 1062 Last_SQL_Error: Error 'Duplicate entry 'foobar@example.comً' for key 'id_foo'' on query. Default database: 'test'. Query: 'ALTER TABLE `foobar` CHANGE `foo` `old_foo` varchar(100) CHARACTER SET utf8mb4 NOT NULL' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 0fdf259d-c390-11e7-a8d1-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: 171107 09:24:17 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) mysql> show collation where `Default`='Yes' and Charset='utf8mb4'; +--------------------+---------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +--------------------+---------+-----+---------+----------+---------+---------------+ | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | +--------------------+---------+-----+---------+----------+---------+---------------+ 1 row in set (0.00 sec)