|
1、创建证书中心 创建证书颁发机构，首先要生成ca自己的私钥，如下： cd /etc/pki/CA (umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048) 生成自签证书,由于需要输入大量用户信息（在私有的CA上创建证书要注意所有的用户信息需要一致，从国家到部门都要相同，否则会造成证书无法使用） 生成自签名证书 openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650 -x509是创建自签证书是需要的参数，在创建其他证书时不能加该参数 然后创建ca所需文件，如下：
cd /etc/pki/CA touch index.txt echo 01 > serial
2、为master服务器创建证书 服务器的名称必须固定，在申请证书时要输入服务器的通用名称，此通用名称必须与服务器外网申请的域名相同。 创建私钥 mkdir /home/mysql/ cd /home/mysql (umask 077;openssl genrsa -out master.key 2048) 生成证书申请文件 openssl req -new -key master.key -out master.csr 在证书服务器上对master的证书进行签发 cd /etc/pki/CA openssl ca -in /home/mysql/master.csr -out /home/mysql/master.crt -days 365
3、创建slave服务器证书 (umask 077;openssl genrsa -out slave.key 2048) openssl req -new -key slave.key -out slave.csr 将slave服务器的证书申请文件复制到证书服务器上进行签发 opessl ca -in slave.csr -out slave.crt -days 356
4、修改证书权限和mysql配置文件 将ca的证书cacert.pem复制到master、slave服务器的相应目录下 cd /home/mysql cp /etc/pki/CA/cacert.pem ./ chown -R mysql:mysql /home/mysql chmod 600 /home/mysql/*.key 修改master服务器的/etc/my.cnf配置文件： vim /etc/my.cnf [mysqld] ssl_ca = /home/mysql/cacrt.pem ssl_cert = /home/mysql/master.crt ssl_key = /home/mysql/master.key
修改slave服务器配置 vim /etc/my.cnf [client] ssl_ca = /home/mysql/cacrt.pem ssl_cert = /home/mysql/slave.crt ssl_key = /home/mysql/slave.key
5、在主服务器上创建复制用户 > grant replication slave on *.* to 'repl'@‘192.168.%' identified by 'repl' require ssl; > flush privileges;
查看主服务器当前二进制位置 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、在从服务器上开始复制 change master to master_host=‘192.168.0.66', master_user='repl', master_password='repl', master_log_file='mysql-bin.000007', master_log_pos=1015, master_ssl=1, master_ssl_ca='/home/mysql/cacrt.pem', master_ssl_cert='/home/mysql/slave.crt', master_ssl_key='/home/mysql/slave.key'; start slave; show slave status;
7 排错 导致lave_IO_Running 为connecting 的原因主要有以下 3 个方面： 解决步骤： 在主机上 show master status \G ;可以查看到
8 线上实例 服务器端配置如下： [ec2-user@vps1 ~]$ cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld according to the skip_name_resolve
open_files_limit=8192 #ssl configure ssl_ca=/home/mysql/cacert.crt ssl_cert=/home/mysql/mysql.crt ssl_key=/home/mysql/mysql.key
log-bin=mysql-bin expire_logs_days=5 server-id=1 replicate-ignore-db=mysql replicate-ignore-db=zabbix
slow_query_log_file=/var/log/mysql/slow.log log_queries_not_using_indexes=1 long_query_time=5
innodb_file_per_table=1 innodb_buffer_pool_size=64M
log_slave_updates relay-log=relay-bin relay-log-space-limit=512000000 slave-net-timeout=360
[mysql] default_character_set=utf8
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
[ec2-user@vps1 ~]$ ll /home/mysql/ 总用量 16 -rw-r--r-- 1 mysql mysql 1318 6月 23 06:09 cacert.crt -rw-r--r-- 1 mysql mysql 3715 6月 23 06:05 mysql.crt -rw-r--r-- 1 mysql mysql 651 6月 23 06:03 mysql.csr -rw------- 1 mysql mysql 891 6月 23 06:02 mysql.key [ec2-user@vps1 ~]$
slave端配置如下： [ec2-user@vps2 ~]$ cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld according to the skip_name_resolve
open_files_limit=8192 #ssl configure ssl_ca=/home/mysql/cacert.crt ssl_cert=/home/mysql/mysql.crt ssl_key=/home/mysql/mysql.key
log-bin=mysql-bin expire_logs_days=5 server-id=2 replicate-ignore-db=mysql replicate-ignore-db=zabbix
slow_query_log_file=/var/log/mysql/slow.log log_queries_not_using_indexes=1 long_query_time=5
innodb_file_per_table=1 innodb_buffer_pool_size=64M
log_slave_updates relay-log=relay-bin relay-log-space-limit=512000000 slave-net-timeout=360
[mysql] default_character_set=utf8
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
[client] ssl_ca=/home/mysql/cacert.crt ssl_cert=/home/mysql/mysql.crt ssl_key=/home/mysql/mysql.key [ec2-user@vps2 ~]$
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.66 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 259 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,zabbix 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: 259 Relay_Log_Space: 403 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /home/mysql/cacert.crt Master_SSL_CA_Path: Master_SSL_Cert: /home/mysql/mysql.crt Master_SSL_Cipher: Master_SSL_Key: /home/mysql/mysql.key 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: 1 1 row in set (0.00 sec) mysql>
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2016-6-24 08:48:50
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