Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons I have set up master-slave replication using MySQL.
1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.
2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.
Ok let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up. I will just touch on very basic ones to get the replication to work. Here are some assumptions:
Master server ip: 10.0.0.1
Slave server ip: 10.0.0.2
Slave username: slaveuser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/
Put the following in your master my.cnf file under [mysqld] section:
# changes made to do master
server-id = 1
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
log-bin = /usr/local/mysql/var/mysql-bin
# end master
Copy the following to slave’s my.cnf under [mysqld] section:
# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup
Create user on master:
mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';
Do a dump of data to move to slave
mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql
import dump on slave
mysql < masterdump.sql
After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='slaveuser', MASTER_PASSWORD='slavepw';
Let us start the slave:
mysql> start slave;
You can check the status of the slave by typing
mysql> show slave status\G
The last row tells you how many seconds its behind the master. Don’t worry if it doesn’t say 0, the number should be going down over time until it catches up with master (at that time it will show Seconds_Behind_Master: 0) If it shows NULL, it could be that slave is not started (you can start by typing: start slave) or it could be that it ran into an error (shows up in Last_errno: and Last_error under show slave status\G).
Related posts:
- How do I dump all tables in a database into separate files?
- How do I import individual table dump files in to MySQL using shell script?
- MySQL backups using mysqldump
————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.
Thanks. Will give it a try. I would be very helpful if you could also explain what the configuration options mean/do.
Regards,
HC
nice entry, was wondering if you can answer this question. you mentioned that doing master/slave replication, you can have insert in master, and select in slave. does it do that automatically by following your tutorial or these’s something else needs to be done? and can i tell which goes to which?
You would do that in code. Just remember that after you do insert, it might take a sec or two to show up on slave. Best thing to do is to do a insert, and leave user on master for a while. Amount of time you leave user on master depends on what kind of setup you have ie. using innodb vs myisam, how many inserts are happening, etc.
I have a lot more to learn, interesting.
Pingback: MySQL: How do you set up master/master replication in MySQL? (CentOS, RHEL, Fedora) | Technology: Learn and Share
Hi, i set last day the master/slave replication – it works! The next step – i would like to replicate different DBs from more masters to one slave. Is it possible?
I wish to have two slaves. Is this possible by using the same config but with “server-id = 3”?
Yes. Just follow the same thing you use to setup first slave to setup n numbers of slaves. Just keep incrementing the id.
if table locks are a problem try converting your tables from MyISAM to InnoDB and and set innodb_table_locks off in your my.cnf if you don’t have access to that I think you can change the setting with SET GLOBAL innodb_table_locks OFF.
one of the superior features of InnoDB is that it can do row level locking rather than the table level.
I don’t have a MY.CNF I have a MY.INI though and it looks like that’s the place to edit ??
I’ve seen various instructions about editing these files, and they’re all different !! So I can’t figure out what’s right.
homebrew > this method is ok.
I used it with two servers : master on linux, slave on windows 2K3
With my.ini, be carreful about the paths. I.e.:
relay-log = “X:/mysql/mysql-relay-bin”
Yes, use slashes like Unix (not backslashes as usual with windows) ans put your path under quotes.
Looks like my.ini is a Windows specific (probably why I didn’t know about it) thing from the link below it looks like on windows mysql will accept configuration options from either a my.ini or a my.cnf (neither MUST exist) and they may be located in a number of places with different effects, either global or user specific.
From a brief read of the page it seems like there is no difference between a my.ini or a my.cnf and the same options can be placed in both including the one I mentioned previously.
I have in my ‘my.cnf’:
innodb_table_locks = 0
http://dev.mysql.com/doc/refman/5.0/en/option-files.html
Also there are loads more fun InnoDB options to play with here:
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html
Hello. Is it possible to replicate using mysql server version 3.x as master and mysql server version 5 as a replication host? Thanks in advance. Norberto
Hi,
I was wondering if the following scenario would be possible.
Server 1: Database X (master)
Server 2: Database Y (master)
– replicating to –
Server 3: Database X, Database Y (slave)
The databases are independent, each master:slave relationship is still 1:1. However, as the slave server only seems to accept configuration details for one master server I can’t figure out if this is possible.
Does anyone have any experience of this?
Electron,
You can run 2 instances on server 3 to accomplish this.
Pingback: MySQL Replication Series: How does MySQL replication works? | Technology: Learn and Share
I would like to know in case that the master does not respond and if for that reason i want to use the slave as the master (until the original master server is alive again) what steps should i do for allowing this
Slave Replication Errors. Can you tell me what is wrong?
[root@dhi015 mysql]# tail mysql.err
080716 8:24:30 InnoDB: Started; log sequence number 0 43665
080716 8:24:30 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.0.45-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution
080716 10:57:05 [Note] Slave SQL thread initialized, starting replication in log ‘FIRST’ at position 0, relay log ‘/var/log/mysql/mysql-relay-bin.000001’ position: 4
080716 10:57:05 [Note] Slave I/O thread: connected to master ‘[email protected]:3306’, replication started in log ‘FIRST’ at position 4
080716 10:57:05 [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
080716 10:57:05 [Note] Slave I/O thread exiting, read up to log ‘FIRST’, position 4
[root@dhi015 mysql]#
Thanks – Chris
Chris it’s caused by this: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work
So in your my.cnf on either master or slave, change the: server-id = # <- to another number. Let me know if it works or not.
[quote]
Hi,
I was wondering if the following scenario would be possible.
Server 1: Database X (master)
Server 2: Database Y (master)
– replicating to –
Server 3: Database X, Database Y (slave)
The databases are independent, each master:slave relationship is still 1:1. However, as the slave server only seems to accept configuration details for one master server I can’t figure out if this is possible.
Does anyone have any experience of this?
[/quote]
Maybe the following is one of the solutions.
http://www.ziddu.com/download/1886062/mm-single-slave-repl.pdf.html
Pingback: HyperDB Explained Part 2 « life by way of media
Hi,
🙂
Thanks Man…..
You saved me!
_^_
AnoopL
* Scenerio: Master/multiple slaves
* Problem: 50GB ibdata1 file
* Goal: reduce the size of ibdata1
* Non replication method: dump the master database, drop all dbs, import dump
* Question: what wil doing this do to the slaves in a replication scenerio? How would you do this and can you explain your answer?
After doing this setup, by default, only Information_schema and test databases are accessible from slave and hence only those two databases are getting replicated onto slave.
Good writeup.
After doing this setup, by default, only Information_schema and test databases are accessible from slave and hence only those two databases are getting replicated onto slave.
How to we provide permission for other databases?
Thanks & Regards
Sundar
Sundar: check your my.cnf to make sure you are replicating all of the db’s.
Hi there,
Can anyone help me with this ? I am trying to do replication of Centos 5.6
when I tried
mysqldump -u root–all-databases –single-transaction –master-data=1 > masterdump.sql
It shows
mysqldump: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: NO) when trying to connect
When I tried
mysqldump -u root -p –all-databases –single-transaction –master-data=1 > masterdump.sql
It shows
mysqldump: Error: Binlogging on server not active
Even, I already added binlogging inside my.cnf file
THanks You so Much
My bad, I fixed. Thanks anyway
Thanks for this, man. I will definitely give it a shot.
Pingback: Where can I find documentation on my.cnf directives? - Admins Goodies
Pingback: moeblog » Mysql master-slave
Pingback: Setting up Replication for MySQL data « KaixersofT { ScriptBlocK } Weblog
Hello admin, i must say you have hi quality content here. Your page can go viral.
You need initial traffic boost only. How to get it?
Search for; Mertiso’s tips go viral
Hellow my name is Martinjax. Wery capable post! Thx 🙂
really nice…………….
it’s a really good article and very informative information and like this site.