[MacPorts] howto/MySQL modified
MacPorts Wiki
noreply at macports.org
Mon Aug 10 18:34:36 UTC 2026
Page "howto/MySQL" was changed by herbygillot
Diff URL: <https://trac.macports.org/wiki/howto/MySQL?action=diff&version=39>
Revision 39
Changes:
-------8<------8<------8<------8<------8<------8<------8<------8<--------
Index: howto/MySQL
=========================================================================
--- howto/MySQL (version: 38)
+++ howto/MySQL (version: 39)
@@ -10,27 +10,28 @@
= Versions = #versions
-MacPorts includes several versions of MySQL and some of its forks. You can install any or all of these versions simultaneously, though it is expected that you will only need to install one of them.
+MacPorts includes several versions of MySQL and some of its forks. You can install any or all of them simultaneously: the MacPorts default configuration disables networking, so several servers can be installed without contending for the same TCP port. Most people need only one.
* MySQL
- * '''mysql8''': MySQL v8.x. This is the latest stable version of MySQL made General Available in 2019-04-19
- * '''mysql57''': MySQL v5.7.x. Support ends Oct 2020.
- * '''mysql56''': MySQL v5.6.x. Support ended Feb 2018
- * '''mysql55''': MySQL v5.5.x. Support ended Dec 2015.
- * '''mysql51''': MySQL v5.1.x. This version reached [https://www.mysql.com/support/eol-notice.html end of life] at the end of 2013 and is therefore no longer recommended.
-
-* MariaDB: a fork of MySQL created by the original MySQL developer after MySQL AB's acquisition by Sun Microsystems
- * '''mariadb-10.4''': MariaDB v10.4.x. This is the latest stable version of MariaDB.
- * '''mariadb-10.3''': MariaDB v10.3.x. This is and older stable version of MariaDB.
- * '''mariadb-10.2''': MariaDB v10.2.x. This is the latest stable version of MariaDB which has been ported to MacPorts as of 7/8/19. Expected support end May 2023.
- * '''mariadb''': MariaDB v5.5.x. Support expected through 2020.
-
- * Percona: another MySQL fork
- * '''percona''': Percona Server v8.0.x.
-
-For each of the above ports, there is a "-server" companion port you should install if you want to run that server.
-
-This page shows how to install and use the mysql56 port, but you can use another version if you prefer; all of these ports use a similar directory layout.
+ * '''mysql8''': MySQL v8.4.x, the current long-term support (LTS) release. Choose this if you want the longest support window.
+ * '''mysql9''': MySQL v9.x, the newest release series.
+ * No longer supported upstream, kept for existing installations (see Oracle's [https://www.mysql.com/support/eol-notice.html end of life notice]):
+ * '''mysql57''': MySQL v5.7.x
+ * '''mysql56''': MySQL v5.6.x
+ * '''mysql55''': MySQL v5.5.x
+ * '''mysql51''' and '''mysql5''': MySQL v5.1.x
+ * '''mysql81''' and '''mysql82''' were short-lived Innovation releases and are now replaced by '''mysql8'''. If you still have one installed, migrate to '''mysql8'''.
+
+* MariaDB: a fork of MySQL created by the original MySQL developer after MySQL AB's acquisition by Sun Microsystems. MacPorts carries many parallel series, currently from '''mariadb-10.3''' through '''mariadb-12.3''', plus '''mariadb-devel''' and the legacy '''mariadb''' (v5.5.x). To see what is available:
+{{{
+$ port list 'mariadb*'
+}}}
+
+MacPorts no longer provides a Percona Server port. ('''percona-toolkit''' is still available, but it is a set of command-line tools, not a server.)
+
+For each of the server-capable ports above there is a "-server" companion port, which you should install if you want to run that server.
+
+The examples below use '''mysql8'''. Any other version works the same way — substitute its name throughout, as all of these ports use the same directory layout with the port name in the path.
= Installation =
== Step 1: Install MySQL == #mysql
@@ -40,41 +41,53 @@
$ sudo port install mysql8-server
}}}
-
-Select the installed version to be the default one
+Select the installed version to be the default one:
{{{
$ sudo port select mysql mysql8
}}}
-
-
-== Step 2: Create a database ==
-
-
-**For MySQL 5.7 and newer**
+This puts the MySQL commands on your `PATH` as `/opt/local/bin/mysql`, `/opt/local/bin/mysqladmin` and so on. To see the versions you can select:
+
+{{{
+$ port select --list mysql
+Available versions for mysql:
+ mysql8
+ mysql9
+ none (active)
+}}}
+
+The list shows the installed ports that participate in the group, so it grows as you install more; `none` means no version is currently selected.
+
+Note that '''mysql5''', '''mysql81''' and '''mysql82''' do not participate in `port select`; for those, call the commands by their full path under `/opt/local/lib/`''portname''`/bin/`.
+
+== Step 2: Initialize the database ==
+
+'''For MySQL 5.7 and newer:'''
{{{
$ sudo /opt/local/lib/mysql8/bin/mysqld --initialize --user=_mysql
}}}
-Make a note of the root user password which is auto-generated.
-
-**For MySQL 5.6 and earlier:**
-
-{{{
-$ sudo -u _mysql mysql_install_db
-}}}
-
-(initial password is blank for MySQL 5.6 and earlier)
-
-Set the owner:
-
-{{{
-$ sudo chown -R _mysql:_mysql /opt/local/var/db/mysql8/
-$ sudo chown -R _mysql:_mysql /opt/local/var/run/mysql8/
-$ sudo chown -R _mysql:_mysql /opt/local/var/log/mysql8/
-}}}
+Make a note of the root password which this auto-generates; it is printed to the terminal, and the account is created in an expired state. You cannot run any statement with it until you set a new password (Step 4), and a non-interactive client will refuse outright:
+
+{{{
+Please use --connect-expired-password option or invoke mysql in interactive mode.
+}}}
+
+Running `mysql -u root -p` interactively, or `mysql_secure_installation`, will prompt you for the replacement.
+
+If you would rather begin with an empty root password and set your own in Step 4, use `--initialize-insecure` instead.
+
+'''For MySQL 5.6 and earlier''' ('''mysql56''', '''mysql55''', '''mysql51''', '''mysql5'''):
+
+{{{
+$ sudo -u _mysql mysql_install_db
+}}}
+
+The initial root password is blank.
+
+The `-server` port creates the `_mysql` user and the `/opt/local/var/db/mysql8`, `/opt/local/var/log/mysql8` and `/opt/local/var/run/mysql8` directories already owned by `_mysql`, so no `chown` is needed. (`/opt/local/etc/mysql8` is deliberately owned by `root`: the server should not be able to rewrite its own configuration.)
== Step 3: Start MySQL ==
@@ -85,31 +98,43 @@
and then verify that it is running:
{{{
-$ ps -ax | grep mysql
-}}}
-
-If running, the command will return something like:
-
-{{{
- /opt/local/bin/daemondo --label=mysql8-server --start-cmd /opt/local/lib/mysql8/bin/mysqld --user=_mysql ; --pid=exec
- /opt/local/lib/mysql8/bin/mysqld --user=_mysql
- grep mysql
+$ ps -ax -o command | grep -E '[m]ysqld|[d]aemondo'
+}}}
+
+If running, the command will return:
+
+{{{
+/opt/local/bin/daemondo --label=mysql8-server --start-cmd /opt/local/lib/mysql8/bin/mysqld ; --pid=exec
+/opt/local/lib/mysql8/bin/mysqld
+}}}
+
+`daemondo` is the MacPorts supervisor; `mysqld` is the server itself. It runs as `_mysql` because `launchd` starts it as that user, so no `--user` argument appears on the command line.
+
+Seeing `mysqld` here is not quite proof that the server is healthy — a server that fails at startup is restarted repeatedly and can appear in a snapshot like this. To be sure it is actually serving:
+
+{{{
+$ mysqladmin --socket=/opt/local/var/run/mysql8/mysqld.sock -u root -p status
}}}
== Step 4: Set the MySQL password ==
-Set the MySQL `root` password (use the auto-generated password from the --initialize command above for MySQL 5.7. For MySQL 5.6 and earlier the password should currently be empty; see also the security option below):
-
-{{{
-$ /opt/local/lib/mysql8/bin/mysqladmin -u root -p password
-}}}
-
- ''Enter password:'' <press return> \\
+The simplest way to set the root password is `mysql_secure_installation` (Step 5), which also handles the expired temporary password that `--initialize` generates. To set it directly instead:
+
+{{{
+$ mysqladmin -u root -p password
+}}}
+
+ ''Enter password:'' <your current password> \\
''New password:'' <new password> \\
''Confirm new password:'' <new password>
-You will first be prompted for your existing password (''Enter password:''); enter the root password (auto generated or no entry depending on your MySQL version) then press Return.\\
-Then <new-password>`is your new desired root password, followed by Return.
+At ''Enter password:'' give the existing root password — the auto-generated one from Step 2, or an empty one if you used `--initialize-insecure` or MySQL 5.6 and earlier. Then enter your desired new password twice.
+
+You can also do it in SQL. Connect interactively — a scripted `mysql -e '...'` will be rejected while the password is expired, unless you add `--connect-expired-password`:
+
+{{{
+mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
+}}}
Test everything by logging in to the server.
{{{
@@ -125,7 +150,7 @@
Once you are logged in, simply exit the session like this:
{{{
-$ mysql> exit ;
+mysql> exit ;
}}}
If desired, reboot your machine and then run:
@@ -134,114 +159,151 @@
}}}
again to verify that the daemon is again running.
-== Step 5: Optional security configuration == #mysql_security
-
-There is an interactive program to secure a MySQL installation.
+== Step 5: Optional security configuration == #mysql_security
+
+There is an interactive program to secure a MySQL installation. It will set the root password if it is unset or expired, remove anonymous users, and drop the test database.
{{{
$ man mysql_secure_installation
-$ /opt/local/bin/mysql_secure_installation
-}}}
-
-
-See also http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html#resetting-permissions-unix
-
-
-== Step 6: Database upgrade as necessary == #mysql_upgrade
-
-If the database exists from a previous installation, you may need to upgrade.
-
-{{{
-man mysql_upgrade -- details on the upgrade program (man page)
-sudo port unload mysql8-server
-sudo /opt/local/lib/mysql8/bin/mysql_upgrade -u root -p
-sudo port load mysql8-server
-}}}
+$ mysql_secure_installation
+}}}
+
+Both commands assume you ran `port select` in Step 1; otherwise use `/opt/local/lib/mysql8/bin/mysql_secure_installation`.
+
+See also the upstream notes on [https://dev.mysql.com/doc/refman/8.4/en/resetting-permissions.html resetting permissions].
+
+== Step 6: Upgrading an existing installation == #mysql_upgrade
+
+'''MySQL 5.7 and earlier.''' Run `mysql_upgrade` '''while the server is running''' — it connects over the socket — and restart afterwards:
+
+{{{
+$ sudo port load mysql57-server
+$ /opt/local/lib/mysql57/bin/mysql_upgrade -u root -p
+$ sudo port unload mysql57-server
+$ sudo port load mysql57-server
+}}}
+
+'''MySQL 8.0.16 and newer.''' The server performs the data dictionary and system table upgrade itself at startup, and `mysql_upgrade` was [https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html removed in MySQL 8.4]. It is not shipped by '''mysql8''' or '''mysql9''', and nothing needs to be run by hand:
+
+{{{
+$ sudo port selfupdate
+$ sudo port upgrade mysql8-server
+$ sudo port load mysql8-server
+}}}
+
+Upgrading the port stops the running server, so the final `port load` is needed to start the new one; it upgrades anything out of date as it starts. Your databases in `/opt/local/var/db/mysql8` are left untouched.
+
+Moving between release series is a different matter from upgrading within one. Oracle supports upgrading only between adjacent series — for example 5.7 to 8.0, then 8.0 to 8.4 — so migrating an old data directory may take more than one step. Check the upstream [https://dev.mysql.com/doc/refman/8.4/en/upgrading.html upgrade documentation] for the versions you are moving between, and back up your data first.
== Configuration my.cnf ==
-/opt/local/etc/mysql8/my.cnf is a good place to customize your mysql8 installation.
-
-On activation if no /opt/local/etc/mysql8/my.cnf file exists one will be created which loads /opt/local/etc/mysql8/macports-default.cnf.
-
-
-Any changes made to /opt/local/etc/mysql8/macports-default.cnf will be lost during port upgrades, deactivations or activations. Currently /opt/local/etc/mysql8/macports-default.cnf contains only one directive; to disable networking. With disabled networking it is possible to install and have running all the MacPorts mysql ports simultaneously.
+`/opt/local/etc/mysql8/my.cnf` is a good place to customize your mysql8 installation.
+
+On activation, if no `/opt/local/etc/mysql8/my.cnf` file exists, one will be created which loads `/opt/local/etc/mysql8/macports-default.cnf`. If `my.cnf` already exists, MacPorts leaves it alone and your changes to it are preserved across port upgrades, deactivations and activations.
+
+Any changes made to `/opt/local/etc/mysql8/macports-default.cnf` will be lost during port upgrades, deactivations or activations, so make your changes in `my.cnf` instead. `macports-default.cnf` currently contains only:
+
+{{{
+[mysqld]
+basedir="/opt/local"
+skip-networking
+}}}
+
+With networking disabled it is possible to install and have running all the MacPorts mysql ports simultaneously. You can see it taking effect in the server log, which reports `port: 0` at startup:
+
+{{{
+[System] [MY-010931] [Server] /opt/local/lib/mysql8/bin/mysqld: ready for connections. Version: '8.4.11' socket: '/opt/local/var/run/mysql8/mysqld.sock' port: 0 Source distribution.
+}}}
+
+Remove or override `skip-networking` in your `my.cnf` if you need to reach the server over TCP.
== Starting and stopping the MySQL server ==
=== Start ===
-{{{
+{{{
$ sudo port load mysql8-server
}}}
=== Stop ===
-{{{
+{{{
$ sudo port unload mysql8-server
}}}
= Troubleshooting =
-see e.g. https://lists.macports.org/pipermail/macports-users/2016-July/041178.html
== Where are ... ==
-see e.g.
-* https://stackoverflow.com/questions/7459103/mysql-data-directory-location
=== Libraries and socket files ===
-The command
-{{{
-mysql_config
-}}}
-will give you some hints
+Each port keeps its sockets in `/opt/local/var/run/`''portname''`/`. For '''mysql8''' that is `/opt/local/var/run/mysql8/mysqld.sock` for the server itself and `/opt/local/var/run/mysql8/mysqlx.sock` for the X Protocol plugin. The server prints the path it is using when it starts, in `/opt/local/var/log/mysql8/mysql.log`.
+
+For include paths, library paths and the compiled-in defaults:
+{{{
+$ mysql_config
+}}}
+
=== data files ===
-There are some subdirectories under
-{{{
-/opt/local/var/db/
-}}}
-e.g.
-{{{
-phobos:org.macports.mysql8-server wf$ls -l /opt/local/var/db/mysql8
-total 221200
--rw-r----- 1 _mysql _mysql 56 31 Mär 09:17 auto.cnf
--rw-r----- 1 _mysql _mysql 273 8 Apr 08:51 ib_buffer_pool
--rw-r----- 1 _mysql _mysql 50331648 8 Apr 08:51 ib_logfile0
--rw-r----- 1 _mysql _mysql 50331648 30 Mär 09:03 ib_logfile1
--rw-r----- 1 _mysql _mysql 12582912 8 Apr 08:51 ibdata1
--rw-r----- 1 _mysql _mysql 0 30 Mär 09:03 mysql-bin.index
-}}}
+Each port keeps its data directory under `/opt/local/var/db/`, named after the port — `/opt/local/var/db/mysql8` for '''mysql8''' — owned by `_mysql`. Before initialization it holds only a `.turd_mysql8-server` placeholder, which MacPorts uses to keep the empty directory registered; `mysqld --initialize` is happy to run alongside it. Afterwards a MySQL 8.4 data directory looks like this:
+
+{{{
+$ ls -A /opt/local/var/db/mysql8
+#ib_16384_0.dblwr ca.pem mysql server-cert.pem
+#ib_16384_1.dblwr client-cert.pem mysql.ibd server-key.pem
+#innodb_redo client-key.pem mysql_upgrade_history sys
+#innodb_temp ib_buffer_pool performance_schema undo_001
+auto.cnf ibdata1 private_key.pem undo_002
+ca-key.pem public_key.pem
+}}}
+
+`ibdata1` is the InnoDB system tablespace, `#innodb_redo` and `undo_00*` are the redo and undo logs, `mysql`, `sys` and `performance_schema` are the server's own schemas, and the `.pem` files are the auto-generated TLS certificates. Databases you create appear as further subdirectories. The exact set varies between MySQL versions.
+
+Two related directories sit alongside it with mode 0700: `/opt/local/var/db/mysql8-files`, the target of `secure_file_priv` for `SELECT ... INTO OUTFILE`, and `/opt/local/var/db/mysql8-keyring`.
+
+=== log files ===
+The server log is at `/opt/local/var/log/mysql8/mysql.log`.
== initialize fails ==
{{{
-sudo -u mysql /opt/local/lib/mysql8/bin/mysqld --initialize --user=_mysql
-2018-04-08T06:52:45.557302Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
-2018-04-08T06:52:45.557337Z 0 [ERROR] Aborting
-}}}
-You might want to backup the content of your data directory or if you absolutely sure remove it's content
-e.g.
-{{{
-rm /opt/local/var/db/mysql8/*
-}}}
-and then rerun the mysqld initialize command.
+$ sudo /opt/local/lib/mysql8/bin/mysqld --initialize --user=_mysql
+[ERROR] --initialize specified but the data directory has files in it. Aborting.
+}}}
+`--initialize` requires an effectively empty data directory. This usually means the database was already initialized, in which case you should skip Step 2 rather than run it again. (The `.turd_mysql8-server` placeholder MacPorts leaves there does not trigger this.) If you are certain the contents are disposable, back them up and remove them:
+
+{{{
+$ sudo rm -r /opt/local/var/db/mysql8/*
+}}}
+
+and then rerun the initialize command.
== reset root password ==
see
+* https://dev.mysql.com/doc/refman/8.4/en/resetting-permissions.html
* https://stackoverflow.com/questions/33467337/reset-mysql-root-password-using-alter-user-statement-after-install-on-mac
-
== Booting fails and you need the error log ==
-You can check /var/log/system.log for messages regarding the launch of the mysqlserver.
-If it does not boot you might see messages like the one below showing that mysqlserver is respawning. Another indicator is that
+
+First look at the server's own log:
+
+{{{
+$ sudo tail -50 /opt/local/var/log/mysql8/mysql.log
+}}}
+
+The startup item sends the server's stdout and stderr there as well, so a server that dies before opening its own log still leaves its complaint in that file.
+
+If nothing is written at all, look at what `launchd` is doing with it. On macOS 10.12 and later this lives in the unified log rather than in `/var/log/system.log`:
+
+{{{
+$ log show --predicate 'process == "daemondo" OR eventMessage CONTAINS "org.macports.mysql8-server"' --last 1h
+}}}
+
+A server that fails at startup is respawned repeatedly, so
{{{
$ ps -ax | grep mysql
}}}
-will show an increasing process number and the mysqld process will only show up every once in a while.
-{{{
-grep mysql /var/log/system.log
-Apr 8 08:26:11 phobos com.apple.xpc.launchd[1] (org.macports.mysql8-server[2262]): Service exited with abnormal code: 1
-Apr 8 08:26:11 phobos com.apple.xpc.launchd[1] (org.macports.mysql8-server): Service only ran for 2 seconds. Pushing respawn out by 8 seconds.
-}}}
-The following stackoverflow question has an answer that shows how you can modify the corresponding plist to give you the stderr output of the mysql server daemon:
-* https://apple.stackexchange.com/questions/231264/mysql-fails-to-load-on-boot
-
-== mysql_upgrade fails ==
-You might end up in a catch22 if your mysql server doesn't want to start due to a failure message like:
+will show the `mysqld` process only intermittently and with a changing process id.
+
+== mysql_upgrade fails == #mysql_upgrade_fails
+
+''Applies to MySQL 5.7 and earlier; newer versions do not ship `mysql_upgrade`.''
+
+You might end up in a catch-22 if your mysql server doesn't want to start due to a failure message like:
{{{
Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
@@ -250,16 +312,20 @@
then trying to run upgrade might give you:
{{{
-sudo /opt/local/lib/mysql8/bin/mysql_upgrade -u root -p
-Enter password:
-mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/opt/local/var/run/mysql8/mysqld.sock' (2) while connecting to the MySQL server
+$ /opt/local/lib/mysql57/bin/mysql_upgrade -u root -p
+Enter password:
+mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/opt/local/var/run/mysql57/mysqld.sock' (2) while connecting to the MySQL server
Upgrade process encountered error and will not continue.
}}}
-If the named socket directory, `/opt/local/var/run/mysql8`, is empty it means the server is no longer running and the named sockets have been removed. The server may be failing to start or has crashed. A look in the MacOS system log and the MySQL server log may be revealing
-{{{
-sudo grep macports.mysql /var/log/system.log
-sudo tail -f /opt/local/var/log/mysql8/mysql.log
-}}}
+`mysql_upgrade` is a client: it needs the server running. If the socket directory `/opt/local/var/run/mysql57` is empty, the server is not running — it either failed to start or crashed, and the socket was removed. The server log and the system log may be revealing:
+
+{{{
+$ sudo tail -50 /opt/local/var/log/mysql57/mysql.log
+$ log show --predicate 'eventMessage CONTAINS "org.macports.mysql57-server"' --last 1h
+}}}
+
+== other ==
+see e.g. https://lists.macports.org/pipermail/macports-users/2016-July/041178.html
[wiki:howto <- Back to the HOWTO section]
-------8<------8<------8<------8<------8<------8<------8<------8<--------
--
Page URL: <https://trac.macports.org/wiki/howto/MySQL>
MacPorts <https://www.macports.org/>
Ports system for macOS
This is an automated message. Someone added your email address to be
notified of changes on 'howto/MySQL' page.
If it was not you, please report to admin at macports.org.
More information about the macports-changes
mailing list