0

We have mysql replication set between two aurora clusters. Everything looks good and the replication is working well. However, I created a few hours ago a user on the master and I still don't see it on the slave. I see that Aurora excluding mysql schema which might be the issue. But I don't know if I should create the user myself or what.

MySQL [(none)]> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: <rds-host>.rds.amazonaws.com
                  Source_User: user
                  Source_Port: port
                Connect_Retry: 60
              Source_Log_File: mysql-bin-changelog.111
          Read_Source_Log_Pos: 133216906
               Relay_Log_File: relaylog.000548
                Relay_Log_Pos: 133217133
        Relay_Source_Log_File: mysql-bin-changelog.111
           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: mysql.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 133216906
              Relay_Log_Space: 133217380
              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: 58182133
                  Source_UUID: cdc9f787-fa44-376f-bceb-a16cd8c88c38
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Slave 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: 0
            Network_Namespace: 

on slave: MySQL [(none)]> select user from mysql.user where user='dms_u'; Empty set (0.00 sec)

on master:

MySQL [(none)]> select user from mysql.user where user='dms_u';
+-------+
| user  |
+-------+
| dms_u |
+-------+
1 row in set (0.00 sec)

3 Answers 3

0

Personally I never replicate the mysql schema , because as it specifically mentioned on The mysql System Schema: The mysql schema is the system schema. It contains tables that store information required by the MySQL server as it runs.

You don't see the user on the slave , as you already figured out that * I see that Aurora excluding mysql schema which might be the issue* .

But I don't know if I should create the user myself or what

Depends, if you need the user on the slave the create it otherwise don't.

0

We can't answer for you whether you should create the same user on the replica. We don't know how you will use that user.

Here are a couple of examples of reasons to create the user on the replica, but this is off the top of my head, not intended to be a complete list:

  • Your clients use the user dms_u and you want them to be able to run queries on the replica using the same user.
  • You will create views or stored routines using the user dms_u as the definer. If you do this on the source instance but the user does not exist on the replica instance, then the DDL statement will cause an error and stop replication.

Do these apply to your case? I can't guess.

0

Aurora replication does not migrate mysql and as a result the user never created. I have created it manually on the replica and it did not break replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.