37. Atlas-自动分表（扩展）¶
1. 前提¶
#MHA+Atlas高可用读写分离架构
#MHA架构
192.168.178.151 #从1
192.168.178.161 #主
192.168.178.162 #从2
#Atlas
192.168.178.152
2. 自动分表介绍¶
#1.策略
range：均匀分布
取模
#2.tables参数设置格式：
数据库名.表名.分表字段.子表数量
#3.缺点
1.不能跨分片访问数据
2.不能跨分片join
3.在添加新分片时，只能是在range分片的方式下才能添加节点
3. 分表思路¶
#1.预估分表的个数，例如3个（school库下的stu表）
#2.准备分表，创建3个分表，并且与原表结构相同，命名规范时stu_0,stu_1,stu_2
#3.配置文件中开启分表规则
tables=school.stu.id.3
#4.测试分表功能
4. 原始数据准备¶
#151，登录到Atlas客户端口
mysql -uroot -p123 -h 192.168.178.152 -P 33060
create database school;
use school;
create table stu(id int,name varchar(20));
[root@151 ~]# mysql -uroot -p123 -h 192.168.178.152 -P 33060
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.81-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cp |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.02 sec)
mysql>
#出现报错。
mysql> create database school;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> use school;
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
ERROR 1049 (42000): Unknown database 'school'
mysql> create table stu(id int,name varchar(20));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
#经检查发现，VIP丢失了。重新设置VIP
#162
[root@162 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:30063) is running(0:PING_OK), master:192.168.178.161
#161
[root@161 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.178.161 netmask 255.255.255.0 broadcast 192.168.178.255
inet6 fe80::59f5:ba12:6b95:3cc7 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:a0:a2:05 txqueuelen 1000 (Ethernet)
RX packets 38518 bytes 2690526 (2.5 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 71839 bytes 8090719 (7.7 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
#重新设置VIP
ifconfig ens33:1 192.168.178.150/24
#161
[root@161 ~]# ifconfig ens33:1 192.168.178.150/24
[root@161 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.178.161 netmask 255.255.255.0 broadcast 192.168.178.255
inet6 fe80::59f5:ba12:6b95:3cc7 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:a0:a2:05 txqueuelen 1000 (Ethernet)
RX packets 39223 bytes 2740926 (2.6 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 73065 bytes 8227874 (7.8 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.178.150 netmask 255.255.255.0 broadcast 192.168.178.255
ether 00:0c:29:a0:a2:05 txqueuelen 1000 (Ethernet)
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
#再次写入数据测试
#151
mysql> create database school;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 9
Current database: *** NONE ***
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cp |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
#成功！
mysql> use school;
Database changed
mysql> create table stu(id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| stu |
+------------------+
1 row in set (0.00 sec)
mysql>
5. 创建分表¶
#151
create table stu_0 like stu;
create table stu_1 like stu;
create table stu_2 like stu;
mysql> create table stu_0 like stu;
Query OK, 0 rows affected (0.06 sec)
mysql> create table stu_1 like stu;
Query OK, 0 rows affected (0.01 sec)
mysql> create table stu_2 like stu;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| stu |
| stu_0 |
| stu_1 |
| stu_2 |
+------------------+
4 rows in set (0.00 sec)
6. 开启分表规则¶
#152
vim /usr/local/mysql-proxy/conf/test.cnf
#c
tables=school.stu.id.3
[mysql-proxy]
admin-username=user
admin-password=pwd
proxy-backend-addresses=192.168.178.150:3306
proxy-read-only-backend-addresses=192.168.178.162:3306,192.168.178.161:3306
pwds=root:3yb5jEku5h4=,oldboy:3yb5jEku5h4=
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
tables=school.stu.id.3
7. 重启Atlas¶
#152
/usr/local/mysql-proxy/bin/mysql-proxyd test restart
[root@152 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart
OK: MySQL-Proxy of test is stopped
OK: MySQL-Proxy of test is started
[root@152 ~]#
[root@152 ~]# ps -ef|grep proxy
root 57336 1 0 11:49 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 57337 57336 0 11:49 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 57421 56096 0 11:50 pts/1 00:00:00 grep --color=auto proxy
[root@152 ~]#
8. 插入数据¶
#151
#检查表
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| stu |
| stu_0 |
| stu_1 |
| stu_2 |
+------------------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> quit;
Bye
[root@151 ~]# mysql -uroot -p123 -h 192.168.178.152 -P 33060
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.81-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use school;
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_school |
+------------------+
| stu |
| stu_0 |
| stu_1 |
| stu_2 |
+------------------+
4 rows in set (0.00 sec)
#貌似有问题啊。。。按理说应该是没有stu表了。继续测试看看。
#插入数据
#151
mysql -uroot -p123 -h 192.168.178.152 -P 33060
use school;
insert into stu values(1,'z3');
insert into stu(id,name) values(2,'l4');
insert into stu(id,name) values(3,'w5');
insert into stu(id,name) values(4,'m6');
commit;
mysql> use school;
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> insert into stu values(1,'z3');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu(id,name) values(2,'l4');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu(id,name) values(3,'w5');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu(id,name) values(4,'m6');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| stu |
| stu_0 |
| stu_1 |
| stu_2 |
+------------------+
4 rows in set (0.00 sec)
mysql> select * from stu;
Empty set (0.00 sec)
mysql> select * from stu where id=1;
+------+------+
| id | name |
+------+------+
| 1 | z3 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from stu_0;
+------+------+
| id | name |
+------+------+
| 3 | w5 |
+------+------+
1 row in set (0.00 sec)
mysql>
9. 测试主表数据¶
#151
mysql -uroot -p123 -h 192.168.178.152 -P 33060
use school;
select * from stu where id=1; #可查询
select * from stu where name='z3'; #不可查询
#查主表
mysql> select * from stu where id=1;
+------+------+
| id | name |
+------+------+
| 1 | z3 |
+------+------+
1 row in set (0.01 sec)
mysql> select * from stu where id=2;
+------+------+
| id | name |
+------+------+
| 2 | l4 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from stu where id=3;
+------+------+
| id | name |
+------+------+
| 3 | w5 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from stu where id=4;
+------+------+
| id | name |
+------+------+
| 4 | m6 |
+------+------+
1 row in set (0.01 sec)
mysql> select * from stu;
Empty set (0.00 sec)
mysql>
10. 测试分表数据¶
#151
mysql -uapp -p123 -h 192.168.178.151 -P 33060
use school;
select * from stu_0;
select * from stu_1;
select * from stu_2;
#查分表
mysql> select * from stu_0;
+------+------+
| id | name |
+------+------+
| 3 | w5 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from stu_1;
+------+------+
| id | name |
+------+------+
| 1 | z3 |
| 4 | m6 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from stu_2;
+------+------+
| id | name |
+------+------+
| 2 | l4 |
+------+------+
1 row in set (0.00 sec)
mysql>
11. Atlas其他功能（了解即可）¶
#1.ip过滤（白名单）
配置文件加入如下参数
client-ips=192.168.178.100,192.168.178.101
#2.SQL黑名单
Atlas会屏蔽不带where添加的delete、update操作，以及sleep函数
#验证SQL黑名单
#151
#delete不带where
mysql> delete from stu;
ERROR 1105 (07000): Proxy Warning - Syntax Forbidden
mysql> delete from stu_0;
ERROR 1105 (07000): Proxy Warning - Syntax Forbidden
#update不带where
mysql> update stu set id=1;
ERROR 1105 (07000): Proxy Warning - Syntax Forbidden
mysql> update stu_0 set id=3;
ERROR 1105 (07000): Proxy Warning - Syntax Forbidden
mysql>
#带where
mysql> delete from stu where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> update stu set id=1 where name='m6';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> update stu set id=1 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
##为何这样查不出来
mysql> select * from stu where id=1;
Empty set (0.01 sec)
#分表能查到修改
mysql> select * from stu_0;
+------+------+
| id | name |
+------+------+
| 3 | w5 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from stu_1;
+------+------+
| id | name |
+------+------+
| 4 | m6 |
+------+------+
1 row in set (0.01 sec)
mysql> select * from stu_2;
+------+------+
| id | name |
+------+------+
| 1 | l4 |
+------+------+
1 row in set (0.00 sec)
mysql>
最后更新:
2022-02-20 08:44:07