Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.5Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Setting Up Binary Log File Position Based Replication

18.1.2 Setting Up Binary Log File Position Based Replication

This section describes how to set up a MySQL server to use binary log file position based replication. There are a number of different methods for setting up replication, and the exact method to use depends on how you are setting up replication, and whether you already have data within your master database.

There are some generic tasks that are common to all setups:


Certain steps within the setup process require the SUPER privilege. If you do not have this privilege, it might not be possible to enable replication.

After configuring the basic options, select your scenario:

Before administering MySQL replication servers, read this entire chapter and try all statements mentioned in Section 14.4.1, “SQL Statements for Controlling Master Servers”, and Section 14.4.2, “SQL Statements for Controlling Slave Servers”. Also familiarize yourself with the replication startup options described in Section 18.1.6, “Replication and Binary Logging Options and Variables”.

User Comments
  Posted by Fred Mitchell on July 12, 2006
If you are setting up replication through an SSH channel, be sure to specify "" as the address of the database, not "localhost", as MySQL will use "localhost" as a trigger to use a pipe for the connection instead of TCP/IP, and thus will fail.

  Posted by Michel Vansaingele on December 18, 2006
For Debian users : after copying all mysql data files from master to slave you can get the following error message :
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
* Which means that Mysql password for user 'debian-sys-maint' isn't the same as in file /etc/mysql/debian.cnf.
* Because this password is randomly generated, they are different for each installation, and we have to 'synchronize' this password between debian.cnf file and Mysql privileges.
1 - On the master, get a copy of the password string in /etc/mysql/debian.cnf
2 - On the slave, stop the Mysql server, edit /etc/mysql/debian.cnf and replace the password by the one of the master. Start the slave server.

If you want to change it
1 - Launch a Mysql client and select 'mysql' database
2 - run this : UPDATE `user` SET `Password` = password('[password]') where user='debian-sys-maint'
(the password seems to be encrypted but is not)
3 - stop the Mysql server (you get an error for 'debian-sys-maint')
4 - change and put same [password] in /etc/mysql/debian.cnf file
  Posted by Coen Spoor on February 19, 2007
For people getting:
"ERROR 1218 (08S01): Error connecting to master: Lost connection to MySQL server during query"
Make sure that ,on you're master replication server, you change the following line in your my.cnf:




Or change the address to your FQDN or ip-addres
  Posted by Jarrod Lowe on April 25, 2007
It is possible to sync a database to a master while the master is in active use. Commands starting "M:" are run on the master, "S:" are run in the slave. Until the end, the slave state is undefined.

This works for InnoDB tables, if you have set transactionality to REPEATABLE-READ.

M: begin;
M: flush tables with read lock;
M: show master status;
M: show databases;
M: (for each db:)
M:__ show tables in $db;
M:__ (for each table:)
M:____ select 1 from $db.$table limit 1;
M: unlock tables;
M: set time_zone = '+00:00';
S: set foreign_key_checks = 0;
S: stop slave io_thread;
S: stop slave;
S: reset slave;
S: reset master;
S: set time_zone = '+00:00';
_: (for each database:)
S:__ show databases like '$db';
_:__ (if it does not exist:)
S:____ create database $db;
_:__ (for each table:)
M:____ show create table $db.$table;
S:____ show create table $db.$table;
_:____ (if different:)
S:______ drop table $db.$table;
_:____ (if different or not on slave:)
S:______ create table $db.$table .... ;
S:____ delete from $db.$table;
M:____ select * from $db.$table;
S:____ insert into $db.$table values ([...from above...]);
S:__ show tables in $db;
_:__ (for each table that shouldn't be there:)
S:____ drop table $db.$table;
S: show databases;
_: (for each db that shouldn't be there:)
S:__ drop database $db;
S: change master to [...details from show master above...];
S: start slave io_thread;
S: start slave;
M: rollback;

(Urghh... I see no way to embed code into these posts. Hence the ugly "____" thing to indicate indentation. I suggest copying into something with a non-proportinal font, to make it easier to read)
  Posted by Jay Long on May 28, 2007
When setting up replication from a Windows installation and replicated to a Unix installation of 5.0.x; Remember that the database tables are Case Sensitive under the Unix variant (See 9.2.2. Identifier Case Sensitivity). So if your application does not take this into effect, sql commands work fine with the Windows copy, but break when ran against the backup/replicated server, or worse, the replication breaks due to the fact the Unix server can't find the table.

Be Safe, target your SQL for the Unix standard of "LowerCase" and you will be a happy developer/administrator!
  Posted by Sean Yap on August 1, 2007
These are the configuration i had try out:

MySQL Failover Circular Replication
Assume we have 2 servers: Server1 and Server2.

Server1 Settings
1. Put the option file my.cnf to Server1 path /etc with these settings:
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1

2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf

3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start

4. Configure the server:

# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root

create user replicant@'%' identified by 'password';

# Grant access rights:
Flush Privileges;

# Specify the info for the serve2:

# Start the listerner:
Start slave;

# Verify whether the replication is working:
show slave status\G

Server2 Settings
1. Put the option file my.cnf on to Server2 path /etc
with these settings:
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2

2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf

3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start

4. Configure the server:

# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root

create user replicant@'%' identified by 'password';

# Grant access rights:
Flush Privileges;

# Specify the info for the serve1:

# Example:
# MASTER_USER='replicant', MASTER_PASSWORD='password';

# Load data from Server1:
Load Data from Master;

# Start the listerner:
Start slave;

  Posted by Nils Hammar on August 24, 2007
It is worth to mention that "SELECT ... FOR UPDATE" doesn't distribute the locks to all slaves, which creates room for "funny" results.

What will happen in a two-node solution with circular replication as described at is that the update is copied to the other node while the local update is overwritten.

This is a flaw that has to be considered, and the right way to resolve this is probably through the MySQL Cluster.

  Posted by Nick Glencross on December 5, 2007
If you are using SSL and find that you can connect using the command line, yet get 'Access Denied' in the replication logs, check the permissions on your certificates! Mine turned out to be readable by root, but not by the mysql user that the replication runs as.

  Posted by Andy Grove on September 14, 2009
Here is a video tutorial for setting up replication that shows what happens in the event of the master server failing
  Posted by Pierre Chambon on March 31, 2010
Here's a step by step simple quick and dirty tutorial on how to setup replication in just 5 mysql command lines and editing of the /etc/mysql/my.cnf file on both the master and the slave.
Basically you just need to setup the server id, create a user with the right privilege and start the replication:

The doc was confusing to me and not in chronological order.
  Posted by Mukesh Vishwakarma on February 23, 2012
  Posted by ALAA ALOMARI on March 19, 2012
a step by step for creating replication with examples is listed at
  Posted by Lasantha Aberathna on September 13, 2012
If you are looking for step by step explanation about replication setup. Please follow

Sign Up Login You must be logged in to post a comment.