ibatis mysql 主从_accumulation/16.mysql5.6主从同步.md at master · csdn23/accumulation · GitHub...

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

查看两个参数，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 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 contextHolder = new InheritableThreadLocal();

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