Koozali.org: home of the SME Server
Obsolete Releases => SME 9.x Contribs => Topic started by: jameswilson 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
-
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):
# 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:
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 (https://wiki.contribs.org/Software_Collections:MySQL57) 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 (https://wiki.contribs.org/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.
-
On my SME 9.2 server -- with phpmyadmin (https://wiki.contribs.org/PHPMyAdmin) installed after mysql57 via software collections (https://wiki.contribs.org/Software_Collections:MySQL57), 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.
-
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.
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:
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.....
-
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
-
# 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
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
-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
-
On my server the path to mysql57 is slightly different from what you found in the wiki.
Instead of this:/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:
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:
/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
-
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.
-
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?
-
One for Jean-Philippe
I have the following file:
/etc/profile.d/msql55.sh
It is not templated.
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)
-
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?
-
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.
-
As you can guess i made the mysql57 from mysql55 package and i miss this file ;)
-
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
scl enable mysql57 bash
Unable to open /etc/scl/prefixes/mysql57!
-
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 ???
-
ran as above
subtility; not mysql57 but rh-mysql57 .... love it when naming convention evolves ! this is one of the worst thing with scl. You start working one way, and they present it differently for the next version
scl enable rh-mysql57 bash
also I have checked the rpm does not provides a /etc/profile.d/mysql55.sh but a /etc/profile.d/mysql57.sh
If you have the first you might have installed also (or only) mysql55.
-
OK
so i have tried
service mysql57-mysqld stop
expand-template /root/.my.cnf
expand-template /var/service/mysql57-mysqld/set.password
/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
at this point i get
# /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
/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
-
Have you tried to find out what directories you have there?
Work your way down (or up) and see what you can see - what is there? What is missing?eg
cd /opt/rh/rh-mysql57/root/usr/cd /opt/rh/rh-mysql57/root/cd /opt/rh/rh-mysql57/cd /opt/rh/
or
ll /opt/rh/rh-mysql57ll /opt/rh/
or use mc to browse about....
Or say:
cd /opt/rh
find -iname mysqld
A little bit of thought and looking may give you some answers rather than blindly following what others paste.
(people make typos etc, so always worth having a look around)
-
While the exercise is interesting....
Trying to get back to the initial problem ie migrating db from mysql51 to mysql57
Signal-event pre-backup
Install smeserver-mysql57, just the yum step
cp /home/e-smith/db/mysql/*.dump /home/e-smith/db/mysql57
Then finish the install of mysql57 by doing the extra steps on wiki. It will do as you were restoring backups on a fresh sme
-
Have you tried to find out what directories you have there?
Work your way down (or up) and see what you can see - what is there? What is missing?eg
cd /opt/rh/rh-mysql57/root/usr/cd /opt/rh/rh-mysql57/root/cd /opt/rh/rh-mysql57/cd /opt/rh/
or
ll /opt/rh/rh-mysql57ll /opt/rh/
or use mc to browse about....
Or say:
cd /opt/rh
find -iname mysqld
A little bit of thought and looking may give you some answers rather than blindly following what others paste.
(people make typos etc, so always worth having a look around)
Thanks I have done that and found a typo but as abive the error relating to file not found etc is called by another
liblz4.so.rh-mysql57-1 so i cant edit the command as such
-
While the exercise is interesting....
Trying to get back to the initial problem ie migrating db from mysql51 to mysql57
Signal-event pre-backup
Install smeserver-mysql57, just the yum step
cp /home/e-smith/db/mysql/*.dump /home/e-smith/db/mysql57
Then finish the install of mysql57 by doing the extra steps on wiki. It will do as you were restoring backups on a fresh sme
That looks brilliant but until i fix the root password issue i assume i shoulndt try it?
-
That looks brilliant but until i fix the root password issue i assume i shoulndt try it?
the root password issue should fix by itself with the template expanding, and might have occurred by trying to play with it in the first place.
just tested:
https://wiki.contribs.org/Software_Collections:MySQL57
yum install --enablerepo=smecontribs smeserver-mysql57
signal-event post-upgrade ; signal-event reboot
then
https://wiki.contribs.org/Software_Collections:MySQL57#Resetting_the_MySQL_root_password
service mysql57-mysqld stop
expand-template /root/.my.cnf
expand-template /var/service/mysql57-mysqld/set.password
scl enable rh-mysql57 bash
/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
exit
service mysql57-mysqld start
-
Thats brilliant and worked a treat. Im unsure why it didnt before. But yes all reset and working correctly.