Конфигурация и инсталация на Mysql репликация

Инсталация и конфигурация на MySQL Master <-> Master репликация.

Master – Master репликация с SSL сертификат, между два MySQL сървъра.

В тази статия ще покажем, как се прави базова конфигурация на репликация( Master – Master ) между два сървъра  бази данни.

И на двата сървъра инсталираме и конфигурираме MySQL бази данни.

[root@master1]# wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
[root@master1]# rpm -ivh mysql57-community-release-el7-7.noarch.rpm
[root@master1]# yum install mysql-community-server
[root@master1]# systemctl enable mysqld
[root@master1]# mysqld --version
mysqld Ver 5.7.25 for Linux on x86_64 (MySQL Community Server (GPL))

Нека наименуваме сървърите по следният начин:
master1 ( сървър 1, айпи адрес: 2.2.2.2)
master2 ( сървър 2, айпи адрес: 1.1.1.1)

Сега трябва да генерираме SSL ключовете, за да може връзката между двата сървъра да бъде криптирана.


[root@master1]# mkdir -p /etc/scripts/ssl
[root@master1]#openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
........................................................+++
..............................................................................................+++
e is 65537 (0x10001)


[root@master1]#
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:server
Email Address []:


[root@master1]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
...........................+++
.............+++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:server
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:


[root@master1]#openssl rsa -in server-key.pem -out server-key.pem


[root@master1]#openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=server
Getting CA Private Key


[root@master1]#openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
.....................+++
.....+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:client
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:


[root@master1] openssl rsa -in client-key.pem -out client-key.pem
writing RSA key


[root@master1] openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=client
Getting CA Private Key


[root@master1]openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: C = XX, L = Default City, O = Default Company Ltd, CN = server
error 18 at 0 depth lookup:self signed certificate
OK
client-cert.pem: OK


[root@master1]chown -R mysql:mysql /etc/scripts/ssl/

Трябва да копираме генерираните ключове и на вторият сървър.

Нека конфигурираме първият сървър.


vim /etc/my.cnf


validate_password_policy=LOW
ssl-ca=/etc/scripts/ssl/ca.pem
ssl-cert=/etc/scripts/ssl/client-cert.pem
ssl-key=/etc/scripts/ssl/client-key.pem

server-id=3
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 200M
binlog_format = MIXED

skip-host-cache
skip-name-resolve

auto_increment_increment = 2
auto_increment_offset = 1

Проверяваме конфигурацията за SSL.


mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/scripts/ssl/ca.pem |
| ssl_capath | |
| ssl_cert | /etc/scripts/ssl/client-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/scripts/ssl/client-key.pem |
+---------------+----------------------------------+
9 rows in set (0.00 sec)

Както виждаме, MySQL разпознава сертификатите които сме конфигурирали.

Конфигурацията на вторият сървър.

vim /etc/my.cnf


validate_password_policy=LOW

ssl-ca=/etc/scripts/ssl/ca.pem
ssl-cert=/etc/scripts/ssl/server-cert.pem
ssl-key=/etc/scripts/ssl/server-key.pem

server-id=4
log_bin = /var/lib/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 200M
binlog_format = MIXED
skip-host-cache
skip-name-resolve
auto_increment_increment = 2
auto_increment_offset = 2


mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/scripts/ssl/ca.pem |
| ssl_capath | |
| ssl_cert | /etc/scripts/ssl/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/scripts/ssl/server-key.pem |
+---------------+----------------------------------+
9 rows in set (0.01 sec)

 

На сървър 1 създаваме потребител за MySQL репликацията.


mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'1.1.1.1' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.02 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)


mysql> ALTER USER [email protected] REQUIRE SSL;
Query OK, 0 rows affected (0.02 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Сега създаваме потребител за MySQL репликацията на сървър 2.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'2.2.2.2' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.02 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)


mysql> ALTER USER 'slave_user'@'1.1.1.1' REQUIRE SSL;
Query OK, 0 rows affected (0.02 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Виждаме позицията на сървър 1.


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 958 | | | |
+------------------+----------+--------------+------------------+-------------------+

Виждаме позицията на сървър 2.


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 958 | | | |
+------------------+----------+--------------+------------------+-------------------+

На сървър 2 изпълняваме:


mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=958, MASTER_SSL=1, MASTER_SSL_CA='/etc/scripts/ssl/ca.pem',MASTER_SSL_CERT='/etc/scripts/ssl/server-cert.pem', MASTER_SSL_KEY ='/etc/scripts/ssl/server-key.pem';
Query OK, 0 rows affected, 2 warnings (0.22 sec)


mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

На сървър 1 изпълняваме:


mysql> CHANGE MASTER TO MASTER_HOST='2.2.2.2', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=958, MASTER_SSL=1, MASTER_SSL_CA='/etc/scripts/ssl/ca.pem',MASTER_SSL_CERT='/etc/scripts/ssl/client-cert.pem', MASTER_SSL_KEY ='/etc/scripts/ssl/client-key.pem';
Query OK, 0 rows affected, 2 warnings (0.21 sec)


mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Проверяваме дали работи репликацията, на master 1.

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.1.1.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 958
Relay_Log_File: linux-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 958
Relay_Log_Space: 532
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/scripts/ssl/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/scripts/ssl/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/scripts/ssl/client-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: 4
Master_UUID: a7172a1e-463d-11e9-9170-fe4a74c3ee66
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 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:
1 row in set (0.00 sec)

ERROR:
No query specified

Сега, проверяваме и на master 2.

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 2.2.2.2
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 958
Relay_Log_File: linux-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 958
Relay_Log_Space: 532
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/scripts/ssl/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/scripts/ssl/server-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/scripts/ssl/server-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: 3
Master_UUID: 2f946c6a-463b-11e9-bf2f-fe9a97ad1d12
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 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:
1 row in set (0.00 sec)

ERROR:
No query specified

 

При нужда от консултация и поддръжка на сървъри от бази данни, винаги може да се свържете с нас.

Свързани статии:

Инсталация на DynamoDB сървър в Линукс.

Обновяване на MariaDB сървър бази данни.

 

 

Заявете безплатна оферта

Ние предлагаме професионални услуги в сферата на ..

Свържи се с нас

Още от нашия блог

Всички постове

Деинсталация на Java в Mac OS X 10.14.6

Този метод на деинсталация на Java работи за всички версии към момента.…
Continue reading

Cisco SPA504G Firmware Upgrade

Ъпгрейд на firmware на ip телефон Cisco SPA504G В тази статия ще…
Continue reading

За какво използваме лоуд балансери ?

Какво e  „Load Balancer“? Какво означава Load Balancer и как работи: Когато…
Continue reading

Adobe в Mac OS X

В тази статия ще ви покажем как сигурно да ресетнете / занулите…
Continue reading

Сигурно изтриване на информация от хард диск

Как да изтрия сигурно информация от  хард  диск ? Много наши клиенти…
Continue reading

5 полезни програми за почистване на вируси

Как да почистим вирусите от нашият Windows 10 ? компютърът ви работи…
Continue reading

5  полезни софтуера които използваме за диагностика и тестване на хард диск или SSD

Програми за тестване и диагностика на твърди дискове Много клиенти се оплакват…
Continue reading

FreeBSD 12 automated installation via PXE

Как да инсталираме FreeBSD 12 сървър чрез автоматизация по мрежата ?
Continue reading
Предишен
Всички постове
Следващ