MySQL Master-slave replication

########### #mysql主从复制 ##########

Lab Environment:
Redhat6.5 # #主从服务器版本一致
ip:172.25.8.1 Master # #主服务器或称主库
ip:172.25.8.2 Slave # #从服务器或称从库 (can be multiple)

Experimental content:
1. Modify Master master server
[Mysqld] # #在 [mysqld] module add-in
Log-bin=mysql-bin # #启用二进制日志
Server-id=1 # #服务器唯一ID, general use IP last paragraph, Master here is 1

2. Modify the slave from the server
[Mysqld] # #在 [mysqld] module add-in
#log-bin=mysql-bin # #从库一般不设置, if there is a-->b-->c Cascade synchronization, the middle of the B database service to open Log-bin
server-id=2 # #服务器唯一ID, generally use IP last paragraph, slave here is 2

3. Restart the master-slave server for MySQL
/etc/init.d/mysql restart

4. Establish account and authorization slave on the master server
mysql> grant replication Slave on * * to ' rep ' @ ' 172.25.8.% ' identified by ' hjy123456 '
# #设置复制用户rep, "%" means any segment, password hjy123456
Mysql> Show variables like ' server_id '; # #查看系统变量及其值
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| server_id |    1 | # #server-id=1
+---------------+-------+
1 row in Set (0.00 sec)
Mysql> select User,host from Mysql.user;
+-------+------------+
| user | Host |
+-------+------------+
| Root | 127.0.0.1 |
| Rep |        172.25.8.% | # #rep用户, 172.25.8.%
| Root | localhost |
| User1 | localhost |
+-------+------------+
Mysql> Show master status; # #显示记录在案的信息, the master-slave copy will start here (259)
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              259 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
"After performing this step do not re-operate master MySQL to prevent position changes"

5. Configure the connection master server from the server
mysql> Change Master to master_user= ' rep ', master_host= ' 172.25.8.1 ', master_port= ' 3306 ', master_password= ' hjy123456 ', master_log_file= ' mysql-bin.000001 ', master_log_pos=259;
# #rep是主服务器上建立的用于复制的用户, 172.25.8.1 is the primary server ip,3306 is the main library port, password is the rep user's password, MYSQL-BIN.000001 is the binary log file name that you just checked, 259 is the binary log offset that you just checked, and the copy read location
Mysql> show Slave status\g; # #查看复制状态
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.25.8.1 # #主库地址
Master_user:rep # #授权账户
master_port:3306 # #主库端口
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:259 # #二进制日志偏移量
relay_log_file:mysqld-relay-bin.000002
relay_log_pos:251
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes # #IO线程负责从从库到主库读取log-bin log, this state must be Yes
Slave_sql_running:yes # #SOL线程负责读取中继日志 (relay-log) data conversion to SQL statement applied to from library, this state must be yes "can use monitoring software, monitor IO and SQL thread, send alert when no" replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:259
relay_log_space:407
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0 # #主从复制过, number of seconds to delay from Cubby Main Library
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:

6. Test Master-slave replication
1) The main library establishes the REP_DB database and inserts the data
mysql> CREATE DATABASE rep_db; # #创建rep_db数据库
mysql> use rep_db; # #进入rep_db库
Mysql> CREATE TABLE REP_TB (ID varchar (), name varchar (20)); # # #创建rep_tb表
mysql> desc REP_TB; # #查看rep_tb表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | varchar (10) |     YES | |       NULL | |
| name | varchar (20) |     YES | |       NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> INSERT INTO REP_TB values (001, ' Tom '); # #插入以一条数据
Mysql> select * from REP_TB; # #查看数据
+------+------+
| ID | name |
+------+------+
| 1 | Tom |
+------+------+

2) View from library
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| rep_db |
+--------------------+
3 Rows in Set (0.00 sec)
Mysql> select * from REP_DB.REP_TB;
+------+------+
| ID | name |
+------+------+
| 1 | Tom |
+------+------+

This article from the "12148275" blog, reproduced please contact the author!

MySQL Master-slave replication