RSS
hese are the steps I’ve walked through to have a working mysql replication between two servers, a master and a slave. We’ll call SERVER1 the master and SERVER2 the slave, OK?
Mysql replication is, by itself, a simple concept: you create a copy (snapshot) of one or more mysql database, put it under the slave control and instruct the master to serve the slave with all the subsequent updates (to the data and to the schema). The slave, then, keep updating its data snaphot over the time.
This setup was made on two debian sarge (3.1) with mysql 4.1, but there is nothing here strictly dependant on the distribution.
For simplicity you need, now, to stop the mysql servers (on SERVER1 and SERVER2). We also assume that the mysqld on SERVER2 has no databases of its own (not replicated from SERVER1). This is to keep things clear: if you can’t stop servers, then look further in the reference of this article for some additional, more advanced topics.
1. Setup the master
Firstly you have to instruct you mysql server on SERVER1 that it’s being promoted to be the MASTER in a one-way replication process. This, in practice, is done by activating the binary log (where the server will log every update operation that then will be sent to the slave) and giving the mysql server an “id”
Open up you /etc/mysql/my.cnf and write (or check for existance of):
[mysqld] log-bin=mysql-bin server-id=1
Do not start the server yet.
2. Take a snapshot
While the server is still not running, it’s safe to acquire a snapshot of its data to start up the slave. The simpler way is to get the WHOLE /var/lib/mysql directory, for now (especially if you have InnoDB around…).
cd /var/lib tar cvzf mysql-snapshot.gz mysql/
Now copy mysql-snapshot.gz on SERVER2, but don’t leave SERVER1: we’ll now start the master.
3. Start the master
Simply start the mysqld server on SERVER1.
Then, open a mysql console and type show master status;
You should note a filename and a number (that is, an offset). Write them down: we’ll use them as a reference when we’ll instruct the slave to start itself from these “coordinates”.
4. Prepare an user for the slave
When the slave will connect to the master, it’ll present itself as someone that need some services. We then need to setup an account on the master with some special right.
You’re are still in the mysql client, aren’t you?
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
(modify everything in lowercase accordingly)
OK, master is done.
5. Setup the slave
Log into SERVER2 now.
Edit its /etc/mysql/my.cnf and add this:
server-id = 2 log-bin = /var/log/mysql/mysql-bin.log master-host = server1 master-user = slave master-password = slavepass skip-slave-start
(You’d probably have a log-big set yet.)
Entries are all self-explanatory but the last. With skip-slave-start we tell the mysqld to NOT start the replication process upon starting. Indeed, we start it manually (for clarity sake).
Do not start the server yet.
6. Setup data for the slave
Remember the data snapshot? Well, time to use it.
Assuming it is in /tmp/ :
cd /var/lib mv mysql mysql.old tar xvfzp /tmp/mysql-snapshot.gz
We’ve used the “p” flag for tar to keep the mysql owner for the mysql directory.
Finally, you can start the mysqld server on SERVER2.
(Note: now you could get a mysql error from the Debian start script. This is because we’ve copied the mysql database too, and the password for the the “debian-sys-maint” mysql user is now wrong.)
7. Starting replication
Log into your mysql client.
Reset every replication information (just in case…)
reset slave;
Tell the slave where to begin replication from:
change master to master_log_file='mysql-bin.000006', master_log_pos=35814;
(the logfile and the logpos where those I did tell you to write down in #3)
Note that the “change master” command could be used to also overwrite all setup we made in my.cnf, regarding host name, username, password, etc.
Fire it up!
slave start;
8. Check replication status
show slave statusG;
(note the G)
If Slave_IO_State is “Waiting for master to send event”, Slave_IO_Running is “Yes” and Slave_SQL_Running is “Yes”, congrats: your replication setup is done and online.
9. Final notes and references
Remember to remove the skip-slave-start option from the my.cnf of the slave.
Remember (if necessary) to set the password for the “debian-sys-maint” user on the slave. The currently used password for your system is in /etc/mysql/debian.cnf. You could get into your mysql client as the root user and:
use mysql
update user set password=password('SfDEngccWAbQrWJs') where user='debian-sys-maint';
flush privileges;
Any errors (Last_Error) will stop the replication. Period.
Replication errors are very unlikely.
You can stop the replication with slave stop.
If something won’t work, chek logs: Debian logs mysqld messages in /var/log/syslog
Mysql replication does not work backwardingly: if you now remove a database from the slave, this will not be re-created.