Shulou(Shulou.com)06/01 Report--

Environment:

192.168.205.37: as master server

192.168.205.47: as middle server

192.168.205.57: as slave server

Version:

OS: centos 7 1810 with mini install

Mariadb-5.5.60

Purpose:

Sometimes our database replication may have to be replicated across the network. If we do not want others to sniff during replication, we can use ssl protocol to achieve encrypted data transmission during replication. In this experiment, three servers are used to achieve semi-synchronous replication, and encrypted replication is enabled between his replications.

Install three master and slave servers using the following script

Install three servers using the following script

[root@centos7 data] # cat / data/maridb_yum.sh #! / bin/bash# use last digit of IP as server-idID= `ip a show dev eth0 | sed-r '3roomdash s@ (. * inet) (. *) (/. *) @\ 2conversation' | cut-d. -f4` # install mariadb-server and create data and logs directoryrpm-Q mariadb-server | | yum install-y mariadb-server [- d / data/mysql] | | mkdir-p / data/mysql [- d / data/logs] | | mkdir-p / data/logschown mysql:mysql / data/ {mysql Logs} # modify the my.cnf # set the location of the data file sed-I's binary log'/ etc/my.cnf # to open the binary log and the starting name of the file: sed-I's binary log binder MyQL login bindings / etc/my.cnf # set the innodb table detach file grep "log" / etc/my.cnf | / sed | -I'/\ [mysqld\] / an innodb_file_per_table = on' / etc/my.cnf# skips name resolution grep "skip_name_resolve" / etc/my.cnf | | sed-I'/\ [mysqld\] / a skip_name_resolve = on' / etc/my.cnf# sets server-id to the last digit of the IP of eth0 You can change grep "server-id" / etc/my.cnf according to your own needs | | sed-I "/\ [mysqld\] / a server-id=$ID" / etc/my.cnf# startup service service mariadb restart master server configuration view the file name of the semi-synchronous plug-in [root@slave1 ~] # rpm-ql mariadb-server... / usr/lib64/mysql/plugin/semisync_master.so/usr/lib64/mysql/plugin/semisync_slave.so... Establish a replication account on the primary server MariaDB [(none)] > grant replication slave on *. * to repluser@'192.168.205.%' identified by 'centos'; determine the location of replication on the primary server MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | bin.000001 | 30373 | | bin.000002 | 1038814 | bin.000003 | 30373 | +-+-+ 3 rows in set (0.00 sec) main service Install the semi-synchronous plug-in MariaDB [(none)] > install plugin rpl_semi_sync_master soname 'semisync_master.so' on the device View the semi-synchronous variable MariaDB [(none)] > show global variables like'% semi%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +-- +-+ 4 rows in set (0.00 sec) enable semi-synchronous replication MariaDB [(none)] > set global Rpl_semi_sync_master_enabled=on looks at the semi-synchronous variable MariaDB [(none)] > show global variables like'% semi%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +-- +-+ 4 rows in set (sec) to view the semi-synchronization status MariaDB [(none)] > show global status like'% semi%' +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 363 | | Rpl_semi_sync_master_net_wait_time | 25473 | | Rpl_semi_sync_master_net_waits | 70 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_ No_tx | 0 | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | Rpl_semi_sync_master_tx_avg_wait_time | 380 | | Rpl_semi_sync_master_tx_wait_time | 13305 | Rpl_semi_sync_master_tx_waits | 35 | | Rpl_semi_ Sync_master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | Rpl_semi_sync_master_yes_tx | 35 | +-+ 14 rows in set (0.00 sec) in two Run change master toMariaDB [(none)] > CHANGE MASTER TO-> MASTER_HOST='192.168.205.37' from the slave server's configuration -> MASTER_USER='repluser',-> MASTER_PASSWORD='centos',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='bin.000003',-> MASTER_LOG_POS=401,-> MASTER_CONNECT_RETRY=10 Query OK, 0 rows affected (0.02 sec) install plug-in on slave server, synchronization status is OFFMariaDB [(none)] > install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show variables like'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync _ slave_trace_level | 32 | +-- +-+ 2 rows in set (0.00 sec) enable semi-synchronization At this point, check the synchronization variable ONMariaDB [(none)] > set global rpl_semi_sync_slave_enabled=on. Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show variables like'% semi%' +-- +-+ | Variable_name | Value | +-- +-+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +-- +-+ 2 rows in set (0.00 sec)

