HomeHowTo › Installing MySQL, Basic Usage and Remote Access

Installing MySQL, Basic Usage and Remote Access

In this article I want to demonstrate how to install a MySQL server on Ubuntu 12.04, how to basically use it and how to enable remote access so that a particular database can be used by another computer.

1. Installing MySQL

Open a Terminal and enter the following commands to update the packet cache and install MySQL:

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

During installation you will be asked for a root password. This will be the password for the database’s root user.

To check if the mysql daemon is running you can use one of these commands:

service mysql status
ps ax | grep mysql
netstat -tnap | grep mysql

To start/stop/restart the daemon use one of these commands:

service mysql start | stop | restart
/etc/init.d/mysql start | stop | restart

The configuration file (my.cnf) is located in /etc/mysql/

2. Basic Usage

To connect to the database, enter:

mysql -u root -p

The -u switch specifies the username and -p means that you want to authenticate with a password.

In the MySQL command processor you can use these commands to navigate through the database system and get information about its structure:

# Find out which databases currently exist
# If you're not connected as root, you'll only see the databases your account has access to
show databases;

# Select a database to work with
use <databasename>;

# Find out which tables the currently selected database contains
# Again, if you're not root, you might not see everything here
show tables;

# Get information about the fields and datatypes of a particular table
describe <tablename>;

# Disconnect from the database
quit;

You can enter any SQL query you want into this command processor. Have a look at section 4 to find some exemplary queries.

3. Enable Remote Access

Open the MySQL configuration file:

sudo nano /etc/mysql/my.cnf

Look for this line in the [mysqld] section:

bind-address = 127.0.0.1

This line effects that the server only listens on localhost, which means that you can only connect to the database from localhost. To allow other computers to connect as well, you have to comment this line out (#) or change the address to 0.0.0.0. You have to restart the database daemon once you’ve made the change.

Now it is possible to connect to the database from a remote computer but no user is allowed to log in yet. For security reasons it might be a good idea to create a new user for the remote access instead of giving root the ability to connect remotely. Here are a few examples on how to create a new user:

# User may connect to the database if his machine's hostname is acidx
CREATE USER 'user'@'acidx' IDENTIFIED BY 'password';

# User may connect to the database if his IP address is 172.16.0.100
CREATE USER 'user'@'172.16.0.100' IDENTIFIED BY 'password';

# User may connect to the database if his IP address begins with 172.16.0
CREATE USER 'user'@'172.16.0.%' IDENTIFIED BY 'password';

# User may connect to the database no matter what
CREATE USER 'user'@'%' IDENTIFIED BY 'password';

Note: ‘user’@’%’ allows the user to connect from any remote location, but it does not allow the user to connect from localhost (cf. MySQL Documentation). For the ability to connect from localhost it is necessary to create an extra account:

# User may connect from localhost
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

All the user information is stored in the user table of the mysql database, which exists by default. To find out which users are allowed to connect from what IP/host use this query:

SELECT Host, User, Password FROM mysql.user;

To revoke access for a particular user:

DROP USER 'username'@'host';

As a last step you have to specify to which databases and/or tables the user has access to. This can be accomplished with the GRANT statement.

Note: It is not mandatory to priorly create the user as described above since GRANT will automatically do this for you if the specified user doesn’t exist yet. If you don’t create the user before however, make sure that you append the IDENTIFIED BY statement to your GRANT command because otherwise the user won’t need a password to log in. Passwords should always be used, especially when dealing with remotely usable accounts!

Note 2: Instead of granting ALL PRIVILEGES you can only set a few privileges like SELECT, CREATE and INSERT. To find out all the available privileges, consult the MySQL Documentation.

1
2
3
4
5
6
7
8
# Only grant access on a particular table
GRANT ALL PRIVILEGES ON databasename.tablename TO 'username'@'host';

# Grant access on a whole database
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'host';

# Grant access on a whole database and set a password for the user
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'host' IDENTIFIED BY 'password';

Line 2 will lead to an entry in mysql.tables_priv, which contains the privileges for single tables.
Line 5 will lead to an entry in mysql.db, which contains the privileges for whole databases.
Line 8 will lead to an entry in mysql.db and it will set the user’s password in mysql.user. If the user already exists, the old password will be overwritten.

Finally, to connect to the database from a remote machine, type:

mysql -h <ip address> -u <username> -p

 

4. Some Basic SQL Queries

# Get everything from table foo
SELECT * FROM foo;

# Get the first 100 rows from table foo
SELECT * FROM foo LIMIT 100;

# Get all rows but only the specified columns from table foo
SELECT bar, baz FROM foo;

# Only get the rows that match the given condition
SELECT * FROM foo WHERE bar=1 AND baz='a';

# Count the number of results
SELECT COUNT(*) AS count FROM foo WHERE bar=1;

# Sort the results
SELECT * FROM foo ORDER BY bar ASC, baz DESC;

# Add a new row
INSERT INTO foo (bar, baz) VALUES (2, 'b');

# Update a specific row
UPDATE foo SET bar=3, baz='c' WHERE id=1;

# Delete a specific row
DELETE FROM foo WHERE id=2;

Leave a Comment