1

I can see many good articles around the web about setting up MySQL Master-Master Replication with GTID.

However, strangely enough, I simply can't find the same about in how to stop and start it.

My understanding is that I can for example put under maintenance mode the only source that applies transactions to the Master-Master, wait for changes to propagate between them and stop the MySQL service.

But, how do I restart the Master-Master replication? I am assuming sudo service mysql start won't be enough.

I have thought perhaps to apply STOP SLAVE; on both Masters before stopping the mysql service. And apply START SLAVE; on both Masters after starting the mysql service.

Below I have some technical specs to provide 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.