The status of this entry is OFF. We need to start the slave thread.

MariaDB [(none)] > show global status like'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | OFF | +- -+-+ 1 row in set (0.00 sec) MariaDB [(none)] > stop slave Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)] > start salve;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'salve' at line 1MariaDB [(none)] > start slave;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show global status like'% semi%' +-- +-+ | Variable_name | Value | +-+-+ | Rpl_semi_sync_slave_status | ON | +- -+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin.000003 Read_Master_Log_Pos: 401Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes at this time we are in the Lord View the status of semi-synchronous MariaDB [(none)] > show global status like'% semi%' on the server +-+-+ | Variable_name | Value | +-+- -+ | Rpl_semi_sync_master_clients | 2 | # two clients are normal | Rpl_semi_sync_master_net_avg_wait_time | 363 | | Rpl_semi_sync_master_net_wait_time | 25473 | | Rpl_semi_sync_master_net_waits | 70 | | Rpl_semi_sync_master_no_times | | 0 | | Rpl_semi_sync_master_no_tx | 0 | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | Rpl_semi_sync_master_tx_avg_wait_time | 380 | | Rpl_semi_sync_master_tx_wait_time | 13305 | | Rpl_semi_sync_master_tx_ | Waits | 35 | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | Rpl_semi_sync_master_yes_tx | 35 | + -+ 14 rows in set (0.00 sec) test imports a library file to the master server Check whether to synchronize [root@master ~] # mysql on two slave servers

Show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) two slave servers Check the library MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) prepare CA and certificate for Simplify the generation of a self-signed root certificate on the primary server First generate a private key [root@master ~] # mkdir / etc/my.cnf.d/ssl [root@master ~] # cd / etc/my.cnf.d/ssl [root@master ssl] # openssl genrsa 2048 > cakey.pem uses the private key to generate a self-signed root certificate [root@master ssl] # openssl req-new-x509-key cakey.pem-out cacert.pem-days 3650

To simplify, we first generate a private key and use this private key to generate a certificate request file for master. Note that it is not a certificate, but a certificate request file.

[root@master ssl] # openssl req-newkey rsa:1024-days 365-nodes-keyout master.key > master.csrGenerating a 1024 bit RSA private key.+...+writing new private key to 'master.key'-You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name ora DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter'. The field will be left blank.-Country Name (2 letter code) [XX]: CNState or Province Name (full name) []: beijingLocality Name (eg, city) [Default City]: beijingOrganization Name (eg, company) [Default Company Ltd]: contoso Organizational Unit Name (eg, section) []: devopsCommon Name (eg) Your name or your server's hostname) []: master.contoso.comEmail Address []: Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []: An optional company name []: [root@centos7 ssl] # lscacert.pem cakey.pem master.csr master.key generates a certificate file based on the request file [root@master ssl] # openssl x509-req-in master.csr-CA cacert.pem-CAkey cakey.pem-set_serial 01 > master.crt Signature oksubject=/C=CN/ST=beijing / L=beijing/O=contoso/OU=devops/CN=master.contoso.comGetting CA Private Key [root@master ssl] # lltotal 20 RW Aug 11-1 root root 1334 Aug 11 21:55 cacert.pem-rw-r--r-- 1 root root 1675 Aug 11 21:52 cakey.pem-rw-r--r-- 1 root root 1034 Aug 11 23:01 master.crt-rw-r--r-- 1 root root 664 Aug 11 21:59 master.csr-rw-r--r-- 1 root root 916 Aug 11 21:59 master.key

Repeat 18 and 19 and generate two more slave node certificate files

