mysql主从配置

主从原理

mysql通过binlog 的传递来实现主从，主库binlog里记录了mysql执行的事件，从库获得binlog之后，回放binlog的事件，达到同步的目的。mysql当前写入的binlog会在每次重启或者FLUSH LOG等命令之后切换。
连接mysql之后执行 show binlog events ;可以查看binlog内容。

主库配置

log-bin=master-bin              #可选，建议配置，binlog名称前缀
log-bin-index=master-bin.index #可选，建议配置，binlog索引
server-id=1533306             #可选，一般配置，不配置则会跟随主机名（主机名会变化），并且主从不能一样，一般使用ip后3位+端口
#binlog-do-db=xxxx1          # 需要同步的数据库，一般不建议配置，如果配置do db，则ignore无效
#binlog-do-db=xxxx1          # 需要同步的数据库，一般不建议配置，如果配置do db，则ignore无效
binlog-ignore-db="mysql"    #忽略的数据库
binlog-ignore-db="test"     #忽略的数据库

从库配置

relay-log=slave-log-bin   #可选，建议配置，中转即从binlog名称前缀
relay_log_index=slave-log-bin.index   #可选，建议配置，中转即从binlog名称索引
server-id=1523306             #可选，一般配置，不配置则会跟随主机名（主机名会变化），并且主从不能一样，一般使用ip后3位+端口

启动mysql之后，开始建立主从关系

• 导出主库镜像。连接主库

  flush tables with read lock; #加上全局锁，阻止之后的更新事件，但是select可能会受到更新事件影响。
  show master status;  # 查看锁住之后的master的binlog文件和位置，要记下来，后面需要用到
  # 假设binlog当前文件为 master-bin.000001 ,位置为 5827
  #使用mysqldump导出数据库,这句在命令行运行。该命令可能会持续很久。注：一般安全起见不直接在命令行输密码
  mysqldump --all-databases --host=192.168.0.152 --port=3306 --user=root --password=xxxxx >backup.sql
  #导出完毕，解开全局锁
  unlock tables;
  

• 建立初始从库，导入从主库获得的镜像。
  mysql --host=192.168.0.153 --port=3306 --user=root --password=xxxxx <backup.sql
  如果直接导入失败，可以先连接 mysql 再使用 source backup.sql或其它方式导入数据。
• 建立连接。连接slave之后，运行以下sql。成功建立主从。

change master to master_host="192.168.0.153",master_port=3306,master_user="root",master_password="xxxxx",master_log_file="master-bin.000001",master_log_pos=5827;
start SLAVE;

其它说明

• 重启从库mysql，slave会自动进行连接。从上一次的binlog去主机取binlog，因此，就算从机或主机停了一段时间，只要binlog正常，状态就会达到同步。 需要更换或停止slave执行以下sql。

		stop slave;   -- 停止复制
		reset slave ;  -- 重置复制状态
		change master to master_host="192.168.0.153",master_port=3306,master_user="root",master_password="xxxxx",master_log_file="master-bin.000001",master_log_pos=5827; --修改主机信息
		start slave ; -- 开启复制。

• 主库的slave帐号密码问题，为安全起见和方便控制，一般会为每组从机建立单独的特定从权限的账号密码。如下，这样子要使某组从机失效，只需要控制这个帐号即可。

		GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'password'; 
		-- 这里其实不需要 REPLICATION CLIENT，但是一般会同时赋予，这样可以方便的交换主从角色。

• 如果要控制管理复制的

		GRANT REPLICATION SLAVE,RELOAD,CREATE USER,SUPER ON *.* TO master@'192.168.0.%' WITH GRANT OPTION;
		-- CREATE USER 用于创建和删除用户
		-- REPLICATION SLAVE 权限用于复制
		-- 执行FLUSH LOG命令需要RELOAD权限
		-- 执行SHOW MASTER STATUS 和 SHOW SLAVE STATUS  需要SUPER或REPLICATION CLIENT 权限。
		-- 执行CHANGE MASTER TO 需要SUPER 权限

从其它从库建立从库

• 如果已经存在SLAVE1，要增加SLAVE2，那么可以从SLAVE1得到SLAVE2。和从master类似，步骤如下：
  • 停止 SLAVE1 的复制，stop slave;
  • flush tables with read lock;然后 dump 出数据。
  • 不同于从master复制使用 show master status;这里要使用 show slave status;然后记录Relay_Master_Log_File binlog文件名称和 Exec_Master_Log_Pos binlog位置，假设也为 为 master-bin.000001 ,位置为 5827
  • 同上，在 SLAVE2 执行

change master to master_host="192.168.0.153",master_port=3306,master_user="root",master_password="xxxxx",master_log_file="master-bin.000001",master_log_pos=5827;
start SLAVE;