We have 2 servers, one is a Master and the other one is a Slave. We tell the Master that it should keep a log of every action performed on it. We tell the slave server that it should look at this log on the Master and whenever something new happens, it should do the same thing.
You should follow the instructions below with two console windows open – one for the Master and one for the Slave
Pre – reqs
1. Servers data:
Hostname Master: node1
IP Master: 127.0.0.1
Hostname Slave: node2
IP Slave: 127.0.0.2
2. Verify that InnoDB is used
3. Verify that the databases are compatible
http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html
4. Modify configuration files
You can modify the my.cnf or create the master.cnf and slave.cnf files (one on each server as appropriate) and the base will be raised with them via command line.
What you can do is incorporate this information into the file my.cnf (The default locations are: /etc/my.cnf o /etc/mysql/my.cnf)
master.cnf:
[mysqld]
server-id=1
log-bin=master-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=mixed
# Information if become a Slave
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
opcional:datadir=/datos/mysql/master/data
slave.cnf:
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# Information if become a Master
log-bin=slave-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=ROW
opcional: datadir=/data/mysql/slave/data
NOTE: Search and delete if there is a previous version of one of these lines, for example server-id
NOTE2: In case of using a new directory, check the permissions
service mysql stop
chown -R mysql:mysql /path/of/bin-log
service mysql start
5. Create users
We need to create a user on the Master and slave. I call mine ‘repl_user’. Log into mysql as root and create the user:
CREATE USER repl_user@(ip slave);
GRANT REPLICATION SLAVE ON *.* TO repl_user@(ip slave) IDENTIFIED BY ‘password’;
CREATE USER repl_user@(ip master);
GRANT REPLICATION SLAVE ON *.* TO repl_user@(ip master) IDENTIFIED BY ‘password’;
On the Master:
mysql -u root –prompt=’master> ‘
CREATE USER repl_user@127.0.0.1;
GRANT REPLICATION SLAVE ON *.* TO repl_user@127.0.0.1 IDENTIFIED BY ‘replica’;
On the Slave:
mysql -u root –prompt=’slave> ‘
CREATE USER repl_user@127.0.0.2;
GRANT REPLICATION SLAVE ON *.* TO repl_user@127.0.0.2 IDENTIFIED BY ‘replica’;
6. Lock master and take note of binlog position
Verify what binlog format is in use:
show variables like ‘binlog_format’;
IMPORTANT: If it is not in ROW or MIXED, set it before continuing.
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;
And modify the parameter in the configuration file so that it takes when it restart
binlog_format=mixed
FLUSH TABLES WITH READ LOCK;
Execute:
master> SHOW MASTER STATUS;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000001 | 4408 | | |
+——————-+———-+————–+——————+
We note both results.
IMPORTANT: DO NOT CLOSE THE WINDOW WHERE THE LOCK IS ESTABLISHED BECAUSE IT WILL LOST.
IMPORTANT: Work from another terminal
#################################################################################################
If return:
Empty set (0.00 sec)
check that it are using the binary log:
master> show binary logs;
if error:
ERROR 1381 (HY000): You are not using binary logging.
We control that the my.cnf is well configured or that it is not an error of permissions.
Note: If the master has been running previously without binary logging enabled, the log name and
position values displayed by SHOW MASTER STATUS will be empty. If this is the case, the values
that you need to use later when specifying the slave’s log file and position are the empty string (”) and 4
7: Export data
Work from another terminal in master server:
mkdir /BACKUPS
chown -R mysql:mysql /BACKUPS
mysqldump -u root base_replica > /BACKUPS/base_replica_master.sql -p
unlock server:
master> UNLOCK TABLES;
8: Copy backup and import data
master> scp /BACKUPS/base_replica_master.sql root@[IP_SLAVE]:/BACKUPS
On the Slave:
[root@slave ~]# mysql -u root -e ‘create database base_replica;’ -p
[root@slave ~]# mysql -u root base_replica < /BACKUPS/base_replica_master.sql –p
9: Start the replication
slave> SLAVE STOP;
slave> CHANGE MASTER TO MASTER_HOST=’127.0.0.1′, MASTER_USER=’repl_user’, MASTER_PASSWORD=’replica’, MASTER_LOG_FILE=’master-bin.000001′, MASTER_LOG_POS=4408;
where:
MASTER_HOST: ip master server, en este caso 127.0.0.1
MASTER_USER: user créate for the replica, “repl_user”
MASTER_PASSWORD: user passwd
MASTER_LOG_FILE: file name created in the step 6
MASTER_LOG_POS: position in the step 6
Start the replica
slave> START SLAVE;
Check status:
slave> SHOW SLAVE STATUSG
In case you do not replicate, verify the error there. A common error can occur with the firewall and communication ports.
10: Start semi synchronous replication
The normal replica must be working.
master> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
master> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
master> SET GLOBAL rpl_semi_sync_master_enabled = on;
slave> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = on;
slave> STOP SLAVE IO_THREAD; START SLAVE;
Post-Configuration
SHOW STATUS LIKE ‘Rpl_semi_sync%’;
SHOW STATUS LIKE ‘Rpl_semi_sync_master_status’;
SHOW STATUS LIKE ‘Rpl_semi_sync_master_clients’;
Finally, we leave it fixed in the startup, in the my.cnf file.
IMPORTANT: In case we perform a failover, these parameters we change.
Master:
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
Slave:
[mysqld]
rpl_semi_sync_slave_enabled=1
