MySQL DB master-slave copy of SSL

Requirements Framework

Preparatory work

Master-Slave server time synchronization

# The master-slave server simultaneously configures the CRONTAB task to synchronize time with the NTP server */ 5172.16. 0.1 &>/dev/null

Deployment Configuration

Main Library Configuration

vi /etc/my.cnf    Server-ID1  # In the replication schema, you need to keep the globally unique    log-bin = mysql- Bin # Default in data directory     1 # set mariadb the binary log is synchronized to disk every time before committing the transaction, ensuring that the server crashes without losing the event =====service mysqld Start # startup Mariadb10=====  -hlocalhost-uroot-P # login mysqlmariadb [mysql]'repluser'@ ' 172.16.%.% ' ' Replpass ' ; # Create a copy account with minimal permissions mariadb [MySQL]> flush privileges; MariaDB [MySQL]> show master Status; # View status information for the main library

Configure from Library

  vi /etc/ my.cnf server - id  =  one   # in the replication schema, you need to keep the globally unique log-bin = mysql- bin # can also be set to none, which is to close the binary log from the library relay -log=/data/relaylogs/relay- Bin # Setting the trunk log file log
     -slave-updates =  1   # allows events that are replayed from the library to be recorded in its own binary log read_only  =  1   # from library to read-only  =====  Service mysqld start # mariadb10  =====  mysql -hlocalhost-uroot- p # Login mysqlmariadb [MySQL]  > Change master to master_host=  '   172.16.251.123   ' , master_user=  '   repluser   ' , master_password=  '   Replpass   ' , master_log_file=  ' mysql  -bin.000005   ' , master _log_pos=  379  ; # Connect to the main library mariadb [MySQL] ;  start slave; MariaDB [MySQL]  > show slave status\g view from library status 

Verify

# Create a new database on the main library and make data mariadb [(none)]> CREATE Database test_for_replication; MariaDB [(None)]> CREATE TABLE test_for_replication.user (IDintnullchar (nullintnullintnull); MariaDB [(None)]> INSERT INTO Test_for_replication.user (NAME,YEAR,CLASSID) VALUES ('Jason Kk  ',2, ' ('Hello Kitty', 1 ); # to see if the data can be synchronized correctly from the library mariadb [(none)]Select * from test_for_replication.user; # See 1MariaDB [None )]> show slave status\g # see 2

Figure 1

Figure 2

Can see the master-slave synchronization is normal, the data is correct!

Secure replication based on SSL

Generate an SSL certificate and private key

Master and slave servers are required to obtain their own certificates, detailed steps are described in the "graphical OpenSSL implementation of private CAS"

The main library and the required certificate files from the library are:

Main Library Configuration

SSL Support status Check :

• If the value of Have_ssl is yes, the SSL function is turned on and used;

• If the value of Have_ssl is no, then the SSL function is not compiled into the current mariadb, the need to recompile the program;

• If the value of HAVE_SSL is disabled, it means that the SSL feature is loaded MARIADB compile-time, but is not enabled, and the mariadb, which is generally installed in the generic binaries, is this value;

To view the library file:

LDd 'whichgrep  SSL # also needs to see if there is a libssl.so file, and if it does not exist, the missing library file # The trouble is to install the missing library file, you need to install LIBOPENSSL. 1.0 . 0 package, but the installation of this package depends on the glibc2.14 version, and the CentOS6.5 system is the original version of the glibc2.12, which involves glibc upgrade, too troublesome, but also very dangerous, so give up the # Then you can only recompile installation MARIADB10, download the latest version of the source package installation from the official website (www.mariadb.com)

After the MARIADB compilation installation is complete, you can configure SSL-based secure replication

chown -R mysql.mysql/etc/master/ssl/ # Note Modify the permissions of the certification-related files =====vi /etc/MY.CNF # Add the following under the MYSQLD segment [Mysqld]ssl_ca=/etc/master/ssl/=/etc/master/ssl/=/etc/master/ssl/  = dhe-rsa-aes256-SHA # Specifies the supported cryptographic algorithm =====service mysqld Reload # overload configuration =====# See the related variable again: MariaDB [( None)]'%ssl%'  # see # Create a Copy account: MariaDB [MySQL]'  Repluser' @'172.16.%.%'replpass' Reuire SSL; # Specifically, you must use SSL to replicate

Configure from Library

chown -R mysql.mysql/etc/slave/ssl/=====MariaDB [MySQL]> Change master to master_host=' 172.16.251.123 ', master_user='repluser', master_password='replpass ', master_log_file='mysql-bin.000010', master_log_pos=776 , master_ssl=1, master_ssl_ca='/etc/slave/ssl/cacert.pem', Master_ssl _cert='/etc/slave/ssl/slave.crt', master_ssl_key='/etc/slave/ Ssl/slave.key'; # directly specify the local certificate file from the library when connecting to the main library mariadb [MySQL]> start slave; MariaDB [MySQL]> show slave status\g # view from library status

Another way to configure from a library

vi /root/=/etc/slave/ssl/=/etc/slave/ssl/=/etc/slave/ssl/= dhe-rsa-aes256-SHA=====MariaDB [MySQL]> Change master to master_host='  172.16.251.123', master_user='repluser', master_password=  'replpass', master_log_file='mysql-bin.000010', master_log_pos=776, master_ssl=1; # just specify master_ssl=1 to

Verify

Char (nullintnull for test;# validation results See