// Build commit: f11a63734bfab93fb223d17387ff401572909b38 date: 2015-03-27 18:03:55 +0100 build-date: 2015-03-28 03:30:33 +0100 short: f11a637 branch: mysql-5.6 MySQL source 5.6.25 // Bringup Master/slave // Master scripts/mysql_install_db --basedir=/export/umesh/mysql-5.6.25 --datadir=/export/umesh/mysql-5.6.25/76493 bin/mysqld --server-id=1 --log_bin=master-bin --basedir=/export/umesh/mysql-5.6.25 --datadir=/export/umesh/mysql-5.6.25/76493 --core-file --socket=/tmp/mysql_ushastry.sock --port=15000 --log-error=/export/umesh/mysql-5.6.25/76493/log.err 2>&1 & // Slave scripts/mysql_install_db --basedir=/export/umesh/mysql-5.6.25 --datadir=/export/umesh/mysql-5.6.25/76493_ bin/mysqld --server-id=2 --replicate-do-db=DB_A --basedir=/export/umesh/mysql-5.6.25 --datadir=/export/umesh/mysql-5.6.25/76493_ --core-file --socket=/tmp/mysql_ushastry_.sock --port=15001 --log-error=/export/umesh/mysql-5.6.25/76493_/log.err 2>&1 & // Setup Replicatation // Master -Session 1 mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 15000 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql> CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 813 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) // Slave - Session 2 mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 15001 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ 1 row in set (0.00 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=15000, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='master-bin.000001', -> MASTER_LOG_POS=813; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 813 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: DB_A 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: 813 Relay_Log_Space: 457 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: 5a54c946-d6cc-11e4-80db-0010e05f3e06 Master_Info_File: /export/umesh/mysql-5.6.25/76493_/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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 1 row in set (0.00 sec) // Master - Session 1 mysql> create database DB_A; Query OK, 1 row affected (0.00 sec) mysql> create database DB_B; Query OK, 1 row affected (0.00 sec) mysql> use DB_B ; CREATE VIEW VIEW_A AS SELECT NULL; Database changed Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | DB_A | | DB_B | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) // Slave - Session 2 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | DB_A | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1194 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 665 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: DB_A 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: 1194 Relay_Log_Space: 838 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: 5a54c946-d6cc-11e4-80db-0010e05f3e06 Master_Info_File: /export/umesh/mysql-5.6.25/76493_/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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 1 row in set (0.00 sec) // Master - Session 1 mysql> use DB_B ; CREATE VIEW VIEW_A AS SELECT NULL; Database changed ERROR 1050 (42S01): Table 'VIEW_A' already exists mysql> show binlog events; +-------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------+ | master-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-enterprise-commercial-advanced-log, Binlog ver: 4 | | master-bin.000001 | 120 | Query | 1 | 288 | CREATE USER 'repl'@'localhost' IDENTIFIED BY PASSWORD '*809534247D21AC735802078139D8A854F45C31F3' | | master-bin.000001 | 288 | Query | 1 | 427 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' | | master-bin.000001 | 427 | Query | 1 | 595 | CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY PASSWORD '*809534247D21AC735802078139D8A854F45C31F3' | | master-bin.000001 | 595 | Query | 1 | 734 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1' | | master-bin.000001 | 734 | Query | 1 | 813 | FLUSH PRIVILEGES | | master-bin.000001 | 813 | Query | 1 | 907 | create database DB_A | | master-bin.000001 | 907 | Query | 1 | 1001 | create database DB_B | | master-bin.000001 | 1001 | Query | 1 | 1194 | use `DB_B`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `VIEW_A` AS SELECT NULL | | master-bin.000001 | 1194 | Query | 1 | 1387 | use `DB_B`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `VIEW_A` AS SELECT NULL | +-------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec) // Slave - Session 2 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1387 Relay_Log_File: hod03-relay-bin.000002 Relay_Log_Pos: 665 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: DB_A Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Table '%-.192s' already exists' error code=1050 ; Error on slave: actual message='no error', error code=0. Default database: 'DB_B'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `VIEW_A` AS SELECT NULL' Skip_Counter: 0 Exec_Master_Log_Pos: 1194 Relay_Log_Space: 1031 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: 0 Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Table '%-.192s' already exists' error code=1050 ; Error on slave: actual message='no error', error code=0. Default database: 'DB_B'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `VIEW_A` AS SELECT NULL' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 5a54c946-d6cc-11e4-80db-0010e05f3e06 Master_Info_File: /export/umesh/mysql-5.6.25/76493_/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: 150330 13:08:54 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)