How To Set Up MySQL Database Replication

***Configuring The Master (server1)***

On the MySQL shell, run the following commands:

GRANT REPLICATION SLAVE ON *.* TO 'slavedb'@'%' IDENTIFIED BY 'slavepasswd';
FLUSH PRIVILEGES;
quit;


Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master),

which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we

add/enable the following lines in /etc/mysql/my.cnf (in the [mysqld]section):

vi /etc/mysql/my.cnf

[...]
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = exampledb
[...]


Then restart MySQL:

/etc/init.d/mysqld restart


Next we lock the exampledb database on server1, find out about the master status of server1, create an SQL dump of exampledb (that we will import

into exampledb on server2 so that both databases contain the same data), and unlock the database so that it can be used again:

mysql -u root -p


On the MySQL shell, run the following commands:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command should show something like this (please write it down, we'll need it later on):

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


Now don't leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must

create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump

snapshot.sql and transfer it to server2 (using scp; again, make sure that the root account is enabled on server2):

server1:

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp


Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell:

server1:

UNLOCK TABLES;
quit;


***Configuring The Slave (server2)***

Now we must configure the slave. Open /etc/mysql/my.cnf and make sure you have the following settings in the [mysqld] section:

server2:

vi /etc/mysql/my.cnf

[...]
server-id=2
master-connect-retry=60
replicate-do-db=exampledb
[...]


The value of server-id must be unique and thus different from the one on the master!

Restart MySQL afterwards:

/etc/init.d/mysqld restart


Before we start setting up the replication, we create an empty database exampledb on server2:

mysql -u root -p

CREATE DATABASE exampledb;
quit;


On server2, we can now import the SQL dump snapshot.sql like this:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql


Now connect to MySQL again...

mysql -u root -p


... and run the following command to make server2 a slave of server1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server1!):

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.


Finally start the slave:

START SLAVE;

Then check the slave status:

SHOW SLAVE STATUS \G

It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should

check your setup again and take a look at /var/log/syslog to find out about any errors);

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.100
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: exampledb
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:
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)


Afterwards, you can leave the MySQL shell on server2:

quit;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!

HOWTO: Setup a rsync server on Centos 5

These instructions assume that you have root access and are operating as the root user. If you only have sudo access, just add sudo to the beginning of most of the commands. The standard port for rsync which is 873 is assumed too.


Install xinetd and rsync packages

$ yum -y install xinetd rsync

Make sure xinetd is running on levels 3, 4 and 5
view sourceprint?
$ chkconfig --level 345 xinetd on

Modify rsync xinetd configuration, and change disable = yes to disable = no
view sourceprint?
$ vi /etc/xinetd.d/rsync

Create rsync secrets file for passwords with format of username:password.
view sourceprint?
$ vi /etc/rsync.secrets

Create configuration for rsync shares
view sourceprint?
$ vi /etc/rsync.conf

Fix up permissions and ownership, and restart xinetd service
view sourceprint?
1.$ chown root.root /etc/rsyncd.*
2.$ chmod 600 /etc/rsyncd.*
3.$ service xinetd restart

Test it out locally, you should get @RSYNCD:
view sourceprint?
$ telnet 127.0.0.1 873

Test it out remotely, and here is where the fun begins. Firewalls and settings stuff!

SELinux (Security Enhanced Linux)

Find out if selinux is running. You can either look in the configuration file located at /etc/selinux/config or just try to look at the variable we are going to disable.

$ sudo /usr/sbin/getsebool rsync_disable_trans

rsync_disable_trans --> off

So this tells us that selinux is running and that rsync is being protected by selinux. The on/off is a bit confusing, but off means nothing will get to your rsync daemon. Let's change that.. and restart the xinetd service
view sourceprint?
1.$ setsebool -P rsync_disable_trans 1
2.$ service xinetd restart

IPTables Firewall

Add an iptables entry for rsync's port.
view sourceprint?
$ vi /etc/sysconfig/iptables

