A MySQL master-slave replication configuration case

Environment-based library is 111.111.111.110 from library to 111.111.111.111

1. Main Library CREATE database replication dedicated user

-- executed on the main library, Create user Repl

MySQL – Uroot

Grant replication Slave,replication Client on * * to [E- Mail protected]' 111.111.111.% ' identified by ' repl ';

Exit

-- Modifying a configuration file/etc/my.cnf

Cp/etc/my.cnf/etc/my.cnf.bak

Vi/etc/my.cnf

Modify the main library parameters as follows

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

Log-bin=mysql-bin

Old_passwords=1

server_id = 110

Sync_binlog = 1

Relay_log =/var/lib/mysql/mysql-relay-bin

# Enable Log_slave_updates

Log_slave_updates = 1

Skip_slave_start

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

2. Close the database

-- executing on the main library

Service Mysqld Stop

3. back up the main library

-- executing on the main library

Tar Zcvf/tmp/mysql.tar.gz/var/lib/mysql

4. Transfer the backup of the main library to the slave library

-- executing on the main library

RSYNC-AUZVP--bwlimit=10240/tmp/mysql.tar.gz 111.111.111.111:/tmp/mysql.tar.gz

rsync – AUZVP--bwlimit=1024/etc/my.cnf 111.111.111.111:/etc/my.cnf

5. Restore the backup of the main library in the standby library

-- in the Standby library execution

Mv/var/lib/mysql/var/lib/mysqlbak

mv/tmp/mysql.tar.gz /var/lib/

Tax xvf/var/lib/mysql.tar.gz

6. Modify the configuration file in the standby library/etc/my.cnf

-- in the Standby library execution

-- Modifying a configuration file/etc/my.cnf

Vi/etc/my.cnf

Modify the parameters as follows

[Mysqld]

SERVER_ID = 111

7. Start the database in the standby library

-- in the Standby library execution

Service mysqld Start

8. Start the database on the main library

Service mysqld Start

MySQL – Uroot – P

Show master status;

9. based on the bin log file name and positionobtained in the previous step, enable replication in the standby library

-- in the repository execution (the command master_log_file= ' mysql-bin.0000099 ', the values in Master_log_pos=5 mysql-bin.0000099 and 0 are modified to the value obtained in the previous step)

Change Master to master_host= ' 111.111.111.110 ',

Master_user= ' Repl ',

Master_password= ' Repl ',

master_log_file= ' mysql-bin.00000xx ',

master_log_pos=0;

-- start the replication process

Start slave;

Show Slave Status\g

Show Processlist\g

This article from "Oracle/db2/mysql" blog, declined reprint!

A MySQL master-slave replication configuration case