环境简介
主服务器: CentOS6.5 下 mysql5.6.30
安装请参考这里网址: https://www.linuxidc.com/Linux/2018-08/153813.htm
从服务器: Win7 下 5.6.17
原理简介及优缺点
MySQL 从 3.23 版本开始提供复制功能, 复制主要是指将主服务器的 DDL 和 DML 操作, 通过二进制日志(binLog 日志), 传到服务的服务器上, 然后在从服务器上对这些日志从新执行, 从而实现从服务器与主服务器的数据同步. MySQL 支持一台主服务器同时向多台从服务器进行复制, 从服务器同时也可以作为其他服务器的主服务器, 实现链状的复制.
优点
如果主服务器出现问题, 可以快速切换到从服务器提供服务.
可以在从服务器上执行查询, 降低主服务器的压力
可以在从服务器上执行备份, 以避免备份期间影响主服务器的性能
局限
由于 MySQL 实现的是异步复制, 所以主从服务器之间的数据存在一定差异, 对实时性要求高的数据仍然需要从主服务器上获得.
前期准备
1. 在 linux 下创建账号
在 win 的命令提示符中输入 ipconfig, 查看 ip
因为我的 win 下 ip 为 192.168.153.1, 所以, 我创建将 Host 设置为 192.168.153.%, 关于原因, 可参考这里
2. 赋予权限
REPLICATION SLAVE 权限针对所有的数据库, 只能通过. , 而不能 shool.*, 因为 REPLICATION SLAVE 是复制 binlog 日志.
3. 将主服务器中的数据, 复制到从服务器, 确保两者搭建主从之前, 数据一致. 关于这, 之前写过一篇文章专门介绍, 可以参考这里
配置
主服务器配置
1. 开启 binlog 日志, 并设置 server_id
从服务器配置
1. 配置 server_id
设置: server-id=2
2. 指定主服务器配置格式如下
- change master to
- master_host='master_host_name', // 从服务器的主机
- master_user='replication_user_name',// 执行复制用户名
- master_password='replication_password',// 执行复制用户密码
- master_log_file='recorded_log_file_name',// 二进制日志文件名
- master_log_pos='recorded_log_postion';// 复制开始位置
根据我的配置信息
bin-log 信息
复制用户信息
我执行的配置代码如下:
- change master to
- master_host='192.168.153.140',
- master_user='lidequan',
- master_password='lidequan',
- master_log_file='bin-log.000003',
- master_log_pos=120;
查看从服务器是否已连接主服务器
执行 start slave;
执行 show processlist;
修改主服务器数据, 查看同步效果
在这里修改主服务器数据, 主要是执行插入, 更新操作.
操作之前, 数据信息如下
执行插入操作
insert into `class` (`name`) values ('三年二班'),('三年五班'),('三年七班');
此时从服务器中也有数据了
执行更新操作
update class set `name`='三年三班' where id=3;
观察下图, 发现数据确实发生了变化
删除操作
观察下图, 数据也是同步的
数据表定义操作
1. 添加一个 student 表
- CREATE TABLE student(
- `id` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT'学生编号',
- `name` CHAR(10) NOT NULL DEFAULT ''COMMENT'学生名',
- `class_id` INT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT'班级编号',
- PRIMARY KEY(`id`)
- ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
2. 往 student 表中添加一个字段
ALTER TABLE student ADD COLUMN age TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT'学生年龄';
指定负责的数据库或者表
可以通过 replicate-do-db,replicate-do-table,replicate-ignore-db,replicate-ignore-table 或者 replicate-wild-do-table 来指定主从数据库复制到从数据库的数据库或者表.
关于数据复制的一些重要参数
在上面搭建主从服务器的时候, 使用了 MASTER_HOST,MASTER_PORT,MASTER_USER,MASTER_PASSWORD,MASTER_LOG_FILE,MASTER_LOG_POS 这些参数都要在从服务器上配置, 下面再来说几个常用的启动选项, 如 log_slave_updates,read_only,master_verify_checksum
log_slave_updates
log_slave_updates 这个参数用来配置从服务器上的更新操作是否写入二进制日志, 默认是不打开的.
首先我们来看一下刚刚 win 下 mysql 的 binlog 日志内容
可以发现, 刚刚我们执行了增, 删, 改等操作, 它并没有记录.
我们可以看到 log_slave_updates 是没有启动的
且该属性是只读属性, 不可以动态的设置, 只能在配置文件中设置, 如下图设置将会报错
read_only
read-only 选项: 对所有的非临时表进行只读控制. 但是有两种特殊情况
1. 对 replication threads 例外, 以保证 slave 能够正常的进行 replication.
2. 对于拥有 super 权限的用户, 可以 ignore 这个选项.
当以没有拥有 super 权限的用户登录时候, 会提示如下:
这样就确保了从数据只负责读数据操作, 而拒绝写数据的操作.
补充:
SUPER 权限 :
1. 可以有 change master to, kill 其他用户的线程的权限.
2. Purge binary logs 来删除 binary log, set global 来动态设置变量的权限.
3. 执行 mysqladmin debug 命令, 开启或者关闭 log, 在 read-only 打开时执行 update/insert 操作.
4. 执行 start slave, stop slave.
5. 当连接数已经达到 max_connections 的最大值时, 也可以连接到 server.
master_verify_checksum
由于软硬件或者网络传输出错, 导致主服务器上运行的 sql 语句与从服务器上运行的 sql 语句不一致, 很难找到问题原因, mysql 的开发人员在 5.6 Milestone Development Release 版本中加入了 replication event checksum(主从复制事件校验)功能. master_verify_checksum 主要用于复制事件校验. 当一个 event 被写入 binary log(二进制日志)的时候, checksum 也同时写入 binary log, 然后在 event 通过网络传输到从服务器 (slave) 之后, 再在从服务器中对其进行验证并写入从服务器的 relay log. 由于每一步都记录了 event 和 checksum, 所以我们可以很快地找出问题所在.
管理与维护
查看从服务器状态
使用 show slave stauts;
在查看这些信息中, 比较重要的是 "slave_io_runing" 和 "slave_sql_runing" 这两个进程
slave_io_runing : 此进程负责从服务器从主服务器上读取 Binlog 日志, 并写入从服务器上的中继日志中.
Slave_SQL_Runing: 此进程负责读取并执行中继日志中的 binlog 日子.
只要期中有一个进程的状态时 no, 则表示复制进程停止.
总结
主从配置
一, 主服务器上配置
1. 创建用户, 并赋予 REPLICATION SLAVE 权限
2. 开启 binlog 日志, 并设置 server_id
二, 从服务器配置
1. 指定 server_id
2. 指定主服务器配置
备注:
1.win 下 mysql 开启与关闭(前提是需要配置 path 路径)
2.server-id 做什么用的
mysql 的同步的数据中是包含 server-id 的, 用于标识该语句最初是从哪个 server 写入的, 所以 server-id 一定要有的, 如果设置多个从服务器, 每个从服务器必须有一个唯一的 server-id 值, 必须与主服务器的以及其它从服务器的不相同.
每一个同步中的 slave 在 master 上都对应一个 master 线程, 该线程就是通过 slave 的 server-id 来标识的; 每个 slave 在 master 端最多有一个 master 线程, 如果两个 slave 的 server-id 相同, 则后一个连接成功时, 前一个将被踢掉.
在 mysql 做主从同步时, 要保证一条数据不会陷入死循环, 这里就是靠 server-id 来实现的.
来源: http://www.linuxidc.com/Linux/2018-08/153812.htm