MySQL Master-slave replication principle and construction

I. Working process of master-slave replication:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/94/BF/wKiom1kNSECCsVYlAACo-eVyDvs057.png "title=" Qq20170506115112.png "alt=" Wkiom1knseccsvylaaco-evydvs057.png "width=" "height=" "border=" 0 "hspace=" 0 "vspace = "0" style= "width:500px;height:350px;"/>

Two. mysql Replication type

1. SQL statement-based replication

2. Row-based replication

3. Hybrid replication

Three. Experimental environment

Os:centos 6.5 x64

master:192.168.0.134

slave:192.168.0.135

Three. Configuring Master-slave replication

1. Configure Time synchronization

Master: Configured as a time server

[[email protected] ~]# Yum install NTP edit/etc/ntp.conf Add the following two lines: Server 127.127.1.0fudge 127.127.1.0 stratm 8service ntpd STA Rt

Slave: Synchronizing Master Time

[[email protected] ~]# yum install ntpdate[[email protected] ~]# ntpdate 192.168.0.134 6 May 06:37:58 ntpdate[6653]: ad Just time Server 192.168.0.134 offset-0.469705 sec

2. Install MySQL

Slave and Master:

[[email protected] ~]# Yum install mysql-server mysql-y[[email protected] ~]#/etc/rc.d/init.d/mysqld start[[email Prot Ected] ~]# chkconfig mysqld on[[email protected] ~]# mysqladmin-u root password "123.com"

3. Edit the configuration file

Master

Edit/etc/my.conf Add the following lines server-id=134 #设置id, master and slave different log-bin=master-bin #开启二进制日志log-slave-update=true restart MySQL service [email protected]~]# Service mysqld Restart

Slave

Edit/etc/my.conf Add the following lines Server-id=135relay-log=relay-log-binrelay-log-index=slave-relay-bin.indexread-only=1 # Here you can set MySQL to read-only, without root restart MySQL service [[email protected] ~]# service mysqld restart

3. Log in to MySQL and give slave authorization

Master

mysql> grant replication slave on *.* to  ' slave ' @ ' 192.168.0.% '   identified by  ' 123456 '; 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 |+-------------------+----------+--------------+------------------+|  master-bin.000001 |      181 |               |                   |+-------------------+----------+--------------+-------------- ----+1 row in set  (0.00 SEC) 

# File: Log name Position: Offset

4. Log in to MySQL, configure synchronization

Slave

Mysql> change master to master_host= ' 192.168.0.134 ', master_user= ' slave ', Master_password = ' 123456 ', master_log_file= ' master-bin.000001 ', master_log_pos=181; query ok, 0 rows affected  (0.12 sec) mysql> start slave; query ok, 0 rows affected  (0.00 sec) mysql> show slave status  \G*************************** 1. row ***************************                Slave_IO_State: Waiting for  master to send event                   Master_Host: 192.168.0.134                   Master_User: slave                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: master-bin.000001           Read_Master_Log_Pos: 181                Relay_Log_File: relay-log-bin.000002                 Relay_Log_Pos: 252         Relay_Master_Log_File: master-bin.000001              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               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: 181               Relay_Log_Space: 405               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:  0master_ssl_verify_server_cert: no                last_io_errno:  0                last_io _error:                last_ sql_errno: 0                last_sql_error: 1 row in set  (0.00 SEC)

#查看同步状态Slave_IO和Slave_SQL是YES说明主从同步成功.

Four. Testing

1. Create a new database on master

mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  test               |+-------- ------------+3 rows in set  (0.00 sec) mysql> create database guoxh  charset  ' UTF8 '; query ok, 1 row affected  (0.00 sec) mysql> show databases;+--------- -----------+| database           |+--------------- -----+| information_schema | |  guoxh              | |  mysql              | |  test               |+-------------------- +4 rows in set  (0.00 SEC)

2. View the database above slave

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | Guoxh | | MySQL | | Test |+--------------------+4 rows in Set (0.00 sec)

#在slave上面可以看到刚才新建的数据库, the master-slave replication configuration is successful.

This article is from "Big Brother next Door" blog, please be sure to keep this source http://guoxh.blog.51cto.com/10976315/1922643

MySQL Master-slave replication principle and construction