Koozali.org: home of the SME Server

Software collections and mysql 5.7

Offline jameswilson

  • ****
  • 739
  • +0/-0
    • Security Warehouse, trade security equipment
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

Offline mmccarn

  • *
  • 2,626
  • +10/-0
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,626
  • +10/-0
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

  • *
  • 3,722
  • +5/-0
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 of software
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

  • *
  • 2,746
  • +11/-0
  • 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


Offline jameswilson

  • ****
  • 739
  • +0/-0
    • Security Warehouse, trade security equipment
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

Offline mmccarn

  • *
  • 2,626
  • +10/-0
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

  • *
  • 2,746
  • +11/-0
  • 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.

Offline jameswilson

  • ****
  • 739
  • +0/-0
    • Security Warehouse, trade security equipment
Re: Software collections and mysql 5.7
« Reply #8 on: May 19, 2019, 08:47:45 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.
so to be clear i use the above command instead of the one above your post?

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: Software collections and mysql 5.7
« Reply #9 on: May 19, 2019, 11:52:15 PM »
One for Jean-Philippe

I have the following file:
/etc/profile.d/msql55.sh

It is not templated.

Code: [Select]
alias mysql55 >/dev/null 2>&1 || alias mysql55="/opt/rh/mysql55/root/usr/bin/mysql  --socket=/var/lib/mysql/mysql55.sock"
alias mysqlshow55 >/dev/null 2>&1 || alias mysqlshow55="/opt/rh/mysql55/root/usr/bin/mysqlshow  --socket=/var/lib/mysql/mysql55.sock"
alias mysqladmin55 >/dev/null 2>&1 || alias mysqladmin55="/opt/rh/mysql55/root/usr/bin/mysqladmin  --socket=/var/lib/mysql/mysql55.sock"
alias mysqldump55 >/dev/null 2>&1 || alias mysqldump55="/opt/rh/mysql55/root/usr/bin/mysqldump  --socket=/var/lib/mysql/mysql55.sock"
alias mysqlimport55 >/dev/null 2>&1 || alias mysqlimport55="/opt/rh/mysql55/root/usr/bin/mysqlimport  --socket=/var/lib/mysql/mysql55.sock"
alias mysqlbinlog55 >/dev/null 2>&1 || alias mysqlbinlog55="/opt/rh/mysql55/root/usr/bin/mysqlbinlog  --socket=/var/lib/mysql/mysql55.sock"

Any relationship with mysql57 ? (Just curious here)
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
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

  • *
  • 2,746
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: Software collections and mysql 5.7
« Reply #10 on: May 20, 2019, 01:57:12 PM »
Yes. They are a bunch of alias to make your life easy

But seems they are refering to mysql 55 not 57. So this is a bug.

Can you post it on our bugzilla?

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: Software collections and mysql 5.7
« Reply #11 on: May 20, 2019, 02:06:47 PM »
Yup when I get a minute.

Bit frantic here at the minute.

Will try & look at the mysql55 rpms to see what else gets installed.
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
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

  • *
  • 2,746
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: Software collections and mysql 5.7
« Reply #12 on: May 20, 2019, 08:24:25 PM »
As you can guess i made the mysql57 from mysql55 package and i miss this file ;)

Offline jameswilson

  • ****
  • 739
  • +0/-0
    • Security Warehouse, trade security equipment
Re: Software collections and mysql 5.7
« Reply #13 on: May 21, 2019, 09:57:17 AM »
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


ran as above

Quote
scl enable mysql57  bash
Unable to open /etc/scl/prefixes/mysql57!

Offline janet

  • ****
  • 4,812
  • +0/-0
Re: Software collections and mysql 5.7
« Reply #14 on: May 21, 2019, 02:18:32 PM »
jameswilson

I notice there are 2 spaces between mysql57 and bash, perhaps a typo.
Maybe if you make it one space the command may run OK ???
« Last Edit: May 22, 2019, 04:42:21 AM by janet »
Please search before asking, an answer may already exist.
The Search & other links to useful information are at top of Forum.