Koozali.org formerly Contribs.org

Software collections and mysql 5.7

Software collections and mysql 5.7
« on: March 08, 2019, 10:05:50 PM »
Hi everyone
I have (my own this time) a server that I use to run a forum (thesecurityinstaller.co.uk)
I update the site as new versions come out etc and its been moaning for a while now about MySQL versions. Now it wont update until I update MySQL.
Looking at it the software collections way seems the advised way to go. I have installed this and can see it in phpMyAdmin
However I have a few questions if I may :-

1. I want to move / upgrade the existing db is there a simple way to do this as I need to keep the existing data. I cant see a way to do it in php myadmin

2. is this 5.7 MySQL backed up by affa, im assuming the MySQL dump just does the native one?

3. should I not be using this way and just upgrading the current MySQL?

Thanks
James
the above may work with a bit of help, down hill with the wind behind it

Offline mmccarn

  • *
  • 2,391
Re: Software collections and mysql 5.7
« Reply #1 on: March 09, 2019, 02:35:10 PM »
1. move data
You need to use a different connection string to connect to mysl57.  You *could* do this with phpmyadmin, but you'd have to backup the current db, then reconfigure phpmyadmin to talk to the new version, then do the restore (I'm curious, so I plan to play with this... more info coming)

After installing mysql57 you get to it from the cli using "mysql57..." instead of "mysql...".  I moved my databases by running mysqldump on the default database, then restoring it using "mysql57..." -- usually after googling "backup and restore mysql"...

From here: http://webcheatsheet.com/SQL/mysql_backup_restore.php (modified for SME, but UNTESTED):
Code: [Select]
# sme server stores root password in /root/.my.cnf so you don't need "-u" or "-p"
mysqldump [dbname] > [dbname].bak

# now you have to create the database in mysql57
#
# either set these values using variables or hard code them into the commands below...
DBNAME=[dbname]
DBUSER=[dbuser]
DBPASS=[dbpassword]
mysql57 -e "drop database IF EXISTS $DBNAME"
mysql57 -e "create database $DBNAME COLLATE=utf8_general_ci"
mysql57 -e "grant all privileges on $DBNAME.* to $DBUSER@localhost identified by '$DBPASS' with grant option"

#
# finally, restore your mysqldump file from the file created above
mysql57 < [dbname].bak

I've never used it, buy you may be able to use "mysqlimport" to create and load the backup in one step.  You'll need to tell mysqlimport to use the mysql57 socket:
Code: [Select]
mysqlimport --socket=/var/lib/mysql/mysql57.sock [dbname] [dbname].bak


2. Affa backups
The smeserver-mysql57 contrib used by the mysql 5.7 software collection contrib adds a new action to the e-smith "pre-backup" event (/etc/e-smith/events/pre-backup/S20mysql57-dump-tables) that dumps the new databases into /home/e-smith/db/mysql57/. 

The Affa page says that if your config has "SMEServer=yes" then the backup will include "the default directories".  This wiki page indicates that "/home/e-smith" is included in "the default directories": https://wiki.contribs.org/Backup_server_config#Standard_backup_.26_restore_inclusions

3. Upgrade MySQL
I think doing this breaks your server. There's a wiki page with some notes (https://wiki.contribs.org/Upgrade_php/mysql).  I suspect your database backups are less likely to work if you do this than if you install the mysql57 software collection.


Offline mmccarn

  • *
  • 2,391
Re: Software collections and mysql 5.7
« Reply #2 on: March 09, 2019, 02:55:41 PM »
On my SME 9.2 server -- with phpmyadmin installed after mysql57 via software collections, I get to choose the "server" at the top left to connect to either "localhost (root)" or "Mysql57 (root)".

It looks like you should be able to backup while connected to "localhost (root)" then change the server to "Mysql57 (root)" and restore.

Offline ReetP

  • *
  • 1,979
Re: Software collections and mysql 5.7
« Reply #3 on: March 09, 2019, 07:12:37 PM »
PLEASE use software collections........ don't just try and upgrade.

From experience with mysql55 I just did a dump of the old DB, created a new one as Mike indicated, and then imported it at a command prompt:

e.g.

