cesardba

Criando uma replica do MySQL no Azure

Hoje o post será sobre como criar uma replica do MySQL no Azure, seguem os passos que usei para criar a replica de leitura e alta disponibilidade.

Server Master  = datasqlbrasil
Server Replica = datasqlargentina

Criei um database chamado teste com uma tabela chamada tabela1 no MySQL primário:

mysql -u cesar -h datasqlbrasil.mysql.database.azure.com -p'password'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.37-azure Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 

MySQL [(none)]> SELECT version();
+--------------+
| version()    |
+--------------+
| 8.0.37-azure |
+--------------+
1 row in set (0,150 sec)

MySQL [(none)]> CREATE DATABASE teste;
Query OK, 1 row affected (0,162 sec)

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| teste              |
+--------------------+
5 rows in set (0,150 sec)

MySQL [(none)]> use teste;
Database changed
MySQL [teste]>
MySQL [teste]> create table teste.tabela1 (code VARCHAR(255), active VARCHAR(255), total VARCHAR(255), data DATETIME);

Realizei uma carga de 100 registros para popular o database teste:

for i in {1..100}; do mysql -u cesar -h datasqlbrasil.mysql.database.azure.com -p'password' -e "INSERT INTO teste.tabela1 (code, active, total,data) VALUES ($i, 1, 1,Now());"; done

Verificiando a carga de dados:

MySQL [teste]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0,151 sec)

Criando a replica do MySQL com nome datasqlargentina:

Alem das informações de criação da replica, ao lado direito podemos verificar o custo desta replica:

Replica datasqlargentina criada e disponivel para uso:

Verificando a role de cada servidor:

Conectando na replica datasqlargentina e fazendo um teste de escrita, como podemos ver a replica é apenas para leitura:

for i in {1..100}; do mysql -u cesar -h datasqlargentina.mysql.database.azure.com -p'password' -e "INSERT INTO teste.tabela1 (code, active, total,data) VALUES ($i, 1, 1,Now());"; done
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement

Realizando mais uma carga de 100 registros na master:

for i in {1..100}; do mysql -u cesar -h datasqlbrasil.mysql.database.azure.com -p'password' -e "INSERT INTO teste.tabela1 (code, active, total,data) VALUES ($i, 1, 1,Now());"; done

Verificando os registros inseridos na master:

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      114 |
+----------+
1 row in set (0,145 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      115 |
+----------+
1 row in set (0,171 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      119 |
+----------+
1 row in set (0,144 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      130 |
+----------+
1 row in set (0,145 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      146 |
+----------+
1 row in set (0,145 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      156 |
+----------+
1 row in set (0,146 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0,146 sec)

Verificando os registros inseridos na replica:

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0,146 sec)

Promovendo a replica datasqlargentina como primária:

Realizando a carga de dados na replica promovida datasqlargentina:

for i in {1..100}; do mysql -u cesar -h datasqlargentina.mysql.database.azure.com -p'password' -e "INSERT INTO teste.tabela1 (code, active, total,data) VALUES ($i, 1, 1,Now());"; done

mysql -u cesar -h datasqlargentina.mysql.database.azure.com -p'password'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 8.0.37-azure Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      218 |
+----------+
1 row in set (0,149 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      220 |
+----------+
1 row in set (0,159 sec)

MySQL [(none)]> SELECT COUNT(*) FROM teste.tabela1;
+----------+
| COUNT(*) |
+----------+
|      300 |
+----------+
1 row in set (0,149 sec)

MySQL [(none)]>

Espero que esse post possa ajudar as pessoas que estão iniciando no MySQL com Azure.

search previous next tag category expand menu location phone mail time cart zoom edit close