Debian Tutorials

Debian Tutorials


Step by step tutorials showing you how to install and configure various applications and services on Debian based Linux distros.

May 2018
M T W T F S S
« Apr    
 123456
78910111213
14151617181920
21222324252627
28293031  

Categories


MySql database replication (master/slave)

Ástþór IPÁstþór IP

In this tutorial we’ll create a simple one-way master/slave database replication. You must have at least one master and one slave but you can use multiple slaves.

Master

1. Configure master to listen on all ip addresses (pico /etc/mysql/my.cnf)

#bind-address = 127.0.0.1

Comment out this line or remove it

2. Configure server id, log file location and which databases are allowed to be replicated (pico /etc/mysql/my.cnf)

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = {database}

Replace {database} with the one you would like to replicate

3. Restart MySql

/etc/init.d/mysql restart

4. Create a user and allow it to act as slave for this server (mysql -u root -p)

GRANT REPLICATION SLAVE ON *.* TO {username}@'{ip}' IDENTIFIED BY '{password}';
FLUSH PRIVILEGES;

{username} = Your preferred username
{password} = Your password
{ip} = IP address of the slave system or % to allow all ip addresses

5. Show current log file and position (mysql -u root -p)

SHOW MASTER STATUS;

This will return something like this:

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 2751 | {database} | |
+------------------+----------+--------------+------------------+

Keep the file name and position. It will be used later on the slave

6. Transfer data from the master to the slave

You can do this using various methods including exporting and importing using phpMyAdmin, creating a database dump from the master and import to the slave and “LOAD DATA FROM MASTER”.

Slave

1. Configure this server to be a slave for the master MySql server (pico /etc/mysql/my.cnf)

server-id = 2
master-host = {master_ip}
master-user = {username}
master-password = {password}
master-connect-retry = 60
replicate-do-db = {database}

{master_ip} = The ip of the master server
{username} = The username you provided earlier on the master server
{password} = The password you provided earlier on the master server
{database} = The database you want to replicate

2. Restart MySql

/etc/init.d/mysql restart

3. Final configurations to make the slave replicate with the master (mysql -u root -p)

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='{master_ip}', MASTER_USER='{username}', MASTER_PASSWORD='{password}', MASTER_LOG_FILE='{log_file}', MASTER_LOG_POS={log_position};
SLAVE START;

{master_ip} = The ip of the master server
{username} = The username you provided earlier on the master server
{password} = The password you provided earlier on the master server
{log_file} = Log file name from the master (ex. mysql-bin.000004)
{log_position} = Log position from the master (ex. 2751)

Comments 4
  • shiv
    Posted on

    shiv shiv

    Author

    iam using centos i applied ur step…but how we know it working or not…


  • shiv
    Posted on

    shiv shiv

    Author

    iam in confused to run command for slave in which ip…
    iam having 2pc .i configure pc1 as master and pc2 as slave.and for master is configured in pc1 and for slave in pc2.but still not working….plz clear


  • leoboiko
    Posted on

    leoboiko leoboiko

    Author

    I found the hard way that replication auth will fail if the password is too long (with no information in the logs either).

    Also, I had to grant RELOAD and SUPER in order to LOAD DATA FROM MASTER to work.


  • Tix
    Posted on

    Tix Tix

    Author

    Hello,

    Thanks for tuto but how test ?
    At the end of tuto i’ve not replication..