felix-cao commented 5 years ago

mysql 复制的原理现阶段都是一样的，master 将操作记录到bin-log中，slave的一个线程去master读取bin-log，并将他们保存到relay-log中，slave的另外一个线程去重放relay-log中的操作来实现和master数据同步。

一、场景描述

master 主服务器，假设主服务器 IP 为 a.b.c.162

slave 从服务器，假设从服务器IP为 a.b.c.185

mysql-proxy mysql官方提供的中间件用于实现负载均衡和读写分离

本文描述的场景应用的 MySQL 版本是5.5.38

二、主从配置

2.1、找到 MySQL 配置文件的位置

find / -name my.cnf

我这里得到的 /etc/my.cnf

2.2、主库

$vi /etc/my.cnf

在 [mysqld] 中插入下面的代码：

server-id=1
log-bin=mysql-bin
binlog-format=mixed
binlog-do-db=fncftc_sql
# binlog-ignore-db=test
expire_logs_days=7

server-id 是给服务器起一个唯一ID， Master 为1， Slave习惯是以ip最后几位为标识
log-bin=mysql-bin 表示以mysql-bin为前缀命名的二进制日志 binary log
binlog-format 二进制日志格式是监听 sql 语句的变化还是影响行的变化 statement代表语句 row代表行如果两者都用且让mysql帮我们判断用哪个则用mixed
binlog-do-db 表示只对fncftc_sql进行备份, binlog-ignore-db 表示不需要备份的库
若没有配置binlog-do-db和binlog_ignore_db，表示备份全部数据库
expire_logs_days 主要用来控制binlog日志文件保留时间，超过保留时间的binlog日志会被自动删除, expire_logs_days=7 表示7天后删除

保存退出，重启 MySQL

$service mysqld restart

高版本的Linux

$systemctl restart mysql

2.3、从库

进入 MySQL 配置文件

$vi /etc/my.cnf

在 [mysqld] 中插入下面的代码：

server-id=185
relay-log=mysql-relay

relay log 很多方面都跟binary log差不多，区别是：从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器。

保存退出

2.4、主库授权一个的账号

在主库中授权一个主从复制的账号，进入主库的mysql 命令行，执行：

GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by '!@#quma123';

mysync 是账号名，密码是 !@#mysync123

2.5、查看二进制日志文件，记录position

二进制文件

cd /usr/local/mysql/var

记录 postion

mysql> show master status;

把二进制文件名 mysql-bin.000015 和 postion : 132897217 记录下来, 它们是用来配置从数据库的关键信息，可以看到上面同步的数据库的 fncftc_sql 数据库

2.6、启动 slave 功能

现在让 slave 连接master，开始读取master二进制日志文件，保存到slave 的 relay-log文件中

mysql> change master to master_host='172.26.150.29',
     > master_user='mysync',
     > master_password='!@#quma123',
     > master_log_file='mysql-bin.000015',
     > master_log_pos=132897217;

master_host、master_user、master_password分别表示主库所在的 IP, 登录主库的用户名和密码
master_log_file 和 master_log_pos 是我们在2.5要求记录下来的
如果以前有配过，可以使用 reset slave 清除以往配置的slave信息
注意不要断开，“132897217”无单引号。

启动 slave 并查看状态

mysql> start slave
mysql> show slave status \G

show slave status \G 是检查主从复制的状态

现在，可以在 Master 中执行 show slave status;

三、报错处理

有时候，我们会发现主从数据库不一致，首先要去从库所在的服务器 mysql 命令行中查看从库的状态

mysql> show slave status\G

也许你会发现几行 Last_Errno 错误

Last_Errno: 1062
Last_Error: Error 'Duplicate entry '1532664' for key 'PRIMARY'' on query. Default database: 'fncftc_sql'. Query: 'INSERT INTO `fanwe_money_detail` (`user_id` , `type` , `old_money` , `new_money` , `create_time`) VALUES (325647 , 19 , 48 , 54 , 1554972364)'

