How to Start & Stop MySQL Manually in OS X El Capitan & Yosemite
Many developers require MySQL on their Macs, but if you’ve tried to install MySQL in OS X El Capitan and Yosemite, you likely noticed that you’ll get an “installation failed” error during the process. That error sounds worse than it is, because you can avoid it entirely by choosing not to install the startup item included in the MySQL bundle, or, you can just ignore the installation error and start MySQL yourself when you need it. Either way, MySQL actually installs fine, it’s just the bundled startup item that does not function. As you probably guessed, that means you’ll need to start and stop MySQL yourself.
Yes a preference panel gets installed that allows for a GUI approach, but many users prefer to use the command line for this purpose since many of us work in the Terminal anyway, and it has the added possibility of being automated.
Starting, Stopping, Restarting MySQL on Mac OS X
Hre are the three basic commands to start, stop, and restart MySQL in Mac OS X, including OS X El Capitan and OS X Yosemite. Be sure to enter the command onto a single line, sudo obviously requires an administrator password to be entered.
Start MySQL
sudo /usr/local/mysql/support-files/mysql.server start
Stop MySQL
sudo /usr/local/mysql/support-files/mysql.server stop
Restart MySQL
sudo /usr/local/mysql/support-files/mysql.server restart
Of course, these can be combined with starting and stopping Apache server if your intention is to setup a local web development environment.
You can download the latest version of MySQL for Mac OS X here. Future versions of the MySQL installer will undoubtedly fix this for OS X but in the meantime if you get the installation error, either customize the installer and avoid the startup item, or ignore the error and start and stop mysql yourself when it’s needed.
Those interested can also follow a workaround posted to StackOverflow here to automatically load MySQL on boot in OS X El Capitan or Yosemite.
Start, Stop, Restart MySQL from Mac OS Preference Panel
Of course, you can also start and stop the MySQL server from the bundled preference panel. To do that, simply go to the Apple menu and open System Preferences. Choose the “MySQL” preference panel, then click on the “Start MySQL Server” button to start MySQL Server on Mac. If the server is already started, the button will change to “Stop MySQL Server”. If you want to restart the server, simply click to turn it off, wait a minute or so, then turn it back on again. This will be the easy approach for many Mac users, though you’ll have to fiddle with the pref panel as necessary, and if you go that route you’ll want to uncheck the auto-start option because it’s going to fail.
I’m partial to the command line approach for the time being, but use whatever is most appropriate for your situation.
By the way, these mySQL server management methods continue to work in MacOS Sierra as well.
MySQL on Mac — Getting Started
Getting started with MySQL on a MacBook in 2020.
Notes For Me | Notes for You
I work on a project or take a self-paced course every few months that requires SQL. However, each time I get started, I forget everything about getting started. Take, for example, a sampling of just four out of many questions I ask in a sweaty panic: I just want SQL, what’s the deal with this server? What was that thing I installed last time? Which version am I running?
Although I always want to get straight to creating a table or writing a SELECT statement, the thing is, there are a few necessary steps to make things happen. As a result, and in all honesty, I’ve written these as notes to my future self on how to get started, but I share them here because I suspect some of you out there might benefit as well.
My Operating Environment
The Things
There are a dozen-plus ways to get started with SQL on a MacBook, but I’ll cover one simple way to get started with MySQL. The two things you’ll need to install are MySQL server and one of the following tools: MySQL Workbench or SQLPro for MySQL. The one thing to remember is that you need a SQL server running on your computer and a SQL management tool connected to that server — once your two things are connected, create tables, run queries, and do whatever from the SQL management tool.
Install Stuff — Step 1 — MySQL Server for Mac
Install MySQL Server for Mac, instructions and download here. Once MySQL is installed, the first thing to do is Start the MySQL Server. From System Preferences, open MySQL, click on Initialize Database, then create a password for the “root” user. If you’ve already installed and configured MySQL, then just take note to start the server in order to get started.
Install Stuff — Step 2 — SQL Management Tool
Now that you have an operational MySQL server on your Mac, you need a management tool to connect to it, create databases, and do great things. Two management tools I’ve used include MySQL Workbench and SQLPro for MySQL. Both tools work just fine; however, MySQL Workbench is free while SQLPro for MySQL costs about $137 for a lifetime license.
The trade-off? Workbench is more sophisticated but also more complex to get started from zero. SQLPro is available from the Mac App Store and has less features but has a more friendly user interface. I’ll do my best to cover the basics of both; however, after committing to the SQLPro app, I’ve started to prefer its simplicity over Workbench’s sophistication.
Connect Management Tools to MySQL Server
Now that you’ve launched a MySQL Server on your Mac and have a MySQL Management Tool installed, it’s time to connect them. Before moving on, take note of the MySQL Server settings: it most likely runs on port 3306 (the default), is called localhost, and has a password and username for “ root.” At this point, if you’ve forgotten the password to “root,” re-initialize the database from System Preferences and make sure to remember the password.
At a high-level, both management tools will detect that a MySQL server is running on the computer. From there, establishing a connection is as easy as selecting the server and entering the username and password.
Connect Stuff — Option A— MySQL Workbench
For MySQL Workbench, there is an option to scan for server connections. As long as the MySQL server is already running from the previous step, Workbench will detect and then prompt for a password.
After making a successful connection, Workbench opens a new tab for “Local instance 3306” that contains things like a query and schema window. In the next screenshot, take notice of the “sys” schema that appears by default. For now, just be aware that this comes as a default to each MySQL database.
Connect Stuff — Option B — SQLPro for MySQL
For SQLPro, you’ll have to establish the connection to MySQL Server manually by entering information such as localhost, 3306, and username and password for root.
After making a successful connection, notice how SQLPro creates a simplified version of what Workbench produces with localhost, sys, and workspace for a new query.
Create Database and Tables
At this point, you are basically ready to rock and roll with an operational MySQL Server and a connected SQL Management tool. In these final steps, let’s take a look at creating a database, creating a table, and inserting data in both MySQL Workbench and SQLPro.
Create Stuff — Option A—MySQL Workbench
From the Workbench Query tab, CREATE a new DATABASE called “applications” then click the lighting icon ⚡️to run it.
After creating the applications database, clear the text in the query tab and start using the database with USE.
Create a new table in the applications database called “resumes” with CREATE TABLE along with some columns.
Now, what do we have? We should have a database called applications with a table called resumes. We can use DESCRIBE to check the parameters.
Lastly, insert some values that represent a resume in the first row.
Below, we can see the results of the INSERT statement with SELECT all. If you’re only working from MySQL Workbench, you’re basically done and can move onto whatever else you’re up to in SQL.
Create Stuff — Option B— SQLPro
In prior steps with SQL Workbench, we created a database called applications and a table in that database called resumes. For this story, I want to show how to utilize both Workbench and SQLPro and as a result, as we switch over to SQLPro, we can see that both tools can work from the same database.
After connecting SQLPro to the MySQL database on your computer, you have two choices. First, you can go through similar steps as above to create a new database and table. Or second, you can add a table to the existing database — I’ll start with this option by creating a new table called “interviews.”
Next, insert some data about the interview candidates, then view the table.
Given all the above, we now have a simple applications database that has a table of resumes and a table of interview dates.
Next Steps
Congratulations — hopefully we will both remember how to get started with a basic SQL database on MacOS with this little tutorial. Next steps might include uploading a database of resumes and interviews to create tables, using joins to select data from both tables, and updating information about candidate interviews.