As a beginning web developer, you'll likely have to work or interact with databases. MySQL is one of the most popular SQL databases available today so it's a good idea to have a local installation on your computer for testing and development purposes.
Although there are many ways to install MySQL, in this article we'll go over how to install it using Homebrew. According to its website, the two main benefits are:
Homebrew installs packages to their own directory and then symlinks their files into
Homebrew won’t install files outside its prefix and you can place a Homebrew installation wherever you like.
Simply put, you can easily install and uninstall software. If you are a neat freak like me, when you uninstall software you don't want to worry about manually "cleaning up" all of the files that were leftover.
Installing Homebrew is probably the easiest step in the whole process. First, install
xcode command line tools by running:
and then paste the following command in your terminal:
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Re-start your terminal and check to see if everything was installed correctly:
At the time of this writing, the default version of MySQL in Homebrew is 8.0. We can check this by running
brew info mysql. However, we’re aiming to install version 5.7 so let's check which is the latest version by appending
@5.7 to the default package key:
brew info firstname.lastname@example.org. The expected output should look something like this:
email@example.com: stable 5.7.29 (bottled) [keg-only]
Open source relational database management system
To install this version enter:
brew install firstname.lastname@example.org. Finally let's add MySQL to our
zsh shell PATH by running:
echo 'export PATH="/email@example.com/bin:$PATH"' >> ~/.zshrc and re-start your terminal.
Since MySQL runs as a
service, we need to check if it is running:
brew services list. Our output should look like this:
Name Status User Plist
Let's start the service so we can connect and secure our installation:
brew services start firstname.lastname@example.org
Some useful commands to keep in mind as we go forward are:
% brew services listto list all running services
% brew services stop email@example.com stop MySQL
% brew services restart firstname.lastname@example.org restart MySQL
Securing your MySQL Installation
Once we have MySQL installed and our service is up and running, our next task is to secure the installation. This is done by running the command
During the secure installation process, some things to keep in mind are:
- Do we want to remove anonymous users? Yes. Since this is a local installation we have no need for anonymous users.
- Disallow root login remotely? Yes. Let's follow the installer recommendation: "Normally, root should only be allowed to connect from ‘localhost’. This ensures that someone cannot guess at the root password from the network."
testdatabase and access to it? Yes. In MySQL, anyone can connect to the
testdatabase so it's a good idea to not worry about this.
Finally, reload privilege tables so that all the changes will take effect.
Connect to MySQL
We are now ready to connect to MySQL and run a few commands. Type
mysql -u root -p in your terminal and enter your password. If you followed the steps correctly, we should get the following output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.29 HomebrewCopyright (c) 2000, 2020, 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.mysql>
Congrats! Now let's create a new database with a simple table:
mysql> CREATE DATABASE test_db;
mysql> USE test_db;
mysql> CREATE TABLE users (
user_id int(11) unsigned NOT NULL AUTO_INCREMENT,
last_name varchar(55) DEFAULT NULL,
first_name varchar(55) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> DESCRIBE users;
In case you decide version 5.7 is not the one you needed, stop the service
brew services stop email@example.com and uninstall it by running:
brew uninstall firstname.lastname@example.org. Finally, perform a cleanup
Note: Before running these commands, remember to back up your database:
mysqldump -u root -p test_db --single-transaction --quick --lock-tables=false > test_db-backup-$(date +%F).sql
Completely Remove MySQL
In most cases, the previous step should be enough to remove the software. You might, however, want to completely remove all the files that were installed. Since we used Homebrew, the only folder we need to worry about is
/usr/local/var/mysql. We can quickly delete this folder by running:
sudo rm -rf /usr/local/var/mysql
There should now be no trace that MySQL was installed on your computer.
As a beginning developer working on a Mac, I highly recommend using Homebrew. It will make keeping track of all of your developer software easier than if you were to install each app separately. If you are interested in Hombrew here is a quick cheatsheet to get you started.