解决的方法有两种

2.1、在mysql命令行中执行

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

2.2、在 MySQL 配置文件中配置

在 MySQL 配置文件中配置的 [mysqld] 中配置

[mysqld]
slave-skip-errors = 1062

但这两种方法都会给主从数据库的一致性带来隐患，因此不建议这么做，导致这种情况的发生有可能是：在做主从一系列配置时，应确保主库中不再有新的数据产生

Reference'

mysql （master/slave）复制原理及配置

felix-cao commented 5 years ago

从库 1032 错误

Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table fncftc_sql.fanwe_reflection; Can't find record in 'fanwe_reflection', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000015, end_log_pos 139184519

分析了一下，这条错误，不会对主从同步造成数据不一致的影响，可以跳过

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

或者在 MySQL 配置文件中配置的 [mysqld] 中配置

[mysqld]
slave-skip-errors = 1062

问题原因分析

今天我们刚上线的MySQL数据库集群系统没能通过早晨的流量高峰，甚至是同一条数据出现N次，我们是一主(A)两从(B、C)，一主(A)一从(B)的服务器在同一机房，另外一从(C)在阿里云，恰恰是这个在阿里云的C从库，频繁的爆发错误

从这个现象不能看出由于从库所在的服务器和主库所在的服务器不在同一机房导致的同步时间差比较大，从而引发这个 频繁的爆发错误

追根要究底，在与同事沟通一番后，我明白了导致这一状况发生的根本原因是：系统在 insert 一条数据后，立即去 select，可 select 不到，于是又去 insert 一条数据，业务基于微信公众号的

我提供的终极解决办法是 缓存处理 + 数据库移到同一个机房。

felix-cao commented 5 years ago

从库报错：Got fatal error 1236

Got fatal error 1236 from master when reading data from binary log

在source那边，执行：

flush logs;
show master status;

记下File, Position。在target端做 2.6 启动slave的动作，

错误原因，启动 slave 时， File 文件写错了，多写了一个 0

felix-cao commented 5 years ago

主库已有数据并正在运行的解决方案

由单机架构切换到一主一从或一主多从，主库正在运行，那么这种场景如何去部署主从复制呢？

我们首先来回顾一下，基于binlog的主从复制过程：

Master将数据改变记录到二进制日志(binary log)中
Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行

从上面的步骤我们可以看出，在进行主从备份的时候，不能再有主库的写入操作，否则会丢失数据，造成数据不准确。

所以，方法有两种：

关闭系统应用
全局锁表

本文采用第二种，全局锁表，所以在这个场景下，我们的步骤是：

1、全局锁表

全局锁表：

mysql> flush tables with read lock;

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。

需要注意的是 flush tables with read lock 会把当前会话锁住。如果 unlock tables，之前的会话还会执行，除非session 时间过了，断开连接。建议在锁表之前修改配置文件增加：

interactive_timeout = 1200000
wait_timeout = 1200000

2、查询主数据库状态，并记下FILE及Position的值

mysql> show master status;

3、备份主数据库

tar -czvf fncftc_sql_20190411_02.tar ./fncftc_sql

4、把备份好的数据传输到从库

scp -r ./fncftc_sql_20190411_02.tar root@127.0.0.1:/usr/local/mysql/var

也可以使用 mysqldump 导出 sql 文

mysqldump -uquma -p --databases fncftc_sql >fncftc_sql.sql

导入时：先创建表：

mysql> create database fncftc_sql;

再导入

mysql -uquma -p fncftc_sql < ./fncftc_sql.sql

5、主从配置

按照本文的步骤进行主从配置

6、解锁

mysql> unlock tables;

总结应该尽可能优化流程，减少锁表时间。

尽可能减少锁表范围，只锁定相关的数据库。

felix-cao commented 5 years ago

