Using MySQL’s Built-In Replication

Here at Soundbreak we stream out live audio
and video 24 hours a day, so it didn’t take much convincing to be able to run some
testing on MySQL’s new replication feature. We discoverd that we could use it to
keep an up to date standby database server that could handle all of the queries
should the primary fail for any reason. It’s not dificult to configure two servers
for just such a purpose, and I’ll walk through the procedure, as well as a discussion
as to how you can use PHP to redirect the queries should the primary server fail.

MySQL’s internal replication is built on a master-slave relationship between two or
more servers, with one acting as the master, and any number acting as slaves. I’ll
walk through configuring two servers as a master slave pair, describing the process
as we move through it. I initially performed the procedure outlined below on 3.23.22,
and have also tested it on 3.23.23. The MySQL developers recommend that you use the
most recent version, and that both the master and slave use the same version while
the 3.23 series is still in beta as versions may not be backward compatible. I
currently have not yet used this procedure on a live site for that reason, as one of
the advantages of having fail-over capabilities is to be able to upgrade one server
without interrupting any of the queries.

Step One: Configure The Master

For the remainder of the article, I’ll refer to two servers, A (10.1.1.1), the primary
server, and B (10.1.1.2), the standby server.

MySQL’s replication is done by having the slave server (B) connect to the master (A)
and read the binary update log, incorporating those changes into its own databases.
The slave needs a user account to connect to the master, so on the master (A) create
an account with only the FILE privilege with the following:

GRANT FILE ON *.* TO [email protected] IDENTIFIED BY 'password';

Don’t worry about running ‘FLUSH PRIVILEGES’ on the master to ensure that the slave can connect,
as we’ll be stopping the server in the next step.

Now we need a snapshot of the codesent data, and to configure the master to start generating binary update logs.
First edit the ‘my.cnf’ file to enable the binary update log, so somewhere under [mysqld] portion add the line:
‘log-bin’. Now the next time the server starts, we’ll be generating the binary update log (named -bin.
). When you shut down the MySQL server to enable the binary update logging, copy all of the database
directories of the master to another directory, then restart mysqld Be sure to get all of the databases, or
you could end up with errors when replicating if a table or database exists on the master but not on the slave.
Now you have a snapshot of the data, as well as a binary log of any updates since the snapshot. Please note
that the MySQL data files (the *.MYD, *.MYI, and *.frm) are file system dependent, so you can’t transfer files
from Solaris to Linux. If you are in a heterogeneous server environment you’ll have to use mysqldump or other
custom script to get your data snapshot.