Solved Convert mysql to mariadb working procesure please.

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
13,529
Location
Maastricht
Since I had troubles upgrading on one server, I tried to upgrade mysql 5.7 to Mariadb 10.2 to be prepared on a test server.

I followed this procedure:
https://help.directadmin.com/item.php?id=676

The only difference is that i used:
./build set mariadb 10.2 in step 4.

For the rest I used exactly every line the same. But again no mysql starting and only errors. Systemctl status mysqld.service showed this:
Code: 
mv: cannot stat '/etc/systemd/system/mysql.service': No such file or directory
Created symlink from /etc/systemd/system/mysql.service to /etc/systemd/system/mysqld.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /etc/systemd/system/mysqld.service.
Ensuring local-infile is disabled for security reasons in MySQL configuration file...
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
Giving mysqld a few seconds to start up...
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
FATAL ERROR: Upgrade failed
/usr/bin/mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) when trying to connect
cp: '/usr/lib/libmysqlclient.so' and '/usr/lib/mysql/libmysqlclient.so' are the same file
Restarting MySQL.
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
Installation completed.

And journalctl -xe said the same.

So what is happening. Why is this conversion failing every time?

Does it need to be first to 10.0, then 10.1 and then 10.2 and then restore the backups?
Which I tried to and to 10.0 goes almost fine and from 10.0 to 10.1 gives the errors again. I'm starting to doubt about custombuild conversions like this.
 
Last edited:
Therer stated :
I do not recommend going directly from MySQL 5.5 to MariaDB 10.x, even though it might work.
Click to expand...

And the MYSQL 5.7 is different to then 5.5

Websearch i did before once find some others, don;t have them

https://mariadb.com/kb/en/library/upgrading-from-mysql-57-to-mariadb-102/

Upgrading from MySQL 5.7 to MariaDB 10.2

Following compatibility report was done on 10.2.4 and may get some fixing in next minor releases

MySQL unix socket plugin can be different. MariaDB can get similar usage via INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; you may have to enable this plugin in config files via load plugin.

When using data type JSON , one should convert type to TEXT, virtual generated column works the same after.

When using InnoDB FULLTEXT index one should not use innodb_defragment
Click to expand...

ON the other hand 5.7 is same base version as 10.2
Within the same base version (for example MySQL 5.5 -> MariaDB 5.5, MySQL 5.6 -> MariaDB 10.0 and MySQL 5.7 -> MariaDB 10.2) you can in most cases just uninstall MySQL and install MariaDB and you are good to go. There is no need to dump and restore databases
Click to expand...
https://mariadb.com/kb/en/library/upgrading-from-mysql-to-mariadb/
 
Last edited:
That's what I found too.

But it's going wrong every time, that's why I now want to know what the exact correct procedure is. Especially because I've got a working mariadb on my test server now after some issues, but I can't connect to it because it keeps complaining about a non existing mysql.sock, which indeed does not exist.
 
Did SMTALK found out something. ?

Mostly shortly after he solved something, there is a kind of update for it in Custombuild.
 
Last edited:
Thank you, but I really would like some answers from smtalk or DA support. It was no password issue, I do have a mysql root password and it's a new installation on my test server.

A proper procedure on how to do this is needed. As a proper procedure to revert to mysql 5.7 because I can't seem to get that either.
So with custombuild.
 
The issue is that mysql 10.0 (which I have to start with) is not compatible with mysql 5.7. So this will give a fail.
So I know how to make mysql dumps or let custombuild create the backup, no issue there. I know how to restore dumps, there is even a DA script for it, no issue there either.

But when I convert (on another server) from Mysql 5.7 via Mariadb 10.0 (will throw errors because can not restore, not compatible) -> 10.1 to 10.2, how do I also get the passwords back? So when the dumps are restored in 10.2 that the sites will work again?

What is the best exact procedure to convert from Myql 5.7 to MariaDB 10.2 so I won't get into trouble? If possible in a (go there, do that way of writing).
I presume staring with a mysql backup?
Set mysql backup also in custombuild.
Then:
Code: 
./build set mariadb 10.0
./build set mysql_inst mariadb
./build update
./build mariadb

Hopefully that will work without errors (last time mysql went down with errors).
Then do the build until I have 10.2.

