mysql复制(高可用架构方案的基础)-白红宇

mysql复制(高可用架构方案的基础)

阅读量：6956 次

发布时间：2019-06-27

本文共 5085 字，大约阅读时间需要 16 分钟。

mysql复制:把一个数据库实例上所有改变复制到另外一个数据库库服务器实例的过程

特点:

1.没有改变就无所谓复制 ;改变是复制的根本与数据源

2.所有的改变:是指可以复制全部改变,也可以复制部分改变可以在全部改变中根据业务需求选择部分库和部分表的复制

复制的场景：

1.数据库容灾

2.需求：创建一个从数据服务器，做数据的测试和分析

3.负载均衡

4.复制时高可用架构方案的基础

mysql高可用架构特点

1.数据库故障的检测与排除

2.主从数据库的切换

3.数据的备份和保护

mysql高可用架构常用方案

1.双主自动/手工切换

2.Altas,opneproxy读写分离方案

3.MMM架构

4.MHA架构

5.DRDB高可用架构

6.mycat高可用分片架构

7.mysql NDB cluster集群架构

8.percona xtradb cluster(pxc)集群架构

9.mysql fabric高可用架构

mysql复制配置

同步复制步骤

1. 配置master服务器

2. 配置slave实例

3. 配置slave的复制连接到master

1.配置master服务

log-bin=/var/lib/mysql/binlog/mysql-bin.log (打开二进制文件及二进制文件的位置,注意是是文件不是文件夹 mysql-bin是二进制日志的开头格式 ,注意binlog目录对mysql用户的权限)

server-id=108 (服务器唯一标识,必须设置,如果没设置，可通过进入mysql 后 show binlog events in 'mysql-bin.000001'; 查看)

mysql> show binlog events in 'mysql-bin.000001';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.35-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+---------------------------------------+

设置完重启mysql服务

mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       143 || mysql-bin.000002 |       120 |+------------------+-----------+mysql> show binlog events in  'mysql-bin.000002';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000002 |   4 | Format_desc |       108 |         120 | Server ver: 5.6.35-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+---------------------------------------+mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+

master的/etc/my.cnf 示例

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-bin=/var/lib/mysql/binlog/mysql-bin.logserver-id=108# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=true

2.从库的配置 my.cnf里

server-id=251

开启从库进程

start slave ;

如果有警告

show warnings;

3.配置slave连接到master的复制

在主库上创建一个复制权限的账号

grant replication slave on  *.* to rep@'%' identified  by 'rep123456';flush privileges;

在从服务器mysql里执行

change master   to master_host='192.168.1.250',     master_port=3306,     master_user='rep',     master_password='rep123456',     master_log_file='mysql-bin.000003',                    master_log_pos=106;

master_log_file 表示从哪个二进制文件开始,master_log_pos 表示从哪个位置开始

比如我做测试的主库是 192.168.1.250 从库是192.168.1.251

查看从库状态

show slave status\G

Slave_IO_State:                  Master_Host: 192.168.1.250                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000003          Read_Master_Log_Pos: 106               Relay_Log_File: mysqld-relay-bin.000001                Relay_Log_Pos: 4        Relay_Master_Log_File: mysql-bin.000003             Slave_IO_Running: No            Slave_SQL_Running: 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: 106              Relay_Log_Space: 106              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:

View Code

主要参数

Slave_IO_Running: No

Slave_SQL_Running: No

开启从库进程

start slave ;

show slave status\G

这时：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

表示配置成功了，在主库相应表里插入数据,从库也会有！

如果出现