My boss has given me the task to migrate a quite old ecommerce legacy web based application to a new server. Its database has around 150m rows in total and it runs on MySQL 5.6 and PHP 5.5.9.
On this application, there is what seems to be a MySQL GTID replication mechanism of some sort.
I am not being able to get a clear picture about the replication setup.
For example, both MySQL servers have an output to the command SHOW SLAVE STATUS and SHOW MASTER STATUS.
Does this make sense if I am expecting one of the servers to be the master and the other the slave?
That being said, I am assuming that what I have at hand is a Master <=(Slave,Master)=> Slave mechanism. Would this statement be correct?
Below I am adding technical information for context:
Server A:
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: db2-mysql-bin.002374
Position: 2535963
Binlog_Do_DB: dbtest,dbproduction
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 6a3c1b11-cda3-11e4-8520-b083fede55b6:1-19481799,
c62838ed-cda2-11e4-851c-bc305bf41198:1-355853745
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 1 | | 3306 | 2 | 6a3c1b11-cda3-11e4-8520-b083fede55b6 |
+-----------+------+------+-----------+--------------------------------------+
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ***.***.***.3
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db1-mysql-bin.000026
Read_Master_Log_Pos: 609988
Relay_Log_File: db2-relay-log.006012
Relay_Log_Pos: 34751
Relay_Master_Log_File: db1-mysql-bin.000026
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dpproduction,dbtest
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 609988
Relay_Log_Space: 35044
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/mysql/db1-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mysql/db1-key.pem
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: 6a3c1b11-cda3-11e4-8520-b083fede55b6
Master_Info_File: /var/lib/mysql/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: 6a3c1b11-cda3-11e4-8520-b083fede55b6:1-19481793
Executed_Gtid_Set: 6a3c1b11-cda3-11e4-8520-b083fede55b6:1-19481793,
c62838ed-cda2-11e4-851c-bc305bf41198:1-355853646
Auto_Position: 1
/etc/mysql/my.cnf
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
## SG
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2
log-bin = db2-mysql-bin
gtid_mode = ON
enforce_gtid_consistency = true
log_slave_updates = true
binlog_format = mixed
expire_logs_days = 10
max_binlog_size = 100M
### GTID Slave Stuff
relay-log = db2-relay-log
#####################
#### REPLICATE DBS
#####################
replicate-do-db = dbproduction
replicate-do-db = dbtest
#
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#####################
#### REPLICATE TABLES
#####################
replicate-ignore-table=***********.*****
replicate-ignore-table=***********.*****
replicate-ignore-table=***********.*****
replicate-ignore-table=***********.*****
#####################
#### REPLICATE BINLOGS
#####################
binlog-do-db = dbtest
binlog-do-db = dbproduction
#
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
#### SKIP ERRORS
## ORIGINAL slave-skip-errors = 1007, 1050, 1062, 1146, 1133
slave-skip-errors = 1007, 1050, 1062, 1146, 1133, 1032
Server B:
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: db1-mysql-bin.000029
Position: 23700675
Binlog_Do_DB: dbtest,dbproduction
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 6a3c1b11-cda3-11e4-8520-b083fede55b6:1-19516339,
c62838ed-cda2-11e4-851c-bc305bf41198:1-356349962
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3306 | 1 | c62838ed-cda2-11e4-851c-bc305bf41198 |
+-----------+------+------+-----------+--------------------------------------+
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ***.***.***.153
Master_User: user2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db2-mysql-bin.002377
Read_Master_Log_Pos: 25879749
Relay_Log_File: db1-relay-log.000078
Relay_Log_Pos: 22521665
Relay_Master_Log_File: db2-mysql-bin.002377
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dbtest,dbproduction
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 25879749
Relay_Log_Space: 24600782
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/mysql/db2-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mysql/db2-key.pem
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: 2
Master_UUID: c62838ed-cda2-11e4-851c-bc305bf41198
Master_Info_File: /var/lib/mysql/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: c62838ed-cda2-11e4-851c-bc305bf41198:352495283-356349193
Executed_Gtid_Set: 6a3c1b11-cda3-11e4-8520-b083fede55b6:1-19516251,
c62838ed-cda2-11e4-851c-bc305bf41198:1-356349193
Auto_Position: 1
/etc/mysql/my.cnf
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
## SG
server-id = 1
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = db1-mysql-bin
gtid_mode = ON
enforce_gtid_consistency = true
log_slave_updates = true
binlog_format = mixed
expire_logs_days = 10
max_binlog_size = 100M
### GTID Slave Stuff
relay-log = db1-relay-log
#####################
#### REPLICATE DBS
#####################
replicate-do-db = dbproduction
replicate-do-db = dbtest
#
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#####################
#### REPLICATE TABLES
#####################
replicate-ignore-table=***********.*****
replicate-ignore-table=***********.*****
replicate-ignore-table=***********.*****
replicate-ignore-table=***********.*****
#####################
#### REPLICATE BINLOGS
#####################
binlog-do-db = dbtest
binlog-do-db = dbproduction
#
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
#### SKIP ERRORS
slave-skip-errors = 1007, 1050, 1062, 1146, 1133