大向二 发布时间: 2019-01-08 17:54:42 浏览 148 评论 0
云栖社区
nginx
系统软件
数据存储与数据库
Linux
MySQL
日志
数据库
配置
cluster
数据迁移
同步
test
binlog
摘要: 在配置主从之前, 首先保证 3306 端口的可用, 或者关闭防火墙, 两台机子可以互相 ping 1,Master 的配置修改 MySQL 配置文件 [root@localhost ~]# VIM /etc/my.cnf 修改 my.
在配置主从之前, 首先保证 3306 端口的可用, 或者关闭防火墙, 两台机子可以互相 ping
1,Master 的配置
修改 MySQL 配置文件
[root@localhost ~]# VIM /etc/my.cnf
修改 my.cnf 文件内容
- [mysqld]
- # 开启二进制日志
- log-bin=MySQL-bin
- # 标识唯一 id(必须), 一般使用 ip 最后位
- server-id=2
- # 不同步的数据库, 可设置多个
- binlog-ignore-db=information_schema
- binlog-ignore-db=cluster
- binlog-ignore-db=MySQL
- # 指定需要同步的数据库 (和 slave 是相互匹配的), 可以设置多个
- binlog-do-db=test
添加日志存储方式和规则 (选填)
- # 设置存储模式不设置默认
- binlog_format=MIXED
- # 日志清理时间
- expire_logs_days=7
- # 日志大小
- max_binlog_size=100m
- # 缓存大小
- binlog_cache_size=4m
- # 最大缓存大小
- max_binlog_cache_size=521m
重启 MySQL
service mysqld restart
进去 MySQL 设置允许从库获得主库日志 注: 这里使用 root 用户配置, 不建议使用, 正常使用新创建的用户进行授权
首先登陆
[root@localhost ~]# MySQL -u root -p
给从库放权限
- MySQL>GRANT FILE ON *.* TO 'root'@'192.168.2.2' IDENTIFIED BY 'root password'; #创建用户
- MySQL>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.2.2' IDENTIFIED BY 'root password'; #修改用户权限
- MySQL>select host ,user ,password from MySQL.user; #查看是否修改成功
- MySQL>FLUSH PRIVILEGES; #刷新
注: 如果数据库有数据需要进行数据迁移保证数据的一致性 数据迁移
创建数据库: 在从库中创建一个和主库相同的数据库, 不然两个数据库不能同步 (进行过数据迁移就跳过)
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
重启 MySQL, 登录 MySQL, 查看主库信息
MySQL> show master status;
显示内容
- +------------------+----------+--------------+----------------------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+----------------------------------+-------------------+
- | MySQL-bin.000006 | 120 | ufind_db | information_schema,cluster,MySQL | |
- +------------------+----------+--------------+----------------------------------+-------------------+
- 1 row in set (0.00 sec)
- MySQL>
注: 如果执行这个步骤始终为 Empty set(0.00 sec), 那说明前面的 my.cnf 没配置对
2,Slave 的配置
从库配置
开启二进制日志 (可以不配置)log-bin=MySQL-bin
- server-id=3
- binlog-ignore-db=information_schema
- binlog-ignore-db=cluster
- binlog-ignore-db=MySQL# 与主库配置一直
- replicate-do-db=test
- replicate-ignore-db=MySQL
- log-slave-updates
- slave-skip-errors=all
- slave.NET-timeout=60
重启 MySQL, 登录 MySQL
关闭 Slave
- MySQL> stop slave; #设置连接主库信息
- MySQL> change master to master_host='192.168.2.1',master_user='root',master_password='root password',master_log_file='mysql-bin.000006', master_log_pos=120;
- change master to master_host='192.168.1.168',master_user='root',master_password='nsv_2018',master_log_file='mysql-bin.000045', master_log_pos=704551739;
开启 Slave
MySQL> start slave;
注: 上面的 master_log_file 是在配置 Master 的时候的 File 字段, master_log_pos 是在配置 Master 的 Position 字段. 一定要一一对应
查看信息
- MySQL> show slave status \G;
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.2.1
- Master_User: root
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000006
- Read_Master_Log_Pos: 120
- Relay_Log_File: localhost-relay-bin.000006
- Relay_Log_Pos: 520
- Relay_Master_Log_File: MySQL-bin.000006
- Slave_IO_Running: Yes // 显示 yes 为成功
- Slave_SQL_Running: Yes // 显示 yes 为成功, 如果为 no, 一般为没有启动 master
- Replicate_Do_DB: test
- Replicate_Ignore_DB: MySQL// 上面的都是配置文件中的信息
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 357
- Relay_Log_Space: 697
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error: // 如果为 no, 此处会显示错误信息
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 2
- Master_UUID: be0a41c0-2b40-11e8-b791-000c29267b6a
- Master_Info_File: /usr/local/MySQL/data/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set:
- Auto_Position: 0
- 1 row in set (0.00 sec)
下面整理一些经常出现的问题与操作
- ERROR:
- No query specified
注: 如果 Slave_IO_Running: No 出现下面的错误
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
说明主服务器的 UUID 和从服务器的 UUID 重复, 更改方式
[root@localhost ~]# VIM /usr/local/MySQL/data/auto.cnf #这是我的安装路径修改 auto.cnf 的 server-uuid
注: 如果 Slave_IO_Running: Connecting 并出现下面错误
error connecting to master 'root@192.168.3.28:3306' - retry-time: 60 retries: 1
解决方法, 查看主库是否授权, 查看 change master to... 是否有用户密码 ip 填写错误
注: 如果 Slave_IO_Running: No 出现下面错误
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解决方法: 复位
- MySQL>stop slave; // 停止
- MySQL>reset slave; // 清空
- MySQL>start slave; // 开启
以上主从 MySQL 已经可以使用了, 欢迎各位多提 bug
小扩展
当只针对某些库的某张表进行同步时, 如下, 只同步 huanqiu 库的 haha 表和 huanpc 库的 heihei 表:
- replicate-do-db = huanqiu
- replicate-wild-do-table = huanqiu.haha // 当只同步几个或少数表时, 可以这样设置. 注意这要跟上面的库指定配合使用;
- replicate-do-db = huanpc
- replicate-wild-do-table = huanpc.heihei // 如果同步的库的表比较多时, 就不能这样一一指定了, 就把这个选项配置去掉, 直接根据指定的库进行同步.
关于增删改查, 主从数据不一致问题:
select 语句, 暂时没有发现问题
insert 语句, 暂时没有发现问题
update 语句, 暂时没有发现问题
delete 语句, 主库删除多条数据, 发现数据不一致
原因: 在主库的 logbin 中的确有执行删除语句, 但是在从库的 logbin 中却没有删除语句
解决: 使用 use database 选取当前数据库架构中的需要操作的数据库, 然后在执行删除, OK 同步成功
查询 binlog 主从日志的方法
查看 binlog 全部文件
MySQL>show binary logs;
查看 binlog 是否开启 NO 为开启
MySQL> show variables like 'log_bin%';
详细信息
MySQL> show variables like 'binlog%';
查看 binlog 日志
MySQL> show binlog events in'mysql-bin.000019';
或者使用 mysqlbinlog, 如果报错使用 --no-defaults(使用全路径)
[root@localhost ~]# /usr/local/MySQL/bin/mysqlbinlog --no-defaults /usr/local/MySQL/data/MySQL-bin.000019
手动清理 master 日志, 最好关闭日志, 在 / etc/my.cnf
手动刷新日志
MySQL> show master status;
删除全部 MySQL> reset slave; 或 REST master;# 删除 MySQL-bin.010mysql> PURGE MASTER LOGS TO 'My
来源: https://yq.aliyun.com/articles/685405