Configuring MySQL


A free and stable version of MySQL is included with Ubuntu. MySQL is also available from the website http://www.mysql.com. The software is available in source code, binary, and APT format for Linux. You can use the Synaptic client to add the software to your system. See Chapter 7 for the details on adding (or removing) software.

After you have MySQL installed, you need to initialize the grant tables or permissions to access any or all databases and tables and column data within a database. You can do this by issuing mysql_install_db as root. This command initializes the grant tables and creates a MySQL root user.

Caution

The MySQL data directory needs to be owned by the user as which MySQL will run (changing ownership using the chown command). In addition, only this user should have any permissions on this directory. (In other words, the permissions should be set to 700 by using chmod.) Setting up the data directory any other way creates a security hole.


Running mysql_install_db should generate output similar to the following:

sudo mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 020916 17:39:05 /usr/libexec/mysqld: Shutdown Complete ... 


The command prepares MySQL for use on the system and reports helpful information. The next step is to set the password for the MySQL root user, which is discussed in the following section.

Caution

By default, the MySQL root user is created with no password. This is one of the first things you must change because the MySQL root user has access to all aspects of the database. The following section explains how to change the password of the user.


Setting a Password for the MySQL Root User

To set a password for the root MySQL user, you need to connect to the MySQL server as the root MySQL user; you can use the command mysql -u root to do so. This command connects you to the server with the MySQL client. When you have the MySQL command prompt, issue a command like the following to set a password for the root user:

mysql> SET PASSWORD FOR root = PASSWORD("secretword"); 


secretword should be replaced by whatever you want to be the password for the root user. You can use this same command with other usernames to set or change passwords for other database users.

After you enter a password, you can exit the MySQL client by typing exit at the command prompt.

Creating a Database in MySQL

In MySQL you create a database by using the CREATE DATABASE statement. To create a database, you connect to the server by typing mysql -u root -p and pressing Enter. After you do so, you are connected to the database as the MySQL root user and prompted for a password. After you enter the password, you are placed at the MySQL command prompt. Then you use the CREATE DATABASE command. For example, the following commands create a database called animals:

sudo mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE animals; Query OK, 1 row affected (0.00 sec) mysql> 


Another way to create a database is to use the mysqladmin command, as the root user, with the create keyword and the name of a new database. For example, to create a new database named reptiles, you use a command line like this:

# mysqladmin -u root -p create reptiles 


Granting and Revoking Privileges in MySQL

You probably want to grant yourself some privileges, and eventually you will probably want to grant privileges to other users. Privileges, also known as rights, are granted and revoked on four levels:

  • Global-level These rights allow access to any database on a server.

  • Database-level These rights allow access to all tables in a database.

  • Table-level These rights allow access to all columns within a table in a database.

  • Column-level These rights allow access to a single column within a database's table.

Note

Listing all the available privileges is beyond the scope of this chapter. See the MySQL documentation for more information.


To add a user account, you connect to the database by typing mysql -u root -p and pressing Enter. You are then connected as the root user and prompted for a password. (You did set a password for the root user, as instructed in the last section, right?) After you enter the root password, you are placed at the MySQL command prompt.

To grant privileges to a user, you use the GRANT statement, which has the following syntax:

grant what_to_grant ON where_to_grant TO user_name IDENTIFIED BY 'password'; 


The first option, what_to_grant, is the privileges you are granting to the user. These privileges are specified with keywords. For example, the ALL keyword is used to grant global-, database-, table-, and column-level rights for a specified user.

The second option, where_to_grant, specifies the resources on which the privileges should be granted. The third option, user_name, is the username to which you want to grant the privileges. Finally, the fourth option, password, is a password that should be assigned to this user. If this is an existing user who already has a password and you are modifying permissions, you can omit the IDENTIFIED BY portion of the statement.

For example, to grant all privileges on a database named sampledata to a user named foobar, you could use the following command:

GRANT ALL ON animals.* TO foobar IDENTIFIED BY 'secretword'; 


The user foobar can now connect to the database sampledata by using the password secretword, and foobar has all privileges on the database, including the ability to create and destroy tables. For example, the user foobar can now log in to the server (by using the current hostnameshuttle2, in this example), and access the database like so:

$ mysql -h shuttle2 -u foobar -p animals Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 43 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 


Note

See the section "The MySQL Command-Line Client" for additional command-line options.


Later, if you need to revoke privileges from foobar, you can use the REVOKE statement. For example, the following statement revokes all privileges from the user foobar:

REVOKE ALL ON animals FROM foobar; 


Advanced database administration, privileges, and security are very complex topics that are beyond the scope of this book. See the section "Reference" at the end of this chapter for links to online documentation. You can also check out Luke Welling's and Laura Thompson's book PHP and MySQL Development from Sams Publishing.



Ubuntu Unleashed
Ubuntu Unleashed 2011 Edition: Covering 10.10 and 11.04 (6th Edition)
ISBN: 0672333449
EAN: 2147483647
Year: 2006
Pages: 318

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net