Mysql

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.

InnoDB

Won't start

• Delete ib_logfile* logs, restart

Info

Create a simple InnoDB table

• CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

InnoDB Status

• show engine innodb status\G;