MYSQL 主从复制 && Atlas读写分离

MYSQL 主从复制与读写分离

一，准备环境

主机	系统	ip	服务
主 mysql	centos7	192.168.211.135	mysql
从mysql	centos7	192.168.211.222	mysql
Atlas调度	centos7	192.168.211.138	Atlas

二，主从复制

前言

在实际生产中，数据的重要性不言而喻。

如果在生产环境中只有一台数据库服务器，那么很容易产生单点故障的问题，比如这台服务器访问压力过大而没有响应或者奔溃，那么服务就不可用了，再比如这台服务器的硬盘坏了，那么整个数据库的数据就全部丢失了，这是重大的安全事故.

为了避免服务的不可用以及保障数据的安全可靠性，我们至少需要部署两台或两台以上数据库服务器来存储数据库数据，也就是我们需要将数据复制多份部署在多台不同的服务器上，即使有一台服务器出现故障了，其他服务器依然可以继续提供服务.

MySQL提供了主从复制功能以提高服务的可用性与数据的安全可靠性。主从复制是指服务器分为主服务器和从服务器，主服务器负责读和写，从服务器只负责读，主从复制也叫 master/slave，master是主，slave是从，但是并没有强制，也就是说从也可以写，主也可以读，只不过一般我们不这么做。

主从复制可以实现对数据库备份和读写分离

主从复制原理

图示

原理

1，当 master 主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中

2，salve 从服务器会在一定时间间隔内对 master 主服务器上的二进制日志进行探测，探测其是否发生过改变（通过二进制文件的大小是否不同来进行判断，日志文件改变了的大小也可以叫作偏移），如果探测到 master 主服务器的二进制事件日志发生了改变，则开始一个 I/O Thread 请求 master 二进制事件日志

3，同时 master 主服务器为每个 I/O Thread 启动一个dump Thread，用于向其发送二进制事件日

4，slave 从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中

5，salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；

6，最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒

注意：主从复制的过程会有很小的延迟，基本没有影响

配置主从复制

提前安装了MYSQLD

https://blog.csdn.net/weixin_52184735/article/details/114773242

删除MariaDB 防止冲突

主服务

一、 建立时间同步环境，在主节点上搭建时间同步服务器

1）安装NTP（关闭防火墙/selinux）
yum -y install ntp
2)配置NTP
vi /etc/ntp.conf 

server 127.127.1.0    #本地时间供给源
fudge 127.127.1.0 stratum 8  #设置时区为+08区

3)重启服务并设置为开机启动

systemctl restart ntpd
systemctl enable ntpd

三、修改主服务器
1）vi /etc/my.cnf

server_id = 11  （修改）
log_bin = master-bin  （修改）# 开启二进制日志文件
log-slave-updates = true  （增加）

开启二进制日志文件 方法二

/etc/init.d/mysqld restart --log-bin=mysql-bin


2）重启服务器
systemctl restart mysqld

3）登录mysql程序，给服务器授权
mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.211.%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;  //刷新
mysql> show master status;  //查看日志
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      412 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
这个需要记录一下，等一下需要

从服务

yum -y install ntpdate
同步时间
ntpdate 主服务ip地址
[root@localhost ~]# ntpdate 192.168.211.135
14 Apr 14:57:57 ntpdate[4184]: step time server 192.168.211.135 offset 603916.763887 sec
[root@localhost ~]# ntpdate 192.168.211.135
14 Apr 14:58:10 ntpdate[4195]: adjust time server 192.168.211.135 offset -0.000323 sec
[root@localhost ~]# ntpdate 192.168.211.135
14 Apr 14:58:19 ntpdate[4204]: adjust time server 192.168.211.135 offset -0.000021 sec

vi /etc/my.cnf

server_id = 22  （修改）
relay-log=relay-log-bin  （增加）
relay-log-index=slave-relay-bin.index   （增加）

重启mysqld
systemctl restart mysqld

在从服务器上配置同步

mysql -u root -p
mysql> change master to master_host='192.168.211.139',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=412;   # master_log_pos =  Position值（刚才记录的值）
# master_log_file = 二进制日志文件
启动同步：
mysql> start slave;
mysql> stop slave；
查看slave状态，确保以下两个值为yes
mysql> show slave status\G;

六、验证
在MASTER上创建测试数据库
搭建完成后，可以在主库show slave hosts查看有哪些从库节点

mysql> show slave hosts;

mysql> create database db_test;

从服务

mysql [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.04 sec)

三，读写分离

前言

在开发项目过程中，当数据库的数据量较大、并大量较高的时候，所有的读写操作都在同一个数据库可能会导致事务处理较为缓慢；

所以，我们有时候就需要对数据库进行主从配置，进行读写分离，增删改的时候用主库，读取的时候用从库，这样也能有效提高数据库的读写效率；

原理：只在主服务器上写，只在从服务器上读，从而使得读与写分开，分担服务器压力
基本原理是让主数据库处理事务性查询，而从数据库处理select查询

实现方式

1. 程序代码实现
2. 基于中间代理层实现

Atlas的简介

Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。
主要功能：

读写分离

从库负载均衡

