330 Technique 44: Making a MySQL Server Your SQL Server

If you plan to use MySQL in your daily work, it’s a good idea to configure your system to start the MySQL server each time you boot your computer: See Technique 20 for the details. Just remember, the MySQL service is named mysql on SuSE and Mandrake systems, and mysqld if you’re running Fedora.

After you start the server for the first time, create a MySQL password for the root user:

1. Open a terminal window and give yourself superuser privileges:

$ su - Password:

2. Type in the following command (substituting your desired password for new-password):

#mysqladmin -u root password newpassword

Now you’re ready to use your MySQL server. If you’re already comfortable with MySQL, we give you a few more timesaving tips in the rest of this chapter. If not, it’s time to hit the books. You can find a wide variety of books on MySQL in your local book store (or at a bookseller online), but we happen to think that MySQL [New Riders/Sams Publishing] by Paul Dubois is one of the best.

Replicating MySQL Data

MySQL supports data replication. When you configure replication, MySQL automatically forwards data modifications (INSERTs, UPDATEs, and DELETEs) from one MySQL server to another. If you have a geographically distributed organization, MySQL can automatically push data from your central database out to your branch offices. Changes that you make to the master database appear almost instantly in each of the remote servers.

Replication offers three important benefits:

It improves the overall reliability of your database. If the master database becomes unavailable for some reason (hardware problems, a

system crash, or normal maintenance), a slave database can take over.

It improves the overall performance of your database. Create a replication slave on a separate computer, and you’ve spread the user load onto two systems. MySQL’s replication architecture makes it easy to configure as many slave servers as you need.

It improves availability while you’re backing up your data. Because the slave servers contain the same data as the master, you can take a slave offline and archive the data from there instead of from the master server.

Configuring replication: The three topologies

Different database vendors offer different replication topologies (the topology of a replication system describes which servers push data to other servers). MySQL supports three basic topologies:

Single-master/single-slave

Single-master/multiple-slave

Single-master/chained-slaves

The single-master/single-slave topology is easiest to understand. One server acts as the replication master. When you change a table in the master, the modification is pushed to another server, called the replication slave.

A single replication master can push data to multiple slave servers. That’s a single-master/multiple-slave topology.

A replication slave can push changes (which it receives from the replication master) to another slave, resulting in a single-master/chained-slaves topology.

Replication data always flows in one direction: from the master to the slave. The replication master records all data modifications to a binary (that is, not human-readable) log file. When a replication slave connects to the master, it reads the log file and applies all the changes to its own copy of the data.

Replicating MySQL Data 331

That means that you should never change the data on a replication slave. If you do change data on a slave, the master and slave servers get out of synch, and you may lose data. When you create a slave, you start with a copy of the data as it exists on the master.

MySQL lets you choose which tables you want to replicate (you don’t have to replicate all the tables in a database). That means that a single server can act as both a master and a slave, as long as it’s a master for some tables and a slave for others.

Setting up replication for a single slave and master

Creating a single-slave/single-master topology is easy, but you have to do a bit of work on both the master and slave servers, as outlined in the following steps:

1. Log in to your MySQL server on the replication master:

# mysql

Welcome to the MySQL monitor. Command end with ; or \g.

Your MySQL connection id is 5 to server version 3.23.58

Type ‘help;’ or ‘\h’ for help.

Type ‘\c’ to clear the buffer.

mysql>

2. Create a new MySQL user account — the replication slave will log into the replication master using this account:

mysql> GRANT FILE ON *.* TO ‘repl_slave’ IDENTIFIED BY ‘repl_pass’;

Query OK, 0 rows affected (0.13 sec)

3. Exit MySQL and shut down the server:

mysql> quit

#service mysqld stop

4.Create a backup of all the databases that you want to replicate:

#cd /var/lib/mysql

#ls

mysql prod devel

#tar -zcvf data.tgz prod devel prod/

prod/cust.frm

prod/cust.MYI

prod/cust.MYD

...

5.Copy the archive (data.tgz) to the replication slave:

#scp data.tgz slave:/tmp/data.tgz

test.tgz

100% 108.0KB/s 00:05

6. Edit the /etc/my.cnf file and find the section that starts with

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

7. Add the following lines to the end of that section:

log-bin server-id = 1

8. Restart the master server:

# service mysqld start

On the replication slave(s), follow these steps:

1. Shut down the MySQL server:

# service mysqld stop

2.Edit the /etc/my.cnf file and find the section that starts with

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

3.Add the following text to the end of that section:

master-host	= master-hostname
master-user	= repl_slave
master-password	=	repl_pass
server-id	=	2

4. Unpack the archive you made on the server:

# cd /var/lib/mysql