Code: [Select]
mysql55 crm < ~/daily_crm_2019-02-01_06h01m_Friday.sql
Note... read the wiki carefully.

If your application can't use a socket then you will need to set this with the right version number so you get a port:

Code: [Select]
mysql5x-mysqld LocalNetworkingOnly no
There is also a nasty gotcha in PHP where it will fail if you use a port like localhost:3307

Make sure you use 127.0.0.1:3307

Backups - automysqlbackup should work for all installable versions of mysql- 53, 55 and 57 + mariadb. Let us know if it doesn’t.....

...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline Jean-Philippe Pialasse

  • *
  • 1,284
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: Software collections and mysql 5.7
« Reply #4 on: March 09, 2019, 08:47:38 PM »
only limit with the phpmyadmin contrib, the user management is not working for mysql57. (reason : need a higher version, but it would not work with older mysql)
appart from that, everything is working the same way as it should with the base mysql.

backups....

if not just open a bug, or ask here


Re: Software collections and mysql 5.7
« Reply #5 on: March 15, 2019, 05:54:18 PM »
Quote
# sme server stores root password in /root/.my.cnf so you don't need "-u" or "-p"
mysqldump [dbname] > [dbname].bak

# now you have to create the database in mysql57
#
# either set these values using variables or hard code them into the commands below...
DBNAME=[dbname]
DBUSER=[dbuser]
DBPASS=[dbpassword]
mysql57 -e "drop database IF EXISTS $DBNAME"
mysql57 -e "create database $DBNAME COLLATE=utf8_general_ci"
mysql57 -e "grant all privileges on $DBNAME.* to $DBUSER@localhost identified by '$DBPASS' with grant option"

#
# finally, restore your mysqldump file from the file created above
mysql57 < [dbname].bak
I did the above but stupidly used the root password and its not what i used and it looks like i have managed to change it.
I tried following the wiki to reset the root password but
Quote
service mysql57-mysqld stop
expand-template /root/.my.cnf
expand-template /var/service/mysql57-mysqld/set.password
/opt/rh/mysql57/root/usr/libexec/mysqld --socket=/var/lib/mysql/mysql57.sock --bootstrap --user=mysql --skip-grant-tables < /var/service/mysql57-mysqld/set.password
service mysql57-mysqld start

Quote
-bash: /opt/rh/mysql57/root/usr/libexec/mysqld: No such file or directory
[root@tsi ~]# /mysql57-mysqld/set.password
-bash: /mysql57-mysqld/set.password: No such file or directory
the above may work with a bit of help, down hill with the wind behind it

Offline mmccarn

  • *
  • 2,391
Re: Software collections and mysql 5.7
« Reply #6 on: March 15, 2019, 10:56:37 PM »
On my server the path to mysql57 is slightly different from what you found in the wiki.

Instead of this:
Code: [Select]
/opt/rh/mysql57/root/usr/libexec/mysqld --socket=/var/lib/mysql/mysql57.sock --bootstrap --user=mysql --skip-grant-tables < /var/service/mysql57-mysqld/set.password
Try this:
Code: [Select]
export LD_LIBRARY_PATH=/opt/rh/rh-mysql57/root/usr/lib64
/opt/rh/rh-mysql57/root/usr/libexec/mysqld --socket=/var/lib/mysql/mysql57.sock --bootstrap --user=mysql --skip-grant-tables < /var/service/mysql57-mysqld/set.password

Before adding export LD_LIBRARY_PATH...  I was getting an error:
Code: [Select]
/opt/rh/rh-mysql57/root/usr/libexec/mysqld: error while loading shared libraries: liblz4.so.rh-mysql57-1: cannot open shared object file: No such file or directory

Offline Jean-Philippe Pialasse

  • *
  • 1,284
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: Software collections and mysql 5.7
« Reply #7 on: March 16, 2019, 01:39:43 PM »
Export is not the way to do


Scl need to be activated before you can call them

scl enable mysql57  bash
/opt/rh/mysql57/root/usr/libexec/mysqld --socket=/var/lib/mysql/mysql57.sock --bootstrap --user=mysql --skip-grant-tables < /var/service/mysql57-mysqld/set.password

exit

Wiki might need an update.