First of all, why do I learn MySQL in the first place. As you may know, I’m a professional iOS developer. I’ve been developing apps for more than 1,5 years already. For now, I can only do the client side of an app. Which is ok. It allows me to have a job and make some money. But I always wanted to be able to create a client-server app all by myself. I always knew that eventually I’m going to learn a little bit of server side programming, but up until recently I’ve been postponing the learning.
Some time ago I started learning Java. I played around with servlet programming and created a couple of hello-world type of programmes. Then I decided to learn about JDBC and connect a simple MySQL database to my hello-world servlet. I had some theoretical knowledge about MySQL an it’s query syntax, so I thought I’d just study how to connect MySQL to Tomcat. After spending some time reading I realised that it was not that easy because I lacked hands-on experience with MySQL.
So I decided to set aside servlet programming for a while and learn some basics of MySQL. Now I’m going to tell you what I’ve found out. If you are just starting out with MySQL, this post may be helpful to you. I must warn you that I’m not an expert on this subject. I’ve only been learning this stuff for a couple of days.
The big picture
Just a couple of days ago I wasn’t sure what MySQL really was. Was it a library that one should include into the Java project? Was it a server that I needed to connect to Eclipse? You know, you can tell Eclipse about your Tomcat server, so that Eclipse can use it. Maybe I should have done something similar with MySQL server? I spent some time figuring this stuff out.
I found out that MySQL is a server. And a server is a program that runs on a computer sort of in the background. It’s job is to wait for clients’ requests and to send responses to those requests. What you need to do is download a program called MySQL server and install it on your computer. Then you should start the program. After the MySQL server has started you can talk to it from command line. You can create your database and populate it with initial data using SQL commands.
How do you use MySQL server from your Java program? You need a connector which will connect your Java program to MySQL server. The connector is called JDBC (Java DataBase Connector) and it is a class or a bunch of classes (I’m not sure now what exactly it is). This connector can work with multiple types of database servers, not only with MySQL. To enable it to work in conjunction with MySQL server you will need to download a driver and attach it to you project. But we won’t discuss it in this post.
Downloading MySQL server
To download MySQL server go to this page: http://dev.mysql.com/downloads/mysql/
I downloaded this one: “Mac OS X 10.11 (x86, 64-bit), DMG Archive”. Installation was pretty straightforward.
Starting the MySQL server
Before you can do anything with the MySQL server you need to start it. To start, stop or restart the server at first I used these commands in the Terminal:
sudo /usr/local/mysql/support-files/mysql.server start
sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server restart
Later I found out that there was a more convenient way to start and stop the server. If after installing MySQL server you go to System preferences you will see a MySQL icon. Click it. Now you have a button with which you can start and stop the server.
To connect to the server from Terminal go to this folder /usr/local/mysql/bin
cd /usr/local/mysql/bin
and execute this command:
./mysql -u root -h localhost -p
(here -u means user, -h means host and -p means password).
Now type in your temporary password which you got right after you installed MySQL on your computer.
Now, if you try to execute some command, mysql will tell you that you need to set a new password instead of your temporary password. I used this command to do it:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;
MyNewPass is your new password, obviously. I used the password 123. So my command looked like this:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘123’;
Annoying issue
After playing around a little with MySQL something went wrong. I wasn’t able to stop or start the server. There was some error like “The server quit without updating PID file”. There were other errors related to this “PID file”. I Googled and Googled. Different people on the internet suggested different things. Somebody said you should edit PID file (I couldn’t even locate this file, by the way). Another person said you should delete it. Yet another one said you should create an empty PID file. Nothing helped. Then somebody said you should just kill the mysql process. Like this:
sudo kill -9 PID
PID is a number of the process. You get it from a list of processes which you can see if you type
ps aux | grep mysql
into the command line. I killed this process but it kept resurrecting. Even restarting my computer didn’t help. At this point I was really annoyed. All I wanted to do was to play around with MySQL server and practise some commands. And instead I spent several hours not even knowing what I was doing.
Finally I decided to just remove the MySQL server from my computer and install it one more time. I found the instruction on how to do it and just typed blindly these commands into the Terminal one by one:
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
edit /etc/hostconfig and remove the line MYSQLCOM=-YES- (I didn’t do this one because there wasn’t such a file on my computer)
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
After I deleted the server the mysql process was still running, so I had to restart my computer one more time to get rid of it. Then I just reinstalled MySQL server and after that it worked just fine.
Learning MySQL commands
I used the “MySQL 5.6 Reference Manual”, which you can find here: https://dev.mysql.com/doc/refman/5.6/en/tutorial.html, and went through it. More precisely, I went through sections 3.1 to 3.5 of chapter 3 called “Tutorial”. I didn’t just read it, but practised commands in the Terminal. It took me about 1,5 hours. I could have gone further and read section 3.6 “Examples of Common Queries”, but I decided I don’t need it for now. The idea was to learn just enough to be able to create a simple database and connect it to Java project.
What’s next
My next step will be to practise servlet programming some more and finally learn how to use MySQL server in my Java project. I will write another post about it.
