fluidBlog

This post will walk you through setting up replication for a MySQL database which has existing data.

Why Replication?

• Backups. As part of a backup strategy, replication avoids shutting down the master if backups are made from the slave.
• Scale-out. Replication can allow the distribution of reads over multiple replication slaves.
• Reliability. Replication can provide a ready backup in the event of a failure.

This post will only address the use of replication in a backup strategy, but the setup steps are valid for other uses as well.

Assumptions:

• The master database has existing data.
• The master and slave have different IP addresses.
• The master database to be replicated is named exampledb.
• The database exampledb exists on the master but not on the slave.
• The database uses MyISAM tables.
• The master and slave run on Fedora. This should only affect the directory structure.
• The master and slave use port 3306. This affects firewall setup and Master configuration. To use a different port, set --master-port in the CHANGE MASTER TO command.

Preliminary steps.

Configure the firewall to allow the master and slave to communicate.

You have a firewall, right? Assuming the master server is 2xx.xx.xx.01 and the slave is 2xx.xx.xx.02 corresponding to server_id=1 for the master and server_id=2 for the slave.

On the master add the following to /etc/sysconfig/iptables

# Allow incoming and outgoing traffic on port 3306 for MySQL slave server
-A INPUT -p tcp -s 20x.xx.xx.02 --sport 1024:65535 -d 2xx.xx.xx.01 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -s 2xx.xx.xx.01 --sport 3306 -d 2xx.xx.xx.02 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

On the slave add the following to /etc/sysconfig/iptables

# Allow incoming and outgoing traffic on port 3306 for MySQL slave server
-A INPUT -p tcp -s 2xx.xx.xx.01 --sport 1024:65535 -d 2xx.xx.xx.02 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -s 2xx.xx.xx.02 --sport 3306 -d 2xx.xx.xx.01 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart the firewall

# /sbin/service iptables restart

Create a replication user, repl on the slave server.

# /usr/sbin/useradd -r repl
# /usb/bin/passwd repl

Grant privileges to the replication user on the master server.

mysql> GRANT FILE, REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%' IDENTIFIED BY 'password';

Prepare the Master

In order to prepare the master, it will have to be locked for a short time. While it’s locked we will create a dump file, and edit the configuration in /etc/my.cnf.

You must enable binary logging so we’ll start by configuring the master server binary logging and restarting the master.

Each server in the replication group must have a unique id. The master server is typically assigned an id of 1.

The master server must allow networking, so ensure that --skip-networking and --bind-address are commented out if present.

While your at it, check that the pemissions of the file my.cnf are 0400 or 0600.

Edit the master database’s configuration in the file /etc/my.conf. Add the lines under the [mysqld] section.

[mysqld]
# enable networking and listen on all IP addresses
# by commenting out the following two lines if they exist
#skip-networking
#bind-address            = 127.0.0.1
# The following line will create the log file
# /var/lib/mysql/mysql-bin.00000x
log-bin=mysql-bin
# set expire_logs_days no lower than the number of days the slave is behind
expire_logs_days=3
server-id=1
# substitue 'hostname' with the actual name of your host
relay-log='hostname'-relay-bin
# duplicate the following line for every database that needs to be replicated
# substitute 'exampledb' with actual name of the database to be replicated
binlog-do-db='exampledb'

Restart the master

# /etc/init.d/mysqld --report-host restart

The --report-host option will make checking the replication status easier.

Check the error log at /var/log/mysqld.log after restarting mysqld.

Next we will lock the master database, record the binary log file’s position, and create a database dump that we’ll use to initialize the slave database.

Lock the database

# mysql -p -u root
mysql> use exampledb;
mysql> FLUSH TABLES WITH READ LOCK;

Obtain status of the binary log on the master.

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Record File and Position, you’ll need it to synchronize the slave.

Leave this client running while the dump file is created.

Create a dump backup file

# mysqldump -u root -p exampledb > exampledb.sql

On master, unlock the tables

mysql > UNLOCK TABLES;

Setting up the slave

Edit the slave’s configuration in the file /etc/my.cnf

[mysqld]
log-bin=mysql-bin
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2
# duplicate the following line for every database that needs to be replicated
# substitute 'exampledb' with actual name of the database to be replicated
replicate-do-db='exampledb'
report-host='slave-hostname'

Since the master.info file overides settings in my.cnf, you may prefer to set up the slave using the CHANGE MASTER TO command. As a minimum, you need to use the CHANGE MASTER TO command to set the values of MASTER_LOG_FILE and MASTER_LOG_POS

Change the option values to the actual values for your servers

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS='recorded_log_position';

Start the slave

$ /etc/init.d/mysqld --skip-slave start

The --skip-slave option tells the server not to start the slave threads. We’re using this option because we aren’t quite ready to replicate.

