Meet MySQL

MySQL is an open-source relational database. In a nutshell, for those unfamiliar with it: A database is where an application keeps its stuff.

To break it down a little further, “relational database” is a term that refers to how data is organized and accessed within the database. The “SQL” part refers to the language used by application queries to retrieve and store data (“Structured Query Language”).

MySQL is free and widely used, meaning you can find a lot of application support, tools, and community help for it. MySQL is a safe choice if you know you need a database but don’t know what to make of the options that are out there.

This article covers a basic installation of a MySQL server on Ubuntu Linux – just enough to get you started. Remember that you might need to install other packages to let apps use MySQL, like extensions for PHP. Check your application documentation for details.

Installing MySQL

The easiest way to install the MySQL server is through the Ubuntu package manager:

sudo aptitude update
sudo aptitude install mysql-server

The installer should ask you to set a root password. It’s strongly advised you do so, but if you miss your chance during the install we’ll show you how to set the root password a little further on.

iptables

If you have iptables enabled and want to connect to MySQL from another machine you’ll need to open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application using MySQL is running on the same machine.

If you do need to open a port (again, only if you’re accessing MySQL from a different machine from the one you’re installing on), you can use the following rules in iptables to open port 3306:

-I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

Launch MySQL

Now that MySQL is installed you can make sure it’s running by trying to launch it:

sudo service mysql start

If you see a message that it’s already running that’s okay (it means that, well, it’s already running).

Launching at boot

This should also have been done for you at install time, but just in case:

sudo /usr/sbin/update-rc.d mysql defaults

That makes sure your machine will launch the MySQL server when it reboots.

The MySQL shell

There is more than one way to manage a MySQL server, so we’ll focus on the most basic and compatible approach: The mysql shell.

At the command prompt, run:

/usr/bin/mysql -u root -p

That will attempt to launch the mysql client and enter the shell as user “root”. When you’re prompted for a password enter the one you set at install time or, if you haven’t set one, just hit enter to submit no password.

You should be greeted by the mysql shell prompt:

mysql>

Setting the root password

If you got in by entering a blank password, or want to change the root password you’ve set, you can do it by entering the following command in the mysql shell. Replace the “password” in quotes with your desired password:

UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';

It’s kind of a mouthful. The reason for this is that MySQL keeps user data in its own database, so to change the password we have to run an SQL command to update the database.

Next we’ll reload the stored user information to make our change go into effect:

FLUSH PRIVILEGES;

Note that we’re using all-caps for SQL commands. If you type those commands in lowercase they’ll work too. By convention the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.

Looking at users

As mentioned in the previous section, MySQL stores the user information in its own database. The name of the database is “mysql”. Inside that database the user information is in a “table”, a dataset, named “User”.

If you want to see what users are set up in MySQL you need to run a query against the “user” table in the “mysql” database. Let’s do that now:

SELECT User, Host, Password FROM mysql.user;

Breaking that down…

The “SELECT” command tells MySQL you’re asking for data.

The “User, Host, Password” part tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case we’re looking for the username, the host associated with the username, and the encrypted password entry.

Finally, the “FROM mysql.user” part of the command tells MySQL to get the data from the “mysql” database and the “user” table.

And then the command ends with a semicolon

About that semicolon

All SQL queries end with a semicolon. MySQL will wait for you to keep entering additions to a query until it sees a semicolon.

That means that you can break lines up into smaller parts to make them easier to read. For example, the above command also works if you enter it in multiple lines in the mysql shell, as in:

mysql> SELECT User, Host, Password
    -> FROM mysql.user;

When you hit “enter” after the “Password” part you’ll get a new line so you can keep typing. The “->” indicates that the shell thinks you’re still in the middle of a statement. You can type a semicolon by itself to end the command if you simply forgot it on the first line.

User hosts

Okay, with that important aside about the semicolon done, let’s look at the output of that query:

SELECT User, Host, Password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | demohost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| debian-sys-maint | localhost | *03C2F472E5290DDE27E889681C90EA91FD6800F3 |
|                  | %         |                                           |
+------------------+-----------+-------------------------------------------+

As you can see, users are associated with a host – specifically, the host they connect to. The “root” user in this case is defined for localhost, for the IP address of localhost, and the hostname of the server (“demohost” in this example). You’ll usually only need to set a user for one host, the one you typically connect from.

