Heads-up: MySQL default Version will switch to 8.0

If you're migrating from mysql57-* to mysql80-*, watch out for other ports which may be linked to the shared MySQL 5.7 library:

Code: 
[root@host ~]# pkg shlib -qR libmysqlclient.so.20

If this gives you any results, those ports will probably need to be rebuilt after you upgrade MySQL. I got snagged by this with dns/bind916, which I have configured with the DLZ_MYSQL option enabled. When I rebooted that machine this afternoon, the named service didn't start, and upon investigating, it was still looking for the old MySQL 5.7 library:

Code: 
[root@host /var/log/named]# service named start
Starting named.
ld-elf.so.1: Shared object "libmysqlclient.so.20" not found, required by "named"
/usr/local/etc/rc.d/named: WARNING: failed to start named

This was fixed by doing a make clean deinstall reinstall on dns/bind916, which linked it against the new library.

I compile ports from source instead of using pkg-add(8), YMMV.
 
Last edited by a moderator:
Also another one I got bitten by without realising this change here, was when I updated to 13.2 - AFAICT there is not a wsrep patched MySQL 8 in packages or ports, so if you have a galera cluster for example, you may not have one after this update.

It would be good to know if there’s now an alternate way forward for Galera clusters on MySQL 8 using ports/packages (advice/guidance very welcome). I am unsure of that at this point.

Although I’ve used MySQL for decades, the Galera cluster I had setup recently to back PowerDNS is new to me, I’m definitely still a bit of a newbie on the wsrep/Galera side.

What I can say is my newly updated MySQL 8 / galera26 (updated the Galera library too) wouldn’t start with the wsrep configuration from MySQL 5.7 in place. I suspect we still do need wsrep but not sure what happened there on the ports/packages side.

Will need further investigation and planning on my side before I bring up the production nodes, as I need a path forward where Galera will still work. There may be an obvious path, I’m just not aware of it as yet - so at least this may serve as a warning to others, while I/we figure it out.
 
GMO said:
It would be good to know if there’s now an alternate way forward for Galera clusters on MySQL 8 using ports/packages (advice/guidance very welcome). I am unsure of that at this point.
Click to expand...
MariaDB (mariadb106-server-10.6.12) comes with galera26. I've been using it at work for several years (on Linux, though), and it's been great. For most intents and purposes, MariaDB is a plug-in replacement for MySQL, at least for 5.7 and earlier.

MySQL has the InnoDB Cluster as well which is a similar multi-master technology to Galera. I don't know whether it works on FreeBSD, but maybe?
 
I've been seeing the notice

Code: 
mysql57-client-5.7.41: Tag: expiration_date Value: 2023-12-31
mysql57-server-5.7.41: Tag: expiration_date Value: 2023-12-31
mysql57-client-5.7.41: Tag: deprecated Value: Upstream support ended in October 2019
mysql57-server-5.7.41: Tag: deprecated Value: Upstream support ended in October 2019

in my daily security run for a while. A few of observations:

1) The current upstream version is 5.7.42 (April 20, 2023).

2) Well before the announcement (about a year and a half ago), I had tried migrating my home server to databases/mysql80-server. After several weeks I dumped my databases and reinstalled databases/mysql57-server. Why? Even after researching and experimenting with various optimization tweaks I found simple insert commands to take over a minute which had previously taken a couple of seconds. I again tried this migration a couple of days ago with the current version of mysql80: same result.

3) Over the last couple of days I tried installing databases/maridab1011. It has no problem with my own DB, the one that caused all the problems with mysql80, but it couldn't handle the tables from my www/nextcloud DB due to a couple of tables with a JSON column. Yes, I looked at https://mariadb.org/making-mariadb-understand-mysql-json/. Furthermore, I had to make a number of changes in the /usr/local/etc/mysql/my.cnf (some are mentioned in previous posts). Unfortunately, mysqld fails to write anything to the error log when startup is aborted. However, you can debug any startup errors by running mysqld_safe with the --syslog option. The only way forward is to dump there all databases and start mariadb from scratch. Even this will run into remnants of mysql57, such as my.cnf.

I wish one could install them side-by-side to get things debugged, but that's a different approach than that of BSD ports.
 
Marius Schamschula said:
Why? Even after researching and experimenting with various optimization tweaks I found simple insert commands to take over a minute which had previously taken a couple of seconds. I again tried this migration a couple of days ago with the current version of mysql80: same result.
Click to expand...
Try on Linux to eliminate FreeBSD having anything to do with it.

If also same performance issues on Linux then you can ask on MySQL forums or raise with Oracle.

I‘ve not migrated any production servers to 8.0 yet but no issues thus far on development machines.
 
Alain De Vos said:
You can install them side by side in a different jail and configuring another port.
Click to expand...
I've been looking into that approach!

I'm still researching how to configure a jail with different packages than the root OS. At this point my only experiences with jails is for poudriere.

I guess I can also test the mysql57 vs mysql80 speed difference on my Mac, as I can install both side by side using MacPorts. The issue is that my Mac is a lot faster (M1 Ultra) than my FreeBSD Dell server (E5-2420 v2 @ 2.20GHz).
 
