| Version 37 (modified by jfshumway, 3 years ago) (diff) |
|---|
About MySQL
MySQL is a relational database management system.
For information on integrating Apache with MySQL and PHP, see MAMP.
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.
- 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 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 v5.6.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.
Installation
Step 1: Install MySQL
If you want to run a MySQL server on this computer, install MySQL like this:
$ sudo port install mysql8-server
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
$ 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/
Step 3: Start MySQL
Activate your MySQL server so that it autostarts when you boot your machine:
$ sudo port load mysql8-server
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
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>
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.
Test everything by logging in to the server.
$ mysql -u root -p
You will be prompted:
Enter password:
Enter your <new password>
Should you receive the message:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
It means you have entered the wrong password. Try again.
Once you are logged in, simply exit the session like this:
$ mysql> exit ;
If desired, reboot your machine and then run:
$ ps -ax | grep mysql
again to verify that the daemon is again running.
Step 5: Optional security configuration
There is an interactive program to secure a MySQL installation.
$ 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
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
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.
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.
Libraries and socket files
The command
mysql_config
will give you some hints
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
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.
reset root password
see
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
$ 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:
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:
Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
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 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