MySQL Replication is incredibly simple to get up and running and this (short) post steps you through it.
Simple Master -> Slave Replication
MySQL allows you to build up complex replication hierarchies, such as multi-master, chains of read slaves, backup databases at a remote site or any combination of these. This post focuses on a simple single master to single slave topology – the more complex solutions are built from this basic building block.
This post also makes the assumption that the 2 MySQL Servers have been installed but that there is no existing data in the master that needs to be copied to the slave – it’s not complex to add that extra requirement and it will be covered in a future post.
Server “black” (192.168.0.31) is to be our master and “blue” (192.168.0.34) the slave.
Step 1: Edit the configuration files & start the MySQL Servers
The first step in setting up replication involves editing the “my.cnf” file on the servers that will serve as the master and slave. A default is provided with the MySQL installation but in case there is already a production MySQL database running on these servers, we provide local configuration files “master.cnf” and “slave.cnf” that will be used when starting up the MySQL servers.
At a minimum we’ll want to add two options to the [mysqld] section of the master.cnf file:
- log-bin: in this example we choose black-bin.log
- server-id: in this example we choose 1. The server cannot act as a replication master unless binary logging is enabled. The server_id variable must be a positive integer value between 1 to 2^32
master.cnf:
[mysqld] server-id=1 log-bin=black-bin.log datadir=/home/billy/mysql/master/data innodb_flush_log_at_trx_commit=1 sync_binlog=1
Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options.
Next, you’ll need to add the server-id option to the [mysqld] section of the slave’s slave.cnf file. The server-id value, like the master_id value, must be a positive integer between 1 to 2^32, It is also necessary that the ID of the slave be different from the ID of the master. If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves.
slave.cnf:
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
datadir=/home/billy/mysql/slave/data
Now, start the MySQL servers using the service manager or directly from the command line if not being run as a service:
[billy@black ~]$ mysqld --defaults-file=/home/billy/mysql/master/master.cnf &
[billy@blue ~]$ mysqld --defaults-file=/home/billy/mysql/slave/slave.cnf&
Step 2: Create Replication User
Create an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege:
[billy@black ~]$ mysql -u root --prompt='master> ' master> CREATE USER repl_user@192.168.0.34; master> GRANT REPLICATION SLAVE ON *.* TO repl_user@192.168.0.34 IDENTIFIED BY 'billy';
Step 3: Initialize Replication
We are now ready to initialize replication on the slave; issue a CHANGE MASTER command:
slave> CHANGE MASTER TO MASTER_HOST='192.168.0.31',
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='billy',
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=4;
Where:
- MASTER_HOST: the IP or hostname of the master server, in this example blue or 192.168.0.31
- MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 2, in this example, “repl_user”
- MASTER_PASSWORD: this is the password we assigned to ”rep_user” in Step 2
- MASTER_LOG_FILE: is an empty string (wouldn’t be empty if there were existing writes to be picked up from the master)
- MASTER_LOG_POS: is 4 (would likely be different if there were existing writes to be picked up from the master)
Finally, start replication on the slave:
slave> start slave;
Step 4: Basic Checks
Now we are ready to perform a basic check to ensure that replication is indeed working. In this example we insert a row of data into the “simples” table on the master server and then verify that these new rows materialize on the slave server:
master> create database clusterdb;
master> create table clusterdb.simples (id int not null primary key) engine=ndb;
master> insert into clusterdb.simples values (999),(1),(2),(3);
slave> select * from clusterdb.simples;
+-----+
| id |
+-----+
| 1 |
| 2 |
| 3 |
| 999 |
+-----+
Hi,
Good write. Typo on last example though, should remove “slave> create database clusterdb;”
Regards
Thanks Shlomi,
I’ve found that I’ve needed to create the database on the slave as well as the master (after that, table creations get replicated).
Regards, Andrew.
That’s very strange. Were you applying any replicate-do-db and co. parameters?
Hi Schlomi,
you were quite right – there’s no need to manually create the database on the slave as replication does it for you (I was getting confused with restoring a backup when introducing replication to an established database).
Thanks again!
First, I got “Error connecting to master: Access denied for user ‘repl_user’@’blue’ (using password: YES)”.
Solution : GRANT REPLICATION SLAVE ON *.* TO repl_user@’%’ IDENTIFIED BY ‘billy’;
(Or @’blue’ maybe more secure)
Then I tried LOAD DATA FROM MASTER;
But I had to add SUPER priviledge.
magix – the “GRANT REPLICATION…” statement is included in Step 2 of the procedure.
Regards, Andrew.
Between step 2 and step 3, I would add this:
master> show master status;
++++–+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
++++–+
| master-bin.000004 | 4 | | |
++++–+
1 row in set (0.00 sec)
This gives you the position to use for step 3’s
MASTER_LOG_POS. This is especially important if your setup requires you to first import data from the master to the slave.
Awesome. Yes just correct innodb for DB. I even did this on same box with two instances using localhost address
Works great, thank you very much.
it very helpfull..but let me confirm one thing that i need to create master.cnf & slve.cnf new files are can use alrdy existng file like my.cnf ??
No reason not to just update your existing .cnf files to make sure that these parameters are set.
Andrew.
Hi Guys,
I’m trying to draw this idea, and setup a replication in my 4 servers, which I have right now sharing one single SQL centralized.
BUT, my question is, dose Slaves are ony to read, or can slaves also insert in the slave, and get written in the Master? I mean, dose this replication are tow ways, or it’s just one single way?? As my use, into all over my servers have to be read,write,update. So, dose this will work for me? Or Slaves will be only to read??
Regards,
(Unless using MysQL Cluster) replication is normally in one direction: Master (server A) -> Slave (server B). In this scenario, any write that was made to a slave would not make it back to the master. You can also make server A be a slave of server B but it is then the applications responsibility that there are no conflicting writes (e.g. don’t update the same rows on different servers).
Andrew.
[…] http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes […]
Nice replication steps. My question is, I do have a replication set up (master/slave). I stopped the replication as per managers request and dropped the replication user. Now, I have to set up replication again, do I have to restart the server since binlog has been enabled already?
Henry
I’ve now moved to MongoDB and so you may have better luck checking with the MySQL forum.
Best Regards, Andrew.
Hello Andrew,
I have a query in Mysql Replication on Windows box. Cant we create replication which transfers the all events(The objectives which are to be replicated) to slave in a time intervals instead of continuous flow of all the objects to salve? I meant to say is , i don’t want to send the modifications continuously to the Slave from master , wanted to set a time to move copy all the records to slave at that particular point of time. Is it possible ? If so , could you please give a brief about it ?
You can use
stop slaveand thenstart slaveon the slave to suspend replication. Provided you’ve configured your binary log to be big enough, it will queue up all of the writes while replication is suspended.where will i find my.cnf files in windows and u can please update screeshots of
the processs
Thanks for your question but I’m afraid that I no longer work for Oracle (I’m now working for MongoDB) and so am not current on MySQL technology. I’d suggest checking the MySQL Cluster forum.
Master.cnf and slave.cnf are separate files which present on two different computer?
Can we test replication process with two separate systems which is connected to same LAN ?
Can you please tell me how do we bind our mysql server with two different Ip Adresses as u have mentioned above i am not able to do that