家里 ADSL 上网，没有办法分配固定外网 IP ，现在想使用群晖自带的MariaDB 安全实现与公网 MySQL 服务器主从同步。
最大的问题实际上是如果暴漏 MySQL 服务器的端口，但是不限制来源 IP 地址的话，会造成非常大的安全隐患。
但是， ADSL 恰恰不能提供固定的 IP 地址，我们需要解决这个问题。如果通过在公网数据库服务器上搭建 OpenVPN 服务器的方式，我们恰恰可以把内外网的设备影射到同一个子网中，而且 OpenVPN 提供的加密服务支持，刚刚好满足我们的安全需求。
注意：一旦配置主从同步，磁盘的休眠会被MySQL的同步写入打断，导致磁盘不能正常休眠，毕竟正常情况下数据库是持续写入的，不存在很长时间的停顿。这样会导致磁盘周期性的咔咔寻道声，尤其是晚上。
噪声问题，参考 群晖(Synology) DS718+希捷酷狼(Seagate IronWolf)12TB空闲发出持续噪声
1. 参考 Ubuntu架设OpenVPN实现内网穿透 搭建整个的 OpenVPN 服务器。映射完成后，设备上会新增一个名为 tun0 的网卡设备。同时所有连接到 VPN 服务器的设备都被被影射到 10.8.0.X 的网段。 公网服务器的地址默认是 10.8.0.1，本文中， 群晖NAS的地址被设定为 10.8.0.7。
2. 配置防火墙规则，许可来自指定网卡指定地址的设备的访问。注意，此处一定要指定网卡为 OpenVPN 创建的虚拟网卡，否则造成安全隐患。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$ 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 #注意，iptables-persistent 与 ufw 冲突, #现象就是系统重启后执行 sudo ufw status 显示 inactive， #但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常， #实际上ufw并没有正常工作。 #如果两者同时安装，需要参考 https://www.mobibrw.com?p=29330 进行配置 $ sudo netfilter-persistent save #配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面， #最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下， #可能会记录了多余的规则，需要手工删除 |
3. 参照 ubuntu 16.04配置基于SSL的MySQL主从同步 配置服务器
4. 接下来是群晖NAS的配置
首先是群晖服务器上没有 MySQL，需要安装 MariaDB ，如下图：
安装完成后，MariaDB 10 的配置信息在 /var/packages/MariaDB10/etc/ 目录下面：
|
1 2 3 4 5 6 |
$ 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 |
里面的内容如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
[mysqld] # 绑定地址禁止外网访问，安全性 bind-address = 127.0.0.1 #设置server-id，必须唯一 server-id = 3 # 根据业务需要配置数据库只读 #read_only = on #super_read_only = on #tx_read_only = on #日志也最好打开,暂时不要打开从服务的 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 # 缓冲区不足，可能会在还原数据的时候报错 # “ERROR 2006 (HY000) at line 241: MySQL server has gone away” # 观察日志报告 Got a packet bigger than 'max_allowed_packet' bytes # 这个缓冲区的大小必须大于或等于主库上设置的大小 max_allowed_packet = 64M |
下载加密证书
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
$ 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 $ scp -P $PORT -r root@$SERVER:$MS_LIB/ca.pem . $ sudo rm -rf $MDB_SSL/ca.pem $ sudo mv ca.pem $MDB_SSL/ $ sudo chown mysql:mysql $MDB_SSL/ca.pem $ scp -P $PORT -r root@$SERVER:$MS_LIB/client-cert.pem . $ sudo rm -rf $MDB_SSL/client-cert.pem $ sudo mv client-cert.pem $MDB_SSL/ $ sudo chown mysql:mysql $MDB_SSL/client-cert.pem $ scp -P $PORT -r root@$SERVER:$MS_LIB/client-key.pem . $ sudo rm -rf $MDB_SSL/client-key.pem $ sudo mv 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主从同步 进行服务器端的备份：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
$ 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';" # 修改已存在用户,要求必须通过SSL才能同步,完成主从同步之后，从库可能会无法正常同步这个修改，需要手工跳过一个错误 "stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;start slave;" $ mysql -u root -p -e "ALTER USER 'repl1'@'10.8.0.7' REQUIRE SSL;" # 刷新权限 $ mysql -u root -p -e "flush privileges;" # 阻止数据库记录写入，避免后期我们备份数据库的时候数据发生变动 # 该命令对于普通账号的只读模式，root 账号无效，因此访问数据库的账号 # 尽量不要使用root账号，如果是root 账号，只能暂时停止所有访问数据库的服务了 $ mysql -u root -p -e "set global read_only=1;" # 获取主数据库的日志状态 $ 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 | | | | +------------------+----------+--------------+------------------+-------------------+ # 执行备份 backup_wordpress.sh 脚本内容参考 https://www.mobibrw.com/?p=10541 $ sudo bash backup_wordpress.sh # 取消普通账号的只读模式 $ mysql -u root -p -e "set global read_only=0;" |
客户端执行恢复数据命令，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
# 出错日志查看 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 < wordpress.*.sql $ 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主从同步会尽可能的缩短复制延迟 # 特殊需求的情况下可指定同步延迟,比如延迟同步防止数据误删除 #$ mysql -u root -p -e "stop slave;" # 设定同步数据延迟一小时 # mysql -u root -p -e "CHANGE MASTER TO MASTER_DELAY = 3600;" $ 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: |