The MySQL database server is one of the most popular open-source database servers commonly used in web application development.
Welcome to part 2 of the MAMP tutorial. In this part we will go over setting up Mysql on your Mac's localhost. Part 1 goes over everything you need to do to setup PHP on a Mac What green means: any lines that you need to replace in this tutorial will be in green. Step 1 Download MYSQL. The first step is to download MySQL server. Go to the MySQL web site and select the version that matches your version of Mac OS. Archive version. After the DMG file finishes downloading click on it to open. Then open the installer inside and follow the installation steps. Lets move now to install MySql database server on Mac OS X. Install MySql on Mac OS X. Download the MySql DMG file from MySql Download Website. Open the DMG file and install MySql server and Preference Pane for starting and stopping MySql server easily. Start the MySql server if its not running and optionally you can select the checkbox to. MySQL User Accounts. Click Add User to create a user account. In the MySQL User Details dialog box, enter a user name, a database role, and a password (for example,!phpuser). At the third MySQL Server Configuration page (3/3), set the following options: Windows Service Name. Specify a Windows Service Name to be used for. The MySQL installer usually installs the files in the directory named mysql within /usr/local/. Just like Windows, MySQL installer for Mac doesn't add MySQL executable to the PATH environment variable. Consequently, we have to execute MySQL by specifying the full path to it in the terminal. We are now ready to start the server.
This document recommends a sequence of steps to set up the MySQL database server 5.6 versions in the Windows operating system. It does not cover MySQL configuration details, it describes a sequence of required steps.
For information about installing and configuring MySQL database server for other operating systems, refer to the Installing and Upgrading MySQL documentation.
Note: The Setting Up the MySQL Database Server 5.1 versions in the Windows Operating System document provides the instructions on setting up the 5.1 versions of MySQL Server on Windows.
Contents
Starting the Download
- Go to http://dev.mysql.com/downloads/installer/.
- Click the Download button.
- Save the installer file to your system.
Create Local Mysql Server Mac
Starting the Installation
After the download completes, run the installer as follows:
- Right-click the downloaded installation file (for example, mysql-installer-community-5.6.14.0.msi) and click Run.
The MySQL Installer starts. - On the Welcome panel, select Install MySQL Products.
- On the License Information panel, review the license agreement, click the acceptance checkbox, and click Next.
- On the Find latest products panel, click Execute.
When the operation is complete, click Next. - On the Setup Type panel, choose the Custom option and click Next.
- On the Feature Selection panel, ensure MySQL Server 5.6.x is selected, and click Next.
- On the Check Requirements panel, click Next.
- On the Installation panel, click Execute.
When the server installation is completed successfully, the information message appears on the Installation panel. Click Next. - On the Configuration panel, click Next.
- At the first MySQL Server Configuration page (1/3), set the following options:
- Server Configuration Type. Select the Development Machine option.
- Enable TCP/IP Networking. Ensure the checkbox is selected and specify the options below:
- Port Number. Specify the connection port. The default setting is 3306 - leave it unchanged if there is not special reason to change it.
- Open Firewall port for network access. Select to add firewall exception for the specified port.
- Advanced Configuration. Select the Show Advanced Options checkbox to display an additional configuration page for setting advanced options for the server instance if required. Note: Choosing this option is necessary to get to the panel for setting the network options where you will turn off the firewall for the port used by the MySQL server.
- At the second MySQL Server Configuration page (2/3), set the following options:
- Root Account Password.
- MySQL Root Password. Enter the root user's password.
- Repeat Password. Retype the root user's password.
Note: The root user is a user who has full access to the MySQL database server - creating, updating, and removing users, and so on. Remember the root password - you will need it later when creating a sample database. - MySQL User Accounts. Click Add User to create a user account. In the MySQL User Details dialog box, enter a user name, a database role, and a password (for example, !phpuser). Click OK.
- Root Account Password.
- At the third MySQL Server Configuration page (3/3), set the following options:
- Windows Service Name. Specify a Windows Service Name to be used for the MySQL server instance.
- Start the MySQL Server at System Startup. Leave the checkbox selected if the MySQL server is required to automatically start at system startup time.
- Run Windows Service as. Choose either:
- Standard System Account. Recommended for most scenarios.
- Custom User. An existing user account recommended for advanced scenarios.
- At the Configuration Overview page, click Next.
- When the configuration is completed successfully, the information message appears on the Complete panel. Click Finish. Note: To check that the installation has completed successfully, run the Task Manager. If the MySQLd-nt.exe is on the Processes list - the database server is running.
See Also
- Working and Connecting with Databases in Developing Applications with NetBeans IDE
This article is part of a series.
- Part 1 - Beginning MySQL for Data Analysts
- Part 2 - Understanding the MySQL Query
- Part 3 - This Article
The last two articles have been getting oriented to SQL, however, the information in them will disappear quickly if we don’t give you a way to practice on data meaningful to you. Let’s face it, as much fun as it is to find out random employees salaries, those don’t mean anything to you.
This article will show you how to setup a copy of MySQL Server on your PC, connect to it, load data from a CSV, and query those data. There’s a lot to get done, so let’s get started.
Each of the three operating systems are a little different on how you must go about setting up a local copy of MySQL Server. Unfortunately, Windows is the most complex. Anyway, feel free to skip to the appropriate section
Windows
First, download the MySQL MSI Installer.
After you’ve downloaded it, open the the file.
If you are prompted to “Upgrade” go ahead and say “Yes”
The installer is a bit confusing, but don’t worry, most everything is fine left on its default.
Click on MySQL Server then the Add button. Add “MySQL Server” and “Connector/OBDC x64.” Then click “Next.” You will see a Installation summary, click on “Execute” and wait for the download to finish and then install wizard to begin.
As I stated, most of the install wizard questions we will leave as default.
On the “Accounts and Roles” section you will need to decide on your password for the SQL Server on your local PC. I obviously didn’t pick a great one. MySQL Server will automatically setup a user with the name of
root
and a password you set here. This root
user will permissions to do anything to the server.Execute the installer and let it finish.
Once it finishes you should now have MySQL Server installed on your local PC. Skip to the last section to test it out.
Mac
Mac’s a bit simpler.
Download the
.dmg
installer.Click on “No thanks, just start my download” and when the download is finished, double click on it.
Double click on the installer. You will need to enter your system password for the installer to setup MySQL Server, but you will also need to provide the MySQL Server
root
user a password. Don’t consfuse the two, unless you plan for them to be the same.Once it finishes you should now have MySQL Server installed on your Mac. Skip to the last section to test it out.
Go ahead and open MySQL Workbench and let’s connect to this new local server.
Click on the “New Connection” icon and leave everything default, except the “Connection Name,” here enter
localhost
.Microsoft office 2016 free download for mac os. Double click on the new connection and enter the password you created during installation. Voila!
Let’s run a command to make sure everything is working.
You should see:
Create a database
Before we create a table, make sure we are using the created datebase.
Now, we need to go over a bit of boring stuff before we get to loading the CSV. Sorry, I’ll try to keep it brief.
Datatypes
In SQL, every field has something called a “datatype.” You can think of a datatype as a tag on your data tell the computer how to read them.
Ultimately, a computer can’t make sense of any human-words. It has to convert everything into
0
and 1
before it understand its. If this conversion was left up to the computer entirely, it might see a word and say, “Oh, yah, this is one of those French words,” when it is actually English, thus, the conversion to 0
and 1
s are incorrect.You may have encountered this in a spreadsheet. If you open a spreadsheet and see something like
The data I actually provided the spreadsheet were:
Zipcode |
---|
75444 |
06579 |
Notice the zero in front of
6579
, this was due to the computer saying, “Oh, these data look like numbers–and since the human didn’t tell me otherwise, I’m going to treat them like numbers. And, well, it is perfectly valid to drop the leading zero of a number.”I wish all datatypes were this simple, however, the above example is about is simple as it gets. We can try to skip over a lot of nuances of datatypes and focus on the three we will probably see the most:
- DATE
- TIME
- INT (short of integer)
- FLOAT
- CHAR (short for character)
Here are what samples of the above data would look like in a spreadsheet:
DATE | TIME | INT | FLOAT | CHAR |
---|---|---|---|---|
2019-10-01 | 2019-10-01 12:01:22 | 42 | 42.4 | The answer to it all. |
DATE
Dates are pretty straightforward, they store a year, month, and day as a number. However, when we retrieve this number it is put in the human readable format listed above.
TIME
Time is exactly like
DATE
, but it also includes hours, minutes, and seconds (sometimes milliseconds).INT
An
INT
stores a number no bigger than 2,147,483,647
. However, one thing an INT
cannot do is store a partial numbers. For example, if we try to store 0.5
in an INT
field it will probably get converted to 1
.FLOAT
Local Mysql Server For Mac Versions
FLOAT
s fill in where INTS
fail. That is, a FLOAT
store only up to the precision you specifiy. For example, if we tried to store a 0.5
in a FLOAT
with two precision points we’d be fine. However, if we tried to store 0.4567
in a FLOAT
with only two precision points, then it would be converted to 0.46
, or rounded up.CHAR
CHAR
is meant to store human readable text. When you put data into a CHAR
field, the SQL program knows this is human readable information and doesn’t try to figure it out at all. It leaves it literally as it is. This is why CHARS
are known as “literals.” They are also called “strings,” because the computer seems them as a bunch of characters strung together.SQL Datatypes
In SQL there are a lot of datatypes, however, some you may never need to use. One way SQL is a bit different than a spreadsheet is it wants to know ahead of time the size it needs to make the field.
CHAR Revisited
This will mainly impact us when dealing with
CHAR
. When the SQL program creates a CHAR
field it wants to know the maximum number of characters which will ever go into the field.For example:
- CHAR(19) could hold the following:
<-------19-------->
- CHAR(5) could hold the following:
<-5->
One important note, if you put a single character in a
CHAR(5)
field, then the SQL program will fill in the other four characters with a NULL
. In short, a CHAR
field will always be full.VARCHAR
There is another type of character field which allows you to put more or less data than was decided at the beginning. The
VARCHAR
datatype stands for “variable character” field. It will allow you to store up to 65,535
characters on MySQL. This is around 3 pages of text.VARCHAR vs. CHAR
Why have
CHAR
at all? Shouldn’t we always use VARCHAR
for everything just in case? Well, usually, but not always.Often, when you design a database you want to make it as efficient as possible (I mean, it’s going to be successful business product, right?). The maximum size of the data a human will try to stuff in the field is important to the SQL program, as it tries to store data in such a way it minimizes space used and maximizes efficiency in retrieving the data.
In short,
CHAR
has a few advantages for your database. And take Social Security Numbers, if your database has to store these data then it should probably be a CHAR
as these data have historically been 9 characters (11 if you include dashes).Pop quiz, why don’t we store a Social Security Number as an
INT
?Creating the Table
Ok, I’ve put you through a crash course of datatypes to get you to this point.
We are going to:
- Create a database called
tasksDB
- Active
tasksDB
- Create a table on
tasksDB
, setting the fields datatype - Then import a CSV into this table
- Lastly, we will write a query against the table
Ready!? Let’s do it!
Creating Database
Open Workbench, type, and run the following:
https://superuser.com/questions/1354368/mysql-error-in-loading-csv-file-data-into-table