读写分离、分库、分表

mysl主从复制

rpm -ivh http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm

yum install mysql-community-server -y 

systemctl start mysqld
systemctl stop mysqld
systemctl enable mysqld

grep "password" /var/log/mysqld.log

set global validate_password_policy=0;
##修改密码
set password for root@localhost = password('12345678');
或者
alter user 'root'@'localhost' identified by '12345678';

grant all on *.* to root@'%' identified by '12345678';
flush privileges;

主 192.168.124.133
vim /etc/my.cnf
```xml
	server-id=100
	log-bin=mysql-bin
	binlog-ignore-db=mysql
	binlog_cache_size=1M
	binlog_format=mixed
```
从 192.168.124.132
vim /etc/my.cnf
```xml
	server-id=102
	log-bin=mysql-slave-bin
	relay_log=edu-mysql-relay-bin
	binlog-ignore-db=mysql
	##如果需要同步函数或者存储过程
	log_bin_trust_function_creators=true
	## 为每个session分配的内存
	binlog_cache_size=1M
	##主从复制的格式
	binlog_format=mixed
	##1062错误是一些主键重复, 1032是因为主从数据库数据不一致
	slave_skip_errors=1062
```
grant replication slave, replication client on *.* to 'root'@'192.168.124.132' identified by '12345678';
flush privileges;

select user,host from mysql.user;
show master status;
二进制日志文件名
从
change master to master_host='192.168.124.133',master_user='root',master_password='12345678',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=623;

start slave;
show slave status\G;

##mysql镜像服务器因错误停止的恢复 Slave_SQL_Running on
stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status\G;

　　

读写分离

server:
  port: 6572

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      # 给每个数据源取别名
      names: ds1,ds2,ds3
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        drive-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.124.133:3306/sharding-jdbc?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: 12345678
        maxPoolSize: 100
        minPoolSize: 5
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        drive-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.124.132:3306/sharding-jdbc?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: 12345678
        maxPoolSize: 100
        minPoolSize: 5
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        drive-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.124.132:3306/sharding-jdbc?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: 12345678
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      # 读写分离
      default-data-source-name: ds1
    masterslave:
      name: ms
      master-data-source-name: ds1
      slave-data-source-names: ds2,ds3
      # 从 round_robin random
      load-balance-algorithm-type: round_robin
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.avision.shardingjdbc.domain