Skip to content

16.mysql5.6主从同步.md

Latest commit

 

History

History
258 lines (234 loc) · 9.41 KB

16.mysql5.6主从同步.md

File metadata and controls

258 lines (234 loc) · 9.41 KB
Raw

MySQL5.6 数据库主从同步配置详解以及读写分离实现

一、安装环境

操作系统 ：CentOS 6.5 
数据库版本：MySQL 5.6
主机A：192.168.1.1 （Master）
主机B：192.168.1.2 （Slave）
这里强调的数据库的版本，是因为MySQL在5.6之前和之后的安装方式是不一样的
注：需要开放主库的端口

二、Master的配置

1.MySQL的配置文件的位置是在 /etc/my.cnf,在该文件下指定Master的配置如下：

log-bin=mysql-bin
server-id=1
binlog-do-db=ufind_db
expire_logs_days=7

参数解释：
log-bin：定义存放待同步日志的日志名，默认位于配置文件datadir定义的目录下
server-id：用于标识唯一的数据库
binlog-do-db：指定需要同步的数据库
expire_logs_days：表示日志保留的天数

2.重启mysql

service mysqld restart

3.进入mysql

mysql -uroot -p 回车，输入数据库密码进入

4.赋予从库权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

5.重启mysql，登录mysql，显示主库信息

mysql> show master status;
+-------------------+----------+---------------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB        | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+---------------------+------------------+-------------------+
| master-bin.000008 |      120 | enterprise_prophesy |                  |                   |
+-------------------+----------+---------------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>
 
参数解释：
这里的 File 、Position 是在配置Slave的时候要使用到的，Binlog_Do_DB表示要同步的数据库，Binlog_Ignore_DB 表示Ignore的数据库，这些都是在配置的时候进行指定的。
另外：如果执行这个步骤始终为Empty set(0.00 sec)，那说明前面的my.cnf没配置对。

三、Slave的配置

1.从库的配置，首先也是修改配置文件：/etc/my.cnf 如下：

log-bin=mysql-bin
server-id=2
replicate-do-db=enterprise_prophesy
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
character-set-server=utf8 
collation-server=utf8_general_ci 
sql_mode='NO_ENGINE_SUBSTITUTION'
lower_case_table_names=1
wait_timeout=388000
interactive_timeout=388000

2.重启mysql

service mysqld restart

3.进入mysql，配置主从同步

mysql> change master to master_host='192.168.1.1',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000008', master_log_pos=120;

4.从库开启数据库复制功能

mysql> start slave;

5.查看配置的信息

mysql> show slave status \G

2.png 查看两个参数，Slave_IO_Running和Slave_SQL_Running，他们都为yes时表示功能开启成功 至此配置结束了。

###注：根据mysql安装方式的不同，在192.168.88.143/142 上操作数据库时命令如下：

登录命令：/data/mysql56-3306/bin/mysql -h 192.168.88.142 -P3306 -uroot -p
重启mysql：ps -ef | grep mysql 先杀mysqld_safe再杀mysqld，然后/data/mysql56-3306/bin/mysqld_safe --defaults-file=/data/mysql56-3306/my.cnf &
导出sql:mysqldump -h192.168.88.143 -P 3306 -uroot -proot 数据库名 > mysql_20200319.sql

四、读写分离（基于springboot）

1.修改SpringBoot配置文件

# 数据库连接URL
spring.datasource.master.url=jdbc:mysql://localhost:3306/enterprise_prophesy?characterEncoding=utf8
# 数据库用户名
spring.datasource.master.username=root
# 数据库密码
spring.datasource.master.password=root
# 数据库驱动
spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.slave.url=jdbc:mysql://localhost:3307/enterprise_prophesy?characterEncoding=utf8
spring.datasource.slave.username=root
spring.datasource.slave.password=root
spring.datasource.slave.driver-class-name=com.mysql.jdbc.Driver

2.启动时读取多数据源

@Configuration
public class DataSourceConfig {