If you’re running your application on the same machine as the MySQL server the host it connects to by default will be “localhost”. That means any new users you create will need to have “localhost” in its “host” field.

If your application connects remotely the “host” entry MySQL will look for is the IP address or DNS hostname of the remote machine (the one the client is coming from).

A special value for the host is “%”, as you’ll see for the blank user (more on that shortly). That’s a wildcard that applies to any host value. You usually won’t want to use that because it’s more secure to limit access specifically to trusted hosts.

Anonymous users

In the example output above you’ll notice there’s one entry that has a host value but no username or password. That’s an “anonymous user”. When a client connects with no username specified it’s trying to connect as an anonymous user.

You usually don’t want one of those in there, but some MySQL installations include one by default. If you see one of those you should either delete the user (refer to the username with empty quotes, like ”) or set a password for it (we cover both tasks later in this series).

Create a database

That covers some basic concepts surrounding users, so now let’s look at creating a database.

It’s worth noting at this point that there is a difference between a “database server” and an actual “database”, even though you’ll often see those terms used interchangeably. MySQL itself is a database server, meaning that it keeps track of databases and controls access to them. An actual database is where all the data goes. That’s what applications are trying to get at when they talk to MySQL.

Some applications will create a database as part of their setup process, while others require you to create a database yourself and tell the application about it later. Fortunately it’s an easy process.

To create a database, log into the mysql shell and run:

CREATE DATABASE demodb;

That’s all there is to it. Replace “demodb” with the name of the database you want to create, of course. You can make sure it’s there by running a query to list all databases:

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

Add a database user

It’s not a good idea to have applications connecting to the database using the root user. That gives them more privileges than they need. We’ll create a user named “demouser” that applications can use to connect to the new database.

To make the user run the following in the mysql shell:

INSERT INTO mysql.user (User,Host,Password) VALUES('demouser','localhost',PASSWORD('demopassword'));

When you make changes to the user table in the mysql database you need to tell MySQL to read the changes by flushing the privileges. To wit:

FLUSH PRIVILEGES;

You can make sure the user is there by running that “select” query again:

SELECT User, Host, Password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | demohost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root             | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| debian-sys-maint | localhost | *03C2F472E5290DDE27E889681C90EA91FD6800F3 |
| demouser         | localhost | *0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6 |
+------------------+-----------+-------------------------------------------+

Grant database user permissions

Right now our new user has no privileges. It can be used to log on, but it can’t be used to make any database changes. Let’s give it full permissions for our new database by running:

GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;

And follow it up with the usual:

FLUSH PRIVILEGES;

To check those privileges were set, we’ll run:

SHOW GRANTS FOR 'demouser'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for demouser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demouser'@'localhost' IDENTIFIED BY PASSWORD '*0756A562377EDF6ED3AC45A00B356AAE6D3C6BB6' |
| GRANT ALL PRIVILEGES ON `demodb`.* TO 'demouser'@'localhost'                                                    |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

What you get back are the commands needed to reproduce that user’s permissions if you were to rebuild the server. The “USAGE on *.*” part basically means they get no privileges on anything by default. Then that gets overridden by the second command, which is the grant you ran for the new database.

Summary

And that’s all you need if you’re just creating a database and a user. We were a bit long on concepts there but that should give you a solid grounding from which to learn more. Good work.

Configuring MySQL server on Ubuntu

Beyond the defaults

In the previous article we covered a basic MySQL server setup on Ubuntu Linux. We set the root password, created a database, and created a user for the database. Now let’s look at MySQL in a little more detail so we can tweak its configuration and be ready in case something goes wrong.

Finding the config files

By default you’ll find MySQL’s configuration files in:

/etc/mysql

If they’re not there, however, you can ask mysqld where it looks for its config. Run the command:

/usr/sbin/mysqld --help --verbose

You’ll get a flood of text back. The first part describes the options you can send to the server when you launch it. The second part is all the configuration stuff that was set when the server was compiled.

What we’re looking for shows up near the start of the output. Find a couple lines that look like:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

And there we are. The server works down that list until it finds a configuration file.

my.cnf

With the location in hand, open the my.cnf file and have a look inside.

/etc/mysql/my.cnf

