How to establish Master-slave replication in MySQL

# # 1 Overview # #

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

# # 2 Replication solved the problem # #

MySQL replication technology has some of the following features:

(1) Data distribution (distribution)
(2) Load balancing (load Balancing)
(3) Backup (Backups)
(4) High-availability and fault-tolerant lines of availability and failover

# # 3 Principles of Replication # #

(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the events in the relay log and apply the changes to your data.

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.

The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

# # 4 Configuration and Environment Preparation # #

There are two MySQL database server master and slave,master primary server, slave from the server, the initial state, master and slave in the same data, when the data in master changes, slave also followed the corresponding changes, The data information of master and slave is synchronized to achieve the purpose of backup.

Environment preparation (same master and slave environment)
Os:centos 6.6
Packages:mysql Mysql-server

# # 5 Software Installation # #
Both are installed MySQL Mysql-server

[Email protected] ~]# yum-y install MySQL Mysql-server

[Email protected] ~]# yum-y install MySQL Mysql-server

# # 6 Database Configuration # #

Modify the database configuration file my.cnf
Turn on logging binary log Files Log-bin and Server-id (two machines Server-id not the same)

[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
Log-bin=mysql-bin
server-id=2
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

Start MySQL login into the database

[[email protected] ~]#/etc/init.d/mysqld start

[Email protected] ~]# mysql-uroot-p
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server Version:5.1.73-log Source Distribution
Copyright (c) and/or, Oracle, its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

Setting up database authorization on Master

mysql> grant replication Slave on * * to ' root ' @ ' Slave-ip ' identified by ' password ';
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000003 |              472 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)


Do the following on the slave:

mysql> Change Master to master_host= "Master-ip", master_user= "root", master_password= "password", master_log_file= " Mysql-bin.000003 ", master_log_pos=472;
Query OK, 0 rows affected (0.02 sec)

mysql> slave start;
Query OK, 0 rows Affected (0.00 sec)

Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:connecting to Master
Master_host: *.*.*.*
Master_user:root
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:472
relay_log_file:mysql-relay-bin.000001
Relay_log_pos:4
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:


Note: Slave_io_running:yes Slave_sql_running:yes
This two is yes and the master-slave synchronization is successful.

How to establish Master-slave replication in MySQL