专题《一》mysql优化 ---------主从复制,读写
主从复制是mysql自带的功能,读写分离用第三方插件
主从复制作用:读写分离(mycat),数据备份,高可用,集群
原理:二进制sql执行文件-----insert,update,delete,create语句
读写分离:提高io性能,减少阻塞(写的操作有连接)
步骤
1.准备两台服务器,不同ip地址。 或者用虚拟主机,如果虚拟主机采用克隆方式获得,修改mysql server uuid。mysql5.7版本,要在my.cnf里增加sql_model
https://www.cnblogs.com/skymyyang/p/7551646.html
2.
修改主(master)服务器
vi /etc/my.cnf 新增以下内容
server_id=177 ###服务器id
log-bin=mysql-bin ###开启日志文件 依靠此文件进行两台服务器通讯
主服务器给从服务器账号授权
主服务器给从服务器账号授权
GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; |
//一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.145.226,加强安全。 |
重启服务器
service mysqld restart
登录主服务器的mysql,查询master的状态
show master status;
|
修改从(slave)服务器
server_id=178 log-bin=mysql-bin binlog_do_db=test |
change master to master_host='192.168.110.177',master_user='mysync',master_password='q123456', master_log_file='mysql-bin.000002',master_log_pos=343; |
启动同步
start slave |
检查从服务器复制功能状态
SHOW SLAVE STATUS
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
注意:在某台服务器重启后,又要重新同步,因为relay_log_pos的数值发生了改变。
读写分离
好处:负载均衡,不暴露真实ip
官网下载1.6.5版本
在linux上安装,修改两个配置文件 vim mycat/conf/server.xml vim mycat/conf/schema.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <!-- 读写都可用的用户 --> <user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">mycat_testdb</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <!-- 只读用户 --> <user name="user"> <property name="password">user</property> <property name="schemas">mycat_testdb</property> <property name="readOnly">true</property> </user> </mycat:server>
注意修改 ip 用户密码 和自己的匹配
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- TESTDB1 是mycat的逻辑库名称,链接需要用的 --> <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <!-- database 是MySQL数据库的库名 --> <dataNode name="dn1" dataHost="localhost1" database="test" /> <!-- dataNode节点中各属性说明: name:指定逻辑数据节点名称; dataHost:指定逻辑数据节点物理主机节点名称; database:指定物理主机节点上。如果一个节点上有多个库,可使用表达式db$0-99, 表示指定0-99这100个数据库; dataHost 节点中各属性说明: name:物理主机节点名称; maxCon:指定物理主机服务最大支持1000个连接; minCon:指定物理主机服务最小保持10个连接; writeType:指定写入类型; 0,只在writeHost节点写入; 1,在所有节点都写入。慎重开启,多节点写入顺序为默认写入根据配置顺序,第一个挂掉切换另一个; dbType:指定数据库类型; dbDriver:指定数据库驱动; balance:指定物理主机服务的负载模式。 0,不开启读写分离机制; 1,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡; 2,所有的readHost与writeHost都参与select语句的负载均衡,也就是说,当系统的写操作压力不大的情况下,所有主机都可以承担负载均衡; --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- 可以配置多个主从 --> <writeHost host="hostM1" url="192.168.52.145:3306" user="root" password="root"> <!-- 可以配置多个从库 --> <readHost host="hostS2" url="192.168.52.146:3306" user="root" password="root" /> </writeHost> </dataHost> </mycat:schema>
在bin里 启动 ./mycat start ------------(停止是 ./mycat stop)
接下来可以用nivacat 进行连接验证
mycat端口号为 8066