Installing MySQL on Ubuntu 14.04

In this post I will cover installing MySQL on Ubuntu 14.04, using the repository available at the mysql apt repo. The idea is to setup a repository that allows our MySQL to be current– in this case MySQL 5.6. The instructions I’m documenting here are derived from the above link, as well as the nice book Jump Start MySQL (April 2015). If this is of interest to you, follow along and be sure checkout the resources mentioned above.

Okay, you’re still reading, so let’s get started.

1. First we download a deb file that will setup the repository information from mysql apt repo– look for the Ubuntu 14.04 download button. As noted in Jump Start MySQL, it looks like you have to login or sign up, but you don’t. Just click No thanks, just start my download near the bottom of the page and choose a convenient place to save the file.

Next, start the terminal and go to the save location. To install, simply type (your file name might be different, so adjust accordingly):

$ sudo dpkg -i mysql-apt-config_0.3.3-1ubuntu14.04_all.deb

This will bring up a menu that asks which MySQL product we’d like to configure:

  • We leave the selection as Server, the default, and hit Enter.
  • Next, we select mysql-5.6, again the default, and hit Enter. This takes us back to main menu.
  • Finally, select Apply and hit Enter– this should exit to the terminal with a simple OK.

2. Now that the repository is setup we can use the usual install commands:

$ sudo apt-get update
$ sudo apt-get install mysql-server

In our Ubuntu install of MySQL we will be prompted to choose a root passsword. Note, just to be clear, that this root account and password apply only to the MySQL server and not to the Ubuntu OS. As always, choose a strong password and record it using a suitable tool.

3. We can check the status of the MySQL server using

$ service mysql status

This should tell us the server is running. To start and stop the server, use

$ sudo service mysql start

and

$ sudo service mysql stop

That’s it, MySQL is up and running.

Beginnings

To connect with the server, try the following, using the root password setup above:

$ mysql -u root -p
Enter password:

The response should be something like:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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>

The mysql> prompt is where we type all of our commands. It is conventional to use CAPS for SQL keywords like so:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

These are the existing DBs after the initial install. To exit, simply type:

mysql> exit
Bye

or, hit CNTRL-D.

User Accounts

As suggested in Jump Start MySQL it is a good idea to create user accounts for daily use instead of using the root account. This can be done with a command like:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

where username and password are substituted with the desired values. The hostname, in this case localhost, can also be changed if connections off of the local machine are needed. However, that’s beyond the scope of this post. Finally, the new user can connect to the MySQL server using:

$ mysql -u username -p

where the appropriate username and password are used. Of course, privileges can be granted and revoked in quite specific ways to databases, tables, etc. Again following Jump Start MySQL, a quick example of this might go:

  • Create testdb database using root account:

    mysql> CREATE DATABASE testdb;
    
  • Enable privileges:

    mysql> GRANT CREATE, DROP, ALTER, INSERT, UPDATE, SELECT,
        -> INDEX ON testdb.* TO 'username'@'localhost';
    
  • Flush privileges to make them active without restarting MySQL server:

    mysql> FLUSH PRIVILEGES;
    

With these commands, the user username should be able to access the new testdb. To revoke some of the privileges, the command would be something like the following:

mysql> REVOKE CREATE, DROP, ALTER, INDEX
    -> INDEX ON testdb.* TO 'username'@'localhost';

Of course, this would be followed by a FLUSH PRIVILIGES; to make the changes active.

Final Thoughts

That’s all the basics. I’ll be posting more on SQL, covering various topics in the coming weeks and months, so check back. As always, corrections, comments and questions are welcome.

Tags

api [1]   arduino [1]   audio [2]   audio features [1]   babel [1]   Bayesian [7]   Beta [1]   blog setup [1]   bootstrap [1]   bottleneck [1]   c++ [1]   caret [1]   cmpy [1]   conditional probability [6]   coursera [1]   coursera intro to data science [3]   css [1]   cython [1]   d3 [2]   decision trees [2]   diy [1]   dropbox [1]   dsp [1]   e1071 [1]   essentia [1]   garmin [1]   geojson [1]   ggplot2 [1]   gis [2]   git [1]   gnuplot [1]   graphs [1]   html5 [1]   igraph [1]   ipython [1]   javascript [7]   joint probability [6]   json [1]   LaTeX [2]   LDA [1]   Lea [2]   machine learning [3]   marginal probability [6]   matplotlib [1]   meteor [2]   mir [1]   MongoDB [3]   music [2]   my python setup [5]   my ubuntu setup [10]   mysql [3]   networks [1]   networkx [1]   nodejs [5]   npm [3]   numexpr [1]   numpy [1]   octave [1]   Open Oakland [2]   openpyxl [1]   pandas [3]   patsy [1]   pip [2]   pweave [1]   pygraphviz [1]   pymc [1]   PySoundFile [2]   python [15]   Python [1]   python 2.7 [5]   python 3.4 [2]   pyyaml [1]   qgis [1]   R [1]   randomForest [1]   restview [1]   resume [1]   rpart [1]   running [1]   scikit-learn [3]   scipy [1]   screen [1]   server setup [1]   shapefile [1]   social networks [1]   Socrata [1]   sound [2]   sphinx [1]   sql [4]   sqlite3 [1]   ssh [1]   ssh keys [1]   statsmodels [1]   supervised learning [2]   sympy [1]   tableau [1]   tinkerer [2]   topic models [1]   tree [1]   ubuntu 14.04 [13]   Ubuntu 14.04 [3]   ubuntu 16.04 [4]   vim [2]   virtualbox [1]   virtualenv [4]   virtualenvwrapper [3]   VPS [1]   vundle [1]   webpack [1]   yaml [1]