mysql 主从搭建，读写分离

基于日志点复制

最早支持。bug较少

对查询sql没有任何限制

缺点：主节点挂了以后新的日志节点难以获取

在主DB上建立服务账号并授权 ,该账号只能在从服务器登录

#创建用户
CREATE USER 'repl'@'192.168.2.115' IDENTIFIED BY '123123'
#授权
grant REPLICATION SLAVE on *.* to 'repl'@'192.168.2.115';

主节点配置

#开启binlog
log-bin=mysql-bin
#日志id,整个集群必须唯一
server-id=1921682114
#日志格式
binlog_format=mixed
#启动日志事务，防止极端情况下日志与数据不一致情况
innodb_support_xa=NO

从节点配置

#开启binlog
log-bin=mysql-bin
#日志id,整个集群必须唯一
server-id=1921682115
#中继日志名称，数据库Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入到中继日志中，然后在Slave数据库执行修改操作。这就是中继日志Relay Log。
relay_log=mysql-relay-bin
#是否把中继日志执行的sql记录到二进制日志中
log_slave_updates=on
#是否只读该数据库
read_only=on

初始化数据库，将主数据库数据复制到从数据库中，并且获取日志点

• mysqldump (会对备份表加锁)

  mysqldump --single-transaction --master-data --triggers --routines --databases  test  >>all.sql -uroot -proot
  

  • –single-transaction 事务隔离保证备份的数据是一直的

    ① 在开始的时候把该 session 的事务隔离级别设置成 repeatable read ；

    ② 然后启动一个事务（执行 begin ），备份结束的时候结束该事务（执行 commit ）

  • –master-data

    该参数用于将主库的bin-log信息写入到dump文件中，即当前文件名(filename)和位置（position），用于主从复制的搭建

  • –triggers

    包含表的触发器

  • –routines

    存储过程

• xtrabackup --slave-info

​ mysql -uroot -proot < all.sql

从节点 启动服务链路

在mysqldump 导出文件中找到 CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000004’, MASTER_LOG_POS=1091;

#配置链路信息
change master to master_host='master_ip',
master_user='repl',
master_password='root',
master_log_file='mysql-bin.000004',
master_log_pos=1091;
#启动链路
start slave;

通过 show slave status\G; 查看链路信息

基于GTID复制

5.6 版本开始，GTID即全局事务id,create select … 语句无法使用。5.6早期的版本bug较多。建议在5.6后期的版本以及5.7以上的版本中使用

• 在主DB上建立服务账号并授权 ,该账号只能在从服务器登录

  #创建用户
  CREATE USER 'repl'@'192.168.2.115' IDENTIFIED BY '123123'
  #授权
  grant REPLICATION SLAVE on *.* to 'repl'@'192.168.2.115';
  

• 主节点配置

  #开启binlog
  log-bin=mysql-bin
  #日志id,整个集群必须唯一
  server-id=1921682114
  #日志格式
  binlog_format=mixed
  #其实日志事务，防止极端情况下日志与数据不一致情况
  innodb_support_xa=NO
  #启用gtid
  gtid_mode=on
  
  enforce-gtid-consistency
  #5.7以下必须使用这个配置
  log-slave-updates=on
  

• 从库配置

  #日志id,整个集群必须唯一
  server-id=1921682115
  #中继日志名称，数据库Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入到中继日志中，然后在Slave数据库执行修改操作。这就是中继日志Relay Log。
  relay_log=mysql-relay-bin
  #启用gtid
  gtid_mode=on
  enforce-gtid-consistency
  
  log-slave-updates=on
  #建议,将复制节点信息存储到表中
  master_info_repository=TABLE
  relay_log_info_repository=TABLE
  

• 同步主从库数据

  mysqldump --single-transaction --master-data=2 --triggers --routines --databases  test  >>all.sql -uroot -proot
  

  mysql -uroot -proot < all.sql

• 配置链路

  #配置链路信息
  change master to master_host='192.168.2.114',
  master_user='repl',
  master_password='root',
  master_auto_position=1;
  #启动链路
  start slave;