Question

自己搭建mysql进行分表分库还是用阿里云的好

Answer 1

当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。
根据个人经验，MySQL执行一个sql的过程如下：
1,接收到sql;2,把sql放到排队队列中 ;3,执行sql;4,返回执行结果。在这个执行过程中最花时间在什么地方呢？第一，是排队等待的时间，第二，sql的执行时间。其实这二个是一回事，等待的同时，肯定有sql在执行。所以我们要缩短sql的执行时间。
mysql中有一种机制是表锁定和行锁定，为什么要出现这种机制，是为了保证数据的完整 性，我举个例子来说吧，如果有二个sql都要修改同一张表的同一条数据，这个时候怎么办呢，是不是二个sql都可以同时修改这条数据呢？很显然mysql 对这种情况的处理是，一种是表锁定（myisam存储引擎），一个是行锁定（innodb存储引擎）。表锁定表示你们都不能对这张表进行操作，必须等我对 表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。如果数据太多，一次执行的时间太长，等待的时间就越长，这 也是我们为什么要分表的原因。

Answer 2

1、使用前，关掉防火墙，包括 selinux，firewalld，或者 MySQL 企业版的firewall（如果用了企业版的话）2、两台机器：（4 台 MySQL 实例）192.168.2.219 centos-ytt57-1 3311/3312192.168.2.229 centos-ytt57-2 3311/3312
3、安装 MySQL（两台都装）, MySQL Shell（任意一台）, mysqlrouter(任意一台，官方建议和应用程序装在一个服务器上)yum install mysql-community-server mysql-shell mysql-router-community

4、搭建 InnoDB-Cluster（两台都装）

1. 配置文件如下：shell>vi /etc/my.cnfmaster-info-repository=tablerelay-log-info-repository=tablegtid_mode=ONenforce_gtid_consistency=ONbinlog_checksum=NONElog_slave_updates=ONbinlog_format=ROWtransaction_write_set_extraction=XXHASH64
2. 系统 HOSTS 配置（两台都配）

• shell>vi /etc/hosts

• 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

• ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

• 192.168.2.219 centos-ytt57-2

• 192.168.2.229 centos-ytt57-3

• 用 MySQL coalesce 函数确认下 report-host 是否设置正确(root@localhost) : [(none)] >SELECT coalesce(@@report_host, @@hostname) as r;+----------------+| r              |+----------------+| centos-ytt57-1 |+----------------+1 row in set (0.00 sec)




3. 创建管理员用户搭建 GROUP REPLICATION （四个节点都要）

• create user root identified by 'Root@123';grant all on *.* to root with grant option;flush privileges;




4. MySQLsh 连接其中一台节点：

• [root@centos-ytt57-1 mysql]# mysqlsh root@localhost:3321




• 5. 检查配置正确性：（如果这里不显示 OK，把对应的参数加到配置文件重启 MySQL 再次检查）dba.checkInstanceConfiguration("root@centos-ytt57-2:3311");dba.checkInstanceConfiguration("root@centos-ytt57-2:3312");dba.checkInstanceConfiguration("root@centos-ytt57-3:3311");dba.checkInstanceConfiguration("root@centos-ytt57-3:3312");mysqlsh 执行检测

• [root@centos-ytt57-1 mysql]# mysqlsh --log-level=8 root@localhost:3311

• MySQL  localhost:3311 ssl  JS > dba.checkInstanceConfiguration("root@centos-ytt57-2:3311")

• {

• "status": "ok"

• }

6. 创建集群，节点 1 上创建。（结果显示 Cluster successfully created 表示成功）

• MySQL  localhost:3311 ssl  JS > var cluster = dba.createCluster('ytt_mgr');

• Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

• At least 3 instances are needed for the cluster to be able to withstand up to

• one server failure.

• 7. 添加节点 2，3，4（全部显示 OK，表示添加成功）MySQL  localhost:3311 ssl  JS >  cluster.addInstance('root@centos-ytt57-2:3312');MySQL  localhost:3311 ssl  JS >  cluster.addInstance('root@centos-ytt57-3:3311');MySQL  localhost:3311 ssl  JS >  cluster.addInstance('root@centos-ytt57-3:3312');




• 8. 查看拓扑图：（describe 简单信息，status 详细描述）

• MySQL  localhost:3311 ssl  JS > cluster.describe()

• {

• "clusterName": "ytt_mgr",

• "defaultReplicaSet": {

• "name": "default",

• "topology": [

• {

• "address": "centos-ytt57-2:3311",

• "label": "centos-ytt57-2:3311",

• "role": "HA",

• "version": "8.0.17"

• },

• {

• "address": "centos-ytt57-2:3312",

• "label": "centos-ytt57-2:3312",

• "role": "HA",

• "version": "8.0.17"

• },

• {

• "address": "centos-ytt57-3:3311",

• "label": "centos-ytt57-3:3311",

• "role": "HA",

• "version": "8.0.17"

• },

• {

• "address": "centos-ytt57-3:3312",

• "label": "centos-ytt57-3:3312",

• "role": "HA",

• "version": "8.0.17"

• }

• ],

• "topologyMode": "Single-Primary"

• }

• }

