MariaDB 10

Questions about hosting a Website, using MySQL, PhP or MariaDB may be posted here.
Forum rules
1) This is a user forum for Synology users to share experience/help out each other: if you need direct assistance from the Synology technical support team, please use the following form:
https://myds.synology.com/support/suppo ... p?lang=enu
2) To avoid putting users' DiskStation at risk, please don't paste links to any patches provided by our Support team as we will systematically remove them. Our Support team will provide the correct patch for your DiskStation model.
Prosperot
Beginner
Beginner
Posts: 24
Joined: Mon Dec 21, 2009 5:27 pm

Re: MariaDB 10

Postby Prosperot » Thu Jul 06, 2017 4:01 pm

I have got my MariaDB5 data transferred to MariaDB 10 and got my (non Synology) wordpress sites up n running again.

Steps:
1 backup MariaDB5 and MariaDB10 (option in Hyperbackup)
2 export all databases from MariaDB5 to a sql dump (option in PhPmyadmin)
3 import sqldump in MariaDB10 (option in PhPMyadmin)
4 edit wordpress wp-config.php: search for "define('DB_HOST', 'localhost');" and change in define('DB_HOST', 'localhost:/run/mysqld/mysqld10.sock');

switch off MariaDB5 and test.. and enjoy :D
Klaestrup
I'm New!
I'm New!
Posts: 6
Joined: Sat Jan 05, 2013 11:03 pm

Re: MariaDB 10

Postby Klaestrup » Thu Jul 06, 2017 6:40 pm

After upgrading from MariaDB 5 to 10, I had to change 'localhost' to '127.0.0.1' in the php sql config files before it worked. Why? And is it possible to change the version 10 back to 3306 (default)?
rtoussaint
I'm New!
I'm New!
Posts: 9
Joined: Sun Jul 02, 2017 8:54 pm

Re: MariaDB 10

Postby rtoussaint » Thu Jul 06, 2017 8:33 pm

Klaestrup wrote:After upgrading from MariaDB 5 to 10, I had to change 'localhost' to '127.0.0.1' in the php sql config files before it worked. Why? And is it possible to change the version 10 back to 3306 (default)?

Just edit the port in /usr/local/mariadb10/etc/mysql/my.cnf and restart MariaDB

The package in DSM will still say it's 3307 though, I think it's just a static image, not a variable that's being shown.
Klaestrup
I'm New!
I'm New!
Posts: 6
Joined: Sat Jan 05, 2013 11:03 pm

Re: MariaDB 10

Postby Klaestrup » Fri Jul 07, 2017 7:08 am

Thank you, I will try that.

rtoussaint wrote:
Klaestrup wrote:After upgrading from MariaDB 5 to 10, I had to change 'localhost' to '127.0.0.1' in the php sql config files before it worked. Why? And is it possible to change the version 10 back to 3306 (default)?

Just edit the port in /usr/local/mariadb10/etc/mysql/my.cnf and restart MariaDB

The package in DSM will still say it's 3307 though, I think it's just a static image, not a variable that's being shown.
wags1
Trainee
Trainee
Posts: 15
Joined: Tue Sep 10, 2013 6:43 pm

Re: MariaDB 10

Postby wags1 » Fri Jul 07, 2017 6:49 pm

For Kodi Users -

If you are using MariaDB 5.5 for your Kodi database and are trying to figure out how to migrate your database to MariaDB 10 here is what I did to get it migrated and working. There are, I'm sure, easier and quicker ways to do this but this is what worked for me. I hope it helps someone.