Add the following entry somewhere near the other protocols, but before the final drop line.
view sourceprint?
#
# rsync - add entry to allow 192.168.0.* to connect to our rsync server.
#
-A INPUT -s 192.168.0.0/255.255.255.0 -p tcp -m tcp --dport 873 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 873 -j DROP

You could also just run the iptables command and add the rules then save it out. Your choice.


Configuring /etc/rsyncd.conf

Being co-written by Andrew Tridgell, author of Samba, it's no surprise that Rsync's configuration file looks just like Samba (and Windows' :-), and that Rsync lets you create projects that look like shared directories under Samba. Accessing remote resources through this indirect channel offers more independence, as it lets you move files on the source Rsync server without changing anything on the destination host.

Any parameters listed before any [module] section are global, default parameters.

Each module is a symbolic name for a directory on the local host. Here's an example:

#/etc/rsyncd.conf
secrets file = /etc/rsyncd.secrets
motd file = /etc/rsyncd.motd #Below are actually defaults, but to be on the safe side...
read only = yes
list = yes
uid = nobody
gid = nobody

[out]
comment = Great stuff from remote.acme.com
path = /home/rsync/out

[confidential]
comment = For your eyes only
path = /home/rsync/secret-out
auth users = joe,jane
hosts allow = *.acme.com
hosts deny = *
list = false

Note: Rsync will not grant access to a protected share if the password file (/etc/rsyncd.secrets, here) is world-readable.


Here's an example under Linux on how to set up a replication through SSH:

rsync -avz -e ssh rsync@remote.acme.com:/home/rsync/out/ /home/rsync/from_remote

An important thing here, is that the presence or absence of a trailing "/" in the source directory determines whether the directory itself is copied, or simply the contents of this source directory.

In other words, the above means that the local host must have a directory available (here, /home/rsync/from_remote to receive the contents of /home/rsync/out sitting on the remote host, otherwise Rsync will happily download all files into the path given as destination without asking for confirmation, and you could end up with a big mess.

On the other hand, rsync -avz -e ssh rsync@remote.acme.com:/home/rsync/out /home/rsync/from_remote means that the an "out" sub-directory is first created under /home/rsync/from_remote on the destination host, and will be populated with the contents of the remote directory ./out. In this case, files will be save on the local host in /home/rsync/from_remote/out, so the former commands looks like a better choice.

Here's how to replicate an Rsync share from a remote host:

rsync -avz rsync@remote.acme.com::out /home/rsync/in

Notice that we do not use a path to give the source resource, but instead just a name ("out"), and that we use :: to separate the server's name and the resource it offers. In the Rsync configuration that we'll see just below, this is shown as a [out] section. This way, admins on remote.acme.com can move files on their server; As long as they remember to update the actual path in the [out] section (eg. PATH=/home/rsync/out to PATH=/home/outgoing), remote Rsync users are not affected.

An Rsync server displays the list of available anonymous shares through rsync remote.acme.com::. Note the ::. For added security, it is possible to prompt for a password when listing private shares, so that only authorized remote users know about the Rsync shares available from your server.


Useful command-line switches

-v, --verbose increase verbosity
-q, --quiet decrease verbosity
-c, --checksum always checksum
-a, --archive archive mode. It is a quick way of saying you want recursion and want to preserve everything.
-r, --recursive recurse into directories
-R, --relative use relative path names
-u, --update update only (don't overwrite newer files)
-t, --times preserve times
-n, --dry-run show what would have been transferred
-W, --whole-file copy whole files, no incremental checks
-I, --ignore-times Normally rsync will skip any files that are already the same length and have the same time-stamp. This option turns off this behavior.
--existing only update files that already exist
--delete delete files that don't exist on the sending side
--delete-after delete after transferring, not before
--force force deletion of directories even if not empty
-c, --checksum always checksum
--size-only only use file size when determining if a file should be transferred
--progress show progress during transfer
-z, --compress compress file data
--exclude=PATTERN exclude files matching PATTERN
--daemon run as a rsync daemon
--password-file=FILE get password from FILE