dbdemon said:
MySQL 8.0 is a wild ride. New features are being added continuously in patch releases - it's "evergreen" - no more semantic versioning here.

Additionally, downgrading to a previous patch release is no longer supported.
Click to expand...
Oracle has actually come to its senses and stopped adding new features as of 8.0.34. 8.0 is now an LTS release whereas development of new features has instead moved to the new Innovation releases, 8.1, 8.2 etc. Downgrading within 8.0 (starting with 8.0.34) is now also possible.
 
CyberCr33p said:
Some of my notes during 5.7 to 8.0 upgrade to hundreds of servers:

Don't forget to take backups:

mysqldump --login-path=local -uroot --add-drop-table --routines --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > /home/mysql-before-upgrade.sql

And for extra safety in case upgrade fails and you have to "rollback" to the older version (I wasn't using ZFS that time as it would be faster to use a snapshot):

mysql --login-path=local -uroot -e "SET GLOBAL innodb_fast_shutdown=0"
rsync --delete -a -v /var/db/mysql/ /home/mysql-before-upgrade/
service mysql-server stop
rsync --delete -a -v /var/db/mysql/ /home/mysql-before-upgrade/

In the above commands we run 2 times rsync (first one while MySQL is running) to minimise the downtime if you have big databases or slow disks.

Remove old version (it will remove mysql57-server too):

yes | pkg del mysql57-client

Replace old settings with new settings:

fetch -o /usr/local/etc/mysql/my.cnf https://example.com/mysql/my.cnf

I had build the new version to another server because portupgrade can take a lot of time to rebuild MySQL:

cd /tmp
fetch https://example.com/mysql/mysql80-server-8.0.28_2.pkg
fetch https://example.com/mysql/mysql80-client-8.0.28_1.pkg
pkg add mysql80-client-8.0.28_1.pkg
pkg add mysql80-server-8.0.28_2.pkg

Start MySQL server (it will take some time to finish so you can use tail -f /var/db/mysql/*.err to check how it proceeds):

service mysql-server start
Click to expand...
Followed pretty well everything you wrote above. After getting MySQL80 installed, and then starting it, only crashes. Errors from /var/db/mysql/name.err only display numbers really. The error log is useless to me.

The link you have above https://example.com/mysql/my.cnf doesn't work so you know.

Used the following for /usr/local/etc/mysql/my.cnf
Code: 
[mysqld]
disable-log-bin = 1
skip-name-resolve = 1
performance-schema = 0
local-infile = 0
mysqlx = 0
bind-address = 127.0.0.1
open_files_limit = 200000
max_allowed_packet = 256M
sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

innodb_dedicated_server = 1
innodb_buffer_pool_instances = 48
innodb_log_buffer_size = 64M
innodb_read_io_threads = 12
innodb_write_io_threads = 12
innodb_stats_on_metadata = 0
innodb_file_per_table = 1

max_connections = 20
thread_cache_size = 128
table_definition_cache = 65536
table_open_cache = 65536

wait_timeout = 10
connect_timeout = 5
interactive_timeout = 30

tmp_table_size = 128M
max_heap_table_size = 128M

read_buffer_size = 256K
join_buffer_size = 512K
sort_buffer_size = 512K
read_rnd_buffer_size = 512K

slow-query-log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql_slow_query.log
log-error = /var/db/mysql/host.some_domain.com.err
 
Ohh i see that your server have 64GB memory.

Note: you need at least 1GB memory per buffer pool instance and no more than 0.75 from the total memory for the buffer pool. So for 20 max connections it's a bit strange for me.

The server on the link that you provided is talking for about 9k-40k queries per sec with max connection of like 45000.
 
DutchDaemon said:
It is my understanding that there will be a final 5.7.43 port that can work with SSLv3 (5.7.42, the current version, certainly will not).
Click to expand...

Unfortunately, this is not the case:

Code: 
.if ( ${OPSYS} == FreeBSD && ${OSVERSION} >= 1400092 && ${SSL_DEFAULT} == base ) || ${SSL_DEFAULT:Mopenssl3*}
IGNORE= Upstream only supports OpenSSL 1.0.1 upto 1.1.X

This is for MySQL 5.7.44, the last one that will be in the ports tree for the 5.7 family.

So MySQL 8 is the only way forward if you want to run a modern SSL.

Note: the pre-requisite MySQL Client 5.7.44 refuses to build here:

Code: 
-- EDITLINE_INCLUDE_DIR /usr/local/include/editline
-- EDITLINE_LIBRARY /usr/local/lib/libedit.a
-- Performing Test EDITLINE_HAVE_HIST_ENTRY
-- Performing Test EDITLINE_HAVE_HIST_ENTRY - Success
-- Performing Test EDITLINE_HAVE_COMPLETION_INT
-- Performing Test EDITLINE_HAVE_COMPLETION_INT - Failed
-- Performing Test EDITLINE_HAVE_COMPLETION_CHAR
-- Performing Test EDITLINE_HAVE_COMPLETION_CHAR - Failed
CMake Error at cmake/readline.cmake:202 (MESSAGE):
  Cannot find system editline libraries.
Call Stack (most recent call first):
  CMakeLists.txt:605 (MYSQL_CHECK_EDITLINE)
 
You must log in or register to reply here.
Back
Top