Any lines starting with “#” are comments, and they mostly document what the different settings are for. They’re good to read through. You’ll find details like the location of log files and where the database files are kept.

Config groups

There are lines in the config file that just contain a word in square brackets, like “[client]” or “[mysqld]”. Those are “config groups” and they tell the programs that read the configuration file which parts they should pay attention to.

See, while we’ve been focusing on the server part of MySQL, it’s technically a collection of tools. That includes the server (mysqld), the client (mysql), and some other tools we’ll talk about in a bit. Those programs look in my.cnf to see how they should behave.

There’s a bit more to it, but basically: The “client” config section controls the mysql client, and the “mysqld” section controls the server config.

Log files

If something does go wrong the best place to start troubleshooting any program is its logs. By default MySQL stores its log files in the directory:

/var/log/mysql

You may need to use sudo to get a listing of the files in that directory.

If you don’t find the MySQL logs in the default directory you’ll need to check MySQL’s config. Look in the my.cnf file and look for a “log_error” line, as in:

log_error = /var/log/mysql/error.log

If you don’t see a line like that, create one in the “mysqld” section so MySQL will use its own error log. We recommend using the location in the example, creating the “/var/log/mysql” directory if it doesn’t already exist. Then restart MySQL to make the change.

Make sure the log directory you choose can be written to by the user controlling the MySQL process (usually “mysql”). The user running the process will be defined in the “user” config value for mysqld in my.cnf.

Network settings

There might be a “port” setting under both the client and server config sections. The port under the server section controls what port the server will listen to. By default that’s 3306 but you can change it to anything you’d like.

The port in the client section tells the client what port to connect to by default. You’ll generally want the two port settings to match up.

If you don’t see the port entries in the config file that just means they’re using the default. If you want to change the port you would add the lines in the appropriate categories:

[client]
port = 3306
 
[mysqld]
port = 3306

The other network setting to look for is the “bind-address” value. That usually gets set to the address for localhost, 127.0.0.1. By binding to localhost the server makes sure no one can connect to it from outside the local machine.

If you’re running your MySQL server on a different machine from your application you’ll want to bind to a remotely-accessible address instead of localhost. Change the bind-address setting to match your public IP address (or, better, a backend IP address on a network that fewer machines can access).

If you don’t see a “bind-address” entry you should put one into the “mysqld” category to help control access to the server:

[mysqld]
bind-address = 127.0.0.1

Remember to account for the client’s hostname when you set up your database users and to poke a hole in your firewall if you’re running iptables.

mysqld and mysqld_safe

Behind the scenes there are actually two versions of the MySQL server, “mysqld” and “mysqld_safe”. Both read the same config sections. The main difference is that mysqld_safe launches with a few more safety features enabled to make it easier to recover from a crash or other problem.

Both mysqld and mysqld_safe will read config entries in the “mysqld” section. If you include a “mysqld_safe” section, then only mysqld_safe will read those values in.

By default the mysql service launches “mysqld_safe”. That’s a good thing, and you should only look to change that if you really know what you’re doing.

mysqladmin

The mysqladmin tool lets you perform some administrative functions from the command line. We won’t talk much about it here because we’re just trying to get you up and running with enough basics to get by. It’s worth looking at the tool in more depth later to see what it can do, particularly if you need to build scripts that perform functions like checking the status of the server or creating and dropping databases.

Backups

You have a few options when it comes to making backups of your databases apart from the usual “back up the whole machine” approach. The main two are copying the database files and using mysqldump.

File copy

By default MySQL creates a directory for each database in its data directory:

/var/lib/mysql

Once you’ve found the data directory, hold off a moment before making a copy of it. When the database server is active it could be writing new values to tables at any time. That means if it writes to a table halfway through your copy some files will change and lead to a corrupt backup. Not a good thing if you’re trying to plan for disaster recovery.

To make sure the database files are copied cleanly you can shut the MySQL server down entirely before the copy. That’s safe but isn’t always ideal.

Another approach you can take is to lock the database as read-only for the duration of the copy. Then when you’re done, release the lock. That way your applications can still read data while you’re backing up files.

Lock the databases to read-only by running, from the command line:

mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

To unlock the database when you’re done, run:

mysql -u root -p -e "UNLOCK TABLES;"

