mysql 加密证件号中间8位 mysql证书密钥分享

转载

mob64ca1404476b 2023-10-20 17:33:56

文章标签 mysql 加密证件号中间8位 mysql ssl mysql 服务器 vim 文章分类 MySQL 数据库

当mysql跨越互联网进行复制时别人可以窃取到mysql的复制信息，这些信息是明文的，因此存在不安全性，这里通过ssl对复制的信息进行加密

1、创建证书中心

在主服务器上创建证书中心

cd /etc/pki/CA

生成私钥

(umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)

cd/etc/pki/CA

生成私钥

(umask077;opensslgenrsa-out/etc/pki/CA/private/cakey.pem2048)

生成自签证书,由于需要输入大量用户信息，因此编辑证书的配置文件，在私有的CA上创建证书要注意所有的用户信息要和CA中的一致，从国家到部门都要相同，否则会造成证书无法使用

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

vim/etc/pki/tls/openssh.cnf
[req_distinguished_name]
countryName    =CountryName(2lettercode)
countryName_default=CN
countryName_min  =2
countryName_max  =2
stateOrProvinceName=StateorProvinceName(fullname)
stateOrpovinceName_default =FJ
localityName    =LocalityName(eg,city)
localityName    =FZ
O.organizationName =OrganizationName(eg,company)
O.organizationName_default =zdz
organizationalUnitName   =OrganizationalUnitName(eg,section)
organizationalUnitName_default =zdz

生成自签证书

openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650

opensslreq-new-x509-key/etc/pki/CA/private/cakey.pem-out/etc/pki/CA/cacert.pem-days3650

-x509是创建自签证书是需要的参数，在创建其他证书时不能加该参数

由于是自签证书因此要修改证书路径

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    #证书中心私钥文件

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  #证书中心私钥文件

创建证书编号

mkdir certs crl newcerts
touch index.txt
echo 00 > serial

mkdircertscrlnewcerts
touchindex.txt
echo00>serial

2、为主服务器创建证书

服务器的名称必须固定，在申请证书时要输入服务器名称，证书和服务器名称对应

创建私钥

mkdir /usr/local/mysql/ssl
cd /usr/local/mysql/ssl
(umask 077;openssl genrsa -out /usr/local/mysql/ssl/master.key 2048)

mkdir/usr/local/mysql/ssl
cd/usr/local/mysql/ssl
(umask077;opensslgenrsa-out/usr/local/mysql/ssl/master.key2048)

生成证书申请

openssl req -new -key master.key -out master.csr

opensslreq-new-keymaster.key-outmaster.csr

在证书服务器上对master的证书进行签发

openssl ca -in master.csr -out master.crt -days 365

opensslca-inmaster.csr-outmaster.crt-days365

3、创建从服务器证书

(umask 077;openssl genrsa -out /usr/local/mysql/ssl/slave.key 2048)

openssl req -new -key slave.key -out slave.csr

(umask077;opensslgenrsa-out/usr/local/mysql/ssl/slave.key2048)

opensslreq-new-keyslave.key-outslave.csr

将从服务器的证书申请文件复制到证书服务器上进行签发

opessl ca -in slave.csr -out slave.crt -days 356

opesslca-inslave.csr-outslave.crt-days356

4、修改证书权限和mysql配置文件

将证书的公钥cacert.pem复制到主从服务器的目录下

cd /usr/local/mysql/ssl
cp /etc/pki/CA/cacert.pem ./
chown -R mysql:mysql master.crt master.key cacert.pem
chmod 600 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

cd/usr/local/mysql/ssl
cp/etc/pki/CA/cacert.pem./
chown-Rmysql:mysqlmaster.crtmaster.keycacert.pem
chmod600master.crtmaster.keycacert.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
修改从服务器配置
cd /usr/local/mysql/ssl
cp /etc/pki/CA/cacert.pem ./
chown -R mysql:mysql slave.crt slave.key cacert.pem
chmod 600 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

cp/etc/pki/CA/cacert.pem./
chown-Rmysql:mysqlslave.crtslave.keycacert.pem
chmod600slave.crtslave.keycacert.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、在主服务器上创建复制用户

grant replication slave on *.* to slave@'192.168.216.133' identified by 'slave' requere ssl;

flush privileges;

grantreplicationslaveon *.*toslave@'192.168.216.133'identifiedby'slave'requeressl;

flushprivileges;

查看主服务器当前二进制位置

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)

mysql>showmasterstatus;
+————————-+————+———————+————————–+————————–+
|File              |Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+————————-+————+———————+————————–+————————–+
|mysql-bin.000007|  1015 |               |                  |                  |
+————————-+————+———————+————————–+—————————+
1rowinset(0.00sec)

6、在从服务器上开始复制

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;

changemasterto
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';
startslave;

查看状态

mysql 加密证件号中间8位 mysql证书密钥分享_服务器

错误1：

如果要确保证书没有问题可以通过建立测试的用户同ssl进行连接在主服务器上开一个权限很大的用户，进行ssl的登录测试

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 be insecure.
ERROR 2026 (HY000): SSL connection error: ASN: before date in the future

grantallprivilegeson *.*toroot@'192.168.216.133'identifiedby'root'requiressl;
[root@slavessl]# mysql -uroot -proot -h192.168.216.133 –ssl-ca=cacrt.pem –ssl-cert=slave.crt  –ssl-key=slave.key
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
ERROR2026(HY000):SSLconnectionerror:ASN:beforedateinthefuture

这是由于虚拟的时间不正确导致

如果这时候不使用ssl方式进行连接则会报出错误

oot@slave ssl]# mysql -uroot -proot -h192.168.216.133;
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.216.132' (using password: YES)

[root@slavessl]# mysql -uroot -proot -h192.168.216.133;
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
ERROR1045(28000):Accessdeniedforuser'root'@'192.168.216.132'(usingpassword:YES)

错误2：

在配置文件中添加证书配置后执行 show variables like '%ssl%'显示

mysql 加密证件号中间8位 mysql证书密钥分享_mysql 加密证件号中间8位_02