simple mysql slave

Simple mysql master->slave replication.

Assumptions:
The mysql/firewalls/etc are already setup, there are no writes going on to master during the setup (or there are provisions to take care of master/slave mismatch, like percona toolkit).

On master:
in /etc/mysql/my.cnf uncomment the following lines :

#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log

And comment the following line:

bind-address            = 127.0.0.1

restart mysql:


service mysql restart

in mysql run following:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'{IP}' IDENTIFIED BY '{PASSWORD}';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

Where {IP} is the IP address of the slave and {PASSWORD} is the password of your choosing.
Note: username does not have to be ‘repl’.

The SHOW MASTER STATUS should return the following:

+------------------+----------+---
| File             | Position | 
+------------------+----------+---
| mysql-bin.000001 |      100 |
+------------------+----------+---

Record two this: File and Position (this will be used when setting up the slave).

On slave:
Dump the database(s) in question and import it into new slave, eg (for each database):

mysql -e "CREATE DATABSE {DB}"
mysqldump -h{MASTER_IP} -u{MASTER_USER} -p{MASTER_PASSWORD} {DB} | mysql {DB}

assuming the root user has no password locally on slave.

Uncomment and modify the following lines (make sure server-id is unique):

server-id              = 2
log_bin                        = /var/log/mysql/mysql-bin.log

And comment the following line (needed only if you need to access slaves remotely):

bind-address            = 127.0.0.1

restart mysql:

service mysql restart

In mysql, setup replication:

CHANGE MASTER TO MASTER_HOST='{MASTER_IP}',MASTER_USER='repl',MASTER_PASSWORD='{PASSWORD}', MASTER_LOG_FILE='{FILE_FROM_MASTER_STATUS}', MASTER_LOG_POS={POSITION_FROM_MASTER_STATUS};
START SLAVE;

Note: MASTER_LOG_POS is unquoted integer.

Check if replication is running:

SHOW SLAVE STATUS \G

Leave a Reply

Your email address will not be published. Required fields are marked *