In our last episode we looked at getting MySQL, today we will be looking at how you connect to a MySQL server and what that implies.
Connection basics
Before a client can connect to a MySQL server it needs a path by which that connection can be made. One method that is almost universal is the 'socket'. As its name implies it is a way of plugging two (or more) applications together. Sockets can either be end points for a network connection (for instance a TCP socket) or can use the same system-level functions but use a local connection. This is sometimes called a UNIX socket, and relies on there being a special file that the two applications can use to initiate a connection. MySQL can use both.
For a network connection you need a number of pieces of information. As a connection has two endpoints you need information for both ends, as a connection is uniquely defined by its endpoints. An endpoint is defined by its address, the port, and the protocol. As we will be talking about TCP connections the protocol is always going to be TCP. The address and port can be thought of as the connections on a tanker filling a number of tanks, the pipes between them need to conect the right pump with the right tank. The address is the tanker, the port is the individual outlet. Generally you don't need to worry about the address and port at the end from which you are making connections, they are normally handled automatically. To make a connection to a remote MySQL server you really only need to supply the address (or hostname) of the remote server, the port will default to 3306 unless specified or unless the configuration file dictates another port.
With a UNIX socket you need the name of a special file. There is no port associated with it and it always resides on the same server as the applications that use it. So you don't have to know the name of the file, MySQL uses the special host name 'localhost' to indicate that the socket method should be used, and gets the socket name from its configuration file.
Localhost vs 127.0.0.1
With network connections, there are two special addresses that always refer to the machine you are currently using. This is either called 'localhost' or has the IP address of 127.0.0.1. In general using these is equivalent and interchangeable, and uses a special path through the networking subsystem called the local loopback interface. However MySQL has reserved 'localhost' for the special purpose of indicating the use of a UNIX socket and not use networking. This can cause some confusion to new users, but if you understand what is going on it is pretty clear.
'localhost' in MySQL uses the UNIX socket and ignores any port you may supply.
127.0.0.1 in MySQL uses networking and requires a port, using the default if you don't supply it.
Why it matters - Authentication
When you make a connection to a MySQL server you need to supply more than just the address. You also need to supply a user and a password, and will at some point need to identify the database you want to use. How these are authenticated depends on the path that you use to connect.
When you supply a user and password for a database the MySQL server checks what connection information you supplied and checks the mysql.user table first. If you look at that table you will find that it includes the fields User, Host and Password, along with a number of permission fields. Both the User and Host fields can include the wildcard character '%' to indicate all possible values.
The Host field indicates the hostname or IP address from which you are connecting. So you can have a user with the same name but connecting in different ways having completely different permissions, and even different passwords! What is more important is that a user on the same host can end up being treated differently if they use the socket connection (by specifying localhost as the host) or using a network connection (by specifying the IP address 127.0.0.1).
To allow a user access to a particular database, the most common method is to use the GRANT syntax. Here are a few examples:
GRANT ALL ON mydb.* TO 'myuser'@'%' IDENTIFIED BY 'reallylongpassword';
GRANT ALL ON mydb.* TO 'myuser'@'localhost' IDENTIFIED BY 'anotherlongpassword';
The syntax is pretty simple, the word (or words) after GRANT indicate the permissions being granted, after ON is given the table specification, in database.table format. In our case we are granting all permissions on all tables in the mydb database. After TO is a user specification indicating the user followed by @ followed by the hostname. Finally a password is supplied in the IDENTIFIED BY clause. More information can be found in the MySQL Reference Manual.
Now look at those two statements. The first grants access to, and creates, the user 'myuser', regardless of which host he is connecting from (the % being a wildcard). The second then grants access to 'myuser' when coming from 'localhost'. Surely the second is not going to be required? Wrong. Many MySQL installations create a default user '%'@'localhost', which would match anyone logging in from the local host that didn't have another entry. So you either need to create the user@localhost, or better still remove the anonymous user entry (the %@localhost) as it can and does cause multiple issues with authentication.
Putting it all together
We now want to connect to our MySQL server. We need four vital pieces of information, the user name, their password, the host and finally the database name. We may also need a port or a socket file, but generally they will be implied.
If you have set up MySQL on your own host, generally you will be using 'localhost' as the host name. The database and user will be dictated by the administrator and if that isn't you, you will need to ask. If you start the mysql command line utility without supplying any connection information it will assume your operating system level username, 'localhost', and no password.
If you are using a hosted site, then you will need to check with the hosting provider. Quite often the host will be 'locahost', but just as often it won't. You need to check.
How you connect depends on what your application is. You can always check the connection using the mysql command line interface (if you have access to it). It is by far the best way of checking for errors and debugging connection issues.
Using the mysql command line interface, you can supply the user, the host, the password and the database for the connection:
mysql -u username -ppassword -h host databasename
Note that there is no space between the -p and the password, if you supply a space the password will be read as being the database name instead and will prompt you for the password. You can leave the password out, and just supply the -p for mysql to prompt for the password. This is the preferred method as it is more secure than passing it on the command line.
If you are setting up an application then read the application installation guide and it will explain how to set up the hostname, username, password and database. In general there will be a configuration file where you add these details.
But I can't connect! What's wrong?
If you've been following thus far you should have a good idea where to start looking, however here is a simple checklist to determine where the problem lies.
Do you have the right host?
Check that there is in fact a MySQL server on the host you are trying to connect to and remember the difference between 'localhost' and 127.0.0.1. If the information was supplied by a third party check that you have the correct details.
Is the host contactable?
Try using basic network tools like 'ping' to check if the host is responding on the network. If not then you need to check if you have a network connection and check with the host owner to see that it is reachable.
Is MySQL running?
You can use 'telnet' to connect to the host and port and you should get some information back. If you cannot connect then chances are the MySQL server is not running, or not listening on that port. An example session would look something like this:
$ telnet localhost 3306
Trying ::1...
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
=
5.1.54-1ubuntu4??X32D>G.?"XxqPz6<i}X"Connection closed by foreign host.
Does the user exist?
If you get errors like:
ERROR 1045 (28000): Access denied for user 'fred'@'localhost' (using password: YES)
This could be that the user hasn't been granted access, that the user doesn't exist or that the password is wrong. Remember that a user is associated with a host, so check that the user on that host has access to the required database.
Does the database exist? Do you have access?
Try connecting without supplying a database name. As long as you have a user/host pairing available you should be able to connect to a MySQL server. Then once connected try using the database. You can use 'USE dbname' in the MySQL command line to check.
mysql> USE mydb;
In our next issue we will look at basic database management, how to create and use a database and table.
If you find this series of use and are looking for more detail, there may be a MySQL training course located near you.
Disclaimer
I am employed by SkySQL Ab, which provides support, training and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.
MySQL and 'MySQL Enterprise' are registered trademarks of Oracle Corporation