概述：
配置主从节点
配置master
以linux（192.168.10.10）上的mysql为master，宿主机（192.168.10.1）上的mysql为slave配置主从复制。
修改master的my.cnf如下
[mysqld] basedir=/export/server/mysql datadir=/export/data/mysql socket=/tmp/mysql.sock user=mysql server-id=10 port=3306 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd log-bin=mysql-bin # 开启二进位制日志 expire-logs-days=7 # 设定日志过期时间，避免占满磁盘 binlog-ignore-db=mysql # 不使用主从复制的数据库 binlog-ignore-db=information_schema binlog-ignore-db=performation_schema binlog-ignore-db=sys binlog-do-db=test #使用主从复制的数据库 [mysqld_safe] log-error=/export/data/mysql/error.log pid-file=/export/data/mysql/mysql.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
重启master
service mysqld restart
登入master检视配置是否生效（ON即为开启，预设为OFF）：
mysql> show variables like \'log_bin\'; ++-------+ | Variable_name | Value | ++-------+ | log_bin | ON | ++-------+
在master的数据库中建立备份账号：backup为使用者名称，%表示任何远端地址，使用者back可以使用密码1234通过任何远端客户端连线master
grant replication slave on *.* to \'backup\'@\'%\' identified by \'1234\'
检视user表可以看到我们刚建立的使用者：
mysql> use mysql mysql> select user,authentication_string,host from user; ++++ | user | authentication_string | host | ++++ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % | ++++
新建test数据库，建立一个article表以备后续测试
CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) DEFAULT NULL, `content` text, PRIMARY KEY (`id`) ) CHARSET=utf8;
重启服务并重新整理数据库状态到储存档案中（with read lock表示在此过程中，客户端只能读资料，以便获得一个一致性的快照）
[[email protected] ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [[email protected] mysql]# mysql -uroot -proot mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)
检视master上当前的二进位制日志和偏移量（记一下其中的File和Position）
mysql> show master status \\G 1. row File: mysql-bin.000002 Position: 154 Binlog_Do_DB: test Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys Executed_Gtid_Set: 1 row in set (0.00 sec)
File表示实现复制功能的日志，即上图中的Binary log；Position则表示Binary log日志档案的偏移量之后的都会同步到slave中，那么在偏移量之前的则需要我们手动汇入。
主服务器上面的任何修改都会储存在二进位制日志Binary log里面，从服务器上面启动一个I/O thread（实际上就是一个主服务器的客户端程序），连线到主服务器上面请求读取二进位制日志，然后把读取到的二进位制日志写到本地的一个Realy log里面。从服务器上面开启一个SQL thread定时检查Realy log，如果发现有更改立即把更改的内容在本机上面执行一遍。
如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进位制日志。此时可以稍做调整，将二进位制日志只给某一从，这一从再开启二进位制日志并将自己的二进位制日志再发给其它从。或者是干脆这个从不记录只负责将二进位制日志转发给其它从，这样架构起来效能可能要好得多，而且资料之间的延时应该也稍微要好一些
手动汇入，从master中汇出资料
mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql
将test.sql中的内容在slave上执行一遍。
配置slave
修改slave的my.ini档案中的[mysqld]部分
log-bin=mysql server-id=1 #192.168.10.1
储存修改后重启slave，WIN+R->services.msc->MySQL5.7->重新启动
登入slave检查log_bin是否以被开启：
show VARIABLES like \'log_bin\';
配置与master的同步复制：
stop slave; change master to master_host=\'192.168.10.10\', -- master的IP master_user=\'backup\', -- 之前在master上建立的使用者 master_password=\'1234\', master_log_file=\'mysql-bin.000002\', -- master上 show master status \\G 提供的资讯 master_log_pos=154;
启用slave节点并检视状态
mysql> start slave; mysql> show slave status \\G 1. row Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.10 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05 Master_Info_File: C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data\\master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
注意检视第4、14、15三行，若与我一致，表示slave配置成功
测试
关闭master的读取锁定
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
向master中插入一条资料
mysql> use test mysql> insert into article (title,content) values (\'mysql master and slave\',\'record the cluster building succeed!:)\'); Query OK, 1 row affected (0.00 sec)
检视slave是否自动同步了资料
mysql> insert into article (title,content) values (\'mysql master and slave\',\'record the cluster building succeed!:)\'); Query OK, 1 row affected (0.00 sec)
至此，主从复制的配置成功！：)
使用mysqlreplicate命令快速搭建 Mysql 主从复制
读写分离
读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读（如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave）。
方案一、定义两种连线
就像我们在学JDBC时定义的DataBase一样，我们可以抽取出ReadDataBase,WriteDataBase implements DataBase，但是这种方式无法利用优秀的执行绪池技术如DruidDataSource帮我们管理连线，也无法利用Spring AOP让连线对DAO层透明。
方案二、使用Spring AOP
如果能够使用Spring AOP解决资料来源切换的问题，那么就可以和Mybatis、Druid整合到一起了。
我们在整合Spring1和Mybatis时，我们只需写DAO界面和对应的SQL语句，那么DAO例项是由谁建立的呢？实际上就是Spring帮我们建立的，它通过我们注入的资料来源，帮我们完成从中获取数据库连线、使用连线执行SQL语句的过程以及最后归还连线给资料来源的过程。
如果我们能在呼叫DAO界面时根据界面方法命名规范（增addXXX/createXXX、删deleteXX/removeXXX、改updateXXXX、查selectXX/findXXX/getXX/queryXXX）动态地选择资料来源（读资料来源对应连线master而写资料来源对应连线slave），那么就可以做到读写分离了。
专案结构
引入依赖
其中，为了方便访问数据库引入了mybatis和druid，实现资料来源动态切换主要依赖spring-aop和spring-aspects
org.mybatismybatis-spring1.3.2org.mybatismybatis3.4.6org.springframeworkspring-core5.0.8.RELEASEorg.springframeworkspring-aop5.0.8.RELEASEorg.springframeworkspring-jdbc5.0.8.RELEASEcom.alibabadruid1.1.6mysqlmysql-connector-java6.0.2org.springframeworkspring-context5.0.8.RELEASEorg.springframeworkspring-aspects5.0.8.RELEASEorg.projectlomboklombok1.16.22org.springframeworkspring-test5.0.8.RELEASEjunitjunit4.12
资料类
package top.zhenganwen.mysqloptimize.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class Article { private int id; private String title; private String content; }
spring配置档案
其中RoutingDataSourceImpl是实现动态切换功能的核心类，稍后介绍。
dp.properties
master.db.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC master.db.username=root master.db.password=root slave.db.url=jdbc:mysql://192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC slave.db.username=root slave.db.password=root db.driverClass=com.mysql.jdbc.Driver
mybatis-config.xml
mapper界面和配置档案
ArticleMapper.java
package top.zhenganwen.mysqloptimize.mapper; import org.springframework.stereotype.Repository; import top.zhenganwen.mysqloptimize.entity.Article; import java.util.List; @Repository public interface ArticleMapper { List findAll; void add(Article article); void delete(int id); }
ArticleMapper.xml
select * from article insert into article (title,content) values (#{title},#{content}) delete from article where id=#{id}
核心类
RoutingDataSourceImpl
package top.zhenganwen.mysqloptimize.dataSource; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import java.util.*; /** * RoutingDataSourceImpl class * 资料来源路由 * * @author zhenganwen, blog:zhenganwen.top * @date 2018/12/29 */ public class RoutingDataSourceImpl extends AbstractRoutingDataSource { /** * key为read或write * value为DAO方法的字首 * 什么字首开头的方法使用读资料员，什么开头的方法使用写资料来源 */ public static final Map> METHOD_TYPE_MAP = new HashMap>; /** * 由我们指定资料来源的id，由Spring切换资料来源 * * @return */ @Override protected Object determineCurrentLookupKey { System.out.println("资料来源为："+DataSourceHandler.getDataSource); return DataSourceHandler.getDataSource; } public void setMethodType(Map map) { for (String type : map.keySet) { String methodPrefixList = map.get(type); if (methodPrefixList != null) { METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(","))); } } } }
它的主要功能是，本来我们只配置一个数据源，因此Spring动态代理DAO界面时直接使用该资料来源，现在我们有了读、写两个资料来源，我们需要加入一些自己的逻辑来告诉呼叫哪个界面使用哪个资料来源（读资料的界面使用slave，写资料的界面使用master。这个告诉Spring该使用哪个资料来源的类就是AbstractRoutingDataSource，必须重写的方法determineCurrentLookupKey返回资料来源的标识，结合spring配置档案（下段程式码的5，6两行）
如果determineCurrentLookupKey返回read那么使用slaveDataSource，如果返回write就使用masterDataSource。
DataSourceHandler
package top.zhenganwen.mysqloptimize.dataSource; /** * DataSourceHandler class * * 将资料来源与执行绪系结，需要时根据执行绪获取 * * @author zhenganwen, blog:zhenganwen.top * @date 2018/12/29 */ public class DataSourceHandler { /** * 系结的是read或write，表示使用读或写资料来源 */ private static final ThreadLocal holder = new ThreadLocal; public static void setDataSource(String dataSource) { System.out.println(Thread.currentThread.getName+"设定了资料来源型别"); holder.set(dataSource); } public static String getDataSource { System.out.println(Thread.currentThread.getName+"获取了资料来源型别"); return holder.get; } }
DataSourceAspect
package top.zhenganwen.mysqloptimize.dataSource; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.springframework.context.annotation.EnableAspectJAutoProxy; import org.springframework.stereotype.Component; import java.util.List; import java.util.Set; import static top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP; /** * DataSourceAspect class * * 配置切面，根据方法字首设定读、写资料来源 * 专案启动时会载入该bean，并按照配置的切面（哪些切入点、如何增强）确定动态代理逻辑 * @author zhenganwen,blog:zhenganwen.top * @date 2018/12/29 */ @Component //宣告这是一个切面，这样Spring才会做相应的配置，否则只会当做简单的bean注入 @Aspect @EnableAspectJAutoProxy public class DataSourceAspect { /** * 配置切入点：DAO包下的所有类的所有方法 */ @Pointcut("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..))") public void aspect { } /** * 配置前置增强，物件是aspect方法上配置的切入点 */ @Before("aspect") public void before(JoinPoint point) { String className = point.getTarget.getClass.getName; String invokedMethod = point.getSignature.getName; System.out.println("对 "+className+"$"+invokedMethod+" 做了前置增强，确定了要使用的资料来源型别"); Set dataSourceType = METHOD_TYPE_MAP.keySet; for (String type : dataSourceType) { List prefixList = METHOD_TYPE_MAP.get(type); for (String prefix : prefixList) { if (invokedMethod.startsWith(prefix)) { DataSourceHandler.setDataSource(type); System.out.println("资料来源为："+type); return; } } } } }
测试读写分离
如何测试读是从slave中读的呢？可以将写后复制到slave中的资料更改，再读该资料就知道是从slave中读了。==注意==，一但对slave做了写操作就要重新手动将slave与master同步一下，否则主从复制就会失效。
package top.zhenganwen.mysqloptimize.dataSource; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import top.zhenganwen.mysqloptimize.entity.Article; import top.zhenganwen.mysqloptimize.mapper.ArticleMapper; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:spring-mybatis.xml") public class RoutingDataSourceTest { @Autowired ArticleMapper articleMapper; @Test public void testRead { System.out.println(articleMapper.findAll); } @Test public void testAdd { Article article = new Article(0, "我是新插入的文章", "测试是否能够写到master并且复制到slave中"); articleMapper.add(article); } @Test public void testDelete { articleMapper.delete(2); } }
小编总结三篇关于MYSQL优化面试文章，希望对大家有帮助，可以收藏本文，关注小编哦，小编会努力更新滴！！！
小编特意给大家伙准备了一些程式设计视讯资料，需要的在文章MYSQL优化面试文章（一）中找领取途径！
最后，小编希望大家能转发给更多需要的人。欢迎大家留言交流讨论
