I wanted to use phpMyAdmin on a Synology to access a MySQL running on a RPI with openHabian. Here is my how-to:
Click to Read MoreClick to Close
First connect on your openHabian using a ssh console.
Obviously, you need MySQL to be installed and configured:
sudo apt update
sudo apt upgrade
sudo apt install mariadb-server
sudo mysql_secure_installation
Then, double check that MySQL is running and listening on port 3306 netstat -plantu | grep 3306
If nothing is displayed by this command, MySQL is not listening on the port 3306.
Enter MySQL as root with the command:
sudo mysql -uroot -p
Check the port used by MySQL
SHOW GLOBAL VARIABLES LIKE ‘PORT’;
Then, type the following MySQL commands to create an account and a database, and grant both local and remote access for this account on the database:
CREATE USER ‘<YourAccount>’@’localhost’ IDENTIFIED BY ‘<YourPassword>’;
CREATE DATABASE <YourDatabase>;
GRANT ALL PRIVILEGES ON <YourDatabase>.* TO ‘<YourAccount>’@’localhost’;
GRANT ALL PRIVILEGES ON *.* to ‘<YourAccount>’@’169.254.0.%’ identified by ‘<YourAccountPassword>’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
Here above, I do grant access from all machines in my local network with ‘169.254.0.%’. One can restrict access to one machine with its specific address, such as : ‘169.254.0.200’
Now, edit 50-server.cnf and configure MySQL to not listen anymore on its local IP only (simply comment the line bind-address) :
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1
Finally, restart MySQL for the changes above to be applied:
sudo service mysqld restart
You can now edit the config of phpMyAdmin to access the MySQL on your RPI. If it is running on Synology, look here.
But upgrading WordPress from 3.3 to 3.4 on my DS209+ was almost “Yet Another Upgrade Nightmare” 🙂
Click to Read MoreClick to Close
Upgrading a package for Synology is usually really a piece of cake. Unfortunately, the upgrade package for WordPress caused me difficulties.
First issue: MySQL password
The setup failed quite immediately after downloading, complaining that there was possibly an issue with the MySQL password. After some investigation, it appeared to me that :
the DSM Package Center is running as Synology’s “root” user and
the setup of WordPress was trying to access MySql with that account but without any password.
I found on Synology’s Forum that “Current wordpress package requires an default mysql account root with empty password to create database successfully. If you have set mysql root password before, please install phpMyAdmin to reset the password first.“
And unfortunately (although quite normal), I did set a password for the MySql “root” user in the past… So, as suggested in the message above, I did reset MySql root password to blank.
Go to phpMyAdmin, opened the “Users” tab and clicked “Edit Privileges” for the user “root” with Host=localhost.
In the “Edit Privileges” dialog box, scroll down to the “Change Password” area and select “No Password” before clicking “Go”.
Finally, back to the “Users” page, click “Create PHP code” to apply the change. At that moment, phpMyAdmin could prompt you to re-enter your credentials (root with a blank password).
I did next restart the update of the WordPress which finally completed fine. But…
Next issue: Page Not Found
My Blog was not reachable anymore after the upgrade ? Damned… WordPress was imply disabled… I had to click “Start” next to the WordPress Package in the DSM Package Center 😆
And finally, my home page appeared… I went immediately to the administration dashboard where I discovered that the setup was actually not yet complete. In this dashboard, I found a message asking me to click a button to upgrade the database. Fortunately, nothing wrong occurred during this upgrade 🙂
Last issue: This is somewhat embarrassing, isn’t it?
Although the home page appeared correctly, I was not able to open anything else: no post, no “custom” pages, no categories, … For each of them, I got the WordPress’ “Page Not Found”… All the permalinks appeared somewhat “corrupted”… No idea why.
Fortunately (bis ter et repetita), fixing this issue was not difficult. I simply had to re-enforce the right Permalinks structure.
Go back to the administration dashboard and select the menu “Permalinks” under “Settings”.
Check that “your” structure of Permalinks is still selected and did click “Save Changes” (Even if you didn’t change the structure).
And miracle! Everything went back to normal. I didn’t lose any customization or plugins except my custom smilies from \web\wordpress\wp-includes\images\smilies (fortunately backuped),…
YASU!!
No! I Don’t mean “Yet Another System Utility”!!! But “Yet Another Successful Update” 😆
It would be a good idea to schedule a daily backup of all the MySQL databases on your Synology. Those will indeed be wiped out if by accident, you have to reset your NAS (reinstall the firmware), ex.: if you lose a disk in your RAID 0.
That can be done using a shell script and a cron job as described here after.
How to BackupClick to close
First, create a user with a local-read-only access on the databases. We will indeed have to let its password in the script, endangering the security. Using a local-read-only user will mitigate the risks.
Go to the web administration interface (DSM) of your NAS.
Install phpMYAdmin via the “Package Center” if not yet done.
Open phpMyAdmin via the “Start Menu” (“Main Menu”).
I am using the login ‘root’ with the password of Syno’s ‘admin’ user
Open the “Users” tab in phpMyAdmin
Click “Add User” (bellow the list of existing users)
Enter the User Name. Ex.: backup
Select “localhost” as an Host.
Enter a password. Ex.: mysql
Keep “none” as “Database for user”
Grant the user with Global privileges: Select (in Data) and Reload, Show Databases and Lock Tables (in Administration)
Click “Add User” at the botton of the page
Next, create a shell scripts in a Shared Folder of the NAS (Ex.: \volume1\backup\backupMySql.sh). Working in a Shared Folder will make it easier for you to copy/open later the backups from your PC). Don’t forget to create a “Unix” file, either using the touch command in a Console or saving a file As a “Unix shell script” within Notepad++. Notice that a script created with NotePad++ and saved on the Shared Folder will belong to the user account accessing that Shared Folder (Most probably your Windows Account if like me you simply created a user on your NAS with the same login and password). A script created with “touch” in a Console will belong to the user accessing the NAS via telnet/SSH (Most probably the “root” account).
[shell]#!/bin/sh
#
DIR=/volume1/backup/sqlbackup/
DATESTAMP=$(date +%Y%m%d%H%M%S)
DB_USER=backup
DB_PASS=mysql
# create backup dir if it does not exist
mkdir -p ${DIR}
# remove backups older than $DAYS_KEEP
#DAYS_KEEP=30
#find ${DIR}* -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null
# remove all backups except the $KEEP latest
KEEP=5
BACKUPS=`find ${DIR} -name 'mysqldump-*.gz' | wc -l | sed 's/\ //g'`
while [ $BACKUPS -ge $KEEP ]
do
ls -tr1 ${DIR}mysqldump-*.gz | head -n 1 | xargs rm -f
BACKUPS=`expr $BACKUPS - 1`
done
#
# create backups securely
#umask 006
# dump all the databases in a gzip file
FILENAME=${DIR}mysqldump-${DATESTAMP}.gz
/usr/syno/mysql/bin/mysqldump --user=$DB_USER --password=$DB_PASS --opt --all-databases --flush-logs | gzip >> $FILENAME
[/shell]
NB: Since DSM 6.0, “/usr/syno/mysql/bin/mysqldump” has been moved to “/bin/mysqldump” !!!
Possibly, type the following command in a Console (telnet/SSH) to set the user ‘root’ as owner of the script:
IMPORTANT notice: I used to have “-u DB_USER” (with a blank space in between) in my script above and -“p$DB_USER” (without a blank) instead of –user and –password. But a reader (Fredy) reported that the script was not running fine for him unless removing the blank. As per documentation of mysqldump’s user parameter and password parameter, there should be a blank after -u but not after -p. However, samples on the web usually illustrate the use of mysqldump with “-uroot”. So, I decided to use the more explicit notation “–user=” and “–password=”. I did test this notation with a wrong username or password and the resulting dump is indeed empty. With the correct username and password, it works fine.
Since DSM 4.2, the Task Scheduler can be used to run the script on a daily basis.
Go to the Start Menu
Open the Control Panel
In the “Application Settings”, open the Task Scheduler
Select “Create a User-Defined Script”
Type a name for that “Task:”
Keep the “User:” root
In the “Run Command” pane, type:
sh /volume1/backup/backupMySql.sh
Don’t forget the “sh” in front of your command, otherwise, it does not work (although the sample provided by Synology is missing it ?!)
If you don’t use the Task Scheduler, you can add a Cron Job to execute the script, e.g. every day at 0:01. Open a Console (Telnet/SSH) and type:
FYI, a cron line syntax is “mm hh dd MMM DDD user task” where:
mm is the minute (0..59)
hh is the hour (0..23)
dd is the day in the month (1..31)
MMM is the month (jan, feb, … or 1..12)
DDD is the day in the week (sun, mon, … or 0..7 where 0 and 7 beeing both sunday)
The following values can also be used:
* : a every unit (0, 1, 2, 3, 4…)
5,8 : at unit 5 et 8
2-5 : units 2 to 5 (2, 3, 4, 5)
*/3 : every 3 units (0, 3, 6, 9…)
10-20/3 : every 3 units, from 10th to 20th (10, 13, 16, 19)
So, the script will start every day at 0:01h
Finally, you must restart the cron daemon to activate the new job. To find the process id and send a SIGHUP signal to this one, type the following command line in a Console (Telnet/SSH):
It should display “Kill -HUP xxxx” where xxx is the pid of the cron daemon.
Added on 03/01/2013Click to Close
Attention: if you copy paste the command to restart the cron daemon from this page into the telnet console, some symbols will be wiped out: the ‘{ and ‘ around print$1… you have to type them manually…
Attention: if you upgrade the version of DSM, the changes made in the cron script are lost (at least, it’s what I have noticed after updating last month…). Reason why I recommend to use the new “Task Scheduler” feature available since DSM 4.2.
Tip to edit the cron script: you can use the package “Config File Editor” available here. Once installed, you can access it via the Main Menu. Then, you have to edit the file named “Config File Editor” and add the following line:
/etc/crontab,crontab
Once this line is added and saved, … I don’t remember how to force the change to be taken into account :/. But restarting the DiskStation is enough 😀
Finally, back into the Config File Editor, select the ‘crontab’ entry and modify this script, save your changes and restart the cron daemon.
Tip to restart the cron daemon: you can use the package “MODS Web Console” available here. To install it, go to the Package Center and add the following url via the Settings > Package Sources : https://www.beatificabytes.be/sspks. Once this url added, go to the tab “Community” and click Install on the “MODS Web Console” icon.
Run the Web Console via the Main Menu, log in with “admin”/”admin” (The defaults values if not changed) and type:
synoservice --restart crond
Added on 01/12/2013Click to Close
If you want a lazy solution to notify the Administrator about the success or failure of the backup, you can use the ‘synonotify’ command (See details here). A more advanced solution would be to configure the “Mail Server” and use its ‘sendmail’ command: /volume1/@appstore/MailServer/sbin/sendmail…
Copy the archive (.gz) you plan to restore on your local PC
Go to “phpyMyAdmin” > “Import” > “Chose File” (next to Browse your computer).
Select the .gz file to be restored
Press “Go” at the bottom of the page (no need to change any settings)
Pay attention: this is a complete restore, I.e.: all databases are dropped and recreated from scratch.
If you want to be able to restore a subset of your databases you better have to change the script to backup specific databases instead of –all-database.
If you want to restore only one table ‘mytable’ from the backup ‘mysqldump-datetime.gz’:
Unzip first the archive: gunzip mysqldump-datetime.gz
Then extract the part that restore the desired table with this command: sed -n -e ‘/DROP TABLE.*mytable/,/UNLOCK TABLES;/p’ mysqldump-datetime > mytabledump