• MySQL  localhost:3311 ssl  JS > cluster.status()

• "clusterName": "ytt_mgr",

• "defaultReplicaSet": {

• "name": "default",

• "primary": "centos-ytt57-2:3311",

• "ssl": "REQUIRED",

• "status": "OK",

• "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

• "topology": {

• "centos-ytt57-2:3311": {

• "address": "centos-ytt57-2:3311",

• "mode": "R/W",

• "readReplicas": {},

• "role": "HA",

• "status": "ONLINE",

• "version": "8.0.17"

• },

• "centos-ytt57-2:3312": {

• "address": "centos-ytt57-2:3312",

• "mode": "R/O",

• "readReplicas": {},

• "role": "HA",

• "status": "ONLINE",

• "version": "8.0.17"

• },

• "centos-ytt57-3:3311": {

• "address": "centos-ytt57-3:3311",

• "mode": "R/O",

• "readReplicas": {},

• "role": "HA",

• "status": "ONLINE",

• "version": "8.0.17"

• },

• "centos-ytt57-3:3312": {

• "address": "centos-ytt57-3:3312",

• "mode": "R/O",

• "readReplicas": {},

• "role": "HA",

• "status": "ONLINE",

• "version": "8.0.17"

• }

• },

• "topologyMode": "Single-Primary"

• },

• "groupInformationSourceMember": "centos-ytt57-2:3311"

9. 简单测试下数据是否同步

• (root@localhost) : [(none)] >create database ytt;

• Query OK, 1 row affected (0.03 sec)

• (root@localhost) : [(none)] >use ytt;

• Database changed

• (root@localhost) : [ytt] >create table p1(id int primary key, log_time datetime);

• Query OK, 0 rows affected (0.08 sec)

• (root@localhost) : [ytt] >insert into p1 values (1,now());

• Query OK, 1 row affected (0.04 sec)

• (root@localhost) : [ytt] >show master status;

• +---------------+----------+--------------+------------------+-------------------------------------------+

• | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |

• +---------------+----------+--------------+------------------+-------------------------------------------+

• | mysql0.000001 |    25496 |              |                  | 6c7bb9db-b759-11e9-a9c0-0800276cf0fc:1-41 |

• +---------------+----------+--------------+------------------+-------------------------------------------+

• 1 row in set (0.00 sec)

• 查看其他三个节点

• (root@localhost) : [ytt] >show tables;

• +---------------+

• | Tables_in_ytt |

• +---------------+

• | p1            |

• +---------------+

• 1 row in set (0.00 sec)

• (root@localhost) : [ytt] >select * from p1;

• +----+---------------------+

• | id | log_time            |

• +----+---------------------+

• |  1 | 2019-08-05 16:44:20 |

• +----+---------------------+

• 1 row in set (0.00 sec)

• 停掉主节点：[root@centos-ytt57-2 mysql0]# systemctl stop mysqld@0




• 现在查看，主节点已经变为本机 3312节点"centos-ytt57-2:3312": {   "address": "centos-ytt57-2:3312",   "mode": "R/W",   "readReplicas": {},   "role": "HA",   "status": "ONLINE"}




10. 报错处理

• 错误日志里显示2019-08-05T09:01:35.125591Z 0 [ERROR] Plugin group_replication reported: 'The group name option is mandatory'2019-08-05T09:01:35.125622Z 0 [ERROR] Plugin group_replication reported: 'Unable to start Group Replication on boot'




• 同时用 cluster.rescan() 扫描，发现The instance 'centos-ytt57-2:3311' is no longer part of the ReplicaSet.




• 重新加入此节点到集群：cluster.rejoinInstance('centos-ytt57-2:3311')




• 再次执行cluster.status()查看集群状态："status": "OK",11. 移除和加入cluster.removeInstance("centos-ytt57-3:3312");The instance 'centos-ytt57-3:3312' was successfully removed from the cluster.cluster.addInstance("centos-ytt57-3:3312");The instance 'centos-ytt57-3:3312' was successfully added to the cluster.




• 12. 用 mysqlrouter 生成连接 MGR 相关信息。涉及到两个用户：--user=mysqlrouter 是使用mysqlrouter的系统用户自动创建的MySQL 用户是用来与MGR通信的用户。如果想查看这个用户的用户名以及密码，就加上--force-password-validation，不过一般也没有必要查看。

Answer 3

一般小同多站用ECS就行了。够用就行。。