Amazon RDS for MySQL から Azure Database for MySQL へレプリケーションしてみた

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.04 sec)

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.04 sec)

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.03 sec)

mysql> show binary logs;
+----------------------------+-----------+-----------+
| Log_name                   | File_size | Encrypted |
+----------------------------+-----------+-----------+
| mysql-bin-changelog.000001 |      1210 | No        |
| mysql-bin-changelog.000002 |       584 | No        |
| mysql-bin-changelog.000003 |       156 | No        |
+----------------------------+-----------+-----------+
3 rows in set (0.03 sec)

mysql> USE world;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.03 sec)

mysql> SELECT COUNT(*) FROM city;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.11 sec)

mysql> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.03 sec)

# AWS
$ mysqldump --opt -u admin -h database-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com -p world > hogedump.sql

# Azure
$ mysql -u iwasa@hogehoge-mysql -h hogehoge-mysql.mysql.database.azure.com -p world < hogedump.sql 
Enter password: 
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

$ mysqldump --opt -u admin -h database-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com -p world --set-gtid-purged=OFF > hogedump.sql

$ mysql -u iwasa@hogehoge-mysql -h hogehoge-mysql.mysql.database.azure.com -p world < hogedump.sql                                  
Enter password: 

:

mysql> USE world;
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
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.05 sec)

mysql> SELECT COUNT(*) FROM city;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.07 sec)

mysql> CREATE USER 'syncuser'@'%' IDENTIFIED BY 'hogehoge';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER USER 'syncuser'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT @@global.redirect_server_host;
+-------------------------------------------------------------+
| @@global.redirect_server_host                               |
+-------------------------------------------------------------+
| fdba5e419682.tr373.japaneast1-a.worker.database.windows.net |
+-------------------------------------------------------------+
1 row in set (0.02 sec)

:

# これを使う
$ nslookup fdba5e419682.tr373.japaneast1-a.worker.database.windows.net
:
Address: 40.79.192.2

# これは使わない
$ nslookup hogehoge-mysql.mysql.database.azure.com
:
Address: 40.79.192.23

mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000034 |      533 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)

SET @cert = '-----BEGIN CERTIFICATE-----
MIIEBjCCAu6gAwIBAgIJAMc0ZzaSUK51MA0GCSqGSIb3DQEBCwUAMIGPMQswCQYD
VQQGEwJVUzEQMA4GA1UEBwwHU2VhdHRsZTETMBEGA1UECAwKV2FzaGluZ3RvbjEi
MCAGA1UECgwZQW1hem9uIFdlYiBTZXJ2aWNlcywgSW5jLjETMBEGA1UECwwKQW1h
em9uIFJEUzEgMB4GA1UEAwwXQW1hem9uIFJEUyBSb290IDIwMTkgQ0EwHhcNMTkw
ODIyMTcwODUwWhcNMjQwODIyMTcwODUwWjCBjzELMAkGA1UEBhMCVVMxEDAOBgNV
BAcMB1NlYXR0bGUxEzARBgNVBAgMCldhc2hpbmd0b24xIjAgBgNVBAoMGUFtYXpv
biBXZWIgU2VydmljZXMsIEluYy4xEzARBgNVBAsMCkFtYXpvbiBSRFMxIDAeBgNV
BAMMF0FtYXpvbiBSRFMgUm9vdCAyMDE5IENBMIIBIjANBgkqhkiG9w0BAQEFAAOC
AQ8AMIIBCgKCAQEArXnF/E6/Qh+ku3hQTSKPMhQQlCpoWvnIthzX6MK3p5a0eXKZ
oWIjYcNNG6UwJjp4fUXl6glp53Jobn+tWNX88dNH2n8DVbppSwScVE2LpuL+94vY
0EYE/XxN7svKea8YvlrqkUBKyxLxTjh+U/KrGOaHxz9v0l6ZNlDbuaZw3qIWdD/I
6aNbGeRUVtpM6P+bWIoxVl/caQylQS6CEYUk+CpVyJSkopwJlzXT07tMoDL5WgX9
O08KVgDNz9qP/IGtAcRduRcNioH3E9v981QO1zt/Gpb2f8NqAjUUCUZzOnij6mx9
McZ+9cWX88CRzR0vQODWuZscgI08NvM69Fn2SQIDAQABo2MwYTAOBgNVHQ8BAf8E
BAMCAQYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUc19g2LzLA5j0Kxc0LjZa
pmD/vB8wHwYDVR0jBBgwFoAUc19g2LzLA5j0Kxc0LjZapmD/vB8wDQYJKoZIhvcN
AQELBQADggEBAHAG7WTmyjzPRIM85rVj+fWHsLIvqpw6DObIjMWokpliCeMINZFV
ynfgBKsf1ExwbvJNzYFXW6dihnguDG9VMPpi2up/ctQTN8tm9nDKOy08uNZoofMc
NUZxKCEkVKZv+IL4oHoeayt8egtv3ujJM6V14AstMQ6SwvwvA93EP/Ug2e4WAXHu
cbI1NAbUgVDqp+DRdfvZkgYKryjTWd/0+1fS8X1bBZVWzl7eirNVnHbSH2ZDpNuY
0SBd8dj5F6ld3t58ydZbrTHze7JJOd8ijySAp4/kiu9UfZWuTPABzDa/DSdz9Dk/
zPW4CXXvhLmE02TA9/HeCw3KEHIwicNuEfw=
-----END CERTIFICATE-----';
CALL mysql.az_replication_change_master('database-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com', 'syncuser', 'hogehoge', 3306, 'mysql-bin-changelog.000034', 533, @cert);

mysql> CALL mysql.az_replication_start;
+------------------------------------------------------------------------------------------------------+
| message                                                                                              |
+------------------------------------------------------------------------------------------------------+
| Successfully start the replication. Please run "show slave status;" to check the replication status. |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: database-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com
                  Master_User: syncuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-changelog.000008
          Read_Master_Log_Pos: 947
               Relay_Log_File: relay_bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin-changelog.000008
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,sys.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 947
              Relay_Log_Space: 155
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: C:\work\primary_ca.pem
           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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.03 sec)

mysql> INSERT INTO country (Code) VALUES ('AA1');
mysql> INSERT INTO country (Code) VALUES ('AA2');
mysql> INSERT INTO country (Code) VALUES ('AA3');
mysql> select code from country order by code limit 3;
+------+
| code |
+------+
| AA1  |
| AA2  |
| AA3  |
+------+
3 rows in set (0.04 sec)

mysql> select code from country order by code limit 3;
+------+
| code |
+------+
| AA1  |
| AA2  |
| AA3  |
+------+
3 rows in set (0.03 sec)