Create the database

# mysql -u root -p
mysql> create database exampledb;
mysql> grant all on exampledb.* to 'repl'@'localhost';

Import the dump file

# mysql -u root -p exampledb < full_dump_file.sql

Start the slave threads

$ mysql> START SLAVE;

Checking the status of the master and slave

On slave

mysql> SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: fluidrails.com
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: hakota-relay-bin.000073
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: garden
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

On master

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 98
Binlog_Do_DB: garden
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SHOW PROCESSLIST\G;
+------+------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id   | User | Host                  | db   | Command     | Time | State                                                    | Info             |
+------+------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 6235 | repl | www.hakota.com:43033  | NULL | Binlog Dump | 3250 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+------+------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+

mysql> SHOW SLAVE HOSTS;
+-----------+------------+------+-------------------+-----------+
| Server_id | Host       | Port | Rpl_recovery_rank | Master_id |
+-----------+------------+------+-------------------+-----------+
|         2 | hakota.com | 3306 |                 0 |         1 |
+-----------+------------+------+-------------------+-----------+
1 row in set (0.00 sec)

Logs and Backups

Now that we’ve enabled binary logging we need to implement a process to manage the logs. One approach is to use SHOW SLAVE STATUS (on slave) and SHOW BINARY LOGS (on master) to determine which logs are in use. Suppose the slave is using Master_Log_File: master-bin.000004 Make sure your backups contain the logs about to be deleted, those prior to the target log master-bin.000004, then

mysql> PURGE MASTER LOGS TO 'master-bin.000004';

will delete all logs prior to master-bin.000004

In addition, now that we’ve implemented replication, our backup strategy needs to include the master.info and relay-log.info status files as well as the binary logs.

Before executing mysqldump on the slave, replication should be temporarily stopped.

# mysqladmin -u root -p  stop-slave
# mysqldump -u root -p exampledb > exampledb.sql
# mysqladmin -u root -p start-slave

References

MySQL 5.0 Reference Manual, Chapter 15 Replication

Brian Reindel wrote a review of syntax highlighters for blog code samples. One of the comments on his post caught my attention. Dan wrote that he uses vim and vividchalk for his blog. I love it when I learn about an existing solution using familiar tools. I gave Dan’s method a try and was very pleased with the result. I know there are many WordPress Plugins for syntax highlighting but this is another tool that comes in handy when source is presented on the web as a separate page, for example, dbbackup.sh.

You will need to use a version of vim with GUI support, gvim, on Fedora, to ensure the colors are correct. You can install gvim on Fedora with the following command

$ sudu yum install gvim*

On Fedora, syntax scripts are in the directory /usr/share/vim/vim70/syntax/ and color schemes are in the directory /usr/share/vim/vim70/colors/. You can get other scripts from vim online.

Launch gvim from a terminal or the menu Applications->Programming->Vi Improved.

If you want embedded style in the HTML output then issue the colon command

~
:let html_use_css = 1

If you like the vividchalk color scheme, issue the command

~
:colorscheme vividchalk

To create an HTML version of the file, issue the command

~
:TOhtml

and save the HTML file by issuing the “ZZ” command, and quit the session to leave the original file unchanged with the command

~
:q!

The :TOhtml command produces a complete HTML page. To embed the highlighted syntax within a post, simply change the <body> tag to an appropriate CSS tag for your theme and delete the <html> and <head> sections. Here is a snippet as an example of embedding the highlighted source within a blog post. The HTML was created with the commands

~
:let html_use_css = 0

~
:TOhtml

Without CSS, the generated HTML needs to be enclosed in a tag that can enclose the <font> tag, for example, the <code> tag.

class Zipcode < ActiveRecord::Base
  belongs_to :postoffice
  has_many :locations

  def self.find_by_postoffice_name(postoffice_name)
    postoffice_id = Postoffice.find_by_name(postoffice_name).id
    find(:all, :conditions => [“postoffice_id = ?“, postoffice_id])
  end

  def self.find_by_postoffice_id(postoffice_id)
      find(:all, :conditions => [“postoffice_id = ?“, postoffice_id])
  end
end

As an added bonus, the output from vim using css validates to HTML 4.01 Strict.

See the vim documentation for a complete rundown on syntax highlighting in vim.

After struggling with Typo for several months, I’ve switched this blog to WordPress. In one sentence, here’s why.

You won’t be seeing a “How-to install WordPress” post on this blog in the style of Installing Typo on Fedora 6 slice from Slicehost because installing WordPress is just plain simple, and it works.

And there you have it, WordPress vs. Typo in one sentence. There are other reasons of course, but I should have seen the other difficulties to come right from the start. More often than we want to admit, first impressions do matter.