一、mysql同步原理

1、mysql主库在事务提交时将数据变更作为时间记录到二进制日志（binary log）中；

2、slave IO线程将master的binary log events读写到它的中继（Relay log）；

3、slave SQL进程读取Relay log，将重做记录数据到数据库中。

wKioL1iicdDBewTxAABQB9xF2mw592.jpg

二、主从同步配置

2,1、配置环境

    masterIP:192.168.152.128

    mysql version：5.7.17

    本地多实例配置  端口为3307、3308.

    前期已配置多实例安装，本方案中3307为主mysql。

2.2、配置my.cnf文件

配置如下：

[mysqld_multi]

mysqld=/usr/local/mysql/bin/mysqld_safe

mysqladmin=/usr/local/mysql/bin/mysqladmin

log=/data/mysql_multi.log


[mysqld3307]

basedir = /usr/local/mysql

datadir = /data/3307

port = 3307

server_id = 3307

log-bin=mysql-bin

binlog_format=mixed

socket = /data/3307/mysql.sock

symbolic-links = 0

character_set_server = utf8

pid_file=/data/3307/mysql.pid

log_error=/data/3307/3307.error

read-only=0

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

relay_log=mysql-relay-bin

log-slave-updates=on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


[mysqld3308]

basedir = /usr/local/mysql

datadir = /data/3308

port = 3308

server_id = 3308

log-bin=mysql-bin

binlog_format=mixed

socket = /data/3308/mysql.sock

symbolic-links = 0

character_set_server = utf8

pid_file=/data/3307/mysql.pid

log_error=/data/3308/3308.error

read-only=0

#replicate-do-db=test

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

relay_log=mysql-relay-bin

log-slave-updates=on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


2.3、重启多实例

[root@localhost data]# /etc/init.d/mysqld_multi restart

[root@localhost data]# /etc/init.d/mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

[root@localhost data]# 


2.4、登录3307端口主mysql，并建立账户并授权slave

[root@localhost data]# mysql -u root -h127.0.0.1 -P3307 -plila3307

mysql> GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by 'lilaslave';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> 

查看master状态信息

mysql> show master status

    -> ;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 |      590 |              |                  |                   |

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

1 row in set (0.00 sec)


2.5、从服务器配置

mysql> change master to master_host='127.0.0.1',master_port=3307,master_user='slave',master_password='lilaslave',master_log_file='mysql-bin.000002',master_log_pos=590;

Query OK, 0 rows affected, 2 warnings (0.01 sec)


mysql> start slave; #启动slave

Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G    #查看slave的状态信息

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 127.0.0.1

                  Master_User: slave

                  Master_Port: 3307

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 590

               Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: mysql,information_schema

           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: 590

              Relay_Log_Space: 527

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 3307

                  Master_UUID: 873a805c-f25c-11e6-a012-000c29bccc66

             Master_Info_File: /data/3308/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)


2.6、主服务器测试，创建新的数据库和表，

mysql> create database ceshi_db;

Query OK, 1 row affected (0.06 sec)


mysql> use ceshi_db;

Database changed

mysql> create table home(id int(10) not null,name char(10));

Query OK, 0 rows affected (0.11 sec)


mysql> 


2.7、查看slave服务器是否有ceshi_db数据库和home表，及表结构是否相同

mysql> show databases;

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

| Database           |

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

| information_schema |

| ceshi_db           |

| mysql              |

| performance_schema |

| sys                |

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

5 rows in set (0.06 sec)


mysql> use ceshi_db;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Database changed

mysql> show tables;

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

| Tables_in_ceshi_db |

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

| home               |

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

1 row in set (0.00 sec)


mysql> desc home;

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

| Field | Type     | Null | Key | Default | Extra |

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

| id    | int(10)  | NO   |     | NULL    |       |

| name  | char(10) | YES  |     | NULL    |       |

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

2 rows in set (0.03 sec)


mysql>