mysql 之主从复制主从集群配置

数据库复制replication的实现原理

- 主服务器有语句的操作，对磁盘也有影响

- 从服务器要么跟着语句走要么跟着磁盘变化走

所以：

1.主服务器

建立2进制日志，每产生语句或磁盘变化，写进日志binlog。

2.从服务器

读取主服务器binlog 形成relaylog（中继日志）经语法分析变成从服务器的数据。

3.授权

由于数据都很私密，所以主服务器要授权复制账号 replication。

从服务器利用复制账号来坚挺主服务器的日志。

操作

1. 首先编辑主服务器mysql的配置文件 my.cnf (apt-get 安装位置 /etc/mysql/mysql.conf.d/ mysqld.cnf)

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id                    = 147      指定server_id
log_bin                       = /var/log/mysql/mysql-bin.log 指定bin-log文件位置
expire_logs_days        = 10 设置过期时间
max_binlog_size         = 100M  设置文件大小上限

#statement row mixed
binlog-format=mixed      设置匹配模式 此处选择mixed模式

- binlog-format 　日志格式用哪种好？
  - 　有statement ,row ,mixed (前两种的混合)　　　　
  - 以 insert into tx (value1,value2) 为例，影响一行且为新增一行，不影响其他行，这种情况用row比较好会直接复制磁盘上一行的变化
  - 以 update tx set name=value1 为例，影响一行且为更新一行，不影响其他行，这种情况用row比较好会直接复制磁盘上一行的变化
  - 以 update tx set count=count+1 为例，对于磁盘上很多行都发生了变化，影响多行，语句却简单，此处适合statement的日志
  - 以上两种格式各有优势，mysql 提供了mixed 模式来帮我们分析

　　2. 编辑从服务器的my.cnf

[mysqld]
port = 3306


server-id=69               指定server-id

relay-log=mysql-relay      指定relay-log

　　　3.重启各自的mysql

sudo /etc/init.d/mysql restart

　　4.主服务器master设置复制授权账号

grant replication client,replication slave on *.* 
to 'rep1'@'192.168.%.%'//授权给192.168打头的
identified by 'rep1';//密码

- 　　　　查看主服务器的状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

- 　　 position 的含义：
- 　　复制的日志文件的开始位置
- 　　 binery log 在/var/lib/mysql下

　　5.从服务器给你一个账号去连你的master

change master to
master_host='192.168.5.199',//主服务器地址
master_user='rep1',//授权用户名
master_password='rep1',//授权账号
master_log_file='mysql-bin.000001', //bin-log文件
master_log_pos=154;    //同步的位置

　 6.重置从服务器并启动slave

mysql>reset slave

- 　　　　启动slave

mysql>start slave

- 　　　　查看slave状态

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.147
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             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: 154
              Relay_Log_Space: 523
              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: 147
                  Master_UUID: dad611d4-3625-11e8-a11f-080027c354d3
             Master_Info_File: D:wamp64inmysqlmysql5.7.14datamaster.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)

测试

1.先查看主服务器，从数据库信息

mysql> show databases;     ----->master
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| twusa              |
+--------------------+
5 rows in set (0.01 sec)

mysql> show databases; ----->slave

2.主服务器创建新的数据库test_master 并查看bin-log大小

-rw-r----- 1 mysql mysql 322 4月 9 16:47 mysql-bin.000009

mysql> create database test_123;
Query OK, 1 row affected (0.01 sec)

-rw-r----- 1 mysql mysql 493 4月 9 16:52 mysql-bin.000009

这个时候回过头来看从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog               |
| ecshop             |
| mysql              |
| performance_schema |
| shop               |
| sys                |
| test               |
| test123            |--------->发现多了这一条
| twusa              |
+--------------------+
10 rows in set (0.00 sec)

至此主从复制完成，撒花 ~

遇到的问题

1.启动slave 一直显示正在connecting

- 　　解决：先试试远程登陆主库，如果登陆不上，有可能是防火墙的原因，关闭防火墙即可，远程登陆成功即可。

2.Got fatal error 1236 from master when reading data from binary log: 'unknown error reading log event on the master; the first event '' at 4, the last event read from '/opt/3306/mysql-bin.000008' at 154, the last byte read from '/var/log/mysql-bin.000008' at 155.'

- 　　解决:
  - - 主库：
      - 　　flush logs;
      - 　　show master status;
      - 　　记下File, Position。
    - 从库
      - 　　stop slave;
      - 　CHANGE MASTER TO MASTER_LOG_FILE...............再来一遍
      - start slave;

备注

1.查看bin-log

sudo mysqlbinlog mysql-bin.000009 > masterbin000009.log

mysql 之主从复制 主从集群配置

数据库复制replication的实现原理

测试

遇到的问题

备注

mysql 之主从复制主从集群配置