MySQL Replication

Replicating a master database mydatabase to slave servers, this is compatible with both InnoDB and MyISAM.

Method 1

Master
Open 2 terminals to your database server, in the first one open up mysql. If you already have a running master, skip step 1 & 2.

1. Edit the my.cnf file and set the following...

report-host=mymaster-server
log-bin=/var/lib/mysql/mysqld-bin
server-id=1

2. Restart MySQL

3. In term 1 run

mysql> FLUSH TABLES WITH READ LOCK;

4. In term 2 run

# cd /var/lib/mysql
# tar -cvf /root/snapshot.tar ib* directory1 directory2

5. In term 1 run

mysql> SHOW MASTER STATUS;

Take note of the File and Position

6. In term 1 close the connection to the DB

7. Grant replication to the slave...

mysql> GRANT REPLICATION SLAVE ON *.* 
  TO 'slave1'@'' IDENTIFIED BY 'slavepass1';
mysql> GRANT REPLICATION SLAVE ON *.* 
  TO 'slave2'@'' IDENTIFIED BY 'slavepass2';

Slave
If you already have a running slave, skip step 1 below. If you setting up a slave for the first time skip step 2.

1. Stop MySQL

2. Set the following in /etc/my.cnf

report-host=slave1
server-id=2
relay-log=/var/lib/mysql/mysqld-relay-bin

3. Remove the following from /etc/my.cnf

replicate-do-db=mydatabase

4. Copy over snapshot.tar

# cd /var/lib/mysql
# tar -xvf /root/snapshot.tar

5. Fire up MySQL

6. Log into mysql and run the following...

mysql> CHANGE MASTER TO
  MASTER_HOST='master_host_name',
  MASTER_USER='replication_user_name',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='recorded_log_file_name',
  MASTER_LOG_POS=recorded_log_position;

7. Add the following to my.cnf...

replicate-do-db=mydatabase

8. Restart MySQL

9. Run the following comman to get status...

mysql> SHOW SLAVE STATUS;

Method 2

Master
Skip step 1 & 2 if you already have a running master.

1. Edit the my.cnf file and set the following...

report-host=mymaster-server
log-bin=/var/lib/mysql/mysqld-bin
server-id=1

2. Restart MySQL

3. Dump the MySQL databases you need

# mysqldump -p --master-data --all-databases --single-transaction --skip-extended-insert > snapshot.mysql

4. Grant replication to the slave...

mysql> GRANT REPLICATION SLAVE ON *.* 
  TO 'slave1'@'' IDENTIFIED BY 'slavepass1';
mysql> GRANT REPLICATION SLAVE ON *.* 
  TO 'slave2'@'' IDENTIFIED BY 'slavepass2';

Slave
If you already have a running slave, skip step 1 below. If you setting up a slave for the first time skip step 2.

1. Set the following in /etc/my.cnf

report-host=slave1
server-id=2
relay-log=/var/lib/mysql/mysqld-relay-bin

2. Remove the follwoing from /etc/my.cnf

replicate-do-db=mydatabase

3. Restart MySQL

4. Copy snapshot.mysql from the master, look at the top of the file for the below block & edit in, keep the values for MASTER_LOG_FILE and MASTER_LOG_POS and add the rest.

CHANGE MASTER TO
   MASTER_HOST='master_host_name',
   MASTER_USER='replication_user_name',
   MASTER_PASSWORD='replication_password',
   MASTER_LOG_FILE='log_file',
   MASTER_LOG_POS=log_pos;

5. Load the snapshot

# mysql -u root -p < snapshot.mysql

6. Add the following to my.cnf...

replicate-do-db=mydatabase

7. Restart MySQL

8. Log into MySQL and run the following command for status...

mysql> SHOW SLAVE STATUS;

Master+Slave Server

Add the following to the slave server setup /etc/my.cnf...

; We're also a master
log-slave-updates
log-bin=/var/lib/mysql/mysqld-bin

SLAVE PROBLEMS!!!! - HELP

For some reason I've seen SQL queries being corrupt from master to slave. When this happens the SQL thread on the slave stops and the slave falls out of sync.

To fix this, use ....

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> SLAVE START SQL_THREAD;

If the slaves binlog gets corrupted, you can force it to recreate the binlog by resetting the master...

First, show slave status .... WRITE DOWN .... Exec_master_log_pos and Relay_master_log_file. You will need these!

mysql> CHANGE MASTER SET MASTER_LOG_POS = <Exec_master_log_pos>, MASTER_LOG_FILE='<Relay_master_log_file>';

---