简介读写分离，简单地说是把对数据库的读和写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。主数据库进行写操作后，数据及时同步到所读的数据库，尽可能保证读、写数据库的数据一致。此处的读写分离采用的是Mycat。Mycat 的原理中最重要的一个词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了 一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库， 并将返回的结果做适当的处理，最终再返回给用户。下面是博主用于配置的主从及读写分离的机器：节点系统环境IPMySQL主从关系MySQL读 OR 写node3CentOS 7LNMP、JDK8192.168.17.130主写node4CentOS 7LNMP192.168.17.150主+从读Mycat部署与配置1、安装jdk环境此处安装的jdk版本为8，可前往官网下载，应保证其下载最低jdk版本不可低于7，其具体jdk配置可参考如下文章：[post cid="862" /]2、下载Mycat，下载地址为：http://dl.mycat.org.cn/，选择Linux环境的版本即可，此处下载的版本为1.6.7.4-release，按需下载并配置wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz #创建一个group groupadd mycat #创建一个新的用户，并加入group useradd -g mycat mycat # 解压缩 tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local #设置权限 chown -R mycat:mycat /usr/local/mycat3、修改Mycat内存配置、大家需要根据自己的机器实际内存情况进行配置vim /usr/local/mycat/conf/wrapper.conf # 大约35行左右，结合自己机器内存大小更改即可： wrapper.java.additional.9=-Xmx2G wrapper.java.additional.10=-Xms512M4、将Mycat添加至系统环境变量vim /etc/profile # 添加如下内容 MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH # 刷新系统环境变量 source /etc/profile5、配置Mycat账号密码和数据库名（server.xml）Mycat作为数据库的中间件，对于上层应用来说，他就是一个数据库。因此需要配置数据库的用户名，密码，数据库名，以及读写权限。user 用户配置节点相关参数说明：参数说明user用户配置节点--name登录的用户名，也就是连接Mycat的用户名--password登录的密码，也就是连接Mycat的密码--schemas数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个数据库db1,db2，则配置db1,db2--privileges配置用户针对表的增删改查的权限--readOnly数据库是否设置为只读--defaultSchema默认数据库，错误前会尝试使用该schema作为schema，不设置则为null,报错vim /usr/local/mycat/conf/server.xml # 修改如下 <user name="root"> <property name="password">123456</property> <property name="schemas">typecho_db,dvwa_db</property> <property name="readOnly">false</property> <property name="defaultSchema">typecho_db</property> </user> 即配置一个用户名为root，密码为123456的用户，同时具有typecho_db,dvwa_db模式下的读写权限，注意该模式即是在schema.xml配置文件定义的模式名一样。6、配置Mycat的schema.xml文件vim /usr/local/mycat/conf/schema.xml # 相关配置 <?xml version="1.0"?> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="typecho_db" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1" /> <schema name="dvwa_db" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn2" /> <dataNode name="dn1" dataHost="SQL1" database="typecho" /> <dataNode name="dn2" dataHost="SQL1" database="dvwa" /> <dataHost name="SQL1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="123456"> <readHost host="hostS1" url="192.168.17.150:3306" user="root" password="123456"> </readHost> </writeHost> </dataHost> </mycat:schema> 参数说明：参数说明schema数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应dataNode分片信息，也就是分库相关配置dataHost物理数据库，真正存储数据的数据库每个节点的属性逐一说明：schema:属性说明name逻辑数据库名，与server.xml中的schema对应checkSQLschema数据库前缀相关设置，建议看文档，这里暂时设为folsesqlMaxLimitselect 时默认的limit，避免查询全表table:属性说明name表名，物理数据库中表名dataNode表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的nameprimaryKey主键字段名，自动生成主键时需要设置autoIncrement是否自增rule分片规则名，具体规则下文rule详细介绍dataNode属性说明name节点名，与table中dataNode对应datahost物理数据库名，与datahost中name对应database物理数据库中数据库名dataHost属性说明name物理数据库名，与dataNode中dataHost对应balance均衡负载的方式类型writeType写入方式dbType数据库类型switchType切换的模式heartbeat心跳检测语句，注意语句结尾的分号要加。其中，balance指的负载均衡类型，目前的取值有4种：balance="0" 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。balance="1" 全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。balance="2" 所有读操作都随机的在writeHost、readhost上分发。balance="3" 所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力switchType指的是切换的模式，目前的取值也有4种：switchType='-1' 表示不自动切换switchType='1' 默认值，表示自动切换switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为show slave statusswitchType='3' 基于MySQL galary cluster的切换机制（适合集群），心跳语句为show status like 'wsrep%'。设置开机自启之前设置过rc.local添加开机启动命令，服务器重启后mycat启动失败，又写一份配置文件控制mycat开机自启，仍失败，后摸索后发现皆是JVM虚拟机未启动导致的报错并停止，遂下方的启动方法是自己摸索中成功实现的一个开机启动方法，其设置如下：1、将Mycat启动文件创建软连接ln -s /usr/local/mycat/bin/mycat /etc/init.d/mycat2、修改mycat路径中conf/wrapper.conf文件，将wrapper.java.command参数设置为java的路径vim /usr/local/mycat/conf/wrapper.conf # 修改为如下内容（后面一定要具体到java文件） wrapper.java.command=/usr/local/java/jdk1.8.0_261/bin/java wrapper.working.dir=..3、添加文件执行权限并加入开机自启列表chmod +x /etc/init.d/mycat chkconfig --add mycat # 查看是否添加成功（mycat后面的2、3、4、5皆为on即成功） chkconfig --list相关命令使用，后面的任一命令皆可控制说明命令启动 Mycatmycat start 、 mycat console 、 systemctl start mycat查看启动状态mycat status 、 systemctl status mycat停止mycat stop 、 systemctl stop mycat重启mycat restart 、 systemctl restart mycat连接MycatMycat 有两个端口，8066 是数据平台的端口，9066是管理配置平台的端口。如果要修改端口号（如端口被占用，冲突等），需要配置serve.xml。最后可使用navicat连接该服务，像连接数据库那样正常连接即可
本周在搭建集群的时候，特意将数据库也建立一个集群为以后方便扩容，故此处带大家了解一下MySQL的主从配置主从复制介绍原理主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。工作原理如图所示，主服务器上面的任何修改都会保存在二进制日志Binary log里面，从服务器上面启动一个I/O thread（实际上就是一个主服务器的客户端进程），连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log里面。从服务器上面开启一个SQL thread定时检查Realy log，如果发现有更改立即把更改的内容在本机上面执行一遍。如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。复制过程1、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。2、Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。3、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从何处开始读取日志。4、Slave的SQL进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。下面是博主配置主从的服务器列表：节点系统环境IPMySQL主从关系node3CentOS 7LNMP192.168.17.130主node4CentOS 7LNMP192.168.17.150主+从主从配置注：在主从配置之前需要确保两台MySQL需要同步的库状态一致。主：配置文件默认在/etc/my.cnf下。在配置文件中新增配置：[mysqld] # 同一局域网内注意要唯一 server-id=1 # 开启二进制日志功能，名称可以随便取（关键） log-bin=mysql-bin # 二进制数据自动删除/过期天数设置，默认值为0，表示不自动删除 expire_logs_days = 7补充：若需要选择性的复制某些数据库，可在下方根据自己的需求选择性的加参数设置：binlog-do-db= 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可binlog-ignore-db= 不需要复制的数据库名，如果不需要复制多个数据库，重复设置这个选项即可此处并不推荐使用这两个设置需要同步与不同步的库，原因是binlog-do-db和binlog-ignore-db它们将语句写入了二进制日志. 意味着你不能使用二进制日志从备份恢复指定时间的数据；且过滤操作带来的负载都在主数据库服务器上，加重主数据库服务器负载。安全的操作则是在slave上配置过滤，来决定是否同步库或表。修改配置后需要重启才能生效：service mysql restart重启之后进入MySQL：mysql -uroot -p在master数据库创建数据同步用户，授予用户slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。CREATE USER 'slave'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';语句中的%代表所有服务器都可以使用这个用户，如果想指定特定的IP，将%改成IP即可。密码password请自定义查看主MySQL的状态：show master status;记录下File和Position的值，并且不进行其他操作以免引起Position的变化。从：在从my.cnf配置中新增：[mysqld] # 设置server_id,注意要唯一 server-id=2 # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用（按需添加，添加后该节点将主从并存） log-bin=mysql-slave-bin # relay_log配置中继日志 relay_log=mysql-relay-bin #作为从库时生效，中继日志relay_log可以自我修复 relay_log_recovery=1 #设置为只读（正常不需添加，若配置读写分离且从库只负责读的话可按需添加） #read_only=1补充：从库不复制部分库，根据需求可按需设置replicate-do-db= 设定需要复制的数据库replicate-ignore-db= 设定需要忽略的复制数据库 replicate-do-table= 设定需要复制的表replicate-ignore-table= 设定需要忽略的复制表 replicate-wild-do-table= 同replication-do-table功能一样，但是可以使用通配符replicate-wild-ignore-table= 同replication-ignore-table功能一样，但是可以加通配符修改配置后需要重启才能生效：service mysql restart重启之后进入MySQL：mysql -u root -p # 配置主从复制项 change master to master_host='192.168.17.130', master_user='slave', master_password='password', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=433, master_connect_retry=30;参数说明：master_host：Master的地址master_port：Master的端口号master_user：用于数据同步的用户master_password：用于同步的用户的密码master_log_file：指定 Slave 从哪个日志文件开始复制数据，即在上面提到的 File 字段的值master_log_pos：从哪个 Position 开始读，即在上面提到的 Position 字段的值master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒在从MySQL中查看主从同步状态：show slave status \G;此时的Slave_IO_Running和Slave_SQL_Running都是No，因为我们还没有开启主从复制过程。开启主从复制：start slave;再次查看同步状态：show slave status \G;Slave_IO_Running和Slave_SQL_Running都是Yes说明主从复制已经开启。遇到的问题1、开启后Slave_IO_Running报No，查看日志发现错误如下：[ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL > server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593这是因为MySQL 5.6的复制引入了UUID的概念，各个复制结构中的server_uuid得保证不一样，但是直接复制数据库的data文件夹后server_uuid是相同的，可使用如下命令查看：show variables like '%server_uuid%';这时候我们找到data文件夹下的auto.cnf文件，修改里面的UUID值（改动一个数值即可），保证各个数据库的UUID不一样，然后再重启数据库若不知道该文件位置，可使用以下命令查找该文件位置：find / -name auto.cnf2、创建主从关系时copy了同样的my.cnf文件，报错，如下：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;错误原因和server_uuid类似，server_id也得保证不一样才行，找到my.cnf配置文件中的server_id，修改从库的server_id保证和复制结构中的其他数据库不一样，再重启数据库即可3、Slave_IO_Running报Connecting，可能有下面几种原因，请逐一排查解决：网络不通，检查IP端口密码不对，检查用于同步的用户名和密码Pos不对，检查Master的PositionMySQL 8特有的密码规则问题引起，可将密码规则修改为：mysql_native_password，如下：ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';4、主从复制出现的1008错误，如下：其发生的原因可以是你在从数据库上将一个数据库删除了，你又在主数据库上将该数据库删除了，导致文件同步过来时，从数据库由于提前删除了该库，导致执行命令时找不到该库，命令执行失败，同步中断；又或是主库上建立了名为11的数据库，然后立马就删除了，导致从库并没有同步完成，然后再去执行log-bin文件中删除的操作时，找不到11的数据库，导致了同步中断。其解决办法就是将log-bin文件的执行指针下移一个位置，命令如下：mysql> stop slave; #将binglog指针下移一个位置，若移动多位指针，可调后面的数值 mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave;
AI 摘要（由 ChatGPT 总结生成）：MySQL创建用户和授权是学习MySQL必备知识。创建用户通过`CREATE USER`命令，可指定用户名、主机和密码，允许本地或远程登录。授权使用`GRANT`命令，设置用户权限，包括数据库和表的操作权限。修改密码通过`SET PASSWORD`命令，撤销权限使用`REVOKE`命令。注意在操作后使用`FLUSH PRIVILEGES`刷新权限。删除用户和解决可能问题也是重要一环。配置文件修改和插件设置可解决远程连接问题。对于学习建站或正在学习MySQL语法知识的小伙伴，这波MySQL知识掌握是必不可少的。一、创建用户命令：CREATE USER 'username'@'host' IDENTIFIED BY 'password';命令说明：username：你将创建的用户名host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器举例：CREATE USER 'abc'@'localhost' IDENTIFIED BY '123456'; //创建用户：abc，密码：123456，仅本地用户可用 CREATE USER 'abc'@'192.168.17.135' IDENTIFIED BY '123456'; //创建用户：abc，密码：123456，指定192.168.17.135地址可登录(多个ip可使用英文逗号分隔) CREATE USER 'abc'@'%' IDENTIFIED BY '123456'; //创建用户：abc，密码：123456，可任意主机远程登录 CREATE USER 'abc'@'%' IDENTIFIED BY ''; //创建用户：abc，无密码，可任意主机远程登录 CREATE USER 'abc'@'%'; //创建用户：abc，无密码，可任意主机远程登录二、授权命令GRANT privileges ON databasename.tablename TO 'username'@'host'命令说明:privileges：用户的操作权限，如SELECT，INSERT，UPDATE，CREATE，DROP，DELETE等，如果要授予所的权限则使用ALLdatabase name：数据库名table name：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*举例：GRANT SELECT, INSERT PRIVILEGES ON test.user TO 'abc'@'%'; //可任意主机登录的用户abc对test数据库中的user表授予SELECT和INSERT权限 GRANT ALL PRIVILEGES ON test.* TO 'abc'@'%'; //可任意主机登录的用户abc对test数据库授予所有操作权限 GRANT ALL PRIVILEGES ON *.* TO 'abc'@'%'; //可任意主机登录的用户abc对所有数据库和表授予所有操作权限注：用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，可用以下命令（若无特定需求可无需此命令授予权限）:GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;若您需要自定义创建数据库，则创建数据库命令：CREATE DATABASE 数据库名;三、修改用户密码命令：SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登录用户修改密码：SET PASSWORD = PASSWORD('newpassword');举例：SET PASSWORD FOR 'abc'@'%' = PASSWORD('123456');四、撤销用户权限命令：REVOKE privilege ON databasename.tablename FROM 'username'@'host';其中privilege, database name, table name同授权部分说明举例：REVOKE SELECT ON *.* FROM 'abc'@'%'; //可任意主机登录的用户abc撤销对任意数据库和表的`SELECT`权限注意:假如你在给用户'abc'@'%'授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO 'abc'@'%'，则在使用REVOKE SELECT ON *.* FROM 'abc'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作。相反，如果授权使用的是GRANT SELECT ON *.* TO 'abc'@'%';则REVOKE SELECT ON test.user FROM 'abc'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。查看数据库中具体某个用户的权限：SHOW GRANTS FOR 'abc'@'%';查看所有用户命令：SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;五、删除用户命令：DROP USER 'username'@'host';六、可能遇到的问题1、在操作 mysql 库做用户的增删修改的时候，操作完毕的时候最好使用 flush privilege命令刷新一下权限。否则可能会修改不生效flush privilege;2、如果在上述添加了用户%可任意主机登录的权限，且已经开放防火墙，在使用Navicat远程连接数据库可能会报10038等错误解决办法。2.1、若您使用的是MySQL 8.0以上版本，在MySQL 8.0以上版本中，caching_sha2_password是默认的身份验证插件，而不是以往的mysql_native_password。故可修改插件为mysql_native_password如果是现有用户：use mysql; ALTER USER '用户名'@'host' IDENTIFIED WITH mysql_native_password BY 'password'; flush privilege;如果是新建用户：use mysql; CREATE USER '用户名'@'host' IDENTIFIED WITH mysql_native_password BY 'password'; flush privilege;其中，用户名为你需要远程账户的用户名，host是你要登录的主机，password为你的用户密码。又或者使用默认加密方式，在配置文件中添加以下内容：default_authentication_plugin=mysql_native_password2.2、上述操作若仍无法登陆，可能是MySQL Server绑定了本地地址127.0.0.1，打开MySQL配置文件（可能不同方式的安装配置文件的位置也不同），此处博主的数据库配置文件为/etc/mysql/mysql.conf.d/mysql.cnf将其中的bind-address = 127.0.0.1 修改为 bind-address = 0.0.0.0重启数据库，即可使用Navicat连接数据库，相比于该操作，较为推荐使用PhpmyAdmin管理工具。