[MacPorts] howto/MySQL added

[MacPorts]

Page "howto/MySQL" was added by magill at mac.com
Comment: new - extract and revision from MAMP
Content:
-------8<------8<------8<------8<------8<------8<------8<------8<--------
[[PageOutline]]

[wiki:howto <- 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 = #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]
-------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 OS X

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 .