MACPORTS - mysql5 install on Leopard - step by step instructions

---------------------------------------------------------------------------------- MACPORTS - mysql5 install on Leopard - step by step instructions ---------------------------------------------------------------------------------- UPDATED : Wednesday, April 2, 2008 (2:37 AM) UPDATE NOTES : Some steps I listed in the tutorial, are not actually required if you just want a list of the commands in either one of the SUMMARIES, so this is an update to the original message, and list the correct commands for the summaries. I wrote a big long script to set permissions, and posted a bunch of notes to solve some problems, that as it turns out are not neccessary if the installation is done in the correct order. I installed mysql5 +server from scratch, and the outcome was as it should be, it worked just fine, so I have listed the 18 steps that should provide anyone else with the same result... There are three sections, make sure to read it all, then feel free to use either SUMMARY : SUMMARY : (the commands, for easy to follow order) COMMAND SUMMARY : (the bare commands, for easy copy paste, or script creation) DETAILS : (a brief set of instructions, designed more as a tutorial) I think the problem most people have with the install, including myself, is that a couple of the steps in the notes created by mysql_install_db5 are not in the correct order. If you are a database admin that's fine, you don't need the exact steps, but if you install mysql every couple of years or so, you tend to follow the instructions step by step which in this case generates a SOCKET error. After MacPorts installs mysql, and you initialize the database using mysql_install_db5, there are some instructions provided, which unfortunately are not in the correct order, and cause frequent problems, particularly with the SOCKET. The instructions show how to set the root password, followed by instructions for starting the database. Since the database is not running when you enter the commands to set the root password, a SOCKET error is generated. To test this installation, here's what I did : (obviously you don't need to do steps ( A ) thru ( E )) ( A ) I reformatted a drive ( B ) Installed OSX 10.5.2 Leopard ( C ) Installed BBEdit, just so I would have an editor... ( D ) Installed MacPorts 1.6.0 ( E ) Installed and ran MySQL-Server, and MySQL-Client v5.0 = = = = = = = = = = ======================================================================== SUMMARY : (the commands, for easy to follow order) = = = = = = = = = = ======================================================================== = = = = = = = = = = ======================================================================== SUMMARY : (the commands, for easy to follow order) = = = = = = = = = = ======================================================================== ( 01 ) [2008.01.04](07:45PM) -> [username] ~ $ sudo port -v install mysql5 +server ( 02 ) [2008.01.04](07:58PM) -> [username] ~ $ sudo -u mysql mysql_install_db5 ( 03 ) [2008.01.04](08:01PM) -> [username] ~ $ sudo /opt/local/lib/ mysql5/bin/mysqld_safe & ( 04 ) [2008.01.04](08:01PM) -> [username] ~ $ ps aux | grep mysql DO EITHER STEP ( 07 ) OR ( 08 ), NOT BOTH ( 07 ) [2008.01.04](08:05PM) -> [username] ~ $ sudo /opt/local/lib/ mysql5/bin/mysql_secure_installation ( 08 ) [2008.01.04](08:06PM) -> [username] ~ $ /opt/local/lib/mysql5/ bin/mysqladmin -u root password '<mysql_root_password>' ( 13 ) [2008.01.04](08:15PM) -> [username] ~ $ sudo cp /opt/local/ share/mysql5/mysql/my-medium.cnf /opt/local/etc/mysql5/my.cnf ( 14 ) [2008.01.04](08:16PM) -> [username] ~ $ sudo open -a / Applications/BBEdit.app /opt/local/etc/mysql5/my.cnf ( 15 ) [2008.01.04](08:17PM) -> [username] ~ $ echo $PATH ( 16 ) [2008.01.04](08:19PM) -> [username] ~ $ mysql5 -u root -p ( 17 ) [2008.01.04](08:21PM) -> [username] ~ $ sudo launchctl load -w / Library/LaunchDaemons/org.macports.mysql5.plist ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- CAUTION : DO NOT DO STEP 18 UNTIL YOU READ THE NOTES BELOW ( 18 ) [2008.01.04](08:27PM) -> [username] ~ $ sudo cp /opt/local/etc/ php.ini-recommended /opt/local/etc/php.ini ---------------------------------------------------------------------------------- THESE STEPS ARE NOT NEEDED UNLESS YOU ARE FOLLOWING THE TUTORIAL ---------------------------------------------------------------------------------- ( 05 ) [2008.01.04](08:02PM) -> [username] ~ $ sudo find /opt -name mysql_secure_installation ( 06 ) [2008.01.04](08:05PM) -> [username] ~ $ sudo echo ( 09 ) [2008.01.04](08:07PM) -> [username] ~ $ /opt/local/lib/mysql5/ bin/mysqladmin -u root -h your_computer_name.local password '<mysql_root_password>' ( 10 ) [2008.01.04](08:08PM) -> [username] ~ $ /opt/local/lib/mysql5/ bin/mysqladmin -u root -h your_computer_name.local -p password '<mysql_root_password>' ( 11 ) [2008.01.04](08:10PM) -> [username] ~ $ sudo find / -name my.cnf ( 12 ) [2008.01.04](08:12PM) -> [username] ~ $ sudo find / -name my[^ \.]*\.cnf = = = = = = = = = = ======================================================================== COMMAND SUMMARY : (the bare commands, for easy copy paste, or script creation) = = = = = = = = = = ======================================================================== sudo port -v install mysql5 +server sudo -u mysql mysql_install_db5 sudo /opt/local/lib/mysql5/bin/mysqld_safe & ps aux | grep mysql DO EITHER THE mysql_secure_installation, or set the password, NOT BOTH sudo /opt/local/lib/mysql5/bin/mysql_secure_installation /opt/local/lib/mysql5/bin/mysqladmin -u root password '<mysql_root_password>' sudo cp /opt/local/share/mysql5/mysql/my-medium.cnf /opt/local/etc/ mysql5/my.cnf sudo open -a /Applications/BBEdit.app /opt/local/etc/mysql5/my.cnf echo $PATH mysql5 -u root -p sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist = = = = = = = = = = ======================================================================== DETAILS : (a brief set of instructions, designed more as a tutorial) = = = = = = = = = = ======================================================================== If you follow (copy and paste) these instructions step by step from ( 01 ) thru ( 18 ), you should achieve the same exact outcome... ---------------------------------------------------------------------------------- ( 01 ) [2008.01.04](07:45PM) -> [username] ~ $ sudo port -v install mysql5 +server ---------------------------------------------------------------------------------- Password: <os_admin_password> --> Fetching zlib --> <snip> --> ... --> </snip> --> Creating launchd control script --> ########################################################### --> # A startup item has been generated that will aid in --> # starting mysql5 with launchd. It is disabled --> # by default. Execute the following command to start it, --> # and to cause it to launch at startup: --> # --> # sudo launchctl load -w /Library/LaunchDaemons/ org.macports.mysql5.plist --> ########################################################### --> <snip> --> ... --> </snip> --> Installing mysql5 5.0.51_0+server --> ****************************************************** --> * In order to setup the database, you might want to run --> * sudo -u mysql mysql_install_db5 --> * if this is a new install --> ****************************************************** --> Activating mysql5 5.0.51_0+server --> Cleaning mysql5 --> Removing build directory for mysql5 ---------------------------------------------------------------------------------- ( 02 ) [2008.01.04](07:58PM) -> [username] ~ $ sudo -u mysql mysql_install_db5 ---------------------------------------------------------------------------------- Password: <os_admin_password> --> Installing MySQL system tables... --> 080401 19:59:48 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive --> OK --> Filling help tables... --> 080401 19:59:48 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive --> OK --> --> To start mysqld at boot time you have to copy --> support-files/mysql.server to the right place for your system ------------------------------------------- NOTE : Here's where the SOCKET problem is generated you need to start the server (daemon) before you set the password for the mysql root user The order of these instructions is wrong. They should specify : Start the daemon first, make sure the server is running, then set the password... $ sudo /opt/local/lib/mysql5/bin/mysqld_safe & # (hit return twice) $ ps aux | grep mysql $ /opt/local/lib/mysql5/bin/mysqladmin -u root password 'new-password' ------------------------------------------- --> PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! --> To do so, start the server, then issue the following commands: --> /opt/local/lib/mysql5/bin/mysqladmin -u root password 'new-password' --> /opt/local/lib/mysql5/bin/mysqladmin -u root -h eagle.local password 'new-password' --> --> Alternatively you can run: --> /opt/local/lib/mysql5/bin/mysql_secure_installation --> --> which will also give you the option of removing the test --> databases and anonymous user created by default. This is --> strongly recommended for production servers. --> --> See the manual for more instructions. --> --> You can START the MySQL daemon with: --> cd /opt/local ; /opt/local/lib/mysql5/bin/mysqld_safe & --> --> You can test the MySQL daemon with mysql-test-run.pl --> cd mysql-test ; perl mysql-test-run.pl --> --> Please report any problems with the /opt/local/lib/mysql5/bin/ mysqlbug script! --> --> The latest information about MySQL is available on the web at --> http://www.mysql.com --> Support MySQL by buying support/licenses at http://shop.mysql.com ---------------------------------------------------------------------------------- ( 03 ) [2008.01.04](08:01PM) -> [username] ~ $ sudo /opt/local/lib/ mysql5/bin/mysqld_safe & ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- ( 04 ) [2008.01.04](08:01PM) -> [username] ~ $ ps aux | grep mysql ---------------------------------------------------------------------------------- --> <snip> --> ... --> If everything went well, this command will show that MySQL-Server is running, and --> display 4-5 lines showing the associated processes, and some other useful information --> ... --> </snip> Let's look for the secure installation script... ---------------------------------------------------------------------------------- ( 05 ) [2008.01.04](08:02PM) -> [username] ~ $ sudo find /opt -name mysql_secure_installation ---------------------------------------------------------------------------------- --> /opt/local/lib/mysql5/bin/mysql_secure_installation --> /opt/local/var/macports/software/mysql5/5.0.51_0+server/opt/local/ lib/mysql5/bin/mysql_secure_installation The preferred way to set the <mysql_root_password> is to run /opt/ local/lib/mysql5/bin/mysql_secure_installation After running "mysql_install_db5", the initial <mysql_root_password> will be empty ---------------------------------------------------------------------------------- ( 06 ) [2008.01.04](08:05PM) -> [username] ~ $ sudo echo ---------------------------------------------------------------------------------- ( 07 ) [2008.01.04](08:05PM) -> [username] ~ $ sudo /opt/local/lib/ mysql5/bin/mysql_secure_installation ---------------------------------------------------------------------------------- Set the <mysql_root_password> now that MySQL-Server is running ---------------------------------------------------------------------------------- ( 08 ) [2008.01.04](08:06PM) -> [username] ~ $ /opt/local/lib/mysql5/ bin/mysqladmin -u root password '<mysql_root_password>' ---------------------------------------------------------------------------------- This worked fine, so the <mysql_root_password> is now set correctly for the localhost. We will now try to set the host Rendevouz address, and steps ( 09 ) and ( 10 ) will generate an error until my.cnf is modified more than likely, that's the only thing I can figure out is causing the error, but for now it's unimportant, so continue with step ( 11 )... ---------------------------------------------------------------------------------- ( 09 ) [2008.01.04](08:07PM) -> [username] ~ $ /opt/local/lib/mysql5/ bin/mysqladmin -u root -h your_computer_name.local password '<mysql_root_password>' ---------------------------------------------------------------------------------- --> /opt/local/lib/mysql5/bin/mysqladmin: connect to server at 'your_computer_name.local' failed --> error: 'Host '192.168.1.176' is not allowed to connect to this MySQL server' Here I will try with the "-p" switch to see if that helps... ---------------------------------------------------------------------------------- ( 10 ) [2008.01.04](08:08PM) -> [username] ~ $ /opt/local/lib/mysql5/ bin/mysqladmin -u root -h your_computer_name.local -p password '<mysql_root_password>' ---------------------------------------------------------------------------------- --> Enter password: <mysql_root_password> --> /opt/local/lib/mysql5/bin/mysqladmin: connect to server at 'your_computer_name.local' failed --> error: 'Host '192.168.1.176' is not allowed to connect to this MySQL server' NOPE, that didn't help, if you need to login this way you might want to read up on : # skip-networking # bind address I normally add all the user GRANTS using Navicat, so this isn't important to me at this point. Let's see if a valid my.cnf exists ---------------------------------------------------------------------------------- ( 11 ) [2008.01.04](08:10PM) -> [username] ~ $ sudo find / -name my.cnf ---------------------------------------------------------------------------------- --> /opt/local/var/macports/sources/rsync.macports.org/release/ports/ databases/mysql4/files/my.cnf A normal MySQL-Server install should have a file /etc/my.cnf, and for MacPorts the file should be /opt/local/etc/mysql5/my.cnf and there isn't one, so that pretty much tells us that MySQL-Server is using the defaults that it was configured with, hence the errors in steps ( 10 ) and ( 11 ). However we'll do a search to see if we find a standard config template, so we will broaden the search. ---------------------------------------------------------------------------------- ( 12 ) [2008.01.04](08:12PM) -> [username] ~ $ sudo find / -name my[^ \.]*\.cnf ---------------------------------------------------------------------------------- --> Password: <os_admin_password> --> --> /opt/local/share/mysql5/mysql/my-huge.cnf --> /opt/local/share/mysql5/mysql/my-innodb-heavy-4G.cnf --> /opt/local/share/mysql5/mysql/my-large.cnf --> /opt/local/share/mysql5/mysql/my-medium.cnf --> /opt/local/share/mysql5/mysql/my-small.cnf --> /opt/local/var/macports/software/mysql5/5.0.51_0+server/opt/local/ share/mysql5/mysql/my-huge.cnf --> /opt/local/var/macports/software/mysql5/5.0.51_0+server/opt/local/ share/mysql5/mysql/my-innodb-heavy-4G.cnf --> /opt/local/var/macports/software/mysql5/5.0.51_0+server/opt/local/ share/mysql5/mysql/my-large.cnf --> /opt/local/var/macports/software/mysql5/5.0.51_0+server/opt/local/ share/mysql5/mysql/my-medium.cnf --> /opt/local/var/macports/software/mysql5/5.0.51_0+server/opt/local/ share/mysql5/mysql/my-small.cnf Next we will copy a medium template to /opt/local/etc/mysql5/my.cnf ---------------------------------------------------------------------------------- ( 13 ) [2008.01.04](08:15PM) -> [username] ~ $ sudo cp /opt/local/ share/mysql5/mysql/my-medium.cnf /opt/local/etc/mysql5/my.cnf ---------------------------------------------------------------------------------- --> Password: <os_admin_password> We can edit the my.cnf file now... ---------------------------------------------------------------------------------- ( 14 ) [2008.01.04](08:16PM) -> [username] ~ $ sudo open -a / Applications/BBEdit.app /opt/local/etc/mysql5/my.cnf ---------------------------------------------------------------------------------- You might want to read up on : # skip-networking # bind address Just for grins, let's check the bash path... ---------------------------------------------------------------------------------- ( 15 ) [2008.01.04](08:17PM) -> [username] ~ $ echo $PATH ---------------------------------------------------------------------------------- --> /opt/local/sbin:/opt/local/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/ local/bin:/usr/local/sbin:/Developer/Tools The path shows that /opt/local/sbin:/opt/local/bin exist, which is very good... Let's try to use MySQL-Client to log into theMySQL-Server, and if that works let's tell the client to show us what databases are available, this will confirm, that life is as it should... ---------------------------------------------------------------------------------- ( 16 ) [2008.01.04](08:19PM) -> [username] ~ $ mysql5 -u root -p ---------------------------------------------------------------------------------- --> Enter password: <mysql_root_password> --> --> Welcome to the MySQL monitor. Commands end with ; or \g. --> Your MySQL connection id is 5 --> Server version: 5.0.51 Source distribution --> --> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases ; --> --> +--------------------+ --> | Database | --> +--------------------+ --> | information_schema | --> | mysql | --> | test | --> +--------------------+ --> 3 rows in set (0.00 sec) --> mysql> quit --> Bye The server is running now, but we want to make sure it starts up every time we re-boot... ---------------------------------------------------------------------------------- ( 17 ) [2008.01.04](08:21PM) -> [username] ~ $ sudo launchctl load -w / Library/LaunchDaemons/org.macports.mysql5.plist ---------------------------------------------------------------------------------- --> Password: <os_admin_password> CAUTION - READ THIS STEP CAREFULLY, SO YOU DON'T OVERWRITE YOUR php.ini, UNLESS YOU NEED IT : CAUTION - SKIP step ( 18 ) if you already have a php.ini STEP ( 13 ) shows how to create "my.cnf" from the templates: STEP ( 18 ) shows how to create "php.ini" from the templates: If you have PHP installed, and don't have an existing /opt/local/etc/ php.ini, you can create one here: ---------------------------------------------------------------------------------- ( 18 ) [2008.01.04](08:27PM) -> [username] ~ $ sudo cp /opt/local/etc/ php.ini-recommended /opt/local/etc/php.ini ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- STANDARD LOCATIONS : You need to be aware of three locations where you have to use matching values for the SOCKET : Normally these would be located at : (MacPorts puts them in a slightly different place as shown below) ( a ) /etc/my.cnf ( b ) /etc/php.ini ( c ) if using a GUI like Navicat the preferences have to be correct for the connection. MACPORTS LOCATIONS : ( a ) /opt/local/etc/mysql5/my.cnf ( b ) /opt/local/etc/php.ini ( c ) if using a GUI like Navicat the preferences have to be correct for the connection. ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Bill Hernandez Plano, Texas