mysql端口 Mysql主从同步，分库分表

安装

apt-get update

apt-get install mysql-server

netstat -anop | grep 3306

如果3306端口处于监听状态，则mysql已经成功启动

mysql -h 127.0.0.1 -P 3306 -uroot -p123 #p后面是我们安装mysql时的密码

问题1：access denied for user root @localhost

ubuntu下安装了mysql后，没有提示创建root的密码就成功安装了，发现却无法以root登录。

提示： access denied for user root @localhost

查了好多资料，发现都不行，原来原因是因为auth_socket的验证类型引起的。

1：首先用debian-sys-maint登录，密码在/etc/mysql/debian.cnf文件。

mysql -u debian-sys-maint -p

2：然后修改root密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

问题2：2003 can't connect to mysql server on 10038

首先我们通过

1：netstat -an|grep 3306

来查看mysql默认的端口3306是否开启，允许哪个ip使用，如果你发现，前面有127.0.0.1，就说明，3306端口只能本机ip

2：打开mysql配置文件vi /etc/mysql/mysql.conf.d/mysqld.cnf

将bind-address = 0.0.0.0；

3：登录mysql，grant all privileges on *.* to 'root'@'%' identified by 'xxxxxx';

这里的root 是你远程登录的用户，xxxxxx是你登录使用的密码，然后可以在mysql数据 表中查看到你这个用户已经被添加到user表中

4：重启mysql服务：sudo /etc/init.d/mysql restart

开发环境安装

apt-get install libmysqlclient-dev，然后再/usr/include/mysql/目录下的mysql.h是主要的API接口头文件

主从同步

Binary Log 简单介绍

因为Binlog dump 线程操作的文件是bin-log 日志文件，并且实现主从复制在主服务器上主要依靠bin-log日志文件，所以我们简单介绍一下bin-log日志文件。

原理

MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方案，特点是异步复制mysql端口，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。与之对应的是另一个同步技术是MySQL Cluster，但因为MySQL Cluster配置比较复杂，所以使用者较少。

MySQL Replication 就是从服务器拉取主服务器上的 二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。

MySQL的Replication是一个异步复制的过程（mysql5.1.7以上版本分为异步复制和半同步两种模式），它是从一个Mysql instance（instance英文为实例）(我们称之为Master)复制到另一个Mysql instance(我们称之slave)。

三个线程

在master与slave之间实现整个复制过程主要由三个线程来完成：

1、Slave SQL thread线程，在slave端

2、Slave I/O thread线程，在slave端

3、Binlog dump thread线程(也可称为IO线程)，在master端

注意：如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程。

要实现MySQL的Replication，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则无法实现mysql的主从复制。因为mysql的整个主从复制过程实际上就是：slave端从master端获取binlog日志，然后再在自己身上完全顺序的执行该日志中所记录的各种SQL操作。有关具体如何开启mysql的binlog日志功能，请大家自己在网上搜。

主从复制流程

MySQL主从复制的基本交互过程，如下：

1、slave端的IO线程连接上master端，并请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。

2、master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。

3、slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info（该文件存slave端）文件中，以便在下一次读取的时候能够清楚的告诉master“我需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我”。

4、slave端的SQL线程在检测到relaylog文件中新增内容后，会马上解析该log文件中的内容。然后还原成在master端真实执行的那些SQL语句，并在自身按顺丰依次执行这些SQL语句。这样，实际上就是在master端和slave端执行了同样的SQL语句，所以master端和slave端的数据完全一样的。

以上mysql主从复制交互过程比较拗口，理解起来也比较麻烦，我简化了该交互过程。如下：

1、master在执行sql之后，记录二进制log文件（bin-log）。

2、slave连接master，并从master获取binlog，存于本地relay-log中，然后从上次记住的位置起执行SQL语句，一旦遇到错误则停止同步。

从以上mysql的Replication原理可以看出：

主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间主从数据不一致的情况。

如果主从的网络断开，则从库会在网络恢复正常后，批量进行同步。

如果对从库进行修改数据，那么如果此时从库正在在执行主库的bin-log时，则会出现错误而停止同步，这个是很危险的操作。所以一般情况下，我们要非常小心的修改从库上的数据。