[root@master ssl] # openssl req-newkey rsa:1024-days 365-nodes-keyout slave1.key > slave1.csr Generating a 1024 bit RSA private key.+.+writing new private key to 'slave1.key'-You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name ora DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter'. The field will be left blank.-Country Name (2 letter code) [XX]: CNState or Province Name (full name) []: beijingLocality Name (eg, city) [Default City]: beijingOrganization Name (eg, company) [Default Company Ltd]: contosoOrganizational Unit Name (eg, section) []: devopsCommon Name (eg) Your name or your server's hostname) []: slave1.contoso.comEmail Address []: Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []: An optional company name []: [root@master ssl] # openssl req-newkey rsa:1024-days 365-nodes-keyout slave2.key > slave2.csr Generating a 1024 bit RSA private key.+.+writing new private key to' slave2.key'-You are about to be asked to enter information that will be Incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name ora DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value If you enter'.', the field will be left blank.-Country Name (2 letter code) [XX]: CNState or Province Name (full name) []: beijingLocality Name (eg, city) [Default City]: beijingOrganization Name (eg, company) [Default Company Ltd]: contosoOrganizational Unit Name (eg, section) []: devopsCommon Name (eg) Your name or your server's hostname) []: slave2.contoso.comEmail Address []: Please enter the following 'extra' attributesto be sent with your certificate requestA challenge password []: An optional company name []: [root@master ssl] # openssl x509-req-in slave1.csr-CA cacert.pem-CAkey cakey.pem-set_serial 02 > slave1.crt Signature oksubject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=slave1.contoso.comGetting CA Private Key [root@master ssl] # openssl x509-req-in slave2.csr -CA cacert.pem- CAkey cakey.pem- set_serial 03 > slave2.crt Signature oksubject=/C=CN/ST=beijing/L=beijing/O=contoso/OU=devops/CN=slave2.contoso.comGetting CA Private Key finally produces the following file [root@master ssl] # lltotal 44 root root 1034 Aug 11 23:01 master-1 root root 1334 Aug 11 21:55 cacert.pem-rw-r--r-- 1 root root 1675 Aug 11 21:52 cakey.pem-rw-r--r-- 1 root root 1034 Aug 11. Crt-rw-r--r-- 1 root root 664 Aug 11 21:59 master.csr-rw-r--r-- 1 root root 916 Aug 11 21:59 master.key-rw-r--r-- 1 root root 1034 Aug 11 23:05 slave1.crt-rw-r--r-- 1 root root 664 Aug 11 23:04 slave1.csr-rw-r--r-- 1 root root 916 Aug 11 23:04 slave1.key-rw-r--r-- 1 root root 1034 Aug 11 23:06 slave2.crt-rw-r--r-- 1 root root 664 Aug 11 23:05 slave2.csr-rw-r--r-- 1 root root 916 Aug 11 23:05 slave2.key copy the file to the slave node Normally, we only need the root certificate and our own private key and certificate to [root@master ssl] # scp-r / etc/my.cnf.d/ssl/ 192.168.205.47:/etc/my.cnf.d/ [root@master ssl] # scp-r / etc/my.cnf.d/ssl/ 192.168.205.57:/etc/my.cnf.d/ to configure the certificate to view the encryption-related variables in the master node. Empty MariaDB [(none)] > show variables like'% ssl%' +-+-+ | Variable_name | Value | +-+-+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | ssl_capath | | ssl_cert | | ssl_cipher | ssl _ key | | +-+-+ 7 rows in set (0.00 sec) modify the configuration file [root@master ssl] # vi / etc/ my.cnf [mysqld] ssl-ca=/etc/my.cnf.d/ssl/cacert.pemssl-cert=/etc/my.cnf.d/ssl/master.crtssl-key=/etc/my.cnf.d/ssl/master.key [root@master ssl] # systemctl restart mariadb

Check the value of the variable at this time, but because you do not use encryption when you connect, the ssl of the status is not in use

MariaDB [(none)] > show variables like'% ssl%' +-+-+ | Variable_name | Value | +-+-+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | / etc/my.cnf.d/ssl/cacert.pem | | ssl_capath | | ssl_cert | / etc/my.cnf.d/ssl/master.crt | | ssl_cipher | | ssl_key | / etc/my.cnf.d/ssl/master.key | +-+-+ 7 rows in set (0.00 sec) MariaDB [(none)] > status-mysql Ver 15.1 Distrib 5.5.60-MariaDB For Linux (x86 / 64) using readline 5.1Connection id: 6Current database:Current user: root@localhostSSL: Not in use …

Using client encryption to connect, you can see that the status is encrypted

[root@master ssl] # mysql-- ssl-ca=cacert.pem-- ssl-cert=master.crt-- ssl-key=master.key MariaDB [(none)] > status-mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86 / 64) using readline 5.1Connection id: 5Current database:Current user: root@localhostSSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 …

Let's test the connection of the master node with ssl from the node

