mysql5.7.17主从同步配置

uytyrt

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

2、slave IO线程将master的binary log events读写到它的中继（Relay log）；
3、slave SQL进程读取Relay log，将重做记录数据到数据库中。

二、主从同步配置
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、重启多实例
[iyunv@localhost data]# /etc/init.d/mysqld_multi restart
[iyunv@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
[iyunv@localhost data]#

2.4、登录3307端口主mysql，并建立账户并授权slave
[iyunv@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>