mysql主从复制与shardingjdbc读写分离

一、本次准备了一虚拟机(从机)，一本地机（主机）(192.168.1.11 127.0.0.1)，

虚拟机使用docker技术进行mysql数据库的拉取，本地机下载mysql

1、下载镜像文件
docker pull mysql:5.7
2、创建实例并启动
docker run -p 3306:3306 --name mysql \ 
　　-v /mydata/mysql/log:/var/log/mysql \ 
　　-v /mydata/mysql/data:/var/lib/mysql \ 
　　-v /mydata/mysql/conf:/etc/mysql \ 
　　-e MYSQL_ROOT_PASSWORD=root \ 
　　-d mysql:5.7
参数说明 -p 3306:3306：将容器的 3306 端口映射到主机的 3306 端口 -v /mydata/mysql/conf:/etc/mysql：将配置文件夹挂载到主机 -v /mydata/mysql/log:/var/log/mysql：将日志文件夹挂载到主机 -v /mydata/mysql/data:/var/lib/mysql/：将配置文件夹挂载到主机-e MYSQL_ROOT_PASSWORD=root：初始化 root 用户的密码
3、创建初始化配置文件（/mydata/mysql/conf/my.cnf）

  [client]
  default-character-set=utf8
  [mysql]
  default-character-set=utf8
  [mysqld]
  init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8
  #collation-server=utf8_unicode_ci
  skip-character-set-client-handshake
  skip-name-resolve

 

二、配置master数据库配置文件（my.cnf/my.ini）127.0.0.1

#Server Id.主从复制，此为主机master,从数据库必须不一样
server-id=1
log-bin=mysql-bin
#设置logbin格式STATEMENT/ROW/MIXED
binlog_format=STATEMENT

#[可选] 0（默认）表示读写（主机），1表示只读（从机） 
read-only=0

　# 设置不要复制的数据库(可设置多个,不同步系统数据库)
  binlog-ignore-db=mysql
  binlog-ignore-db=information_schema
  binlog-ignore-db=performance_schema

  #设置需要复制的数据库
  #binlog-do-db=shardingdb_1

三、配置从数据库（my.cnf）192.168.1.11

#主从复制
relay-log=mysql-relay
#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=2
#设置需要同步的数据库,区别replicate_wild_do_table
#从库(slave)使用replicate_do_db和replicate_ignore_db两个参数时在主库操作需要使用usedb;然后再进行其他操作,不然从库(slave) 会报 
#例如: 'Table 'dbxx.table01 ' doesn't exist' 错误.MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句在Slave上会被忽略。

#replicate_wild_do_table=shardingdb_.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

 

四、授权（主数据库master）

mysql ‐h localhost ‐uroot ‐p  //进入到mysql的客户端
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.1.11' IDENTIFIED BY '123456';  //给账号slave1授权可复制，并规定从192.168.1.11地址链接
#刷新权限 
FLUSH PRIVILEGES;
#确认位点 记录下文件名以及位点,用于从数据库的复制开始位置 
show master status;

五、从数据库配置

mysql ‐h localhost ‐P3307 ‐uroot ‐p  //进入到mysql的客户端

#修改从库指向到主库，使用上一步记录的文件名以及位点

CHANGE MASTER TO master_host = '主数据库ip', master_user = 'slave1', master_password = '123456', master_log_file = 'mysqlbin.000212', master_log_pos = 487; 

#启动同步 
START SLAVE;   //开启同步
STOP  SLAVE;   //暂停同步
RESET SLAVE；  //重置从数据库的配置，需要从修改从数据库指向到主库开始配置，然后再开启同步

查看slave状态：必须满足从库中的IO线程（从主库中读取binlog日志）和sql线程（读取relay日志进行数据操作的运行增、删、改）运行状态（YES）

　　

EG：遇到的问题

1、主数据库授权不成功，报错（首先需要肯定的一点就是用户名和密码不会出错）

 

解决步骤：

1）首先知道用户信息存储在数据库mysql（系统库）的user表中

　　SELECT HOST,USER,grant_priv FROM mysql.user WHERE USER='root'  ; //查询当前root用户所拥有的权限，

　　grant_priv 表示此用户是否具有给其他用户授权的权限

　　UPDATE mysql.user SET Grant_priv='Y' WHERE USER='root' AND HOST='127.0.0.1'; 

//更新用户的权限，使其具有给用户授权的权限，确定host为127.0.0.1的host拥有授权权限

2）使用 mysql -h 127.0.0.1 -uroot -proot 进入客户端此时再进行授权

 

 

 

2、当误操作导致root用户不能进行密码链接数据库的时候（可能在mysql.user表中直接修改密码等导致不是加密密码），不管是进行命令行链接还是SQLyog等第三方工具链接的解决方法（密码错误）

需要暂停mysql的整个服务，并在配置文件最后配置   skip-grant-tables   可使mysql -uroot -p之后输入密码项回车进入客户端，不进行密码的验证

 

SELECT * FROM mysql.user WHERE USER='root' AND HOST='127.0.0.1'\G;  //查看用户的权限和密码，当authentication_string是密文状态表明是加密的密码，如果是明文那么说明密码不可用

>update mysql.user set authentication_string = password('123456') where user='root';   //对用户的密码进行加密处理

>flush privileges;

关闭mysql的整个服务，然后去掉配置文件的skip-grant-tables，重启mysql服务

 

 

 六、进行shardingjdbc读写分离

# shardingjdbc分片策略
# 配置数据源，给数据源起名称,
# 水平分库，配置两个数据源
spring.shardingsphere.datasource.names=m0,s0

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true
# 主服务器
spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.jdbcUrl=jdbc:mysql://localhost:3306/master_slave?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456

# master_slave 从服务器
spring.shardingsphere.datasource.s0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.jdbcUrl=jdbc:mysql://192.168.1.11:3306/master_slave?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root

# 主库从库逻辑数据源定义 ds0 为mytbl

spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0 
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0 
# 配置数据库里面 mytbl专库专表 
spring.shardingsphere.sharding.tables.mytbl.actual-data-nodes=ds0.mytbl 

　　

测试

//#devfenli #读写分离，主从复制
@Test
public void testZCFZ_Read(){
    List<Mytbl> mytbls = mytblMapper.selectList(null);
    for (Mytbl mytbl : mytbls) {
        System.out.println(mytbl);
    }
}
//#devfenli #读写分离，主从复制
@Test
public void testZCFZ_Write(){
    Mytbl mytbl=new Mytbl();
    mytbl.setId(9);
    mytbl.setName("4234fe");
    mytblMapper.insert(mytbl);
}

 测试读写分离可以将从库的数据与主库的数据不一致，当查询的时候看查出的内容就

//创建测试表
create table `mytbl` (
`id` int (11),
`NAME` varchar (48)
);
//实体类
@Data
public class Mytbl {
    private Integer id;
    private String name;
}

@Repository
public interface MytblMapper extends BaseMapper<Mytbl> {
}