Mysql master slave

From FoppaWiki

MySQL Master-Slave replication on Centos / RHEL - howto made on Centos5.5 and MySQL 5.0.77.

In this setup transactions are mirrored from the master MySQL to the slave, and the tutorial will work with the following:

NOTE: This is an idle mysql, if you are writing to the master, you have to flush with read lock in order to keep the position for the replication.

Master Server: 10.0.0.10
Slave Server: 10.0.0.15
MySQL Data path: /var/lib/mysql
MySQL slave user named slave_user

Contents 1 Master config 2 Slave config 3 MySQL MASTER 4 MySQL SLAVE 5 Kill MySQL slave

Master config

First, add/replace the following lines in the mysql config:

nano /etc/my.cnf
-------------------------------
# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
# Stop Modification

and then restart the mysql server:

/etc/init.d/mysqld restart

Slave config

nano /etc/my.cnf
-------------------------------
# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/var/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
# Stop Modification

and restart the mysql:

/etc/init.d/mysqld restart

MySQL MASTER

First we need to make the replication user:

#mysql -u root -p
mysql> STOP SLAVE;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;

Then we check if its working, and we also need the location from where we will start the replication:

mysql> USE Any_database_name;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   451228 |              |                  |
+------------------+----------+--------------+------------------+
1 row IN SET (0.00 sec)

write down the file and position number, we need that info to set up the slave. Now dump your database and move it to the slave:

mysqldump -u root -p --all-databases --single-transaction --master-data=1 > /root/MasterSnapshot.sql
scp /root/MasterSnapshot.sql root@10.0.0.15:/root/

MySQL SLAVE

Import the databasedump on the slave MySQL:

mysql -u root -p < /root/MasterSnapshot.sql

Now we set the slave to read from the master server, starting at the position we wrote down:

mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.10', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

The status output on the slave should have following:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
<snip>
Seconds_Behind_Master: 0

Kill MySQL slave

If you had your MySQL setup as slave and need to start over or just remove the slave config all you have to do is:

STOP SLAVE; CHANGE MASTER TO MASTER_HOST=''; RESET SLAVE;