IP过滤

自动分表

DBA可平滑上下线DB

自动摘除宕机的DB

Atlas相对于官方MySQL-Proxy的优势

将主流程中所有Lua代码用C重写，Lua仅用于管理接口

重写网络模型、线程模型

实现了真正意义上的连接池

优化了锁机制，性能提高数十倍

下载Atlas

wget https://github.com/Qihoo360/Atlas/release
s/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm

安装

rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

会自动生成 mysql-proxy 这个文件

cd /usr/local/mysql-proxy

文件下有这四个文件

bin conf lib log

bin目录下放的都是可执行文件

“encrypt”是用来生成MySQL密码加密的，在配置的时候会用到
“mysql-proxy”是MySQL自己的读写分离代理
“mysql-proxyd”是360的，后面有个“d”，服务的启动、重启、停止。都是用他来执行的
conf目录下放的是配置文件

“test.cnf”只有一个文件，用来配置代理的，可以使用vim来编辑
lib目录下放的是一些包，以及Atlas的依赖
log目录下放的是日志，如报错等错误信息的记录

配置

[root@localhost mysql-proxy]# cd bin/
[root@localhost bin]# ls
encrypt  mysql-proxy  mysql-proxyd  VERSION
[root@localhost bin]# ./encrypt 123456
/iZxz+0GRoA=    //　得到一串字符串，复制下来，在配置文件里要用到

修改配置文件
/usr/local/mysql-proxy/conf

vim test.cnf

------------------------------------------------------------------------------------------修改配置文件开始--------------------------------------------------
[mysql-proxy]

#带#号的为非必需的配置项目

#管理接口的用户名
admin-username = myatlas

#管理接口的密码
admin-password = 123456

#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 192.168.211.135:3306

#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负
载均衡，若省略则默认为1，可设置多项，用逗号分隔
#proxy-read-only-backend-addresses = 127.0.0.1:3305@1
proxy-read-only-backend-addresses = 192.168.211.222:3306@1

#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密
程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用
户名和加密密码！
pwds = myslave:/iZxz+0GRoA=


#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
daemon = true

#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true,true后
面不能有空格。
keepalive = true

#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置，设置为CPU的核数
event-threads = 8

#日志级别，分为message、warning、critical、error、debug五个级别
log-level = message

#日志存放的路径
log-path = /usr/local/mysql-proxy/log

#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
sql-log = ON

#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：ms)的日志记录。不设置该参数则输出全部日志。
sql-log-slow = 1000

#实例名称，用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234

#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345

#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项
#tables = person.mt.id.3

#默认字符集，设置该项后客户端不再需要执行SET NAMES语句
charset = utf8mb4

#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置
#lvs-ips = 192.168.1.1

配置好后开启服务

cd /usr/local/mysql-proxy/bin
[root@localhost bin]# ./mysql-proxyd test start
OK: MySQL-Proxy of test is started
查看端口
[root@localhost bin]# netstat -nlpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:2345            0.0.0.0:*               LISTEN      2441/mysql-proxy    
tcp        0      0 0.0.0.0:1234            0.0.0.0:*               LISTEN      2441/mysql-proxy    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1160/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1378/master         
tcp6       0      0 :::22                   :::*                    LISTEN      1160/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      1378/master  

测试

yum install -y mysql  //安装一个客户端

mysql -h127.0.0.1 -P2345 -uatlas -p123	*#用atlas的管理用户和密码登录到2345端口查看  这里用户和密码就是配置文件里面写的*

[root@localhost bin]# mysql -h127.0.0.1 -P2345 -umyatlas -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and other
s.
Type 'help;' or '\h' for help. Type '\c' to clear the current inpu
t statement.
MySQL [(none)]> 

因为是管理用户命令不一样查看帮助
MySQL [(none)]> select * from help;
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)

MySQL [(none)]> 

查看代理主机
MySQL [(none)]> SELECT * FROM backends;
+-------------+----------------------+-------+------+
| backend_ndx | address              | state | type |
+-------------+----------------------+-------+------+
|           1 | 192.168.211.135:3306 | up    | rw   |
|           2 | 192.168.211.222:3306 | up    | ro   |
+-------------+----------------------+-------+------+
2 rows in set (0.00 sec)
# 出现down 可能是未授权


代理访问Mysql
[root@localhost bin]# mysql -h192.168.211.138 -P1234 -umyslave -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.81-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 
查看库
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+

创建表
MySQL [(none)]> use test
Database changed
MySQL [test]> create table student (
    -> `id` int ,
    -> `name` varchar(2))
MySQL [test]> insert into student (id,name) values (1,'io');
Query OK, 1 row affected (0.05 sec)
MySQL [test]> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | io   |
+------+------+
1 row in set (0.01 sec)

在主从上查看
mysql [test]>select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | io   |
+------+------+
1 row in set (0.00 sec)

mysql [test]>select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | io   |
+------+------+
1 row in set (0.00 sec)

ok

05 sec)
MySQL [test]> select * from student;
±-----±-----+
| id | name |
±-----±-----+
| 1 | io |
±-----±-----+
1 row in set (0.01 sec)