Follow this step by step procedure to replicate a MySQL server (master) to another (slave):

1. CONFIGURE THE MASTER

1.1. Enable binary log. To do this, add in /etc/my.cnf under [mysqld] section:

log-bin=mysql-bin

1.2. Configure a unique server id. Again, add in /etc/my.cnf under [mysqld] section:

server-id=1

1.3. Make sure that the directive skip_networking is not enabled in /etc/my.cnf. If it is, comment it out (or just remove it)

1.4. If bind-address is used in /etc/my.cnf, make sure the IP address specified is either the IP of the slave server OR is set to ‘0.0.0.0’. Otherwise, comment it out (or remove it).

1.5. If InnoDB dataabase with transactions are used, add the following to /etc/my.cnf under [mysqld] section:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

1.6. Restart the MySQL server

2. CREATE A USER FOR REPLICATION

2.1. Open a mysql session in the master server:

# mysql 

2.2. Create a user:

mysql> CREATE USER 'repluser'@'%' IDENTIFIED BY 'replpassword';

2.3. Grant the user just created replication privilege:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

2.4. Exit the mysql session:

mysql> quit

3. CONFIGURE THE SLAVE

3.1. Configure a unique server id (make sure the number you specify is unique). Add in /etc/my.cnf under [mysqld] section:

server-id=2

3.2. Enable binary log (this is not required for slave servers, but it’s useful because it will allow, for example, to promote a slave server to master in case the original master fails). Add in /etc/my.cnf under [mysqld] section:

log-bin=mysql-bin

3.3. Make the slave server visible to the master (not required, but useful). Add in /etc/my.cnf under [mysqld] section:

report-host=<slave>

where <slave> is the name of the slave server

3.4. Define a directory to store temporary replication files. Add in /etc/my.cnf under [mysqld] section:

slave-load-tmpdir=<dir>

where <dir> is the name of the directory

IMPORTANT!:
Don’t use /tmp or any other directory of a memory-based filesystem, it should be in a disk-based filesystem to make sure the contents is not deleted in a reboot, usually /var/tmp is fine.

3.5. Don’t start (or restart) the slave server yet.

4. OBTAIN MASTER BINARY LOG COORDINATES AND COPY EXISTING DATA TO SLAVE

4.1. Start a mysql session to the master:

# mysql

4.2. Lock tables to prevent updates:

mysql> FLUSH TABLES WITH READ LOCK;

4.3. Open another session (don’t close the previous one!) and execute the following (record the values of File and Position columns):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4.4. You can close the session opened in step 4.3. (keep the other one open).

4.5. Create the file dump, executing from a shell:

mysqldump --all-databases --lock-all-tables >dump.sql

4.6. Exit the session opened in step 4.1

mysql> quit

4.7. Start the slave server with the --skip-slave-start option to prevent replication from starting

# mysqld_safe --skip-slave-start &

4.8. Import the dump file created in step 4.5; in the slave server execute:

mysql < dump.sql

5. CONFIGURE REPLICATION COORDINATES IN THE SLAVE SERVER AND START REPLICATION

5.1. Open a mysql session to the slave and execute the following (replace <file> and <position> with the values recorded previously from the 'SHOW MASTER STATUS' command, replace <master_hostname> with the hostname of the master server, make sure the master hostname is valid):

mysql>  CHANGE MASTER TO
    ->     MASTER_HOST='<master_hostname>',
    ->     MASTER_USER='repluser',
    ->     MASTER_PASSWORD='replpassword',
    ->     MASTER_LOG_FILE='<file>',
    ->     MASTER_LOG_POS=<position>;

5.2. Start the slave threads:

mysql> START SLAVE;

5.3. Verify the slave is replicating, from a slave session execute:

mysql> SHOW SLAVE STATUS \G

Verify that Slave_IO_Running is Yes, also check the values of Seconds_Behind_Master and Slave_IO_State.

5.4. Shutdown the server started with the option --skip-slave-start and start it normally

# mysqladmin shutdown
# systemctl start mysql

NOTE

If you need start your slave with a fresh copy of the master database, you will need to issue the commands STOP SLAVE and RESET SLAVE before you do a CHANGE MASTER to give it the new file name and position (and before you restart the slave!) Otherwise, it tries to pick up where it left off, using the old master file and position.

Share!