Section 7.9. Creating Databases and Accounts on a MySQL Server

7.9. Creating Databases and Accounts on a MySQL Server

MySQL is an open source database system that has become very popular due to its high performance, lightweight design, and open source license.

Many software packages, including web applications such as the Serendipity blog software (http://www.s9y.org/), use MySQL to store data. In order to use these programs, you will need to create a MySQL database and access account.

7.9.1. How Do I Do That?

First, you'll need to select names for your database and access account; for this example, let's use chrisblog for the database name and chris for the access account. Both names should start with a letter, contain no spaces, and be composed from characters that can be used in filenames.

To create the database and account, use the mysql monitor program:

# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database chrisblog; Query OK, 1 row affected (0.01 sec) mysql> grant all privileges on chrisblog.* to 'chris'@'localhost'  identified by 'SecretPassword'; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye

Make sure that the mysqld service is running!

You can then enter the database, access account, and password information into the configuration of whatever software will use MySQL.

MySQL recommends that you add a password to root's access of the MySQL server. You can do that with these commands (\ indicates that text continues on the following line):

                   # /usr/bin/mysqladmin -u root password 'Secret'                   # /usr/bin/mysqladmin -u root -h $(hostname) \                   password 'Secret'                

Secret is the root password that you wish to use. After you enable the root password, you'll need to use the -p option to mysql so that you are prompted for the password each time:

# mysql -p                

For example, to install Serendipity:

1. Download the Serendipity software from http://www.s9y.com and place it in the /tmp directory.

2. Unpack the Serendipity software in the /var/www/html directory:

3. # cd /var/www/html # tar xvzf /tmp/serendipity*.tar.gz

   
   

4. Access that directory through a web browser at http://<hostname>/serendipity. You will see the initial verification page shown in Figure 7-23.

Figure 7-23. Serendipity Installation verification page

1. If there are any permission errors, correct them using the instructions on the page and then click the Recheck Installation link at the bottom of the page. Once the check is successful, click on the Simple Installation link.

2. As shown in Figure 7-24, enter the database, hostname, access account (database user), and password that you created in the MySQL database. Fill in the other fields, such as the blog title and the username and password you wish to use to administer the blog, using values of your choosing. Click on the Complete Installation link at the bottom of the page.

3. Figure 7-25 shows the confirmation page that appears. Click on the link labeled "Visit your new blog here" to see your initial blog page, shown in Figure 7-26.

Figure 7-24. Serendipity Installation page

Figure 7-25. Serendipity Installation confirmation page

Figure 7-26. Serendipity blog front page

7.9.2. How Does It Work?

MySQL is a Structured Query Language (SQL) database server. It provides rapid access to large sets of structured data, such as customer lists, sports scores, student marks, product catalogs, blog comments, or event schedules. The MySQL database runs as a server daemon named mysqld, and many different types of software can connect to the server to access data.

Connections to the database server are made through the network socket /var/lib/mysql/mysql.sock (local connections) or on the TCP port 3306 (remote connections). If the MySQL server is running on the same machine as your application, you should leave port 3306 closed in your firewall configuration, but you must open it if you separate the MySQL server and the application onto different machines (which you might do for performance reasons if you're using the database heavily).

The mysql monitor command is a very simple command-line interface to the MySQL server. It permits you to enter commands to the server and to see the results of those commands on your screen.

MySQL data is stored in /var/lib/mysql; each database is stored in a separate subdirectory.

7.9.3. What About...

7.9.3.1. ...creating my own scripts and programs that access MySQL data?

Most scripting and programming languages have modules to access MySQL data. For example, you can use the database driver (DBD) module DBD::mysql to access the basic database interface (DBI) abstraction layer to work with databases in Perl. For details on writing software that accesses a MySQL database, see Chapter 22 in the MySQL documentation (http://dev.mysql.com/doc/refman/5.0/en/apis.html).

7.9.4. Where Can I Learn More?

• The manpages for mysqld, mysql, mysqladmin, mysqldump, and mysqlshow

• The MySQL manual: http://www.mysql.com/doc

• Documentation on the Perl DBI module: perldoc DBI

• Documentation on the PHP MySQL functions: http://ca.php.net/mysql