MySQL on MAC

Should I use this instead of XAMPP?

The answer is probably no. XAMPP is a much simpler, fully integrated package which is a fit for the needs of this course. You can, however, substitute actual MySQL for the MariaDB used in XAMPP and use the remaining Apache/Php part of XAMPP, or, make separate installations of those components.

Terminal shell, sudo, nano

It is important to be able to execute certain commands from the Terminal shell. On a MAC, as on other UNIX-like systems, administrative commands are usually prefaced by the sudo command:
$ sudo SOME-ADMINISTRATIVE-COMMAND
If you've never used sudo, it requests that you enter the password for your computer login on initial usage within each shell. Occasionally you have to edit administrative files and it is often easiest to do so from the shell as well. I would recommend the nano editor as the easiest to use:
$ sudo nano /PATH/TO/SOME-ADMINISTRATIVE-FILE
An alternative is to "become root" and do the editing directly without the constant sudo qualifier. To do so, run:
$ sudo su
# ....
The hash prompt # indicates that you are root and can do administrative tasks without the sudo qualifier.

Prepare for mysql shell usage

After installation, the mysql shell client will be:
/usr/local/mysql/bin/mysql
There are other useful client related client programs, all in the directory
/usr/local/mysql/bin/
and so it is a good idea to make them easily accessible. What you need to do is add this directory to the executable PATH by adding this line
PATH=/usr/local/mysql/bin:$PATH
into the "appropriate" file: ~/.profile. Edit .profile by:
$ nano ~/.profile
In any case, add the one line:
PATH=/usr/local/mysql/bin:$PATH
Be careful to have no spaces around the equals sign! Then quit this shell and start up another one. Test the effectiveness of what you've done by typing this in the shell:
$ echo $PATH
You should see the /usr/local/mysql/bin component as the first component in the PATH variable.

MySQL Installation

This is a free MySQL community edition package available (in other, perhaps later, versions) for download from the MySQL home site:
Home: http://www.mysql.com
Download: http://www.mysql.com/downloads/mysql
You have to double-check that the version of your MAC OS is suitable for the latest download. Assuming your MAC version is at least 10.10, choose the DMG version:
Mac OS X 10.12 (x86, 64-bit), DMG Archive
and download, getting:
mysql-5.7.17-osx10.12-x86_64.dmg
Open the ".dmg" file to reveal the installation file:
mysql-5.7.17-osx10.12-x86_64.pkg
If you have an older version of MySQL running, stop it first. Double-click to start the installation. MySQL version 5.7 automatically generates a root password and presents it to the user. Save this password. You must reset it after installation. If you do lose the password, you can still break in.

Server Control

As of this recent version of 5.7, running on recent versions of MAC OS X, there is a MySQL Control Panel found in System Preferences. Opening it reveals:
It is self-explanatory how to use this, but you can effectively "turn off" this version of MySQL to avoid competing with, say, the XAMPP installation.

Mysql client executable access

As indicated in the above section, add this PATH component
/usr/local/mysql/bin
You can check that the executables are in place by:
$ which mysql
Initially get in by:
$ mysql -u root -p
Password: THE-PASSWORD-GIVEN-TO-YOU
Once you're in reset the password (you cannot do anything until you've done so):
mysql> alter user root@localhost identified by 'NEW-PASSWORD';
mysql> quit
Don't use your login password! This one is of much lesser importance. Test it:
$ mysql -u root -p
Password: NEW-PASSWORD

Password-less access

Instead of trying to remember this MySQL root password, create the following convenience file in your home directory (adjust if you have single quotes in your NEW-PASSWORD):

~/.my.cnf
[client]
user=root
password='NEW-PASSWORD'
With this in place, you get password-less MySQL root access:
$ mysql
mysql> quit

Create test database and guest user

Create the user/database we employ in our examples:
$ mysql
mysql> create database test;
mysql> create user guest@localhost;
mysql> grant all on test.* to guest@localhost;
mysql> quit
Verify that you can get in as guest with empty password:
$ mysql -u guest test -p
Enter password: ENTER
mysql> quit


© Robert M. Kline