家里 `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` 创建的虚拟网卡，否则造成安全隐患。
$ 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/` 目录下面：
$ 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 # 根据业务需要配置数据库只读 #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
下载加密证书
$ 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主从同步 进行服务器端的备份：
$ 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;"
客户端执行恢复数据命令，如下：
# 出错日志查看 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: