SFEmysql80 - MySQL 8.0

  • Posted on: 7 April 2023
  • By: tomww

Documentation can always be enhanced, please help with your ideas!

Use Cases for MySQL 8.0 on Solaris (...)
See https://sfe.opencsw.org/SFEmysql80-use-cases

Note: Installing mysql-80 may set your mediator from mysql-5.5 to mysql-8.0
If you want continue to use mysql-5.5 then you need to set back the mediator mysql to 5.5.
If you want to migrate, you need to either snapshot / copy / rename your old data directory or do a full dump / restore cycle.

First steps
Have the SFE publisher configured for your OS version (see quicklinks above on the right or use search).
pkg install -v mysql-80
Make sure, you have installed at minimum these package timestamps:g>
S11.4 GA - mysql-80@8.0.27-0.11.4.0.0.1.15.0:20220114T130636Z
or
S11.4+SRU - mysql-80@8.0.27-0.11.4.39.0.1.107.1:20220114T001032Z
or
S11.3+SRU - mysql-80@8.0.27,5.11-0.0.175.3.33.0.2.0:20220114T191436Z

mysql-80 for OmniOS and OpenIndiana will take a little while to appear, stay tuned.

If you want an empty database to start with, then just enable the SMF Service:
svcadm enable -s mysql:version_80
This will initialize an *empty* database for you with a random password.
You can query the password from the server logfile:
grep password /var/mysql/8.0/data/${HOSTNAME}.err

Upgrade MySQL 5.7 to MySQL 8.0
If you want to upgrade your database, you have two choices:
1) export the database, disable SMF mysql:version_57, then enable the SMF mysql:version_80, restore the database into the new instance. Beware of possibly changed mysql authentication mechanisms for/in your application.
2) disable SMF mysql:version_57, then transfer all files from /var/mysql/5.7/data over to /var/mysql/8.0/data, then enable the SMF mysql:version_80

More then one Database Instance on the System
You may add more instances in SMF with separate my.cnf, data and mysql.sock.
This is done with another instance of the SMF Service.
(How this can be configured is to be determined by volunteers please).

Surprises by IPS pkg uninstall
Don't be too surprised by a design decision in IPS: If you uninstall a package and the data directory is maintained in the package: Once you run "pkg uninstall mysql-80" all your old database files are removed from the data directory.
IThey are not deleted, but all files moved to /var/pkg/lost+found/var/
Copy the database back yourself in a safe way, but be sure to first stop running SMF service mysql:version_80.

Packages Names
database/mysql-80. (Server, pulls in client and library)
database/mysql-80/client
database/mysql-80/library
sfe/database/mysql-common (have mysql-common version 5.7 or version 8.0 installed)

database/mysql-80/test (optional. Is anybody so keen to run as much tests as possible?)

Autocreate Database -> Random Password Assigned
Beware, access to MySQL is password protected and you need to initially change that password with a client that allows expired passwords, e.g.
mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';

(Note: "mysqladmin" does not allow you changing an expired password, use "mysql" above).

For your login with "mysql" the initial temporary password is accessible with:
grep password /var/mysql/8.0/data/${HOSTNAME}.err

Database Conversion for Upgrades
If you fill /data/*/* with old database files before starting the daemon then you may need an extra upgrade step with mysqld (that procedure changed a short while ago, read the official mysql docs or even better volunteer and tell us the procedure you've tested, so we can include this here in the doc - thanks!).

Authentication with PHP
Make sure for PHP that the mysql authentication mechanism fits.
(any hints what to do for e.g. NextCloud?)

Have Separate ZFS Filesystem for /var/mysql/8.0/data/
Again, IPS may annoy you a bit, but this only affects the "pkg uninstall".
For new install or for upgrades, all is fine with IPS.
Danger: You are manipulating your data files! STOP the database server processes *completely* before doing this. Have a working backup of the files created *after* the daemon stopping. Verify your backup! Then you may continue on your own risk.


svcad, disable -s mysql:version_80
zfs create -o canmount=off -o mountpoint=/var ZFSPOOLNAME/var
zfs create -p ZFSPOOLNAME/var/mysql/8.0/data
#make sure, this is your ZFS filesystem: df -h /var/mysql/8.0/data
chown 70:70 -R /var/mysql/
(or: chown mysql:mysql -R /var/mysql/ in case you have installed mysql-common)
#now move in your old database files from 5.7 and start SMF *or* dont copy but initialize a new/empty database by
svcadm enable -s mysql:version_80

Release Binaries Without Debug Symbols
Finally packages starting with timestamp 20220113T........ will have stripped binaries. That is, all debug information ist removed. The size of e.g. the mysqld file goes down from 1Gigabyte to 69Megabytes. In the rare case that you need debug binaries then please drop me a note. I can run pkgtool --with_debug build SFEmysql80.spec for you to get a package containing debug information (you can see the code in the debugger runs).
Usually binaries without debug information run faster and load faster. The packages size is 250MB non-debug and 850Mb for debug.

Any more useful tipps? Please me drop a note. We could create a sub-page to store other use cases, step-guides and so on.

This document is meant to be edited by the community. Create an account here on the blog and ping me for assigning edit permissions. Alternatively send in your ideas/tipps/scripts via comments or by email to sfepackages at g mail dot com . The community with overload you with appreciation! Me too.
Version: 2022-01-12 10:41:00 +0100

Comments

The package mysql-80 starting with timestamp 20220113T...... have "strip"ped binaries, that means they run faster and are much smaller in size. Just do a "pfexec pkg update -v" (or run "-nv" and select pkgs to upgrade manually on the command line).

[SOLVED in mysql-80@8.0.28.0.1]
Hi!
Currently there is a problem with the Solaris build mysql 8.0.28. If you can, run version mysql 8.0.27 instead.
Unfortunatly 8.0.27 has an older database format.

Currently I'm trying to find the root of the crash in mysql 8.0.28 in case you use "ALTER TABLE" to add an index.
One of the attempts is a binary with *no* optimization "-O0 -gdwarf-4" and debug info. That works (you find the 8.0.28 "-O0" package in the "Summary").

Another attempt is using the Solaris provided compiler gcc-11.2 (that doesn't contain the SFE changes for the runtime search directories).

Will post updates mainly on twitter @sfepackages and finally here in the blog.

Sorry for any inconveniences this mysql oddities provide!

Regards
Thomas