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 following from /etc/my.cnf
replicate-do-db=mydatabase
3. Restart MySQL
4. Copy snapshot
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 =
