Basic steps for configuring MySQL replication

The name of MySQL master-slave replication is actually: Copy data from one server to another server and execute it again. One server is treated as a primary server (master) and one or more servers as a Slave server (SLave) during the replication process. The primary server writes updates to the binary log file and maintains an index to track the log loop. These logs can record updates that are sent to the server, and when a connection is made from the server to the primary server, it notifies the primary server of the location of the last successful update read from the server in the log, receives any updates from the server since then, and then blocks and waits for the primary server to notify the new update.

It is divided into three steps as a whole:

1.master will change the record to log file (binary log)

2.slave Copy binary log events (binary log event) to the trunk log (relay log)

3.slave redo the relay log to transform the change into its own data

650) this.width=650; "src=" Http://hi.csdn.net/attachment/201202/28/0_1330439010P7lI.gif "alt=" 0_ 1330439010p7li.gif "/>

Environment:

System: centos6.3

master:10.10.0.224

slave:10.10.0.226

1. Create a backup account, only allow permissions from the server

Mysql> Grant all privileges on * * to [e-mail protected] identified by ' 123456 ';

mysql> flush Privileges;

2. Copying data

Stop the MySQL service or lock table, copy the data from master to slave, ensure the consistency of the data on both sides, and ensure that the write operation is forbidden in master and slave until the data synchronization is complete!

Mysql> flush tables with read lock; #锁表

mysql> unlock tables; #解锁

3. Configure Master

# VIM/ETC/MY.CNF

#选择唯一的server-id

Server-id = (0--2^32-1)

#启动二进制日志

Log-bin = Mysql-bin

Log-bin-index = Mysql-bin.index

# Service Mysqld Restart

# MySQL

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|  mysql-bin.000004 |              3318591 |                  | |

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

1 row in Set (0.00 sec)

4. Configure slave

# VIM/ETC/MY.CNF

server_id = 2

Log-bin = Mysql-bin

Log-bin-index = Mysql-bin.index

Relay-log = Relay-log

Relay-log-index = Relay-log.index

5. Connect to the master server and start copying data

mysql> stop Slave;

mysql> Change Master to master_host= ' 10.10.0.224 ', master_port= ' 3306 ' master_user= ' backup ',

Master_password= ' 123456 ', master_log_file= ' mysql-bin.000005 ', master_log_pos=360;

mysql> start slave;

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.10.0.224

Master_user:backup

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000005

read_master_log_pos:360

relay_log_file:relay-log.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000005

Slave_io_running:yes

Slave_sql_running:yes

1. Row ***************************

Show Processlist\g; can view i/0 thread status

This article is from the "ngames" blog, make sure to keep this source http://ngames.blog.51cto.com/3187187/1655959

Basic steps for configuring MySQL replication