<- Back to the HOWTO section

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
  • mysql57: MySQL v5.7.x. This is the latest stable version of MySQL.
  • mysql56: MySQL v5.6.x.
  • mysql55: MySQL v5.5.x.
  • 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.1: MariaDB v10.1.x. This is a development version of MariaDB not recommended for production use.
  • mariadb-10.0: MariaDB v10.0.x. This is the latest stable version of MariaDB.
  • mariadb: MariaDB v5.5.x.
• 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.

Step 1: Install MySQL

If you want to run a MySQL server on this computer, install MySQL like this:

$ sudo port install mysql57-server

Step 2: Create a database

Also, mysql57 now uses the ''port select'' mechanism to manage the creation of symlinks in /opt/local/bin. Using port select will install a link for exactly one of the available installations. Alternatively, you may simply add the path containing the desired mysql installation to your PATH.

Use one of these two:

$ sudo port select mysql mysql57

$ export PATH=$PATH:/opt/local/lib/mysql57/bin

Once you have done at least one of the two previous commands, set up the main database. For MySQL 5.7:

$ sudo /opt/local/lib/mysql57/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/mysql57/ 
$ sudo chown -R _mysql:_mysql /opt/local/var/run/mysql57/ 
$ sudo chown -R _mysql:_mysql /opt/local/var/log/mysql57/ 

Step 3: Activate the installation

Activate your MySQL server installation so that it autostarts when you boot your machine:

$ sudo port load mysql57-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=mysql57-server --start-cmd /opt/local/lib/mysql57/bin/mysqld --user=_mysql ; --pid=exec
     /opt/local/lib/mysql57/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/mysql57/bin/mysqladmin -u root -p password <new-password> 

Note: This command line form of the command will generate:

Warning: Using a password on the command line interface can be insecure.

Therefore it is recommended that you use the interactive form:

$ /opt/local/lib/mysql57/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

The following is a more detailed process that may achieve the same results as the interactive program. Place the following into mysql_security.sql and replace the 'MyNewPass' with your root password. Note that the SQL will remove all access for root from any location other than 'localhost'. You might like to keep this SQL file - all the lines beginning with '--' are doc-comments about how to use it.

Wrapper no longer exists...

-- HOW TO USE THIS FILE (with a MacPorts installation):

--$ sudo port unload mysql57-server

-- $ sudo mysqld_safe --init-file=mysql_security.sql &
-- The init will terminate if there are any errors in the init file.
-- Wait a bit to be sure the server is running.
-- If it's running, then shutdown the server (root password required):
-- $ /opt/local/bin/mysqladmin -u root -p shutdown
-- Check that everything worked.  There may be an ERROR if the test database
-- doesn't exist.  Otherwise there should be no errors in the file reported by
-- 'mysqld_safe Logging to ..." during the mysql_safe5 startup from above:
-- $ sudo tail -n 20 /opt/local/var/db/mysql5*.err 

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

The server startup options --skip-networking and --skip-grant-tables may be useful while implementing security.

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 mysql57-server
sudo /opt/local/lib/mysql57/bin/mysql_upgrade -u root -p
sudo port load mysql57-server

Starting and stopping the MySQL server

Start

$ sudo port load mysql57-server

Stop

$ sudo port unload mysql57-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

data files

There are some subdirectories under

/opt/local/var/db/

e.g.

phobos:org.macports.mysql57-server wf$ls -l /opt/local/var/db/mysql57
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/mysql57/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/mysql57/*

and then rerun the mysqld initialize command.

reset root password

see

• ​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

$ 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.mysql57-server[2262]): Service exited with abnormal code: 1
Apr  8 08:26:11 phobos com.apple.xpc.launchd[1] (org.macports.mysql57-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:

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/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.

Please add a solution here if you find one.

<- Back to the HOWTO section