Installing a Local MySQL Server

Installing and setting up a local MySQL server for use, simply because DB SQLite is too slow

The only thing I knew about SQL was just writing statements. Given that most of my practices came from hackerrank which already have an sql server built in, I did not really give much thought on setting one up on my own. I installed the basic DB SQLite a while back and I thought that was enough.

I found out how wrong I was when I was given a dataset with over 24 thousands rows. At that point I realize how slow DB SQLite actually was. So while my patience was running low, I started to look into SQL server I can set up for free. Unfortunately, that was also relatively tough. There are so many different kinds. Which one is right for me? After some thinking I went with the SQL language I was most familiar with: MySQL. After some googling and youtubing, I finally came across a video that helped me install the server. Here is the youtube video for those interested: Installing MySQL Workbench. However, there was some issues. Although overall the video is really well made and deserve credit, it is also a bit old and is missing at least one crucial information. The temporary root password. According to the video, they will give you one, but with the newest download version for Mac, you have to make your own root password. So I thought of just adding my mini tutorial as a supplement to the video for new installer.

Step one is to go to this website MySQL and download the latest version for your MAC (sorry windows user, I don’t have experience in that field but it should be relatively similar).

Once that is downloaded, follow along the installation and give your main user login password

Step two is the big difference between the video and the current model as of this writing. In the configuration step, they will ask you to input a root password. It can be as simple as you want or something a bit more complex.

I made my root password too long but can easily change it later

Step three is similar to the video again. At this point you can pretty much follow the video but here is the written version anyway. Open up your terminal, make sure you are at the PWD section and look for your bash_profile. If you have ever installed anything through conda, you will already have a bash_profile. Using the code below in your terminal PWD, open your bash_profile:

open -t .bash_profile
#And then insert the following code:
export PATH=${PATH}:/usr/local/mysql/bin/

After that, save and close your bash, clear the terminal and start a new terminal by opening a new tab or a new windows. Then run the following command to new terminal:

mysql -u root -p

Step Four is to make sure that you are connected to the MySQL server at this point (Just an FYI, I forgot this part at first). Otherwise you would get an error. If they ask for a password within the terminal, it is the password you have created (NOT YOUR COMPUTER LOGIN PASSWORD). Now you are successfully logged into the server. If you wish to change your MySQL password, run the following code:

mysql$ ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
#The 'MyNewPass' is the new changed password in this case
#You can also just run this if you forget your password:
mysqladmin -u root password NEWPASSWORD <- this is where you put your new password

Once you have successfully logged in, you will see your MySQL tables

Now that has been set up, let’s move on to installing the MySQL Workbench. The download link can be found here. Once you go through the download process, make sure to move the file into your application folder, like the video suggest. It will ask you for your user ID password, which is your main computer login password. If they ask you for your root password, then it will be the new password you just created at time of installation.

Step five is- if you still have your MySQL running from the start, is to open the workbench. You will see something like below:

If you don’t see the blue highlighted box Local instance then you don’t have your MySQL server running

If you don’t see the small blue box above, that means that your SQL server is not running. In order to run that, go click the apple in the top left of your screen, then System Preference. And a window like this should show up for you:

This was also a difference between the video and myself. He was able to open MySQL through preference whereas I just get this

If your MySQL server is offline, your buttons will be red and it will say Start MySQL Serve. Just click that and you have your server ready. Then just go to your workbench and upload or load up your database and start querying.

The installation tutorial I followed

Download MySQL Community Server

MySQL :: Begin Your Download