When MySQL is replicating across the internet, others can steal information about MySQL's replication, which is plaintext, so there is no security, where the replicated information is encrypted via SSL. When the client does not have a fixed IP to access the server, MySQL to allow access to arbitrary addresses, server and client through certificate validation can prevent violent cracking.
Before we begin, let's review the installation process for SSL protocol client OpenSSL:
Install OpenSSL
Mkdir/test/setup
cd/test/setup
tar zxvf openssl-0.9.8b.tar.gz
cd openssl-0.9.8b
./config
Make && make Install
Open the SSL function in MySQL
Login to MySQL View
Mysql> Show variables like '%ssl% ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| Have_ssl | DISABLED |
| Ssl_ca | | | | ssl_capath | | | | | |
Ssl_key | |
+---------------+----------+
If the MySQL output is as described above, then continue the operation to turn on SSL; if not, recompile install MySQL, note that when generating makefile the correct parameters are filled in.
Exit MySQL, edit/etc/my.cnf
Between [mysqld] and [mysqldump], add the following configuration information:
Restart MySQL after saving and login to MySQL again
Mysql-uroot-p
mysql> Show variables like '%ssl% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| Have_ssl | YES | | ssl_ca | | | ssl_capath | | |
ssl_ cipher | | | |
ssl_key
| | +---------------+-------+
Okay, let's get down to business:
MySQL based on SSL replication
1. Create Certificate Center
Create a certificate center on the primary server
Generate private key
(Umask 077;openssl GENRSA-OUT/ETC/PKI/CA/PRIVATE/CAKEY.PEM 2048)
Generated from the visa book, because of the need to enter a large number of user information, so edit the certificate's profile, on a private CA to create a certificate to note that all user information to the CA and the same, from the country to the department will be the same, otherwise the certificate can not be used
Vim/etc/pki/tls/openssh.cnf
[Req_distinguished_name]
CountryName = Country Name (2 letter code)
Countryname_default = CN
countryname_min = 2
Countryname_max = 2
stateorprovincename = State or province name (full name)
Stateorpovincename_default = FJ
Localityname = locality Name (eg,city)
localityname = FZ o.organizationname
= Organization Name (eg,company)
o.organizationname_default = Zdz
organizationalunitname = Organizational unit Name (eg,section)
Organizationalunitname_default = Zdz
Generate self-signed certificate
OpenSSL req-new-x509-key/etc/pki/ca/private/cakey.pem-out/etc/pki/ca/cacert.pem-days 3650
-x509 is a parameter that is required to create a visa book and cannot be added when creating another certificate
Since it's a visa, you have to modify the certificate path
Vim/etc/pki/tls/openssl.cnf
[Ca_defalut]
dir =/etc/pki/ca
certs = $dir/certs #存放生成证书的目录
crl_dir = $dir/crl #存放吊销证书的目录
database = $dir/ Index.txt #证书的索引文件
new_certs_dir = $dir _newcerts #新签的证书目录
serial = $dir/serial #序列号
CRL = $dir/crl.pem
private_key = $dir/private/cakey.pem #证书中心私钥文件
Create a certificate number
mkdir certs CRL Newcerts Touch
index.txt
echo > serial
2. Create a certificate for the main server
the name of the server must be fixed and the server name should be entered when the certificate is requested, and the certificate and server name correspond
Create private key
Mkdir/usr/local/mysql/ssl
cd/usr/local/mysql/ssl
(umask 077;openssl genrsa-out/usr/local/mysql/ssl/ Master.key 2048)
Generate Certificate Request
OpenSSL Req-new-key master.key-out MASTER.CSR
Signing Master's certificate on the Certificate Server
OpenSSL ca-in master.csr-out master.crt-days 365
3. Create a certificate from a server
(Umask 077;openssl genrsa-out/usr/local/mysql/ssl/slave.key 2048)
OpenSSL Req-new-key slave.key-out SLAVE.CSR
The certificate request file from the server is copied to the Certificate Server for signing
Opessl ca-in slave.csr-out slave.crt-days 356
4, modify the certificate permissions and MySQL configuration file
Copy the certificate's public key CACERT.PEM to the master-slave server directory
Cd/usr/local/mysql/ssl
Cp/etc/pki/ca/cacert.pem./
chown-r mysql:mysql master.crt master.key Cacert.pem
chmod master.crt master.key cacert.pem
vim/usr/local/mysql/my.cnf
SSL Ssl_ca =/usr/local/ Mysql/ssl/cacrt.pem
Ssl_cert =/usr/local/mysql/ssl/master.crt
ssl_key =/usr/local/mysql/ssl/ Master.key
Modify from server configuration
Cd/usr/local/mysql/ssl
Cp/etc/pki/ca/cacert.pem./
chown-r mysql:mysql slave.crt slave.key Cacert.pem
chmod slave.crt slave.key cacert.pem
vim/usr/local/mysql/my.cnf
SSL Ssl_ca =/usr/ Local/mysql/ssl/cacrt.pem
Ssl_cert =/usr/local/mysql/ssl/slave.crt
ssl_key =/usr/local/ Mysql/ssl/slave.key
5. Create a replication user on the primary server
Grant replication Slave on *.* to slave@ ' 192.168.216.133 ' identified by ' slave ' requere SSL;
Flush privileges;
View the current binary location of the primary server
Mysql> Show master status;
+-------------------------+------------+---------------------+--------------------------+---------------------- ----+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-------------------------+------------+---------------------+--------------------------+---------------------- ----+
| mysql-bin.000007 | 1015 | | | |
+-------------------------+------------+---------------------+--------------------------+---------------------- -----+
1 row in Set (0.00 sec)
6, start copying from the server
Change Master to
master_host= ' 192.168.216.132 ',
master_user= ' slave ',
master_password= ' slave
', Master_log_file= ' mysql-bin.000007 ',
master_log_pos=1015,
master_ssl=1,
master_ssl_ca= '/usr/local/ Mysql/ssl/cacrt.pem ',
master_ssl_cert= '/usr/local/mysql/ssl/slave.crt ',
master_ssl_key= '/usr/local/ Mysql/ssl/slave.key ';
Start slave;
View status
Error 1:
If you want to make sure that there are no problems with the certificate, you can connect to SSL by establishing a test user to open a user with a large permission on the primary server for SSL logon tests
Grant all privileges on *.* to root@ ' 192.168.216.133′identified by ' root ' require SSL;
[Root@slave ssl]# Mysql-uroot-proot-h192.168.216.133–ssl-ca=cacrt.pem–ssl-cert=slave.crt–ssl-key=slave.key
Warning:using a password on the command line interface can is insecure.
ERROR 2026 (HY000): SSL connection Error:ASN:before date in the future
This is because the virtual time is incorrect and causes
If you do not use SSL to connect then you will report an error
[Root@slave ssl]# mysql-uroot-proot-h192.168.216.133;
Warning:using a password on the command line interface can is insecure.
ERROR 1045 (28000): Access denied for user ' root ' @ ' 192.168.216.132′ (using Password:yes)
Error 2:
Perform show variables like '%ssl% ' when you add a certificate configuration to a configuration file
This is because the owner of the certificate is not changed to MySQL, you can tell from the log that there is no permission to obtain the private key