We’re using a new option with the mysql client, “-e”. That tells the client to run the query in quotes as if we’d entered it in the mysql shell proper.

Note that if you’re setting these commands up in a script you can put the password in quotes right after “-p” with no space between the two, as in:

mysql -u root -p"password" -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p"password" -e "UNLOCK TABLES;"

Just make sure you set the permissions on that file to restrict read access. We don’t want just anyone to be able to see that password.

mysqldump

Another approach to backing up your database is to use the “mysqldump” tool. Rather than copying the database files directly, mysqldump generates a text file that represents the database. By default the text file contains a list of SQL statements you would use to recreate the database, but you can also export the database in another format like CSV or XML. You can read the man page for mysqldump to see all its options.

The statements generated by mysqldump go straight to standard output. You’ll want to specify a file to redirect the output to when you run it. For example:

mysqldump -u root -p demodb > dbbackup.sql

That command will tell mysqldump to recreate the “demodb” database in SQL statements and to write them to the file “dbbackup.sql”. Note that the username and password options function the same as the mysql client, so you can include the password directly after “-p” in a script.

Restore from mysqldump

Restoring a mysqldumped database looks similar to what was used to create it, but we use plain old “mysql” instead of “mysqldump”:

mysql -u root -p demodb < dbbackup.sql

We also change from a greater-than to a less-than sign. That switches the command from redirecting its output to telling it to read its input from the existing file. That input is sent to the “mysql” command, causing the mysqldumped instructions to recreate the database.

Note that by default the SQL statements generated would just add to existing database tables, not overwrite them. If you’re restoring a backup over an existing database you should drop the database’s tables first, or drop and recreate the database itself. You can change that behavior by using the “–add-drop-table” option with the command that creates the mysqldump. That causes mysqldump to add a command to the backup files it writes that will drop tables before recreating them.

Database engine

The last concept we’ll talk about here is that of the “database engine”. The engine is the process that’s churning away behind the scenes, writing to and reading data from files. You won’t usually need to know anything other than that it’s there, but sometimes you’ll want to run an application that’s been optimized for a particular database engine.

The engine type is set when a table is created. Tables are usually created by the application that’s going to use them, which is why we aren’t going to get into that syntax here.

To see the engine used by your database’s tables you can run the following command in the MySQL shell:

SHOW TABLE STATUS FROM demodb;

Change “demodb” to the name of your database.

Choosing an engine

Ideally you won’t need to choose an engine. If you’re not very familiar with MySQL that’s certainly the safest way to go – let the application do its thing, and if you’re writing the application, use the default engine until you’re more comfortable with your options.

If you have a database administrator, do whatever he or she says. They’re smart people, they know what they’re talking about.

The two database engines used most often with MySQL are “MyISAM” and “InnoDB”. The default database engine for MySQL version 5.1 and earlier is MyISAM, while InnoDB is the default database engine starting with MySQL version 5.5.

MyISAM

Because MyISAM has been the default in MySQL for a while it’s the most compatible choice of the two main engines. Certain types of searches perform better on MyISAM than InnoDB. Just because it’s the older of the two doesn’t mean it can’t be the best for a given application type.

InnoDB

InnoDB is more fault-tolerant than MyISAM and handles crashes and recovery with a much smaller chance of database corruption. This is a good thing.

The main trouble with InnoDB is that for best performance it requires a lot of tweaking for your environment and access patterns. If you have a DBA that’s no problem, but if you’re a developer who just wants a database up and running for a test server you probably won’t want to deal with tuning InnoDB.

It’s possible you may be running an application that requires InnoDB, and if you’re using MySQL 5.1 or earlier there might not be any settings already in the my.cnf config file. That can be a problem if you’re running on a server that doesn’t have an abundance of memory.

Some settings to get you started with InnoDB on a shared server with 256 megs of RAM are:

innodb_buffer_pool_size = 32M
innodb_log_file_size = 8M
innodb_thread_concurrency = 8
innodb_file_per_table

Add those to the [mysqld] section of the config file. Again, those are only rough guides – enough to get you running, but definitely not optimized. For that you’ll probably want a DBA, or at least to experiment with incremental changes over time.

Summary

Now you should have MySQL configured for your environment, and might even have accounted for the database engine being used by your tables.