This time I will show you how to install MySQL database on your Mac, show some basic commands and recommend good free GUI tool to play with your database.
Installing MySQL on Mac OS X starts with opening your web browser and navigating to MySQL download page. Select the Generally Available (GA) Releases tab and select Mac OS X from Select Platform from drop down menu, then find Mac OS X 10.7 (x86, 64-bit), DMG Archive on the list of versions to download and click Download button on the right.
On the site that will open after clicking Download click No thanks, just start my download. link on the bottom of the page. This will start the download.
Once the download is finished run mysql-5.6.15-osx10.7-x86_64.dmg file – this should mount disk image file.
Inside the image click mysql-5.6.15-osx10.7-x86_64.pkg. If you get an error popup like this:
It means you have to allow installing software from undefined developer. To do that go to System Preferences > Security & Privacy, click the padlock icon in the corner, enter your password and select Anywhere from Allow apps downloaded from finally confirm your choice by clicking Allow From Anywhere:
You have to do it only once. From now on you will be able to install any software downloaded from the Internet. Close System Preferences and click mysql-5.6.15-osx10.7-x86_64.pkg file again. This time installer should appear on your screen:
Follow instructions on the screen till you see Installation was successful message.
Starting MySQL server and installing Preferences pane
MySQL pane in System Preferences gives you a convenient way to start / stop MySQL server and allows you to make it start automatically every time you start your computer. To add MySQL pane to System Preferences double click MySQL.prefPane file in the mounted image. This will open System Preferences with dialog box:
Click install. This should add MySQL icon to your System Preferences. Click it and press Start MySQL Server button.
Setting root password
At this time MySQL server is running. But we need to set root password. To do that go to the Terminal and issue command:
$ /usr/local/mysql/bin/mysql -u root -h localhost -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 64 Server version: 5.6.15 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
When asked for password just press ENTER to log in. Now it’s time to set the root password. Type the following commands:
mysql> UPDATE mysql.user SET Password = PASSWORD('secret_root_password') WHERE User = 'root'; mysql> FLUSH PRIVILEGES; mysql> exit Bye $
Instead of * secret_root_password* use password you would like to have. Now every time you connect with:
$ /usr/local/mysql/bin/mysql -u root -h localhost -p Enter password:
You will have to enter your new password.
The MySQL server is set up and ready to use. Below I will show you few basic commands to test new installation. We will create test_log table, create user and finally connect to database from PHP.
Creating test_log table
The installation of MySQL comes with test database. We will use it to create our test_log table. While in MySQL command line interface issue following command:
mysql> use test; Database changed
to choose the database, then we create table:
mysql> CREATE TABLE test_log (id INT NOT NULL AUTO_INCREMENT, message VARCHAR(255) NULL, PRIMARY KEY (id)) ENGINE = InnoDB;
We did not get any errors but just for the sake of this tutorial we confirm the test_log table was created:
mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | test_log | +----------------+ 1 row in set (0.00 sec)
Root user should be used only for server administration. For everything else you create users with more restricted privileges. For example to add user rafal with password secret_pass issue:
mysql> CREATE USER 'rafal'@'localhost' IDENTIFIED BY 'secret_pass'; mysql> FLUSH PRIVILEGES;
The user we just created will be able to connect to MySQL server only from localhost. If you want to create user that can connect from anywhere replace localhost with %. Ok, our user can now connect to the database but has no privileges to any tables – login privileges are not enough. To grant ALL privileges to the table we created do:
mysql> GRANT ALL ON test.test_log TO 'rafal'@'localhost'; mysql> FLUSH PRIVILEGES;
Connecting to MySQL from PHP
In my last bog post I described how to install and configure PHP now I’ll give you an example of how to connect and run queries on our MySQL server from PHP. In the
~/ws/site1/ directory add
mysql.php file with this content:
Now when you go to
http://localhost/mysql.php you should see:
The script when run for the first time created five rows in our
test_log table and displayed them. To see the rows from MySQL command line interface use this:
mysql>SELECT id, message FROM test_log; +----+-----------+ | id | message | +----+-----------+ | 1 | message 0 | | 2 | message 1 | | 3 | message 2 | | 4 | message 3 | | 5 | message 4 | +----+-----------+ 5 rows in set (0.00 sec)
It’s good to know how to use MySQL command line interface but for beginners the easier way to start is to use some kind of GUI for MySQL. My personal favorite is Sequel Pro. It’s good, fast and most importantly FREE. I recommend you install it.
That’s it! Comments are welcome. If you like the post / video tutorial make sure to give me thumbs up on YouTube