Implementing MySQL

MySQL is a freely downloadable open-source RDBMS that uses SQL. Before continuing, you need to understand that there are two versions of MySQL:

• MySQL This is the "standard" version of MySQL that is included with your SUSE Linux distribution.

• MySQL-Max DB This is the "heavy duty" version of MySQL that is designed for use by large enterprises.

In this section, we're going to focus on MySQL. You'll learn how to install and configure MySQL on your SUSE Linux system.

Installing MySQL

Most likely (especially if you installed SUSE Linux with a minimal amount of services), you will not have MySQL installed because it's not installed by default. As long as you have the installation CD-ROMs, you can install it quickly and easily using the YaST Software Management module, as shown in Figure 29.1.

Figure 29.1. Using YaST to install MySQL.

To install MySQL on your system, complete the following:

1.	Run YaST and navigate to Software, Software Management.
2.	In the Search field, enter mysql; then select Search.
3.	In the Package column, select the following packages, as shown in Figure 29.1: mysql mysql-administrator mysql-client mysql-shared

There are many other MySQL-related packages that you could install. Most of these packages, such as apache2-mod_auth_mysql, are used to enhance the functionality of the MySQL service. You can, optionally, install these packages as well:

1.	In the Search field, enter knoda; then select Search. The knoda package provides a database client that can be used to access your MySQL server.
2.	Mark the knoda package in the right frame.
3.	Select Accept.
4.	When prompted that the packages are installed, select Finish.

When you are done with your installation, you are highly encouraged to run the YaST online update so that you can patch up your databases with any new tools, security fixes, or patches that may be available. You may also be interested in viewing the www.MySQL.com website for the latest version of the product. You can download the source files in an RPM package and install the latest version if your needs require it.

After installing MySQL, a number of initial configuration tasks must be completed before you begin working with your first database table. We'll look at these tasks next.

Completing MySQL Initial Configuration Tasks

After installing your MySQL packages, you need to do the following:

• Initialize the grant tables.

• Start the MySQL server.

• Secure the MySQL user accounts.

Let's discuss how to do each of these.

Initializing the Grant Tables

One of the first tasks you need to complete is to set up your MySQL server's grant tables. To understand the grant tables, you must first understand that MySQL access controls, including user accounts and permissions, are managed from within the MySQL service itself. This is done using five tables within the MySQL database:

• user This table is used to specify whether a specific user is allowed to connect to the MySQL server.

• db This table is used to define which databases a user is allowed to access.

• host This table is used to define which hosts are allowed to access a given database.

• tables_priv This table is used to define access privileges for a given table.

• columns_priv This table is used to define access privileges for specified columns of data for a given table.

These tables must be initialized before you can use MySQL. This is done by running the mysql_install_db script from a shell prompt. To do this, complete the following:

1.	Open a terminal session and switch to your Root user account.
2.	At the shell prompt, enter cd /usr/bin.
3.	At the shell prompt enter ./mysql_install_db.

Grant tables are then installed and initialized, as shown in Figure 29.2.

Figure 29.2. Initializing the grant tables.

You need to run mysql_install_db only once after initially installing the MySQL packages. The script creates the database's data directory, the grant tables, and a test database that can be used to test your implementation. The utility also creates entries in the grant tables for two MySQL user accounts:

• root

• anonymous-user

Note

It's important that you understand that MySQL has its own user accounts that are separate from your SUSE Linux system's user accounts. Even though MySQL uses an account named root, it's not the same as the root account on your Linux system. The MySQL database runs on your system as a Linux system user account named mysql, which is created when the MySQL packages are installed.

With the grant tables initialized, you now need to start the MySQL service. Let's review how to do this next.

Starting the MySQL Server

You can start the MySQL service on your server by entering the following command from the shell prompt:

mysqld_safe --user=mysql & 

The --user option specifies which Linux system account the MySQL server should run as. The ampersand at the end of the command indicates that the process should run in the background. You may have to wait for a few minutes for the server to finish loading.

Note

You can also start the server by entering rcmysql start at the shell prompt.

To verify that the server is running, enter the following command at the shell prompt:

mysqladmin version 

You should see output similar to that shown in Figure 29.3.

Figure 29.3. Verifying that MySQL is running.

Note

The commands presented in this subsection assume that you haven't created a password for your MySQL Root user account yet (we'll talk about how to that later in the chapter). If, by chance, you've already created a Root user account, you'll have to use the following syntax for the commands presented here:

command -h localhost -u root -p option 

For example, to perform the preceding test, you would enter the following:

mysqladmin -h localhost -u root -p version 

If you want to make sure your MySQL service automatically starts at boot, open YaST and navigate to the System, System Services (Runlevel) module and configure the mysql daemon to start at the desired runlevels (probably 3 and 5).

At this point, you can execute several tests to verify that the MySQL server is functioning properly. Complete the following:

1. At the shell prompt, view the databases currently on the server by entering /mysqlshow. You should see the databases shown in Figure 29.4 displayed.

   Figure 29.4. Viewing MySQL databases.

   

   
   

   Note

   The MySQL database contains the grant tables discussed earlier. The test database is an empty database that you can experiment with as you learn how to use MySQL.

   
   

2. View the tables contained within the MySQL database by entering mysqlshow mysql. You should see the tables shown in Figure 29.5.

   Figure 29.5. Viewing tables within a MySQL database.

   
   
   

Now that you're sure the MySQL server is running properly, you need to work with your MySQL user accounts. Notice in Figure 29.2 that you are prompted, after initializing the grant tables, to secure your MySQL Root user account. We'll talk about how to do this next.

Securing the MySQL User Accounts

After running msql_install_db, your root MySQL user account is created but has no password, allowing anyone access to your MySQL server. This is something you should change as soon as possible. To do this, complete the following:

1.	Open a terminal session and switch to your Root user account.
2.	Verify that your MySQL server is currently running.
3.	Create a password for your MySQL Root user by completing the following: At the shell prompt, enter mysqladmin -u root password 'your_new_password'. At the shell prompt, enter mysqladmin -u root -h your_system_hostname password your_mysql_root_password.

Now that your MySQL server has been installed and initialized, you can learn how to use the MySQL Control Center.

Using the MySQL Control Center

Because the services are installed on your Linux system, let's look at how to manage them. First, you will have to open the MySQL Control Center. This can be found by launching your KDE Menu to the Office, Database section. You will see that you now have access to both the console and Knoda. Select the mysqlcc shortcut to open the Control Center, as shown in Figure 29.6.

Figure 29.6. Viewing the MySQL Control Center.

After you open the Control Center, which includes the Console Manager, you will immediately need to add your own server (or a remote one) as a manageable server in the console in the Register Server dialog box shown in Figure 29.6. To do this, add your server by entering the required information, as shown in Figure 29.7.

Figure 29.7. Configuring the MySQL server.

The Name field is the name of the manageable entity in the MySQL Console Manager. The Host Name is very important. If you don't know what your host's name is, you can use YaST again in the Network Services section under the DNS and Host Name section. When you are done adding your client server (which, by the way, can be either MySQL or PostgreSQL if you change the driver), click Add to add your server to the console, as shown in Figure 29.8.

Figure 29.8. Viewing the configured MySQL server.

If your server is up and operational, all you need to do is connect a client to it to test it.