-- 8.0.39 build used cat docs/INFO_SRC commit: 47f6c032ca169c5e3a81027736452f7c2ef53217 date: 2024-07-12 21:10:57 +0200 build-date: 2024-07-12 19:17:03 +0000 short: 47f6c032ca1 branch: mysql-8.0.39-release MySQL source 8.0.39 -- Setup simple Source/Replica setup SOURCE='source' REPLICA='replica' ### File based rm -rf $SOURCE/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/$SOURCE --log-error-verbosity=3 bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/$SOURCE --core-file --socket=/tmp/mysql_source.sock --port=3333 --log-error=$PWD/$SOURCE/log.err --log-bin=$SOURCE-bin --server_id=1 --mysqlx=0 --log-error-verbosity=3 --secure-file-priv=/tmp/ --performance-schema=ON --binlog-rows-query-log-events=ON 2>&1 & rm -rf $REPLICA/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/$REPLICA --log-error-verbosity=3 bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/$REPLICA --core-file --socket=/tmp/mysql_replica.sock --port=6666 --log-error=$PWD/$REPLICA/log.err --log-bin=$REPLICA-bin --server_id=2 --mysqlx=0 --log-error-verbosity=3 --secure-file-priv=/tmp/ --performance-schema=ON 2>&1 & -- Source bin/mysql -uroot -S/tmp/mysql_source.sock --prompt='Source>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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. Source>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.01 sec) Source>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) Source> Source>CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.01 sec) Source>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) Source>FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) Source> Source>CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.01 sec) Source>show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | source-bin.000001 | 1905 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) -- Replica bin/mysql -uroot -S/tmp/mysql_replica.sock --prompt='Replica>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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. Replica>CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=3333, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='source-bin.000001', -> MASTER_LOG_POS=1905, -> GET_MASTER_PUBLIC_KEY=1; Query OK, 0 rows affected, 10 warnings (0.02 sec) Replica>start replica; Query OK, 0 rows affected (0.01 sec) Replica>show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: localhost Source_User: repl Source_Port: 3333 Connect_Retry: 60 Source_Log_File: source-bin.000001 Read_Source_Log_Pos: 1905 Relay_Log_File: support-cluster03-relay-bin.000002 Relay_Log_Pos: 327 Relay_Source_Log_File: source-bin.000001 Replica_IO_Running: Yes Replica_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_Source_Log_Pos: 1905 Relay_Log_Space: 549 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 1 Source_UUID: 5fcfca92-6535-11ef-8bfc-020017078e24 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) -- follow steps from report Source>create database test; Query OK, 1 row affected (0.01 sec) Source>use test Database changed Source> Source>CREATE TABLE `t1` ( -> `id` bigint(20) unsigned NOT NULL, -> `col` varchar(255) DEFAULT NULL, -> UNIQUE KEY `uk` (`col`) USING BTREE, -> KEY `sk` (`id`) USING BTREE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected, 1 warning (0.03 sec) Source>show warnings; +---------+------+------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------+ | Warning | 1681 | Integer display width is deprecated and will be removed in a future release. | +---------+------+------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Source>delimiter $$ Source>create procedure auto_insert(IN times int) -> BEGIN -> declare i int default 1; -> truncate table t1; -> while(i insert into t1 values(i,null); -> set i=i+1; -> end while; -> END$$ Query OK, 0 rows affected (0.00 sec) Source>delimiter ; Source>call auto_insert(1000); Query OK, 1 row affected (2.54 sec) Source>select count(*) from t1; +----------+ | count(*) | +----------+ | 999 | +----------+ 1 row in set (0.00 sec) -- Replica Replica>use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed Replica>select count(*) from t1; +----------+ | count(*) | +----------+ | 999 | +----------+ 1 row in set (0.00 sec) -- Source Source>xa begin 'x1'; Query OK, 0 rows affected (0.00 sec) Source>delete from t1 where id=100 and col is null; Query OK, 1 row affected (0.00 sec) Source>xa end 'x1'; Query OK, 0 rows affected (0.00 sec) Source>xa prepare 'x1'; Query OK, 0 rows affected (0.00 sec) Source>delete from t1 where id=99 and col is null; Query OK, 1 row affected (0.01 sec) -- Replica Replica>show processlist; +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 584 | Waiting on empty queue | NULL | | 7 | root | localhost | test | Query | 0 | init | show processlist | | 8 | system user | connecting host | NULL | Connect | 358 | Waiting for source to send event | NULL | | 9 | system user | | NULL | Query | 55 | Replica has read all relay log; waiting for more updates | NULL | | 10 | system user | | test | Query | 55 | Applying batch of row changes (delete) | delete from t1 where id=99 and col is null | | 11 | system user | | NULL | Query | 170 | Waiting for an event from Coordinator | NULL | | 12 | system user | | NULL | Connect | 358 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 358 | Waiting for an event from Coordinator | NULL | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) Replica>show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: localhost Source_User: repl Source_Port: 3333 Connect_Retry: 60 Source_Log_File: source-bin.000001 Read_Source_Log_Pos: 341518 Relay_Log_File: support-cluster03-relay-bin.000002 Relay_Log_Pos: 339589 Relay_Source_Log_File: source-bin.000001 Replica_IO_Running: Yes Replica_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_Source_Log_Pos: 341167 Relay_Log_Space: 340162 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 66 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 1 Source_UUID: 5fcfca92-6535-11ef-8bfc-020017078e24 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) Replica> Replica>show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: localhost Source_User: repl Source_Port: 3333 Connect_Retry: 60 Source_Log_File: source-bin.000001 Read_Source_Log_Pos: 341518 Relay_Log_File: support-cluster03-relay-bin.000002 Relay_Log_Pos: 339589 Relay_Source_Log_File: source-bin.000001 Replica_IO_Running: Yes Replica_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_Source_Log_Pos: 341167 Relay_Log_Space: 340162 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 417 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 1 Source_UUID: 5fcfca92-6535-11ef-8bfc-020017078e24 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 1 Network_Namespace: 1 row in set (0.00 sec) Replica>show processlist; +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 950 | Waiting on empty queue | NULL | | 7 | root | localhost | test | Query | 0 | init | show processlist | | 8 | system user | connecting host | NULL | Connect | 724 | Waiting for source to send event | NULL | | 9 | system user | | NULL | Query | 421 | Replica has read all relay log; waiting for more updates | NULL | | 10 | system user | | test | Query | 421 | Applying batch of row changes (delete) | delete from t1 where id=99 and col is null | | 11 | system user | | NULL | Query | 536 | Waiting for an event from Coordinator | NULL | | 12 | system user | | NULL | Connect | 724 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 724 | Waiting for an event from Coordinator | NULL | +----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------+ 8 rows in set, 1 warning (0.00 sec) Replica>show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Replica>select * from performance_schema.processlist\G *************************** 1. row *************************** ID: 5 USER: event_scheduler HOST: localhost DB: NULL COMMAND: Daemon TIME: 986 STATE: Waiting on empty queue INFO: NULL EXECUTION_ENGINE: PRIMARY *************************** 2. row *************************** ID: 7 USER: root HOST: localhost DB: test COMMAND: Query TIME: 0 STATE: executing INFO: select * from performance_schema.processlist EXECUTION_ENGINE: PRIMARY *************************** 3. row *************************** ID: 8 USER: system user HOST: NULL DB: NULL COMMAND: Connect TIME: 760 STATE: Waiting for source to send event INFO: NULL EXECUTION_ENGINE: PRIMARY *************************** 4. row *************************** ID: 9 USER: system user HOST: NULL DB: NULL COMMAND: Query TIME: 457 STATE: Replica has read all relay log; waiting for more updates INFO: NULL EXECUTION_ENGINE: PRIMARY *************************** 5. row *************************** ID: 10 USER: system user HOST: NULL DB: test COMMAND: Query TIME: 457 STATE: Applying batch of row changes (delete) INFO: delete from t1 where id=99 and col is null EXECUTION_ENGINE: PRIMARY *************************** 6. row *************************** ID: 11 USER: system user HOST: NULL DB: NULL COMMAND: Query TIME: 572 STATE: Waiting for an event from Coordinator INFO: NULL EXECUTION_ENGINE: PRIMARY *************************** 7. row *************************** ID: 12 USER: system user HOST: NULL DB: NULL COMMAND: Connect TIME: 760 STATE: Waiting for an event from Coordinator INFO: NULL EXECUTION_ENGINE: PRIMARY *************************** 8. row *************************** ID: 13 USER: system user HOST: NULL DB: NULL COMMAND: Connect TIME: 760 STATE: Waiting for an event from Coordinator INFO: NULL EXECUTION_ENGINE: PRIMARY 8 rows in set (0.00 sec)