MySQL master-slave synchronization deployment Summary

Operating Environment

MASTER:192.168.1.124

From:192.168.1.125

Mysql- 5.0.56 .Tar.gz,CentOS5.2

Database1:/Data/3306/Database2:/Data/3307/

1Modify master databaseMy. cnf

======================================

# Vi/etc/my. cnf

Server-id = 1

Log-bin

======================================

2, Restart the master database to take effect

======================================

#/Data/3306/mysql stop

#/Data/3306/mysql start

======================================

3Log on to the master database

======================================

# Mysql-uroot-p-S/data/3306/mysql. sock

======================================

4Grant the slave database permission account to allow users to access the master databaseLOAD TABLEAndLOAD DATA

========================================================== ======================================

Mysql> grant file, SELECT, REPLICATION SLAVE ON *. * TO repl@192.168.1.125 identified by '20140901 ';

========================================================== ======================================

5, Check whether the creation is successful

========================================================== ======================================

Select user, host from mysql. user;

========================================================== ==========

6, Lock the master database table

======================================

Mysql> flush tables with read lock;

======================================

7Displays master database information

RecordFileAndPosition, Slave database settings will be used

==================================

Mysql> show master status;

==================================

+ --------------- + ---------- + -------------- + ------------------ +

| File | Position | Binlog_do_db | Binlog_ignore_db |

+ --------------- + ---------- + -------------- + ------------------ +

| Mysql-bin.030 | 870279 |

+ --------------- + ---------- + -------------- + ------------------ +

8Open another terminal to log on124, Package the master database for data migration

======================================

# Cd/data/3306

# Tar zcvf data124.tar.gz./data

======================================

Ii. Set slave Database

1, Transfer to the master database package, unpackage

====================================

Log on to the slave Database

# Cd /..

# Rz //SetA.tar.gzUpload

# Tar zxvf a.tar.gz //Extract

====================================

2, Unlock the master database table

========================

Mysql> unlock tables;

========================

3Modify slave DatabaseMy. cnf

================================

# Vi my. cnf

# Slave

Server-id = 2

================================

4Restart slave database to take effect

==================================

#/Data/3306/mysql stop

#/Data/3306/mysql start

==================================

5Verify the connection to the master database

======================================

# Mysql-h192.168.1.124-urepl-p123456

Mysql> show grants for repl@192.168.1.125;

======================================

6, Set synchronization on the slave Database

Set connectionMASTER MASTER_LOG_FILEFor the master databaseFile,MASTER_LOG_POSFor the master databasePosition

==================================

Change master to master_host = '192. 168.1.124 ', master_user = 'repl', master_password = '2016030', master_log_file = 'mysql-bin1_30', master_log_pos = 192;

==================================

7Start the slave Database Service

======================================

Mysql> slave start;

======================================

8Check slave database status

======================================

Show slave status \ G

======================================

If twoYesStarted successfully!

Determine the synchronization status from the database.

Mysql-uroot-p123456-e "show slave status \ G" | sed-n'/Yes/'P | wc-l

Mysql-uroot-p '000000'-S/data/123456/mysql. sock-e "show slave status \ G" | sed-n'/Yes/'P | wc-l

= 2Is normal.

Production example:

LetMysql slaveRecordBinlogMethod

Vi my. cnf

Log-slave-updates

Log-bin =/data/3307/mysql33-bin

Expire_logs_days = 7