Installing and Starting MySQL Server

MySQL Database Management System

 
MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. It is intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.

Installing MySQL Server:

To install MySQL, run the following command from a terminal prompt:

sudo apt-get install mysql-server

During the installation process you will be prompted to enter a password for the MySQL root user.

Once the installation is complete, the MySQL server should be started automatically. You can run the following command from a terminal prompt to check whether the MySQL server is running:

sudo netstat -tap | grep mysql

When you run this command, you should see the following line or something similar:

tcp        0      0 localhost.localdomain:mysql           *:* LISTEN -

If the server is not running correctly, you can type the following command to start it:

sudo /etc/init.d/mysql restart 
 

Configuring MySQL for Network Access:

The typical default install of MySQL server only permits connections from localhost (127.0.0.1); this is presumably for reasons of security. While this is certainly secure, in some cases it is undesirable. This post explains how to permit network access to a MySQL server from remote clients.

Locate the my.cnf file, which is the master configuration file for MySQL server. (On a Ubuntu system this file may be located in /etc/mysql.)

Open this file in your favorite editor and look for the following entry:

bind-address = 127.0.0.1

This limits the MySQL server to listening to connections on the localhost address, as explained earlier.  To instead make the MySQL server listen on all interfaces, edit this entry to the following:

bind-address = 0.0.0.0

Save the file, then restart the MySQL server:

sudo /etc/init.d/mysql restart

Your MySQL server should now be network accessible. To verify that it's listening on all interfaces, issue the following command:

netstat -anp | grep 3306

If you see the following, then your configuration is complete:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN 

From the MySQL prompt, grant the user access to a specific database schema. The general format of the command to use is:

GRANT ALL PRIVILEGES ON database.* to ‘user’@'yourremotehost' IDENTIFIED BY 'newpassword';

Where:

‘user’ is the user name of an existing MySQL account. The username is enclosed in single quotes, as shown.

database is the name of the database schema where access will be granted. Either the name of the schema can be used, or an asterisk (*) can be used to specify all databases.

‘yourremotehost’ contains either an IP address where the user will access from, or a domain name. The IP address or host name must appear in single quotes, as shown.

‘newpassword’ contains the password the user must use to access the server. The password must appear in single quotes as well.

To force the changes to take effect immediately, enter the following command:

FLUSH PRIVILEGES;

Specific Examples:
Example A: Granting access for the user jsmith from an IP address:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';

Example B: Granting access from a domain:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'%.mycompany.com' IDENTIFIED BY 'jimspassword';

Example C: Granting access to all schemas:

GRANT ALL PRIVILEGES ON *.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';

Example D: Granting access from a specific host name on a domain:

GRANT ALL PRIVILEGES ON *.* to jsmith@'jimspc.mycompany.com' IDENTIFIED BY 'jimspassword';