1364:Field 'sex' doesn't have a default value

SET @@GLOBAL.sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

参考： https://www.cnblogs.com/lovebing/p/6830407.html

felix-cao commented 5 years ago

MySQL 5.6 后，从库支持多线程复制

查看是否开启多线程

mysql> show variables like '%slave_parallel%'

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

结果显示是单线程的

felix-cao commented 5 years ago

TP5 报错 General error: 1243

参考 http://www.thinkphp.cn/topic/47606.html

如果出现 -bash: mysqldumpslow: command not found 错误，请执行

ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin

mysqldumpslow -s r -t 20 /usr/local/mysql/var/slow.log

felix-cao commented 5 years ago

MySQL慢查询（一） - 开启慢查询

felix-cao commented 5 years ago

MySQL 主从同步、读写分离遇到的数据不一致

今天，发现 'Duplicate entry '1211' for key 'PRIMARY'' on query，意思是主从数据库不一致，很奇怪，为什么会不一致呢，仔细的查了一下，大吃一惊，发现从库，居然有 100 多条与主库不一致的 user 数据，而这些数据竟然在主库中是没有的，也就是说，从库发生了写入的操作，那么为什么？这些数据是从哪里来的呢？

于是，我做了两个动作：

开启 Atlas 中间件的 sql-log = ON
禁止从库的用户 insert 、update、delete 的权限，请阅读《MySQL 添加用户、为用户分配权限 #》

这两个动作之后就可以进一步的追踪。

felix-cao commented 5 years ago

利用 MySQL 中间件 Atlas 实现读写分离

上面我们已经有了一主(a.b.c.162)一从(a.b.c.185), 再单独那一台服务器(a.b.c.162)，做 Atlas 服务器，需要注意的是，这台 Atlas 服务器也是需要安装 mysql 软件的。

安装 atlas 的步骤参考 github, 也可以参考MySQL + Atlas --- 部署读写分离，安装配置完成后

进入管理

mysql -h127.0.0.1 -P2345 -uuser -ppwd

查看帮助

mysql> select * from help;

查看机器状态

mysql> select * from backends

felix-cao commented 5 years ago

备份指定数据库的指定表

mysqldump -uquma -p dbname tableName > ./mysqlBaks/dbname.tableName.sql

felix-cao commented 5 years ago

MySQL server has gone away

导入数据量比较大时，显示这个错误

vi /etc/my.cnf

修改 max_allowed_packet 的值，设置大点

felix-cao commented 5 years ago

Specified key was too long; max key length is 767 bytes

请阅读 https://www.cnblogs.com/kerrycode/p/9680881.html

felix-cao commented 3 years ago

Mac 下 mysql 的安装

下载地址： https://dev.mysql.com/downloads/mysql/ 官方文档：https://dev.mysql.com/doc/refman/8.0/en/osx-installation-pkg.html

felix-cao / Blog

MySQL 配置主从读写分离实现及错误分析 #163

一、场景描述

二、主从配置

2.1、找到 MySQL 配置文件的位置

2.2、主库

2.3、从库

2.4、主库授权一个的账号

2.5、查看二进制日志文件，记录position

2.6、启动 slave 功能

三、报错处理

2.1、在mysql命令行中执行

2.2、在 MySQL 配置文件中配置

Reference'

从库 1032 错误

从库报错：Got fatal error 1236

主库已有数据并正在运行的解决方案

1、全局锁表

2、查询主数据库状态，并记下FILE及Position的值

3、备份主数据库

4、把备份好的数据传输到从库

5、主从配置

6、解锁

MySQL 5.6 后，从库支持多线程复制

TP5 报错 General error: 1243

MySQL 主从同步、读写分离遇到的数据不一致

利用 MySQL 中间件 Atlas 实现读写分离

备份指定数据库的指定表

MySQL server has gone away

Specified key was too long; max key length is 767 bytes

Mac 下 mysql 的安装