[root@slave1 ssl] # mysql-- ssl-ca=cacert.pem-- ssl-cert=slave1.crt-- ssl-key=slave1.key-h292.168.205.37-urepluser-pcentosMariaDB [(none)] > status-mysql Ver 15.1 Distrib 5.5.60-MariaDB For Linux (x86: 64) using readline 5.1Connection id: 8Current database:Current user: repluser@192.168.205.47SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384, but we can also connect without encryption So we can set up a connection database MariaDB [(none)] > grant replication slave on *. * to repluser2@'192.168.205.%' identified by 'centos' require ssl that enforces the use of encrypted parties. Query OK, 0 rows affected (0.00 sec)

Try to log in from another server with the established account

[root@slave1 ssl] # mysql-h292.168.205.37-urepluser2-pcentos ERROR 1045 (28000): Access denied for user 'repluser2'@'192.168.205.47' (using password: YES) [root@slave1 ssl] # mysql-ssl-ca=cacert.pem-ssl-cert=slave1.crt-ssl-key=slave1.key-h292.168.205.37-urepluser2-pcentosWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 14Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > status-mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86 / 64) using readline 5.1Connection id: 14Current database:Current user: repluser2@192.168.205.47SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 configure the certificate in the slave node

So if we use repluser2 to establish replication with the master server, we need to modify the configuration file

[root@slave1 ssl] # vi / etc/ my.cnf[mysqld] ssl-ca=/etc/my.cnf.d/ssl/cacert.pemssl-cert=/etc/my.cnf.d/ssl/slave1.crtssl-key=/etc/my.cnf.d/ssl/slave1.key [root@slave1 ssl] # systemctl restart mariadb [root@slave2 ssl] # vi / etc/ my.cnf[mysqld] ssl-ca=/etc/my.cnf.d/ssl/cacert.pemssl-cert=/etc/my.cnf. D/ssl/slave2.crtssl-key=/etc/my.cnf.d/ssl/salve2.key [root@slave1 ssl] # systemctl restart mariadb stops the currently used repluser replication on the slave node Re-use repluser2 for replication (determine the location of the primary server before replication) MariaDB [(none)] > stop slave Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > reset slave all;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > CHANGE MASTER TO-> MASTER_HOST='192.168.205.37',-> MASTER_USER='repluser2',-> MASTER_PASSWORD='centos',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='bin.000004',-> MASTER_LOG_POS=496,-> MASTER_SSL=1 Query OK, 0 rows affected (0.01 sec)

Start slave to check the status. Once the connection and replication are normal.

MariaDB [(none)] > start slave;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser2 Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin.000004 Read_Master_Log_Pos: 415 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 693Relay_Master_Log_File: bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes... Master_SSL_Allowed: Yes... test

Delete previously unused copy accounts, create tables or delete library tests

MariaDB [(none)] > drop user repluser@'192.168.205.%';Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > select user,host,password from mysql.user +-+ | user | host | password | +- -+-+-- + | root | localhost | root | centos7.localdomain | | | root | 127.0.0.1 | root |:: 1 | localhost | | centos7.localdomain | | repluser2 | 192.168.205.% | * 128977E278358FF80A246B5046F51043A2B1FCED | +-- | -+ 7 rows in set (0.00 sec) MariaDB [(none)] > create database db1-> Query OK, 1 row affected (0.01sec) MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | +-+ 6 rows in set (0.00 sec)

Test whether the library is established on the slave node, and an error is found. The reason is that the server copied it after the account repluser was established, so there was an error when we deleted it because there was no one on the server. The solution is to skip this error, test again, and find that the db1 copy is successful and do the same test in slave2.

MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000004 Read_Master_Log_Pos: 749 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 602 Relay_Master_Log_File: bin.000004 Slave_IO_Running: Yes Slave _ SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1396 Last_Error: Error 'Operation DROP USER failed for' repluser'@'192.168.205.%'' on query. Default database:'. Query: 'drop user repluser@'192.168.205.%''... # Note that this jump includes correct and wrong counts, and incorrect replication may occur if the correct one is skipped. MariaDB [(none)] > set global sql_slave_skip_counter = 1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > stop slave;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > start slave Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000004 Read_Master_Log_Pos: 749 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000004 Slave_IO_ Running: Yes Slave_SQL_Running: Yes... .MariaDB [(none)] > show databases;+-+ | Database | +-+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | +-+ 6 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.