Database replication in MySQL :: Master

Database replication in MySQL :: Master – Slave

September 1, 2009 | databases, linux
author: Karol Zielinski | comments: 1 | views: 833
Tags: master, mysql, replication, slave

I will try to describe how to set up database replication in MySQL. Replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. Today I will describe how to set up replication Master – Slave, next time – I will write about replication Master – Master. So let’s go.

Configure master

edit /etc/mysql/my.cnf

and comment these lines:

#skip-networking
#bind-address            = 127.0.0.1

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.

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=database_name_to_replication
server-id=1

(…) and now:

/etc/init.d/mysql restart

GRANT REPLICATION SLAVE ON *.* to 'replication'@'%' IDENTIFIED BY 'some_password';
FLUSH PRIVILEGES;

propably these privileges won’t be enough (more about it could be found here), so for sure:

GRANT RELOAD ON *.* TO 'replication'@'%'
GRANT SUPER ON *.* TO 'replication'@'%'
FLUSH PRIVILEGES;

now we need to flush tables in our database:

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

we should see something like:

| mysql-bin.006 | 83 | database_name_to_replication |      |

Our master is configured properly. Now let’s configure slave and move all data to it.

Configure slave

CREATE DATABASE database_name_to_replication;

edit /etc/mysql/my.cnf:

master-host = host_to_master_server
master-user = replication
master-password = 
master-port = 3306
master-connect-retry=60
replicate-do-db=database_name_to_replication

restart MySQL server:

/etc/init.d/mysql restart

STOP SLAVE;
RESET SLAVE;
START SLAVE;
SHOW SLAVE STATUS;

last commend should return something like:

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
(...)

the most important: there should be some information in Slave_IO_State and two fields: Slave_IO_Running and Slave_SQL_Running have to be set to YES.

OK, now we should load data from our master…

LOAD DATA FROM MASTER;

or… you can always use mysql -usome_user_name -psome_password -Ddatabase_name_to_replication < dump_from_master_db.sql

after our data is moved to slave server… we can unlock our tables.

so log in to MySQL sever (master) and:

USE database_name_to_replication;
UNLOCK TABLES;

That’s all. Now you can change something in your database (master) and you will see changes in you slave, as well.

Article based on some other sources, including:

Post Database replication in MySQL :: Master – Slave to develway

Comments (1)

Twitter Trackbacks for Database replication in MySQL :: Master – Slave – Just a tech stuff [karolzielinski.com] on Topsy.com
September 1, 2009, 5:49 am

[...] Database replication in MySQL :: Master – Slave – Just a tech stuff tech.karolzielinski.com/database-replication-in-mysql-master-slave – view page – cached Database replication in MySQL :: Master – Slave – Just a tech stuff – made by Karol Zielinski. My tech life including things like… python, java, php — From the page [...]