Loading... <h2>MariaDB数据库基于SSL实现远程访问和主从复制</h2> <h3>实验环境</h3> <p>系统环境:Centos6.5</p> <p>数据库版本:5.5.36-MariaDB-log MariaDB Server</p> <p>虚机数量:2</p> <h3>方案实施:</h3> <p>1,配置CA;并为node1和node2生成key和证书</p> <p>2,在node1和node2上安装Mariadb</p> <p>3,配置节点1为MariaDB主节点</p> <p>4,配置节点2为MariaDB从节点</p> <p>5,配置节点1和节点2主从复制模式</p> <p>6,配置节点1和节点2支持ssl</p> <p>7,在node1上新建ssl模式的用户</p> <p>8,node2已ssl的方式访问数据库node1</p> <p>9,node2以ssl的方式连接数据库,并设置支持主从复制默认</p> <p>10,Windows下通过“Navicat for MySQL”以ssl的方式连接访问node1</p> <h4>1,配置CA;并为node1和node2生成key和证书</h4> <p>确认系统已安装openssl组件,如果未安装,通过以下方式安装并查看</p> <pre># yum install openssl -y # rpm -ql openssl /etc/pki/CA /etc/pki/CA/certs /etc/pki/CA/crl /etc/pki/CA/newcerts /etc/pki/CA/private /etc/pki/tls /etc/pki/tls/certs /etc/pki/tls/certs/Makefile /etc/pki/tls/certs/make-dummy-cert /etc/pki/tls/certs/renew-dummy-cert /etc/pki/tls/misc /etc/pki/tls/misc/CA /etc/pki/tls/misc/c_hash /etc/pki/tls/misc/c_info /etc/pki/tls/misc/c_issuer /etc/pki/tls/misc/c_name /etc/pki/tls/openssl.cnf /etc/pki/tls/private /usr/bin/openssl ...... 修改openssl.cnf的配置文件,修改几个地方 # vim /etc/pki/tls/openssl.cnf countryName_default = CN 国家 stateOrProvinceName_default = BEIJING 城市 localityName_default= Tongzhou 区县 0.organizationName_default = TEST 公司 organizationalUnitName_default = IT 部门 node1以CAserver的身份自建CA证书 # cd /etc/pki/CA # (umask 077;openssl genrsa -out private/cakey.pem 2048) # openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650 # touch {index.txt,serial} # echo 01 > serial 给node1生成key,证书请求和证书 # cd /etc/pki/CA # (umask 077;openssl genrsa -out master.key 2048) 一路回车即可 # openssl req -new -key master.key -out master.csr 需要点两次y确认 # openssl ca -in master.csr -out master.crt -days 3650 给node2生成key,证书请求和证书 # cd /etc/pki/CA # (umask 077;openssl genrsa -out slave.key 2048) 一路回车即可 # openssl req -new -key slave.key -out slave.csr 需要点两次y确认 # openssl ca -in slave.csr -out slave.crt -days 3650</pre> <h4>2,在node1和node2上安装Mariadb</h4> <p>使用软件安装包为mariadb-5.5.36-linux-x86_64.tar.gz,上传至root目录下,安装配置Mairadb,并设置mydata目录为数据和日志存放目录;以下步骤在node1和node2上一致</p> <pre># cd /root # tar -xzf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local/ # ln -sv mariadb-5.5.36-linux-x86_64 mysql # groupadd=mysql # useradd=mysql -g mysql # mkdir /mydata/{data,binlog,relaylog} -pv # chown -R mysql.mysql /mydata/* # mkdir /etc/mysql # cp /usr/local/mysql/support-files/my-large.cnf /etc/mysql/my.cnf 修改/etc/mysql/my.cnf # vim /etc/mysql/my.cnf 最后一行增加以下语句 datadir=/mydata # chown -R mysql.mysql /etc/mysql/* # echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh # source /etc/profile.d/mysql.sh # mkdir /var/lib/mysql/ # chown -R mysql.mysql /var/lib/mysql/ # cd /usr/local/mysql # cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld # chmod +x /etc/init.d/mysqld # scripts/mysql_install_db --user=mysql --datadir=/mydata/data # service mysqld restart</pre> <h4>3,配置节点1为MariaDB主节点</h4> <p>在node1部署为MariaDB的master节点,修改node1的配置文件,增加以下内容</p> <pre># vim /etc/mysql/my.cnf server-id=1 设置server-id号为1 log-bin=/mydata/binlog/master-binlog 是此服务器支持binlog</pre> <h4>4,配置节点2为MariaDB从节点</h4> <pre># vim /etc/mysql/my.cnf server-id=1 设置server-id号为2 relay-log=/mydata/relaylog/relay-bin 是此服务器支持relaylog</pre> <h4>5,配置节点1和节点2主从复制模式</h4> <pre>在node1节点上,新建一个用户nossl@'%.%.%.%',密码nossl,并授权所有访问权限 # mysql MariaDB [(none)]> grant all on *.* to nossl@'%.%.%.%' identified by "nossl"; Query OK, 0 rows affected (0.06 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 2682 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.02 sec) 在node2节点上,使用此账户登录,并设置同node1实现主从复制 # mysql -unossl -hnode1 -pnossl mysql> CHANGE MASTER TO MASTER_HOST='nod1',MASTER_USER='nossl',MASTER_PASSWORD='nossl',,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2682; Query OK, 0 rows affected (0.25 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: node1 Master_User: nossl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 2682 Relay_Log_File: zabbix-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ..... Master_Server_Id: 1 1 row in set (0.00 sec)</pre> <h4>6,配置节点1和节点2支持ssl</h4> <pre>在node1上,新建文件夹用于存放ca证书和node1自己的key+证书 # mkdir /etc/mysql/ssl # cp /etc/pki/CA/private/cakey.pem /etc/mysql/ssl # cp /etc/pki/CA/master.key /etc/mysql/ssl # cp /etc/pki/CA/master.crt /etc/mysql/ssl # chown -R mysql.mysql /etc/mysql* 修改mysql的配置文件,使mysql支持ssl模式 # vim /etc/mysql/my.cnf ssl 开启SSL功能 ssl-ca = /etc/mysql/ssl/cacert.pem 指定CA文件位置 ssl-cert = /etc/mysql/ssl/master.crt 指定证书文件位置 ssl-key = /etc/mysql/ssl/master.key 指定密钥所在位置 在node2上,新建文件夹用于存放ca证书和node2自己的key+证书 # mkdir /etc/mysql/ssl # scp node1:/etc/pki/CA/private/cakey.pem /etc/mysql/ssl # cp node1:/etc/pki/CA/slave.key /etc/mysql/ssl # cp node1:/etc/pki/CA/slave.crt /etc/mysql/ssl # chown -R mysql.mysql /etc/mysql* # vim /etc/mysql/my.cnf ssl 开启SSL功能 ssl-ca = /etc/mysql/ssl/cacert.pem 指定CA文件位置 ssl-cert = /etc/mysql/ssl/slave.crt 指定证书文件位置 ssl-key = /etc/mysql/ssl/slave.key 指定密钥所在位置</pre> <p>在node1和node2上经过以上配置后,需要重启mysqld服务,并通过以下方式查看</p> <pre># service mysqld restart # mysql mysql> show variables like "%ssl%"; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca| /etc/mysql/ssl/cacert.pem | | ssl_capath| | | ssl_cert | /etc/mysql/ssl/mysql.crt | | ssl_cipher| | | ssl_key | /etc/mysql/ssl/mysql.key | +---------------+---------------------------+ 7 rows in set (0.00 sec)</pre> <h4>7,在node1上新建ssl模式的用户</h4> <pre>MariaDB [(none)]> grant all on *.* to 'ssluser'@'%.%.%.%' identified by 'sslpass' require ssl;</pre> <h4>8,在node2以ssl的方式访问数据库node1</h4> <pre> # mysql -ussluser -psslpass -hnode1 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key</pre> <h4>9,node2以ssl的方式连接数据库,并设置支持主从复制默认</h4> <p>在正式环境里,为了安全期间,可以删除nossl用户,仅保留ssluser用户用来远程连接mysql;</p> <pre># mysql -ussluser -psslpass -hnode1 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key mysql> CHANGE MASTER TO MASTER_HOST='node1',MASTER_USER='ssluser',MASTER_PASSWORD='sslpass',master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl_cert='/etc/mysql/ssl/mysqls.crt',master_ssl_key='/etc/mysql/ssl/mysqls.key',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=26822; Query OK, 0 rows affected (0.23 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.97.47 Master_User: ssluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 44904037 Relay_Log_File: zabbix-relay-bin.00000 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.00000 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 44904037 Relay_Log_Space: 245 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/ssl/mysqls.crt Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/mysqls.key Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) 通过状态查看,slave已经以ssl的工作模式正常执行同步</pre> <h4>10,Windows下通过“Navicat for MySQL”以ssl的方式连接访问数据库node1</h4> <p>将node1/etc/mysql/ssl下的几个文件存放到windows上,此处我放到我的D盘下ssl目录</p> <p>注意,在ssl里选择证书和key的时候,需要先点击“使用验证”,等选择完毕后再勾除“使用验证”</p> <p><img src="//cto.wang/usr/uploads/2016/07/20160703163944-60.jpg" title="1463561537583257.jpg" /></p> <p><img src="//cto.wang/usr/uploads/2016/07/20160703163944-75.jpg" title="1463561537772476.jpg" /></p> <p><img src="//cto.wang/usr/uploads/2016/07/20160703163944-57.jpg" title="1463561776373031.jpg" alt="ssl2.jpg" /></p> <p></p> 最后修改:2021 年 12 月 10 日 10 : 53 AM © 允许规范转载 赞赏 如果觉得我的文章对你有用,请随意赞赏 赞赏作者 支付宝微信