MySql: Configure replication


Okay. This is not the unique article about MySql replication. There are tons of manuals like this in the internet. I need it it for I shouldn’t have to look for the tutorial every time I need it.

First of all update the /etc/hosts files with the following entries on all servers:

mysql.master 192.168.10.10
mysql.slave 192.168.10.20

Next we need to update the mysql configuration files (/etc/my.cnf or /etc/mysql/my.cnf depending on the OS)

On Master server:

[mysqld]
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1 
sync_binlog=1

Restart mysqld to apply changes:

/etc/init.d/mysqld restart

Launch mysql console:

mysql -u root -p

Create replication user:

GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@’%’ IDENTIFIED BY ‘password‘;

Replication in mysql is based on the data from so-called “binary log” (binlog). It contains the history of all
transactions. The slave server simply duplicates all actions that caused the database(s) updates on the master server, according to the binlog entries.

Delayed_Replication1

Run the following to get the state of master server and binlog position:

mysql> show master status;

You should receive something like the following:

+---------------+-----------+--------------+------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+-----------+--------------+------------------+
| binlog.000005 | 849349769 |              |                  | 
+---------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

We need the value of “Position”. This will be the replication start point on the slave server.
Нужно его запомнить/скопировать/записать. Именно с этого места начнется “дублирование” на новом сервере.

Next copy all databases from master to the slave server.

On Slave server update MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf):

[mysqld]
server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1 
sync_binlog=1

Restart mysqld to apply changes:

/etc/init.d/mysqld restart

Launch mysql console:

mysql -u root -p

All databases should have been transferred to this server already. Run the following in order to enable the slave role:

CHANGE MASTER TO 
MASTER_HOST='mysql.master', 
MASTER_USER='repl', 
MASTER_PASSWORD='password', 
MASTER_LOG_FILE='binlog.000005', 
MASTER_LOG_POS=849349769;

Now you can start slave role:

START SLAVE;

Check slave status:

show slave status \G;

You need to find the following lines in the output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If any of them is not “yes” then you must have missed something or something went wrong. Read the mysql logs to determine the root cause of this.

Share Button

Leave a Reply

You must be logged in to post a comment.