家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步

家里 ADSL 上网，没有办法分配固定外网 IP ，现在想使用群晖自带的 MariaDB 安全实现与公网 MySQL 服务器主从同步。

最大的问题实际上是如果暴漏 MySQL 服务器的端口，但是不限制来源 IP 地址的话，会造成非常大的安全隐患。

但是， ADSL 恰恰不能提供固定的 IP 地址，我们需要解决这个问题。如果通过在公网数据库服务器上搭建 OpenVPN 服务器的方式，我们恰恰可以把内外网的设备影射到同一个子网中，而且 OpenVPN 提供的加密服务支持，刚刚好满足我们的安全需求。

1. 参考 Ubuntu架设OpenVPN实现内网穿透 搭建整个的 OpenVPN 服务器。映射完成后，设备上会新增一个名为 tun0 的网卡设备。同时所有连接到 VPN 服务器的设备都被被影射到 10.8.0.X 的网段。 公网服务器的地址默认是 10.8.0.1 。

2. 配置防火墙规则，许可来自指定网卡指定地址的设备的访问。注意，此处一定要指定网卡为 OpenVPN 创建的虚拟网卡，否则造成安全隐患。

$ export slave_ip=10.8.0.7
 
#删除可能已经存在的配置，避免出现多条重复记录
$ sudo iptables -D INPUT -i tun0 -p tcp -s $slave_ip --dport 3306 -j ACCEPT

#增加配置，只允许特定地址访问数据库端口
$ sudo iptables -I INPUT -i tun0 -p tcp -s $slave_ip --dport 3306 -j ACCEPT
 
$ sudo iptables -L -n -v

#保存配置
$ sudo apt-get install iptables-persistent

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面，
#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除

3. 参照 ubuntu 16.04配置基于SSL的MySQL主从同步 配置服务器

4. 接下来是群晖NAS的配置

首先是群晖服务器上没有 MySQL ，需要安装 MariaDB ，如下图：

安装完成后， Maria DB 10 的配置信息在 /var/packages/MariaDB10/etc/ 目录下面：

$ sudo touch /var/packages/MariaDB10/etc/my.cnf
 
# 权限必须是 644
$ sudo chmod 644 /var/packages/MariaDB10/etc/my.cnf
 
$ sudo vim /var/packages/MariaDB10/etc/my.cnf

里面的内容如下：

[mysqld]
# 绑定地址禁止外网访问，安全性
bind-address = 127.0.0.1
 
#设置server-id，必须唯一
server-id = 3
 
#日志也最好打开,暂时不要打开从服务的 bin log 目前群晖上目录权限存在问题
# log_bin                 = /var/log/packages/MariaDB10/mysql-bin.log
 
#如果日志开启了，最好把日志格式设置为row格式，这样如果主从数据不一致，可以尝试mysql flashback功能
# binlog-format    = row
 
#如果数据库是从5.7版本之前升级的，并且是wordpress那么会遇到无法更改数据库的情况，
#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的，我们需要去掉这个限制，原因在于
#wordpress创建的表中存在
#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
#这样的定义是没办法进行后续的操作的，因此我们需要重新定义sql_mode来解除这个限制
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
 
# 不同步哪些数据库  
binlog-ignore-db = mysql
  
binlog-ignore-db = test  
 
binlog-ignore-db = information_schema

下载加密证书

$ export MDB_SSL=/var/packages/MariaDB10/etc/ssl
 
$ sudo mkdir $MDB_SSL
 
#从服务端获取证书
$ export PORT=22
 
$ export SERVER=10.8.0.1
 
$ export MS_LIB=/var/lib/mysql
 
$ sudo scp -P $PORT -r root@$SERVER:$MS_LIB/ca.pem $MDB_SSL/
 
$ sudo chown mysql:mysql $MDB_SSL/ca.pem
 
$ sudo scp -P $PORT -r root@$SERVER:$MS_LIBclient-cert.pem $MDB_SSL/
 
