wiki:howto/MySQL

Version 1 (modified by magill@…, 9 years ago) (diff)

new - extract and revision from MAMP

<- Back to the HOWTO section

This page describes the installation of the MySQL server under OSX (Yosemite 10.10.2) via MacPorts (version 2.3). Date 28 December 2014

For information on integration Apache with MySql and PhP, see: howto/MAMP

Step 1: Install MySQL

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

sudo port install mysql56-server

Step 2: create a database

 '''NOTE:''' This needs a definition of the "Port Slect" mechanism. i.e. explain what it does. 
It does NOT produce the equivalent path of the path command below. 
Which begs the question -- does the "OR" need to be "AND" or is the path command completely redundant. 
I have assumed "AND" as the PATH needed to be set for me.
Does this make links in /opt/local/bin for /opt/local/lib/mysql56/bin/ files?

sudo port select mysql mysql56

generates the output:

Selecting 'mysql56' for 'mysql' succeeded. 'mysql56' is now active.

but makes no changes to the PATH.

Also, mysql56 now uses the ''port select'' mechanism, so you will have to run one of the following to get the mysql commands in your PATH: either:

sudo port select mysql mysql56

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

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

sudo -u _mysql mysql_install_db 
sudo chown -R _mysql:_mysql /opt/local/var/db/mysql56/ 
sudo chown -R _mysql:_mysql /opt/local/var/run/mysql56/ 
sudo chown -R _mysql:_mysql /opt/local/var/log/mysql56/ 

Step 3: Activate the installation

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

sudo port load mysql56-server

and then verify that it is running:

ps -ax | grep mysql

Step 4: Set the MySQL password

Set the MySQL root password (it should currently be empty, see also the security option below):

/opt/local/lib/mysql56/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/mysql56/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:); since it is empty, just 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.

-- HOW TO USE THIS FILE (with a MacPorts installation):
-- $ sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop
-- $ 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.0/en/resetting-permissions.html#resetting-permissions-unix

Step 6: Optional database 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 mysql56-server
sudo /opt/local/lib/mysql5/bin/mysql_upgrade -u root -p
sudo port load mysql56-server

Starting and stopping the MySQL server

Start

sudo port load mysql-server56

Stop

sudo port unload mysql-server56

<- Back to the HOWTO section