1 - Using the Synology package center install MariaDB 10 and then upgrade PHPMyAdmin.
2 - Using PHPMyAdmin login to MariaDB 5.5 (if both 10 and 5.5 are running you should have a selection box at the bottom of the PHPMyAdmin login screen).
3 - On the home screen click on Export, accept the defaults and click on Go. This will export your existing databases to your browsers download folder.
4 - Click on the logoff icon.
5 - Using PHPMyAdmin login to MariaDB 10. Note - the default login is root with no password. After your first login you should change it for security.
6 - If your Kodi databases were using a userid and password complete the following step, if not skip it - Click on User Accounts and create an account with the exact same username, password, and privileges as your were using with MariaDB5.5.
7 - Click on Import and point to the file you exported in step 3 and accept the defaults. Once the import competes you should see your Kodi databases in the left pane.
8 - If you created a username and password in step 6 click on Databases and then click on Privileges next to each of your Kodi databases to ensure that the user that you created is there and has the correct privileges.
9 - Click on the logoff icon.
10 - Using the Synology Package Center stop MariaDB 5.5.
11 - For each of your Kodi systems you will need to edit your advancedsettings xml file and change the port setting from 3306 to 3307.
12 - Run each of your Kodi systems to test and ensure they are working correctly.
13 - If everything is working you can uninstall MariaDB 5.5 and delete the databases associated with that server.

That's it, at that point you should be good to go.
rtoussaint
I'm New!
I'm New!
Posts: 9
Joined: Sun Jul 02, 2017 8:54 pm

Re: MariaDB 10

Postby rtoussaint » Sat Jul 08, 2017 5:54 am

Or change the port of MariaDB 10 back to 3306 on your Synology.
Nico-S
I'm New!
I'm New!
Posts: 7
Joined: Thu Dec 23, 2010 12:31 pm

Re: MariaDB 10

Postby Nico-S » Mon Jul 10, 2017 10:47 pm

Code4R7 wrote:I've advanced a bit already. The first two lines can be solved:
170701 23:52:11 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
170701 23:52:11 [Note] Recovering after a crash using tc.log