$ sudo chown mysql:mysql $MDB_SSL/client-cert.pem
 
$ sudo scp -P $PORT -r root@$SERVER:$MS_LIB/client-key.pem $MDB_SSL/
 
$ sudo chown mysql:mysql $MDB_SSL/client-key.pem
 
# 重启 MariaDB,有时候重启无效，此时请重启服务器
$ /usr/syno/bin/synopkg restart MariaDB

按照 ubuntu 16.04配置MySQL主从同步 进行服务器端的备份：

$ mysql -u root -p -e "CREATE USER 'repl1'@'10.8.0.7' IDENTIFIED BY 'slavepass';" #创建用户
 
#  select user,host from mysql.user;  查看创建的用户列表
#  set password for  'repl1'@'10.8.0.7' = password('pass'); 修改用户的密码
 
#分配权限
$ mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'10.8.0.7';"
 
#刷新权限
$ mysql -u root -p -e "flush privileges;"
 
# 获取主数据库的日志状态
$ mysql -u root -p -e "SHOW MASTER STATUS;"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000664 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
 
# 执行备份
$ sudo bash backup_wordpress.sh

客户端执行恢复数据命令，如下：

# 出错日志查看 sudo tail -f /var/packages/MariaDB10/target/mysql/DS718.err
 
$ export PATH=/usr/local/mariadb10/bin/:$PATH
 
$ export SERVER=10.8.0.1
 
$ scp -P 22 -r root@$SERVER:~/wordpress.*.sql ./
 
#删除可能存在的一行警告信息，这行警告信息可能导致我们无法恢复数据
$ sed -i "/^mysqldump: \[Warning\] Using a password on the command line interface can be insecure\./d" wordpress.*.sql
 
$ mysql -u root -p -e "stop slave;"
 
$ mysql -u root -p -e "drop database wordpress;"
 
$ mysql -u root -p -e "create database wordpress;"
 
$ mysql -u root -p wordpress  
$ mysql -u root -p -e "stop slave;"
 
#修改同步信息
$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='10.8.0.1', MASTER_USER='repl1', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000664',MASTER_LOG_POS=154 ,master_ssl=1,master_ssl_ca='/var/packages/MariaDB10/etc/ssl/ca.pem', master_ssl_capath='/var/packages/MariaDB10/etc/ssl', master_ssl_cert='/var/packages/MariaDB10/etc/ssl/client-cert.pem', master_ssl_key='/var/packages/MariaDB10/etc/ssl/client-key.pem';"
 
$ mysql -u root -p -e "start slave;"
 
#查看从库状态
$ mysql -u root -p -e "show slave status\G;"
Enter password:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.8.0.1
                  Master_User: repl1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000664
          Read_Master_Log_Pos: 1266018
               Relay_Log_File: VM-xxx-xxx-xxxxx-relay-bin.000003
                Relay_Log_Pos: 162225
        Relay_Master_Log_File: mysql-bin.000664
             Slave_IO_Running: Yes
            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: 174762
              Relay_Log_Space: 1188728
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /var/packages/MariaDB10/etc/ssl/ca.pem
           Master_SSL_CA_Path: /var/packages/MariaDB10/etc/ssl
              Master_SSL_Cert: //var/packages/MariaDB10/etc/ssl/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /var/packages/MariaDB10/etc/ssl/client-key.pem
        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
                  Master_UUID: xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

参考链接

• ubuntu 16.04配置MySQL主从同步


• linux iptables初步应用


• ubuntu 16.04配置基于SSL的MySQL主从同步


• 如何用 phpMyAdmin 来管理 Synology NAS 中的 MySQL/MariaDB 数据库


• How to list all iptables rules with line numbers on Linux


• 在 Synology NAS 中可对 MariaDB 设置进行自定义吗？


• Synology(群辉) NAS Mariadb 配置文件修改


• Mysql使用mysqldump和mysqlbinlog进行备份