MySQL的主从配置

主从配置常用作考虑在主服务器上做读写操作,从服务器相当于备用节点。但也有主服务只写,slave只读这种达到读写分离。如果有多个从服务器,那其实每个从服务器可以单独来读,如数据分析、远程调用等而不暴露主库。

记得面试时问我MySQL的主从是怎样的,虽然使用过MySQL,但都是单机部署经验,随口一说结果被面试官问住了,回头亲自实践了下作个记录。

摘录一段原理:

引用文字
MySQL数据库的每次更新变化都会以“events”的方式记录到一个二进制的log文件,从库可以配置从主库中读取这些log文件,并在从库的本地执行,这样便可以把主库的数据同步到从库。
每个从库都会从主库读取完整的log文件,所以需要从库来记录“上次”同步的位置。这种设计可以保证不同的从库可以独立工作,更新不同的内容。即使从库崩溃了,只要本地记录了上次更新的位置,依然可以把主库的数据同步回来。

这个二进制log 就是"binary log", 主要作用是主从复制,备份恢复。

一。 在Master上

  1. 修改my.cnf配置文件,
[mysqld]
log-bin=mysql-bin
// 全局唯一的ServerId用来标示服务器在主从集群中的位置
server-id=1
binlog-do-db = blog
log-slave-updates
sync_binlog = 1
log_bin_trust_function_creators = 1

配置修改完后重启mysqld

  1. mysql登录master库, 创建slave用于访问master的用户
grant replication client,replication slave on *.* to root@'192.168.1.%' identified by '123456';
flush privileges;

二、在Slave上

  1. 配置my.cnf
server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
replicate-do-db = blog
slave-net-timeout = 60
log_bin_trust_function_creators = 1
  1. 重启myqld
  2. 执行
change master to master_host='10.11.6.11',master_user='root',
master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=107;

其中mysql-bin.000003 , master_log_pos, 可以在master的mysql上查询:
show master status\G;
结果如:

*************************** 1. row ***************************
             File: mysql-bin.000008
         Position: 336085
     Binlog_Do_DB: blog
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

其实如果是新建的slave结点,创完库后,也可以将master_log_pos改为0, 这样会自动同步已有表结构和数据。

  1. 执行 start slave。 可用show slave status\G; 查看状态。

三,踩的坑如下:
Slave上查询show slave status\G; 可能会有Last_SQL_Error, 比如从数据库未创,需要先创个数据库,再有表结构已经有了,无法复制等错误,排查完后需要 stop slave再 start slave.
这两个如果是Yes那就是正常的。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes