mycat
Atlas
Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。
它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。
360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。
下载地址
https://github.com/Qihoo360/Atlas/releases
注意：
1、Atlas只能安装运行在64位的系统上
2、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm，Centos 6.X安装Atlas-XX.el6.x86_64.rpm。
3、后端mysql版本应大于5.1，建议使用Mysql 5.6以上
安装配置
yum install -y Atlas*
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
vi test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 172.16.190.130:3306
proxy-read-only-backend-addresses = 172.16.190.129:3306,172.16.190.130:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8
启动atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start
ps -ef |grep proxy
atlas 功能测试
测试读操作：
mysql -umha -pmha -h 172.16.190.130 -P 33060
db03 [(none)]>select @@server_id;
测试写操作：
mysql> begin;select @@server_id;commit;
用户需求
开发人员申请一个应用用户 app( select update insert) 密码123456,要通过172网段登录
1. 在主库中,创建用户
grant select ,update,insert on *.* to app@'172.16.%' identified by '123456';
2. 在atlas中添加生产用户
/usr/local/mysql-proxy/bin/encrypt 123456 ---->制作加密密码
vim test.cnf
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,app:/iZxz+0GRoA=
/usr/local/mysql-proxy/bin/mysql-proxyd test restart
[root@db03 conf]# mysql -uapp -p123456 -h 172.16.190.130 -P 33060
atlas 基本管理
连接管理接口
[root@db03 conf]# mysql -uapp -p123456 -h 172.16.190.130 -P 2345 #错误的账户登录
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): unknown user
[root@db03 conf]# mysql -uuser -ppwd -h 172.16.190.130 -P 2345 #管理账户登录
打印帮助
db03 [(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, ... #在线后端id |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... #添加主 |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... #添加从节点 |
| ADD GMASTER $group_id $backend | example: "add gmaster 1 127.0.0.1:3306", 备用主节点 |
| ADD GSLAVE $group_id $backend | example: "add gslave 1 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| REMOVE GBACKEND $group_id $backend_id | example: "remove gbackend 1 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 |
+---------------------------------------+---------------------------------------------------------+
19 rows in set (0.00 sec)
查询后端所有节点
SELECT * FROM backends;
动态添加删除节点
add slave 127.0.0.1:3306
REMOVE BACKEND 3;
保存配置到配置文件
SAVE CONFIG
自动分表
使用Atlas的分表功能时，首先需要在配置文件test.cnf设置tables参数。
tables参数设置格式：数据库名.表名.分表字段.子表数量，
比如：
你的数据库名叫school，表名叫stu，分表字段叫id，总共分为2张表，那么就写为school.stu.id.2，如果还有其他的分表，以逗号分隔即可。
关于读写分离
MySQL-Router ---> MySQL官方
ProxySQL --->Percona
Maxscale ---> MariaDB
MyCAT
基础架构图
MyCAT基础架构准备
环境准备
两台虚拟机 db01 db02
每台创建四个mysql实例：3307 3308 3309 3310
清楚历史环境
pkill mysqld
rm -rf /data/330*
mv /etc/my.cnf /etc/my.cnf.bak0321
创建相关目录并初始化数据
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql_cluster/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql_cluster/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql_cluster/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/opt/mysql_cluster/mysql
准备配置文件和启动脚本
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/opt/mysql_cluster/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target