一个衍生的配置是双主、互为主从配置，只要双方的修改不冲突，则可以工作良好。

如果需要多主库的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。

整体过程：

MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定。

MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。

主服务器创建一个线程，即I/O线程，将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。

从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。

第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

一主一从配置

主ip：192.168.3.30

从ip：192.168.3.31

主配置：

1：打开sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf，输入以下配置：

log-bin=test-bin #binlog文件

server-id=2

#binlog-ignore-db=mysql #不同步的数据库

binlog-do-db=test #同步的数据库的名称

service mysql restart

show master status;

从配置：

1：修改sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf，增加如下选项

server-id = 3

2：sudo service mysql restart

3:mysql -uroot -p

change master to master_host='192.168.3.30', master_port=3306, master_user='root',

master_password='123456', master_log_file='test-bin.000001', master_log_pos=3299;

start slave;

show slave status\G

分库分表

1．博客 ，评论，读多于写场景，解决方案(mysql)

2．定位，日志，写多于读场景，解决方案(mongdb)

3．一起写，读与写一样多，解决方案（mysql）

分表解决慢查询

例如，user_id = 101 那么，我们在获取值的时候的操作，可以通过下边的sql语句：

select * from order_1 where user_id= 101

其中，order_1是根据 1010 计算所得，表示分表之后的第一章order表。

在实际的开发中，我们的用户ID更多的可能是通过UUID生成的，这样的话，我们可以首先将UUID进行hash获取到整数值，然后在进行取模操作。

分库提高并发插入慢

将用户ID进行取模操作，这样的话获取到具体的某一个数据库，关键字有：用户ID、库容量，上图中库容量为100，如果用户ID为UUID请先hash然后在进行取模

分库分表

例如：数据库有256 个，每一个库中有1024个数据表，用户的user_id＝262145，按照上述的路由策略，可得：

中间变量＝ 262145%（256*1024）= 1;

库序号＝取整（1／1024）= 0;

表序号＝1％1024 = 1;

对于user_id＝262145，将被路由到第０个数据库的第１个表中。

水平分库分表切分规则

1．RANGE

从0到10000一个表，10001到20000一个表；

2.HASH取模

一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。

3.地理区域

比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

4.时间

按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

分库分表后面临的问题

关于分库分表，如果用户的ID是通过UUID的方式生成的话，我们需要单独的进行一次hash操作，然后在进行取模操作等，其实hash本身就是一种分库分表的策略，使用hash进行路由策略的时候，我们需要知道的是，也就是hash路由策略的优缺点，优点是：数据分布均匀；缺点是：数据迁移的时候麻烦，不能按照机器性能分摊数据。

上述的分库和分表操作，查询性能和并发能力都得到了提高，但是还有一些需要注意的就是，例如：原本跨表的事物变成了分布式事物；由于记录被切分到不同的数据库和不同的数据表中，难以进行多表关联查询，并且不能不指定路由字段对数据进行查询。分库分表之后，如果我们需要对系统进行进一步的扩阵容（路由策略变更），将变得非常不方便，需要我们重新进行数据迁移。

事务支持

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

多库结果集合并（group by，order by）

TODO

跨库join

TODO 分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 粗略的解决方法： 全局表：基础数据，所有库都拷贝一份。 字段冗余：这样有些字段就不用join去查询了。 系统层组装：分别查询出所有，然后组装起来，较复杂。

分库分表方案产品

目前市面上的分库分表中间件相对较多，其中基于代理方式的有MySQL Proxy和Amoeba， 基于Hibernate框架的是Hibernate Shards，基于jdbc的有当当sharding-jdbc， 基于mybatis的类似maven插件式的有蘑菇街的蘑菇街TSharding， 通过重写spring的ibatis template类的Cobar Client。

还有一些大公司的开源产品：

参考文献：

blog.csdn.net/achuo/article/details/72229236

blog.csdn.net/xlgen157387/article/details/51331244

blog.csdn.net/xlgen157387/article/details/52451613

blog.csdn.net/weixin_43184819/article/details/84000936

blog.csdn.net/oqqJohn1234567890/article/details/89409567

（编辑：航空爱好网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!