Setting up database replication on MySQL

Database replication is, as the americans would say, quite awesome. It provides, for one, an always fresh database backup. But most of the time you won't do it for backups. The greatest thing about database replication is that you can use it for load balancing.

Just replicate the database onto another server, and you have two atabase servers for SELECTs and you can leave the master for INSERTs, DELETEs and UPDATEs. The only bad thing about this is that you will need different connections for read and write (nothing that Dr. Nic's magic can't resolve), and you must have a fast connection between the two servers or you may have records that don't show up instantly when you store them (that's bad for a web application).

First of all, I'm supposing you already have a production database running in a server. If you start from scratch with several database servers, then it's much easier, as you don't have to dump/restore databases.

Go to your master server (let's say her name is scherie), and edit /etc/mysql/my.cnf:

Make sure you have set server-id to 1:

server-id = 1

You need to uncomment the bind-address line, as we need port 3306 open for our slave:

#bind-address = 127.0.0.1

By default, Debian for instance, already saves the transaction log. Uncomment or add it if you don't have it:

log_bin  = /var/log/mysql/mysql-bin.log

Add a line for the database that will be dumped to the binary log:

binlog-do-db=database_to_be_replicated

You can now restart the database:

scherie:~# /etc/init.d/mysql restart

Open a mysql client, and create a new user called replication_user (change this to whatever you want):

scherie:~# mysql -u root -p

mysql> grant replication slave on *.* to 'replication_user'@'%' identified by 'password';

Now we have to lock temporarily the database while we proceed to dump it, so we have the correct binary log offset (Position).

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.00002
Position: 230
Binlog_Do_DB: database_to_be_replicated
Binlog_Ignore_DB:

1 row in set (0.00 sec)


Don't close the mysql client
. Leave the terminal open, otherwise, the database will be unlocked. Write down the File and Position values, we will need them later.

Dump the database (you could use load data from master, but it's deprecated and will be removed in the future).

scherie:~# mysqldump -u root -p database_to_be_replicated > database_to_be_replicated.sql
Enter password:

Once dumped return to the open terminal, and unlock tables:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

It would be a good idea to compress the database dump before transferring it:

scherie:~# bzip2 database_to_be_replicated.sql
scherie:~# scp database_to_be_replicated.sql daniel@athena:

You should also allow access from the slave to the master server, but block it otherwise (basic iptables rules):

-A INPUT -s ip_of_slave_server -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable

You're done with the master configuration. Let's open a terminal to our slave server (let's say she is called athena):

Check whether we have access to the master:

athena:~# mysql -u replication_user -p -h scherie
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 38
Server version: 5.0.45-Debian_1-log Debian etch distribution

We can restore now the database dump from the master:

athena:~# bzip2 -d insolitus.sql.bz2
athena:~# mysql -u root -p insolitus < insolitus.sql
Enter password:

Add the following data to the slave's my.cnf, under the [mysqld] section:

server-id                   = 2
master_host             = ip_of_master_server
master_user             = replication_user
master_password    = password
replicate_do_db       = database_to_be_replicated

Restart mysql:

athena:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.

Connect as root to our slave server:

athena:~# mysql -u root -p

Stop the slave:

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

Now, we need those File and Position parameters we got from the master, change the values on master_log_file and master_log_pos for those you wrote down previously:

mysql> change master to master_host='ip_of_master_server', master_user='replication_user', master_password='password',master_log_file='mysql-bin.000002', master_log_pos=230;
Query OK, 0 rows affected (0.01 sec)

We start the slave:

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

Now, if we issue a show slave status, you should see the following variables to 'Yes':

mysql> show slave statusG;

          Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

If you have Slave_IO_Running set to No, you haven't probably locked correctly the database prior to dumping it. You will have to repeat the dumping process locking correctly the database.

If everything is correct, your database will be automatically replicated. Doing a simple show processlist; in either the master or the slave will show whether the communication is working.

You can also take a look at the full guide to replication at the MySQL official site.



 


Trackback

Trackback URL for this entry:
http://www.gra2.com/trackback.php/setting-up-database-replication-on-mysql

Here's what others have to say about 'Setting up database replication on MySQL':

Step-by-step: how to setup MySQL Database replication | Aciddrop.com
Tracked on Thursday, January 10 2008 @ 05:49 PM CET

Comments

Post a comment

sefse

Anonymous on Wednesday, May 25 2011 @ 08:25 AM CEST Reply | #

XVBMbP Internet is written with the capital letter in a sentence, by the way. And hundredths are written not with a point but with a comma. This is according to the standard. And actually everything is very good..!

Cheap oem software on Thursday, September 29 2011 @ 01:44 AM CEST Reply | #

Which came first? chicken or the egg

Tailliact on Friday, November 04 2011 @ 05:53 AM CET Reply | #

mutuelle animaux - http://www.kvti.edu.bt/modules/forum/profile.php?id=1342

assurance chien chat on Friday, January 13 2012 @ 05:35 PM CET Reply | #

Wahooooo,

susan2 on Monday, May 07 2012 @ 05:17 AM CEST Reply | #

Wahooooo,

paul56 on Thursday, May 10 2012 @ 04:18 AM CEST Reply | #

oh yes,

dave56 on Wednesday, June 20 2012 @ 11:48 PM CEST Reply | #

love it,

jamesy679p on Sunday, June 24 2012 @ 05:44 PM CEST Reply | #

love it,

susan2 on Tuesday, September 04 2012 @ 05:35 AM CEST Reply | #

oh boy,

david6677ll on Friday, September 07 2012 @ 02:13 AM CEST Reply | #

Search



About

newton.gra2.com is a blog about technology, opinion and random thoughts written by Daniel Alvarez, a computer engineer currently living in Zurich, Switzerland.

Topics

News (20/0)
Manuals (24/0)
Security (7/0)
Music (3/0)
Weeklog (1/0)
Personal (34/0)
Photos (3/0)
Opinion (14/0)
Windows (5/0)

Blogroll

Pros i contres (Jordi)
Entrepa de fusta (Oriol)
Spaghetti Code (Isaac)
Made in net (Eric)
Nogare (Juan)
Blog de Isaac Jimenez
Web d'en Jaume Benet
Montcada Wireless (Fran)
Blog d'en Ricard Forniol
Angela Fabregues
in.solit.us

Libertad Digital
FOX News
The Wall Street Journal
The Washington Times
The Jerusalem Post

Michelle Malkin
Eurabian News
Nihil Obstat
Barcepundit
Expose the left
Davids Medienkritik
Johan Norberg
Ayaan Hirsi Ali

User Functions

:

:


Lost your password?

Latest posts

Stories

No new stories

Comments last 2 days

No new comments

Trackbacks last 2 days

No new trackback comments

Links last 2 weeks

No recent new links