Installation

5.5 on CentOS 6

rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
yum --enablerepo=remi list mysql mysql-server
#if mysql is currently installed, I removed my old version.
yum remove mysql mysql-server
yum --enablerepo=remi install mysql mysql-server
#Add exclude=mysql* to the other repos (centos-base, epel, etc)

Replication (overview)

The process of setting up a mysql slave is very easy. I offer you two ways to read my process, the short version or the long version. They both accomplish the exact same task, they just provide more or less detail.

Overall, the process is simple:

Stop mysql on the master or slave you are copying from, and get the Master_Log_File and Read_Master_Log_Pos values from show slave status or show master status
Grant the slave replication permission on the master
Copy over the entire mysql datadir, as well as any innologs.
Copy over /etc/my.cnf to the new slave, edit it for the new server
Delete relay-log.info and master.info from the slave's datadir
Start mysql on the new slave, issue CHANGE MASTER TO command
Start the master or the first slave back up, then start the slave new with start slave

Replication (Short & quick version)

The obvious: master is your master server. slave1 is the first slave, slave2 is the second, etc etc. This assumes any slaves have never been set up before. If your a re-syncing to a pre-existing slave (ie it broke, or you wanted to make it dev then make it a live slave again or whatever the reason) then delete everything from the datadir & delete all innodb, bin, and relay logs.

Copying data to a slave

One master, one slave

On master:

mysql -p
- use mysql; grant replication slave on *.* to 'replication'@10.252.12.115 identified by 'slavepassword';
- show master status
  - Save this output.
If you have myisam only:
- FLUSH TABLES WITH READ LOCK
If you use innodb, stop mysql
rsync -e ssh -avz /mysqldatadir root@slave1:/mysqldatadir
scp /mysqllogdir/innologs/ root@slave1:/mysqllogdir/innologs/
scp /etc/my.cnf root@slave1:/etc

One master, two slaves

This is the quickest way, copying data from slave1 to slave2

On slave1:

