MySQL on Ubuntu 8.04

Posted:	2008-06-07 01:01
Tags:	Web

I do this sort of MySQL administration a lot so I thought I'd document it this time. Much of the information is taken straight from the excellent MySQL 5.0 manual.

Installation

Get the latest MySQL:

sudo apt-get install mysql-server

Set a root password:

Package configuration

  ┌────────────────────┤ Configuring mysql-server-5.0 ├─────────────────────┐
  │ While not mandatory, it is highly recommended that you set a password   │
  │ for the MySQL administrative "root" user.                               │
  │                                                                         │
  │ If that field is left blank, the password will not be changed.          │
  │                                                                         │
  │ New password for the MySQL "root" user:                                 │
  │                                                                         │
  │ _______________________________________________________________________ │
  │                                                                         │
  │                                 <Ok>                                    │
  │                                                                         │
  └─────────────────────────────────────────────────────────────────────────┘

You have to repeat it:

┌──────┤ Configuring mysql-server-5.0 ├───────┐
│                                             │
│                                             │
│ Repeat password for the MySQL "root" user:  │
│                                             │
│ ___________________________________________ │
│                                             │
│                   <Ok>                      │
│                                             │
└─────────────────────────────────────────────┘

Once the install is finished MySQL will be running.

If you ever want to update the root password you can do it like this (you don't need to be the UNIX root user):

mysqladmin -u root -p password 'new-password'

Enter the password you just entered during the installation when you see the Enter password: prompt.

Listening on All Interfaces

Remote access is disabled on Ubuntu by default. If you want MySQL to listen on all interfaces, not just localhost, edit /etc/mysql/my.cnf and comment out the line bind-address = 127.0.0.1:

sudo vim /etc/mysql/my.cnf

Then we restart MySQL:

sudo /etc/init.d/mysql restart

Now check that networking is enabled. Run:

netstat -tap | grep mysql

You should see a line like this one:

tcp        0      0 *:mysql                 *:*                     LISTEN     22565/mysqld

It looks like this if you only listen on localhost:

tcp        0      0 localhost:mysql         *:*                     LISTEN      9119/mysqld

The MySQL Command Prompt

You can now login to the mysql server with this:

mysql -u root -p

You'll need to type in the password that you entered earlier:

root@james-laptop:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql>

You can exit by typing exit.

Existing Databases

MySQL sets up two databases by default:

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

mysql> use information_schema ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

You shouldn't modify these databases directly unless you are a MySQL expert and know precisely what you are doing. For testing various commands you are much better creating your own database.

Creating a Database

Create a database:

mysqladmin -h localhost -u root -p create first

This creates the directory /var/lib/mysql/first. You can also create a database from the mysql> command prompt with the CREATE DATABASE first DEFAULT CHARACTER SET UTF8; SQL which also allows you to set the default charcter set. You might think you could achieve the same by using --default-character-set=utf8 with the mysqladmin command above but it doesn't work.

You can get information about a database with this SQL:

mysql> SHOW CREATE DATABASE first;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| first    | CREATE DATABASE `first` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Creating a User

As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

You can create MySQL accounts in two ways:

By using statements intended for creating accounts, such as CREATE USER or GRANT
By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE

The preferred method is to use account-creation statements because they are more concise and less error-prone.

Operating system usernames are completely unrelated to MySQL usernames which can be a maximum of 16 characters long. Many MySQL clients use the current UNIX username as a default if a username is not specified as a convenience but the two are not related. Likewise MySQL and UNIX account passwords are completely unrelated. MySQL encrypts passwords using its own algorithm. This encryption is different from that used during the Unix login process. MySQL password encryption is the same as that implemented by the PASSWORD() SQL function which you use if you are inserting new users directly into tables - you shouldn't usually do that though and the GRANT statements mentioned below handle the encryption for you anwyay. Incidentally, UNIX password encryption is the same as that implemented by the ENCRYPT() SQL function.

First, use the mysql program to connect to the server as the MySQL root user (not the same as the root system user):

mysql -u root -p mysql

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

The accounts created by these GRANT statements have the following properties:

Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order.
One account has a username of admin and no password. This account can be used only by connecting from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges allow the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing additional GRANT statements.
One account has a username of dummy and no password. This account can be used only by connecting from the local host. No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an account without giving it any privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.
The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a non-empty password.

Here's a statement which grants a user custom with password obscure access to just one database bankaccount and only with specific commands:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost'
    ->     IDENTIFIED BY 'obscure';

As of MySQL 5.0.2, you can remove an account and its privileges as follows:

mysql> DROP USER user;

You can find out which privileges a user has with:

mysql> SHOW GRANTS FOR 'root'@'localhost';

To show all grants you could do this:

mysql> SHOW GRANTS;

For just the current user's grants:

mysql> SHOW GRANTS FOR CURRENT_USER;

You can set passwords like this:

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

Only users such as root that have update access to the mysql database can change the password for other users. If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:

mysql> SET PASSWORD = PASSWORD('biscuit');

Note

Do you have to FLUSH PRIVILEGES after this??

You can also use a GRANT USAGE statement at the global level (ON .) to assign a password to an account without affecting the account's current privileges:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

The privileges you can set are described here: http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html

Secure Connections

It is worth noting that data is not encrypted by data between client and server as this is CPU intensive. Find out about secure connections here: http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html

Creating Tables

Described completely here: http://dev.mysql.com/doc/refman/5.0/en/create-table.html