HP OSMS Blueprint: Database Server on HP Server Platforms with MySQL and RHEL5

3. Import the script by entering the following command:

# mysql –u root –p < osmsample.sql

4. At the prompt, enter the database password.

The following results are displayed:

# mysql -u root -p < osmsample.sql

Enter password:

customerID name purchased product productID

1 Jenny 2006-07-07 13:00:33 HP Laptop nc6220 5001

2 Kate 2006-07-09 09:38:12 HP LaserJet 5M 9076

id depno name cardnumber

1 10 Mike 100081

2 10 Tom 100082

3 10 Jack 100083

4 20 Mary 100084

5 20 Cherry 100085

6 20 Jones 100086

Figure 4 Database Error Message

If this error occurs, remove the pre-existing database using the instructions in “Creating and

Deleting the MySQL Database ” (page 12), and rerun the script to create the database.

NOTE: For security, after testing is complete, you must destroy all sample objects, such as the

sample database, user accounts, and tables.

Configuring MySQL Server Replication

The MySQL replication feature allows the database on one server to be precisely duplicated on

one or more other servers. This capability is enabled through an asynchronous master/slave

relationship. Each slave server starts out with an exact copy of the data held on the master server.

The master server publishes a binary log to the slave servers of all changes made to the data in

the database. The slave servers read changes to the binary logs that have occurred since the last

successful update.

In this way, the slave servers are able to update their copies of the database. All changes to the

database must be made to the master server, but clients may access data for reading through

either the master server or any slave server.

Figure 5 depicts the replication configuration.

This replication scheme offers two key advantages. First, backing up the database is easier and

safer. A slave server can be taken off line, and all its data can be backed up without impacting

the overall operation of the live database. During backup, the master server and any other slave

servers continue responding to queries uninterrupted. When the backup is complete, the

backed-up slave server is brought up to date with any changes that have occurred during the

backup, and then the slave server is returned to normal operation.

The second advantage of replication is improved read performance. You can scale out the MySQL

server in response to higher loads using this type of one-way replication. Splitting the load for

processing client queries between the master server and slave servers provides better response

time for clients. The select queries that read data are sent to the slave server to reduce the

query processing load of the master server. Commands that modify data are still sent to the

master server to ensure that the master server and slave servers remain synchronized. This