MySQL

Setup MySQL on macOS Sierra.

1. Download and install the latest MySQL Community Server version.
   For example: MySQL Community Server 5.7.17 for Mac OS X 10.12 (x86, 64-bit), DMG Archive
   
   
2. Once the installation has been completed successfully, you will be provided with your temporary root password. Save this temp password. We will change it!
   
   
3. Modify your .bash_profile file, add the following line:
   export PATH=$PATH:/usr/local/mysql/bin
   
   
4. To change the root password:
   
   • Stop MySQL Server, open a terminal and type:
     
     sudo /usr/local/mysql/support-files/mysql.server stop
     Enter your mac password
     
     You will see:
     
     Shutting down MySQL
     .. SUCCESS!
     
     
   • Set MySQL in safe mode, type:
     
     sudo mysqld_safe --skip-grant-tables
     Enter your mac password
     
     Leave this terminal open!
     
     
   • Open a new terminal and type:
     
     mysql -u root
     
     You will see:
     
     mysql>
     
     
   • Enter a new MySQL root password, for example: mysecret, type:
     
     FLUSH PRIVILEGES;
     ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysecret';
     quit
     
     Note:
     Watch out for the single quotes used in root and localhost.
     
     
   • Start MySQL Server, open a terminal and type:
     
     sudo /usr/local/mysql/support-files/mysql.server start
     Enter your mac password
     
     You will see:
     
     Starting MySQL
     SUCCESS!
     
     Note:
     You can also start/stop MySQL server by GUI:
     - Select Mac System Preferences
     - Double click the MySQL icon
     
     
   • You can now close both terminals.
     
     
5. To show your MySQL version, type:
   
   mysql -v -uroot -p
   Enter your mysql root password
   
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 170
   Server version: 5.7.17 MySQL Community Server (GPL)
   
   
6. Link MySQL socket, type:
   
   sudo mkdir /var/mysql
   sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock
   
   Note:
   MySQL create the socket in the /tmp folder
   MacOS expect the MySQL socket to be in folder /var/mysql
   
   
7. Download the latest phpMyAdmin web application.
   For example: phpMyAdmin 4.7.0
   
   Note:
   Download phpMyAdmin-4.7.0-english.tar.gz
   
   
8. Unpack this file in folder: ~/Sites/
   
   Note:
   After unpacking you will see: ~/Sites/phpmyadmin
   
   
9. Open a browser and goto:
   http://localhost/~username/phpmyadmin/setup/
   
   For example:
   http://localhost/~robertlie/phpmyadmin/setup/
   
   Note:
   In my OSX Apache setup I have implemented a name based virtual hosting.
   See tutorial: Setup Apache in macOS Mojave with PHP, Server Side Includes and name-based virtual hosting
   
   File /usr/local/etc/httpd/extra/httpd-vhosts.conf contains:
   
   <VirtualHost *:80>
      DocumentRoot "/Users/robertlie/Sites/phpmyadmin"
      ServerName sand.phpmyadmin.nl
      ErrorLog "/usr/local/var/log/httpd/phpmyadmin-error.log"
      CustomLog "/usr/local/var/log/httpd/phpmyadmin-access.log" common
   </VirtualHost>
   
   And file /etc/hosts file contains:
   
   #Local sites
   127.0.0.1      sand.phpmyadmin.nl
   
   To access phpmyadmin:
   http://sand.phpmyadmin.nl
   
   
10. Select the Overview screen and press the "New Server" button.
    
    
11. Select tab "Authentication"
    
    
12. Enter in field "User for config auth": root
    Enter in field "Password for config auth": mysecret
    
    Press the Apply button
    
    Note:
    Enter the MySQL root password you have set earlier.
    
    
13. Go back to the Overview screen and press the "Download" button to download the configuration file config.inc.php
    
    
14. Move the configuration file to: ~/Sites/phpmyadmin/config.inc.php
    
    
15. Open a browser and goto:
    http://localhost/~username/phpmyadmin
    
    For example:
    http://localhost/~robertlie/phpmyadmin
    
    Enter username: root
    Enter password: mysecret