   @Bean
   @ConfigurationProperties("spring.datasource.master")
   public DataSource masterDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean
   @ConfigurationProperties("spring.datasource.slave")
   public DataSource slave1DataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "dataSource")
   @Primary
   public DataSource DataSourceContextHolder(@Qualifier("masterDataSource") DataSource masterDataSource,
                                @Qualifier("slave1DataSource") DataSource slave1DataSource) {
      Map<Object, Object> targetDataSources = new HashMap<>();
      targetDataSources.put("master", masterDataSource);
      targetDataSources.put("slave", slave1DataSource);
      DataSourceContextHolder myRoutingDataSource = new DataSourceContextHolder();
      myRoutingDataSource.setDefaultTargetDataSource(slave1DataSource);
      myRoutingDataSource.setTargetDataSources(targetDataSources);
      return myRoutingDataSource;
   }

}

注：在SpringBoot启动类中创建内部类DataSouceConfig，也可以在外部创建，然后在启动类中导入。
    首先先创建两个数据源masterDataSource和slaveDataSource并添加注解@ConfigurationProperties读取配置文件；
    最后再加上路由数据源，用它来存储配置的数据源，切换数据源就是根据map中存放的数据源来获取；
    setDeafultTargetDataSource是设置启动时的默认数据源，并在该方法上加上@Primary注解，启动项目时默认使用这个数据源。

3.实现多数据源切换

package com.jspxcms.core;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DataSourceContextHolder extends AbstractRoutingDataSource {

    public static final String READ_DS = "slave";
    public static final String WRITE_DS = "master";

    private static final ThreadLocal<String> contextHolder = new InheritableThreadLocal<String>();
    private final Logger log = LoggerFactory.getLogger(this.getClass());

    // 设置数据源名
    public static void setDB(String dbType) {
        contextHolder.set(dbType);
    }

    // 获取数据源名
    public static String getDB() {
        return (contextHolder.get());
    }

    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        String typeKey = DataSourceContextHolder.getDB();
        if (typeKey == DataSourceContextHolder.WRITE_DS) {
            System.out.println("使用了写库");
        } else {
            System.out.println("使用了读库");
        }
        return typeKey;
    }
}

注：创建类DataSourceContextHolder并继承AbStractRoutingDataSource，
   重写他的determineCurrentLookupKey方法，用来返回当前数据源，
   这里面两个常量的值就是项目启动时创建的map的key，所以要保持一致

4.测试

@Transactional(propagation = Propagation.SUPPORTS)
public InfoDetail save(InfoDetail detail, Info info) {
  DataSourceContextHolder.setDB(DataSourceContextHolder.WRITE_DS);
  info.setDetail(detail);
  detail.setInfo(info);
  detail.applyDefaultValue();
  dao.save(detail);
  DataSourceContextHolder.clearDB();
  return detail;
}

注：1.在Controller中找到需要切换数据源的方法，在service方法之前添加DataSourceContextHolder.setDB(DataSourceDontextHolder.WRITE_DS),手动切换数据源
    2.并在service方法执行完成之后调用DataSourceContextHolder.clearDB()，切换回默认数据源
    3.进入Service层，修改Transaction注解的事务传播行为，设置为SUPPORTS

五、注意事项

1.分析读写分离的解决方案以及局限性

a.目前的解决方案需要在程序中手动指定数据源，比较麻烦，如果很多地方需要使用，可以通过AOP思想来解决这个问题。
b.建议数据访问层使用jdbc、ibatis，不建议hibernate
c.不支持@Transactional注解事务，此方案要求所有读方法必须是read-only=true，因此如果是@Transactional，这样就要求在每一个读方法头上加@Transactional且readOnly属性=true，相当麻烦

2.出现Navicat连不上mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pwd123.com' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

3.修改MySQL端口后重启不了

关闭 SELinux
[root@dytl05 ~]# setenforce 0

4.项目启动过程中报错 "Access denied for user 'root'@'%' to database 'xxx'"

a、创建数据库
> create database mytest;
b.连接数据库，报以下错：
> Access denied for user 'root'@'%' to database 'mytest'
原因：创建完数据库后，需要进行授权，在本地访问一般不会存在这个问题。
c.授权数据库操作
> grant all on xxx.* to 'root'@'%' identified by 'password' with grant option;
其中：xxx代表创建的数据库; password为用户密码，在此为root的密码。

5.项目启动过程中报错 "找不到相应的表"

在mysql的配置文件my.cnf的[mysqld]节点下加入
lower-case-table-names=1

6.修改my.cnf的log-bin的存储位置，启动mysql报错“权限不足”

# chown mysql.mysql -R  /home/mysql

7.主从同步设置的重要参数log_slave_updates

参考网址：https://blog.csdn.net/liqfyiyi/article/details/50978284