mysql主从同步最佳详细步骤

一、原理

  MYSQL主从复制的原理是主服务器数据库的每次操作都会记录在mysql下的data目录中二进制文件mysql-bin.xxx里，从服务器的I/O线程使用专用账号登录到主服务器中读取该二进制文件，并将文件内容写入到从服务器的relay-log日志文件中，然后从服务器的SQL线程会根据relay-log日志中的内容执行SQL语句。

1、binlog模式

SBR模式

  SBR(statement-based replication)只执行SQl语句，SBR的日志量取决于SQl的情况，SBR不会记录每一行的变化，节约了binlog日志量,减少IO,提高性能。

• SBR缺点
    1、 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候，
    2、调用具有不确定因素的UDF时复制也可能出问题
    3、使用以下函数语句无法复制：
  * LOAD_FILE()
  * UUID()
  * USER()
  * FOUND_ROWS()
  * SYSDATE()
    4、执行INSERT … SELECT 会产生比RBR更多的行级锁
    5、复制需要进行全表扫描的UPDATE时，需要比RBR请求更多的行级锁
    6、对于有AUTO_INCREMENT字段的INNODB表，INSERT语句会阻塞其他的INSERT.
    7、对于一些复杂的语句，在服务器上的耗资情况会更严重，而RBR模式下，只会对那个发生变化的记录产生影响
    8、从节点mysql数据库的数据表必须和主服务器一致才可以，否则容易导致同步出错。

  RBR模式（ROW-BASED REPLICATION）

  • 优点
    • 任何情况都可以被复制，对于数据来说是最安全可靠的，和大多数数据库系统复制技术一样
    • 从服务器上的表如果有主键，复制会快很多
    • 复制一下语句时行级锁少
      • INSERT … SELECT
      • 包含AUTO_INCREMENT字段的INSERT日志
      • 没有附带条件或者没有修改很多记录的UPDATE、DELETE语句
    • 执行INSERT、UPDATE、DELETE语句时锁更少
    • 服从器上采用多线程来执行复制称为可能
  • RBR缺点
    • binlog日志大了很多
    • 复杂的回滚时binlog中包含大量的数据
    • 主服务器上执行UPDATE语句时，所有发生变化的记录都会写入到binlog中，而SBR只会写一次，这会导致频繁发生binlog并发写问题
    • UDF产生的大量BLOB值会导致复制变慢
    • 无法从binlog中看到都复制写了什么语句
    • 当在非事物表上执行一段堆积的SQL语句时，最好采用SBR模式，否则容易导致主从服务器的数据不一致情况发生

2、作用

• 数据分布
• 负载均衡
• 备份
• 高可用性和容错行

3、主从模式

本案例讲述为一主一从

• 一主一从
• 一主多从
• 多主一从

二、主从配置

1、master节点

配置文件

  1、修改mysql的配置文件。
vim /etc/my.cnf

log-bin = mysql-bin
server_id = 1
binlog-do-db = hain_res #如果有多个库同步，重复设置此参数
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 1
expire_logs_days = 7
log_bin_trust_function_creators = 1

2、重启mysql
  重启mysql使配置生效。

systemctl restart mysql

创建用户

1、创建同步专用账号
%：代表所有的地址都可以使用此用户进行访问，建议换成指定的从库的地址。

GRANT REPLICATION SLAVE ON *.* to 'mysyxs'@'%' identified by '****'; 

2、刷新

flush privileges;

查看id和文件名称

1、登录mysql

mysql -h127.0.0.1 -uroot -p****

2、复制id和日志文件名称

  show master status;

2、slave节点

slave配置

  1、开启relaylog
修改/etc/my.cnf配置文件

vim /etc/my.cnf
relay-log = /data/3307/relay-bin
server-id = 2

2、重启mysql服务

systemctl resatrt mysqld

设置replication

1、登录mysql

mysql -h127.0.0.1 -uroot -p****

2、设置replication

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',  
    -> MASTER_PORT=3306,
    -> MASTER_USER='mysqlxs',
    -> MASTER_PASSWORD='*****',
    -> MASTER_LOG_FILE='mysql-bin.0003',根据master的日志文件名进行修改
    -> MASTER_LOG_POS=337;#根据master的id进行修改

3、启动slave

mysql> start slave;

4、查看slave

mysql> show slave status\G

三、主从同步测试

1、在master节点创建表并插入数据（略）
2、在slave节点进行查看结果（略）

才华不溢，梦想依旧
唯心沉淀，遨游宇宙