But then... I can use the restore script, but I will most certainly start complaining about maybe innodb not present or it will not recognize passwords maybe.
What is the best way to proceed when 10.2 is installed?
 
Hello did you find a solution?
I need to convert from MySQL 5.7.40 to MariaDB 10.3, but I can't find any documentation.
 
Backup all databases with their configs. Reinstall Mysql to Mariadb. Import databases.
 
Last edited:
Alessio said:
Hello did you find a solution?
I need to convert from MySQL 5.7.40 to MariaDB 10.3, but I can't find any documentation.
Click to expand...

Following the advice of the user @Zhenyapan, in relation to Directadmin the steps will be the same as in the post #6. Just keep in mind that you need to change a mariadb version. For it replace 10.0 with 10.3 or even 10.4 (if you do a full reinstall, or consider all risks for skipping release in performing the upgrade).

The steps might be missing instructions on how to dump and restore databases, so check these article:

- https://help.poralix.com/articles/how-to-manage-sql-dumps-console-directadmin

Though the suggest way to move to MariaDB 10.3 should be fine for most cases (considering all risks for skipping releases in performing such an upgrade), you might for better upgrade one major release per time:

- from MariaDB 5.7 to MariaDB 10.0
- from MariaDB 10.0 to MariaDB 10.1
- from MariaDB 10.1 to MariaDB 10.2
- from MariaDB 10.2 to MariaDB 10.3

using the steps from the post #6.
 
Alessio said:
Thanks for your replies, I followed the docs:
Click to expand...
No you didn't. ;)

Upgrading MariaDB package should be done per versions so mysql_upgrade can perform necessary tasks, example upgrade path from MySQL 5.5 to MariaDB 10.3 :

MySQL 5.5 > MariaDB 5.5 > MariaDB 10.0 > MariaDB 10.1 > MariaDB 10.2 > MariaDB 10.3​

Click to expand...
So you're lucky all is working fine.
 
You wrote:
Alessio said:
I changed ./build set mariadb 5.5 in to ./build set mariadb 10.3
Click to expand...
So typo on your side then.

Still lucky all went well because in the docs it says:
5.710.2

Not 10.3. ;)
It's not for you, but for others reading this, that they can better use the correct way, just if they have something else, they won't run into issues.
However, 5.7 to 10.3 is also possible according to MariaDB indeed.
 
Thanks for the clarification, however I had a snapshot of the vps and database backups, in case of problems I could restore the previous situation.
Anyway I tested all the Drupal and WordPress sites on the VPS and everything seems to be working, in fact they seem to be faster.

P.S.
You wrote:
Alessio said:
I changed ./build set mariadb 5.5 in to ./build set mariadb 10.3
Click to expand...
So typo on your side then.
Click to expand...
It is not a typo: the documentation indicate a conversion from MySQL 5.5 to MariaDB 5.5,
I made a conversion from MySQL 5.7 to MariaDB 10.3, so I specified that instead of writing ./build set mariadb 5.5 you should change to ./build set mariadb 10.3, may be like you said it is more safe to convert to mariadb10.2 and then update to mariadb10.3 or a newer version.

​

 
Last edited:
Alessio said:
It is not a typo: the documentation indicate a conversion from MySQL 5.5 to MariaDB 5.5,
Click to expand...
Yep, sorry. I totally misread indeed. I mistakenly read mysql 5.5 instead of mariadb 5.5 my apologies for that.
The docs make more steps between the mariadb versions updates, which is safer due to some incompatibilities which might occur on some systems.
But one can always try on own risk.

I also got the impression that MariaDB seems a bit faster for Wordpress sites.
 
Now that I have MariaDB 10.3x intalled and running for a few days, what version of MariaDB do you think I should use? From what I see 10.06 is out in 2021 and will have long term support ( until July 2026) , maybe it is the best choice?

I have all WordPress and Drupal sites all updated to the latest version, the minimum suggested requirements for the database is MariaDB 10.37 or higher.
 
Be sure to use the renaming option mentioned in the docs after each upgrade:
mv mysql_backups mysql_backups.`date +%F`
or use a version number in that name.
This way you can go back to any version you upgraded in between, if needed afterwards.
 
You must log in or register to reply here.
Back
Top