Updated 2017-02. Ubuntu 16.04 LTS. Apache 2.4.18, PHP 7.0.13, MySQL 5.7.17

Background

These are my notes I use to get the MySQL server software setup on my development computer. I want to be able to work on the databases from the local computer as well as from other computers on a trusted LAN.

MySQL Server Setup

  1. Install the Software

    This is not an unattended install. You will be asked if you want to supply a password for the MySQL root user during installation. It is a good idea to have one. Then there are a couple of question to go along with phpmyadmin as well. Assuming you have Apache installed, you can have it automatically configured.

    me@myhost$ sudo apt-get install mysql-server mysql-workbench
    me@myhost$ sudo apt-get install phpmyadmin

    There is some redundancy in the software. You might be able to get everything done with only the MySQL GUI, but if you will be accessing your databases over the internet, it will probably be worth learning how to manage them using the phpmyadmin program.

  2. A Simple Test

    Here are a couple of quick commands you can run in a terminal to confirm your MySQL installation. This shows commands to log in and see the databases and the users of the MySQL database.

    me@myhost:~$ mysql -h localhost -u root -p
    Enter password: [Type password used at installation.]
    mysql> show databases;
    [see grid of databases]
    mysql> use mysql;
    mysql> select user, host, password from user;
    [see grid of users]

    If you get some kind of error at this point, it probably doesn't make sense to proceed.

  3. Create Databases

    To create databases, my notes are going to use the phpmyadmin tool for simplicity. Note: I actually have to use the Cpanel interface to do the database creation on my website.

    Log into phpmyadmin as the root MySQL user. [This is a little confusing, as the automated setup asked you to provide a password for phpmyadmin. I believe that is only for the internal database]. From the "Home" panel, click on the "Databases" tab. You will see the existing mysql (database of the databases), phpmyadmin and the "test" database. Type in the name of your new database, choose a collation and click "Create". One of the most flexible collations is utf8_general_ci.

    phpmyadmin create DB

  4. Import Previous Databases If Applicable

    Since I probably use these notes more than anybody, I'm including this step which is only needed if you are upgrading software on your server. In a previous episode, I would have clicked on a database, gone to the Export tab, kept all the defaults to create backup SQL file and clicked Go. This file was then stored on a backup hard drive, which actually had the power cord uplugged during upgrade for safety. To import the complete database, click on the database name, go to the Import Tab, browse to the stored SQL file, accept the defaults and click Go. This has worked easily for me over a number of years.

    phpmyadmin import DB

  5. Adding a Non-root User and Granting Priveledges

    Your PHP scripts should not be using the root account to be accessing the database. It makes sense to create a lesser user with only the priveleges needed. I go a little overboard in my example and grant everything except the GRANT and SUPER priveleges in the example below.

    As the root user, go to the Home screen and click on the Priveleges Tab. Click on the Add A New User link and fill in the username, host, and password. If you are only using 1 development computer, host=localhost.

    phpmyadmin add user

  6. MySQL Access From Another Computer via phpmyadmin

    To connect from another computer in my network, I use fixed IPs to make things easier for myself. Create another entry for your user as above and include the CLIENT computer IP address.

    This worked immediately for me with 2 computers having fixed IP addresses. The URL for reaching phpmyadmin is http://SERVER_IP/phpmyadmin. If you want to modify the web pages on your server in a linux-to-linux setup, I suggest using NFS File Sharing for the most seamless operations.

Notes

This is currently the preferred way to restart the mysql service.
me@myhost$ service mysql restart