Lao Sen Chang Tan IT Help

Synology MariaDB imports sql files via DBeaver

Recently, we started to move our managed WordPress to Synology NAS，Import this piece in the database，As expected, there were a lot of problems，It took a lot of time to study and solve it one by one，Document the process，Share it with those who need it。

【環境】

Synology's MariaDB、phpMyAdmin has been built。
The .sql file has been downloaded from the hosting website，The size is about 250MB。
DBeaver is installed on your local computer，The local production system is Manjaro。

【Key Steps】

SSH can be added to MariaDB from Non-localhost Connected accounts。This is because the default settings for accounts are that they can only connect from localhost (i.e., the NAS itself).。
Increase the max_allowed_packet limit for MariaDB。By default, there is only 1M。
Install maria-clients locally，to allow DBeaver to use the directives required to restore the database。

New MariaDB Account

# 透過 SSH 連線 Synology NAS
ssh myaccount@192.168.1.1 # myaccount 為 Synology 帳戶；192.168.1.1 為 Synology 位址

# 確認 bind-address 為 0.0.0.0 （預設值）
more /volume1/@appstore/MariaDB10/usr/local/mariadb10/etc/mysql/my.cnf | grep bind-address

# 確認 port 為 3306，且 Synology 上的防火牆有開啟對應的 port
more /var/packages/MariaDB10/etc/my_port.cnf

# 以 root 身份登入資料庫
/volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysql --user=root --port=0 --password --host=localhost

# 檢視資料庫現有的使用者 
SELECT User, Host FROM mysql.user; # 記得最後要輸入分號

# 建立一個 dbeaver-restore 帳戶，密碼為 superstrongpassword，並允許其從 192.168.*.* 登入
CREATE USER 'dbeaver-restore'@'192.168.%' IDENTIFIED BY 'superstrongpassword';

# 給予 dbeaver-restore 存取所有資料庫的權限
GRANT ALL PRIVILEGES ON *.* TO 'dbeaver-restore'@'192.168.%' WITH GRANT OPTION;

# 斷開資料庫連線
exit

Adjusting MariaDB Packet Limits

#  確認目前 [mysqld] 底下的 max_allowed_packet 的數值
more /volume1/@appstore/MariaDB10/usr/local/mariadb10/etc/mysql/my.cnf

# Synology 建議不要修改預設的 my.cnf 檔案
# 而是使用下面路徑 my.cnf 檔來套用自訂值 
sudo vim /var/packages/MariaDB10/etc/my.cnf

# 在 vim 按 i 後，貼上下面文字
[mysqld]
max_allowed_packet = 500M # 設成大於 sql 檔大小的數值

# 按 ESC 後，存檔離開
:wq!

# 重啟 MariaDB 服務
sudo /usr/syno/bin/synopkg restart MariaDB10

Restoring a SQL Database with DBeaver
1. Because DBeaver needs to restore the database via mariadb or mysql，Therefore, you need to install the "mariadb-clients" or "mysql-client" package locally。

2. Confirm the location of mysql by using the which mysql command，By default, it will be in /usr/bin/mysql。

which mysql

3. In the DBeaver screen that sets up the database connection，You need to go to the local client to set the location of mysql。
Click Browse on the local client。

4. A new location for MySQL has been added。
◎ According to the official document description of DBeaver，If the location of mysql is in /usr/bin/mysql，Then this is set to /usr，Skip the /bin/mysql that follows。
◎ If DBeaver is the version downloaded from the Snap kit library，Convert the path to /run/…，This will cause subsequent executions to fail。If so, this is the case，Instead, install DBeaver from the official package library。