$ sudo rm -r /volume1/@database/mariadb10/*
/var/packages/MariaDB10/target/usr/local/mariadb10/bin$ sudo mysql_install_db --user=mysql --basedir=/var/packages/MariaDB10/target/usr/local/mariadb10 --ldata=/volume1/@database/mariadb10

The rm command destroys all mariadb10 data but there is nothing of importance since it never ran in the first place.


Thanks a lot ! :D
It completely solved my problem :D

Nicolas.
1 x DS101G+
1 x DS409+/DSM4.2/3xHDD RAID 5
1 x DS715/DSM6/2xHDD RAID 1
rtoussaint
I'm New!
I'm New!
Posts: 9
Joined: Sun Jul 02, 2017 8:54 pm

Re: MariaDB 10

Postby rtoussaint » Thu Jul 13, 2017 9:44 pm

Word of warning, should anyone have changed the port back to 3306. After an update of MariaDB 10 it'll switch back to 3307.
DavePDX
I'm New!
I'm New!
Posts: 8
Joined: Tue Feb 15, 2011 7:11 pm

Re: MariaDB 10

Postby DavePDX » Thu Jul 13, 2017 9:58 pm

rtoussaint wrote:Or change the port of MariaDB 10 back to 3306 on your Synology.


Is there a way to do this from the GUI or do we need to manually edit the cfg file? And in light of this post about updates reverting the port, is there any way to lock this down so we don't have to do this every time there's an update?
User avatar
Freeco
Experienced
Experienced
Posts: 111
Joined: Wed Dec 24, 2008 12:11 pm
Location: Belgium
Contact:

Re: MariaDB 10

Postby Freeco » Fri Jul 14, 2017 11:28 am

DavePDX wrote:
rtoussaint wrote:Or change the port of MariaDB 10 back to 3306 on your Synology.


Is there a way to do this from the GUI or do we need to manually edit the cfg file? And in light of this post about updates reverting the port, is there any way to lock this down so we don't have to do this every time there's an update?

Let's make a feature request to Synology :)

This morning I've uninstalled MariaDB 5 & 10 (& phpMyAdmin), and only reinstalled MariaDB 10. The used port is still 3307 :| Seems that port is hardcoded in the package.
For the time being I've edited my advancedsettings.xml on my Kodi HTPC's, and will edit my DB connection php files for my dynamic websites to use TCP3307.

Model: DS414 (1.33GHz dualcore - 1GB RAM)
Firmware: DSM 6.1
RAID1: 3 x WD Red 4TB (WD40EFRX)
Network: Netgear GS724T-300
Backup: Sharkoon SATA QuickDeck Pro USB3 + Seagate Barracuda 7200.14 3TB (ST3000DM001)

Sold: DS212 & DS209+
pedro_ady
I'm New!
I'm New!
Posts: 1
Joined: Sat Jul 15, 2017 7:38 pm

Re: MariaDB 10

Postby pedro_ady » Sat Jul 15, 2017 7:45 pm

Edit config.inc.php, add selector between MariaDB 5.5 and MariaDB 10 for PHPMyAdmin



/**
* First server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['AllowSynoRoot'] = true;
$cfg['Servers'][$i]['host'] = 'localhost:3306';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
/*
* Second server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['AllowSynoRoot'] = true;
$cfg['Servers'][$i]['host'] = 'localhost:3307';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
/*
Akirainblack
I'm New!
I'm New!
Posts: 5
Joined: Mon Feb 02, 2015 4:03 pm

Re: MariaDB 10

Postby Akirainblack » Mon Jul 24, 2017 2:28 pm

wags1 wrote:For Kodi Users -

If you are using MariaDB 5.5 for your Kodi database and are trying to figure out how to migrate your database to MariaDB 10 here is what I did to get it migrated and working. There are, I'm sure, easier and quicker ways to do this but this is what worked for me. I hope it helps someone.

1 - Using the Synology package center install MariaDB 10 and then upgrade PHPMyAdmin.
2 - Using PHPMyAdmin login to MariaDB 5.5 (if both 10 and 5.5 are running you should have a selection box at the bottom of the PHPMyAdmin login screen).
3 - On the home screen click on Export, accept the defaults and click on Go. This will export your existing databases to your browsers download folder.
4 - Click on the logoff icon.
5 - Using PHPMyAdmin login to MariaDB 10. Note - the default login is root with no password. After your first login you should change it for security.
6 - If your Kodi databases were using a userid and password complete the following step, if not skip it - Click on User Accounts and create an account with the exact same username, password, and privileges as your were using with MariaDB5.5.
7 - Click on Import and point to the file you exported in step 3 and accept the defaults. Once the import competes you should see your Kodi databases in the left pane.
8 - If you created a username and password in step 6 click on Databases and then click on Privileges next to each of your Kodi databases to ensure that the user that you created is there and has the correct privileges.
9 - Click on the logoff icon.
10 - Using the Synology Package Center stop MariaDB 5.5.
11 - For each of your Kodi systems you will need to edit your advancedsettings xml file and change the port setting from 3306 to 3307.
12 - Run each of your Kodi systems to test and ensure they are working correctly.
13 - If everything is working you can uninstall MariaDB 5.5 and delete the databases associated with that server.

That's it, at that point you should be good to go.


Every time I attempt Step 7 it starts the import and then I get an ngix error, any ideas as to how to get past this?
wags1
Trainee
Trainee
Posts: 15
Joined: Tue Sep 10, 2013 6:43 pm

Re: MariaDB 10

Postby wags1 » Wed Jul 26, 2017 2:00 pm

The first thing that I would try would be to delete the exported file and start again at step 3.
Akirainblack
I'm New!
I'm New!
Posts: 5
Joined: Mon Feb 02, 2015 4:03 pm

Re: MariaDB 10

Postby Akirainblack » Thu Jul 27, 2017 11:09 am

I've tried with multiple different exports now, still no go, always that error.
I've even tried compressing it to .sql.zip and uploading that, still the same error.
I've increased the timeout in case that was causing it, still the same. :(
Any other ideas?

I finally managed it.
I had to import my database using heidisql.
Once I did that it imported correctly and is now working perfectly! :)
Thanks for the guide and help.
wags1
Trainee
Trainee
Posts: 15
Joined: Tue Sep 10, 2013 6:43 pm

Re: MariaDB 10

Postby wags1 » Thu Jul 27, 2017 2:17 pm

Glad it finally forked for you. One question, had you upgraded PHPMyAdmin to the latest release before you did the export/import?

Return to “Web Server + PHP / MySQL + MariaDB”

Who is online

Users browsing this forum: No registered users and 2 guests