mysql -p
- stop slave;
- show slave status\G
- save the Master_Log_File and Read_Master_Log_Pos values.
If you have innodb, you'll need to stop mysql.
rsync -e ssh -avz /mysqldatadir root@slave2:/mysqldatadir
scp /mysqllogdir/innologs/* root@slave2:/mysqllogdir/innologs/
scp /etc/my.cnf root@slave1:/etc

Setting up the slave

At this point, regardless of the number of slaves you have, the process is the same to get it up and running. Nomenclature of slave is the same as slave1 for a single slave config, or slave2 if you have multiple.

On slave

Make sure mysql is stopped
cd /mysqldatadir; rm relay-log.info master.info
edit /etc/my.cnf, update the server-id and anything that is hostname dependent (ie name of logs)
start mysql
mysql -p
- change master to MASTER_HOST='mysqlmaster', MASTER_USER='replication', MASTER_PASSWORD='slavepassword', MASTER_LOG_FILE='mysqlmaster-bin.000096', MASTER_LOG_POS=1011891162;
- show slave status
  - Make sure there are no errors in this output. If there are, check error logs before moving on
- start slave

On master or slave1 if you copied from there

start mysql if you stopped it, or if you locked the tables, unlock them with UNLOCK TABLES;
If copied from a slave, start that slave
Check the output of show processlist\G; on the master and you should see the replication slave in action.

mysql> show processlist;
+-----------+--------------+---------------------+------------------+-------------+--------+----------------------------------------------------------------+------------------+
| Id        | User         | Host                | db               | Command     | Time   | State                                                          | Info             |
+-----------+--------------+---------------------+------------------+-------------+--------+----------------------------------------------------------------+------------------+
| 143806241 | replication  | 10.93.112.58:52883  | NULL             | Binlog Dump | 327161 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             | 
| 149584536 | root         | localhost           | NULL             | Query       |      0 | NULL                                                           | show processlist | 
+-----------+--------------+---------------------+------------------+-------------+--------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

Replication (the long winded version)

Grant slave server replication access

do this on your master: grant replication slave on *.* to 'replication'@10.252.12.115 identified by 'slavepassword';

Copy datafiles & logs over to slave

Copy files to first slave

If you don't have a slave, you'll need to copy over the datafiles & logs from your master. You could use the live backup, but that is terribly slow and I have not been successful in getting it to work. This method does require stopping your master (if you use innodb) but it is fool-proof and quick, depending on your db size.

If your db uses innodb, you will need to stop the mysql server process. I have not found a way around this. If you only use myisam, then you don't need to stop it, but you will need to put your tables in read only
- FLUSH TABLES WITH READ LOCK or, stop the server.
- Get the current master position: show master status; and save this information.
Copy the contents of datadir and logdir to the slave server. I use rsync. Some files can be excluded. The example below is executed on the master, but you could change the arguments around to do it from the slave. Both work.
- rsync -e ssh -avz /mysql_data/ mysqlslaveserver:/mysql_data/
- For the datadir, you won't need the binlogs (eg mysql-master-bin.000001) or the relay logs. Just the innologs. Don't forget to create any log directories you need (error, binlog, relay, etc)
- Double check all permissions after rsync.
Skip the next section since this is your first slave, and continue on with the process

Copy files from slave1 to slave2

This is significantly easier than copying from the master. No need to stop production since you already have a slave from which you can copy data. For this section, slave1 is a slave server that is currently in sync with the master, and slave2 is a new slave you want to set up.

on slave1: log in to mysql as root, and run stop slave
You'll need the current position of the log files (so you know where you are in the sync process later on.) On slave1, run show slave status\G. The relevant information you need is this:

Master_Log_File: mysqlmaster-bin.000096
Read_Master_Log_Pos: 1011891162

If you have innodb tables, you will need to stop the mysql process on slave1. If you don't then you can leave it running. Just don't start the slave. It must remain in this state until the copy to slave2 is complete.
Mysql should not be running on slave2 at this point. If it is, stop it, delete everything in the datadir and remove any binlogs, relaylogs, or innodb log (eg ib_logfile0.)
Now, rsync the datadir & logdirs over. Same as above with the first slave setup, no relay logs or binlogs are needed.
- Once copied over, cd in to the datadir on slave2 and delete the files master.info and relay-log.info.

Configure slave

Grant slave replication permissions (see above)
copy over your my.cnf file from either the master if this is your first slave, or better if you have a slave already, copy it from there. Be sure to rename anything you need to (error log names, binlogs, etc)
Start mysql on the new slave.
Log in and tell the slave to point to the master server. Update the username & password to the values you gave on the master. The values for MASTER_LOG_FILE and MASTER_LOG_POS are from the output of show slave status\G on an existing slave you copied over the datadir from, or from the master if this is your first slave from show master status;
- change master to MASTER_HOST='mysqlmaster', MASTER_USER='replication', MASTER_PASSWORD='slavepassword', MASTER_LOG_FILE='mysqlmaster-bin.000096', MASTER_LOG_POS=1011891162;

Now you should be able to run show slave status\G on the new slave. Things to look at here are:

Slave_IO_Running: No
Slave_SQL_Running: No
Seconds_Behind_Master: 1234

The slave isn't running because we did not start it yet. At this point, if this is your first slave, start t by executing start slave in mysql. If this is not your first slave (ie slave2) then I highly recommend you let slave1 catch up first. Just in case you mess something up, you want to make sure you have one working slave. Start mysql or start the slave on slave1, then start it on slave2 when finished.

At this point, all of your slaves should show this from show slave status\G:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

If you don't see Yes for those, you might have messed up somewhere. Check your error logs & see what went wrong. It does take some time to catch up, so the Seconds_Behind_Master will decrease with time. If you see a yes for the Running status, but you are still not caught up (Seconds_Behind_Master is high and increasing) then check your error logs to see why it can't catch up. Could be you made a mistake, or 3306 isn't open through the firewall , or any other number of reasons.

my.cnf files for master & slaves

Master with at least one slave

Note: I have removed a lot from this. The values in this conf file are required to set up a slave.

[mysqld]
server-id=1

#switch these two if making master (log-bin uncommented for master)
#relay-log=/mysql_logs/relay/dsmysqlvm01-log-bin
#uncomment this if master
log-bin=/mysql_logs/binlog/dsmysqlvm01-bin

Slave #1

[mysqld]
server-id=2

#switch these two if making master (log-bin uncommented for master)
relay-log=/mysql_logs/relay/dsmysqlvm02-log-bin
#log-bin=/mysql_logs/binlog/dsmysqlvm02-bin

Mysql

Contents