MySQL Master-slave replication

MySQL master-slave replication

-------------------------------------------------------------

Environment:

Two servers (two instances of a single server)

Main Library: (IP 192.168.1.1 port:3306)

from library: (IP 192.168.1.2 port:3306)

Authorized account:

User:max

Password:123.com

Start thinking:

1. Open the main library binlog log file

2. User authorization (master) created in primary library replication

3. Main library for backup, find backup point

4. Restoring data backed up from the main library to the slave library

5. Configure synchronization parameters (from)

6. Turn on sync from library (from)

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/77/CF/wKioL1Zulqfx9gPaAADSJaLGWFg351.gif "title=" MySQL master-slave. gif "alt=" Wkiol1zulqfx9gpaaadsjalgwfg351.gif "/>

• turn on binlog, set sync parameters (comment out skip-external-locking and bind-address)

= = Main Library = =

# VIM/ETC/MY.CNF

[Mysqld]

Server-id = 1 # host flag, Integer cannot follow the same server
log_bin= Mysql-bin # Make sure this file is writable

1. View the configuration results

# egrep "Log-bin|service-id"/etc/my.cnf

2. See if it takes effect (1). See If there is a log (with mysql-bin and mysql-bin.index)

(2). go see it in MySQL.

> Show variableslike ' Log-bin '

= = from library = =

# VIM/ETC/MY.CNF

[Mysqld]

Server-id = 2 # Don't be like the Lord.

Second, create to synchronize user account (Master)

> Grant replication Slave on * * to ' max ' @ ' 192.168.1.0 ' identified by ' 123.com ';

> Flush Privileges

Third, the main library to backup, find the location point

1. Primary Insecure library lock only readable , view location point

>flush table with read lock

> Show Master Status

2. back up and put the backup point into the backup file

> Mysqldump–u root–p–a–b–events–master-date=2|gzip >/home/hui/backup/bak.sql.gz

3. after the guide, unlock the table, resume the writable

>unlock tables

Iv. restoring data backed up from the main library to the slave library

1. upload the files from the main library to the library

2. Restore

> Mysql–uroot–p

v. Configuring synchronization parameters from the library ( will be placed in master info)

1. Configuration Parameters

# Changmaster to

Master_host = ' 192.168.1.2 ',

master_port = ' 3306',
Master_user= ' Max ',
Master_password= ' 123.com ',

master_log_file= ' mysql-bin.000003 ( location point ) ',

master_log_pos=3323

2. View the contents of the Write

Cat/etc/master.info

Six, open from the library Synchronization (from)

> Start slave

Vii. Verification

> Show Slave status\g

IO and SQL threads are all open

Slave_io_running:yes

Slave_sql_running:yes

the description behind the main library is 0

seconds_bchind_master:0

1.relay_log, where's the record?

# Cat Relay-bin.index

2. Create a library in the main library to see if the library is synchronized and the synchronization is successful !

MySQL Master-slave replication