主从原理
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 执行
- 停止 SLAVE1 的复制,
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;