[[PageOutline]]

[wiki:howto <- Back to the HOWTO section]

This page describes the installation of the '''MySQL56-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 = #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 = #mysql_security

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 = #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 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
}}}


[wiki:howto <- Back to the HOWTO section]