REDHAT 5 – MYSQL

Posted: September 7, 2010 in REDHAT 5 LinUX

MYSQL

Introduction

Most home/SOHO administrators don’t do any database programming, but they sometimes need to install applications that require a MySQL database. This chapter explains the basic steps of configuring MySQL for use with a MySQL-based application in which the application runs on the same server as the database.

NFS Configuration in SERVER SIDE

NFS Configuration in CLIENT SIDE

Preparing MySQL For Applications

In most cases the developers of database applications expect the systems administrator to be able to independently prepare a database for their applications to use. The steps to do this include:

  1. Install and start MySQL.
  2. Create a MySQL “root” user.
  3. Create a regular MySQL user that the application will use to access the database.
  4. Create your application’s database.
  5. Create your database’s data tables.
  6. Perform some basic tests of your database structure.

The rest of the chapter is based on a scenario in which a Linux-based application named sales-test needs to be installed. After reading the sales-test manuals, you realize that you have to create a MySQL database, data tables, and a database user before you can start the application. Fortunately sales-test comes with a script to create the tables, but you have to do the rest yourself. Finally, as part of the planning for the installation, you decided to name the database salesdata and let the application use the MySQL user mysqluser to access it.

I’ll cover all these common tasks in detail in the remaining sections.

Installing MySQL

In most cases you’ll probably want to install the MySQL server and MySQL client RPMs. The client RPM gives you the ability to test the server connection and can be used by any MySQL application to communicate with the server, even if the server software is running on the same Linux box.

You need to make sure that the mysql-server and mysql software RPMs is installed. When searching for the RPMs, remember that the filename usually starts with the software package name followed by a version number, as in mysql-server-3.23.58-4.i386.rpm.

There are a number of supporting RPMs that may be needed, so the yum utility may be the best RPM installation method to use.

Starting MySQL

You have to start the MySQL process before you can create your databases. To configure MySQL to start at boot time, use the chkconfig command:

[root@bigboy tmp]# chkconfig mysqld on

You can start, stop, and restart MySQL after boot time using the service commands.

[root@bigboy tmp]# service mysqld start

[root@bigboy tmp]# service mysqld stop

[root@bigboy tmp]# service mysqld restart

Remember to restart the mysqld process every time you make a change to the configuration file for the changes to take effect on the running process.

You can test whether the mysqld process is running with

[root@bigboy tmp]# pgrep mysqld

The Location of MySQL Databases

According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.

Creating a MySQL “root” Account

MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. Only two steps are necessary for a brand new MySQL installation.

  1. Make sure MySQL is started.
  2. Use the mysqladmin command to set the MySQL root password. The syntax is as follows:

[root@tmp bigboy]# mysqladmin -u root password new-password

Accessing The MySQL Command Line

MySQL has its own command line interpreter (CLI). You need to know how to access it to do very basic administration.

You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p, which tells MySQL to prompt for a password. Here user root gains access:

[root@bigboy tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58

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

mysql>

Note: Almost all MySQL CLI commands need to end with a semi-colon. Even the exit command used to get back to the Linux prompt needs one too!

Creating and Deleting MySQL Databases

Many Linux applications that use MySQL databases require you to create the database beforehand using the name of your choice. The procedure is relatively simple: Enter the MySQL CLI, and use the create database command:

mysql> create database salesdata;
Query OK, 1 row affected (0.00 sec)

mysql>

If you make a mistake during the installation process and need to delete the database, use the drop database command. The example deletes the newly created database named salesdata.

mysql> drop database salesdata;
Query OK, 0 rows affected (0.00 sec)

mysql>

Note: Sometimes a dropped database may still appear listed when you use the show databases command explained further below. This may happen even if your root user has been granted full privileges to the database, and it is usually caused by the presence of residual database files in your database directory. In such a case you may have to physically delete the database sub-directory in /var/lib/mysql from the Linux command line. Make sure you stop MySQL before you do this.

[root@bigboy tmp]# service mysqld stop

Viewing Your New MySQL Databases

A number of commands can provide information about your newly created database. Here are some examples:

  • Login As The Database User: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the root account.
[root@bigboy tmp]# mysql -u mysqluser -p salesdata
  • List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:
mysql> show databases;
+-----------+
| Database  |
+-----------+
| salesdata |
+-----------+
1 row in set (0.00 sec)

mysql>

Listing The Data Tables In Your MySQL Database

The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.

The example uses the salesdata database; notice that it has a table named test.

mysql> use salesdata;
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_salesdata |
+---------------------+
| test                |
+---------------------+
1 row in set (0.00 sec)

mysql>

Viewing Your MySQL Database’s Table Structure

The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.

mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
| Field         | Type         | Null | Key | Default    | Extra          |
+---------------+--------------+------+-----+------------+----------------+
| num           | int(11)      |      | PRI | NULL       | auto_increment |
| date_modified | date         |      | MUL | 0000-00-00 |                |
| name          | varchar(50)  |      | MUL |            |                |
| description   | varchar(75)  | YES  |     | NULL       |                |
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)

mysql>

Viewing The Contents Of A Table

You can view all the data contained in the table named test by using the select command. In this example you want to see all the data contained in the very first row in the table.

mysql> select * from test limit 1;

With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.

MySQL Database Backup

The syntax for backing up a MySQL database is as follows:

mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]

In the previous section, you gave user mysqluser full access to the salesdata database when mysqluser used the password pinksl1p. You can now back up this database to a single file called /tmp/salesdata-backup.sql with the command

[root@bigboy tmp]# mysqldump --add-drop-table -u mysqluser \
  -ppinksl1p salesdata > /tmp/salesdata-backup.sql

Make sure there are no spaces between the -p switch and the password or else you may get syntax errors.

Note: Always backup the database named mysql too, because it contains all the database user access information.

MySQL Database Restoration

The syntax for restoring a MySQL database is:

mysql -u [username] -p[password] [database] < [backup_file]

So, using the previous example, you can restore the contents of the database with

[root@bigboy tmp]# mysql -u mysqluser -ppinksl1p salesdata \
  < /tmp/salesdata-backup.sql

Conclusion

MySQL has become one of the most popular Linux databases on the market and it continues to improve each day. If you have a large project that requires the installation of a database, then I suggest seeking the services of a database administrator (DBA) to help install and fine-tune the operation of MySQL. I also suggest, no matter the size of the project, that you practice an application installation on a test Linux system to be safe. It doesn’t necessarily have to be the same application. You can find free MySQL-based applications using a Web search engine, and you can use these to be come familiar with the steps outlined in this chapter before beginning your larger project.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s