配置MySQL主从/双主引发的反思原

阿dai学长

发布于 2019-04-03 10:24:18

9530

发布于 2019-04-03 10:24:18

文章被收录于专栏：阿dai_linux

记一次mysql配置双主全过程

[danger] 强烈建议在执行本节所有操作前将所有操作命令操作copy到Sublime Text编辑器替换port为要配置主从的mysql实例对应的端口，避免误操作！！！ [danger] 强烈建议在执行本节所有操作前将所有操作命令操作copy到Sublime Text编辑器替换port为要配置主从的mysql实例对应的端口，避免误操作！！！ [danger] 强烈建议在执行本节所有操作前将所有操作命令操作copy到Sublime Text编辑器替换port为要配置主从的mysql实例对应的端口，避免误操作！！！

备份主库数据

使用innobackupex工具进行备份（因本次涉及到的数据库实例较多，所以编写shell脚本，减少出错，单实例同样适用）：

#!/bin/bash

#ports='3312 3313 3315'
port=3314  # 单实例时手动指定数据库port

# for循环和if判断只在多实例时使用
#for port in $ports;do

#  if [ $port = "3313" ];then
#    passwd='aaaaaaa'
#  else
#    passwd='bbbbbbb'
#  fi
passwd='xxxxxxx'
mkdir /disk1/backup/$port

  innobackupex --socket=/home/mysql/$port/mysql.sock --user=root --password=$passwd  --parallel=1 /disk1/backup/$port/  > /tmp/"$port"_backup.log
  # 将数据rsync由 **monitor1/mysql5** 机器同步到从库主机（myslq6）：
  rsync -avP --bwlimit 50000 /disk1/backup/$port adai@mysql6:/data2/backup/  > /tmp/"$port"_rsync.log
  
  echo "Finish $port"

#done

Note： 执行脚本前保证主库、从库磁盘空间充足，并且从库服务器数据文件存放目录（“/data2/backup/”）存在，还有注意文件权限，否则可能会导致rsync失败！！！

如果有近期备份好的数据可以直接同步到从库服务器使用，但是仅当备份的binlog文件序号小于主库保留的最小binlog文件序号时即备份文件的binlog创建日期在主库保留的binlog文件后面，如mysql-bin.00008（备份文件binlog）、mysql-bin.00005（主库保留的binlog），备份才可用，即必须确保从库搭建好之后，开启同步时，主库还保留有同步位置之前的日志（保证数据连续性、一致性）。

搭建mysql双主结构

配置文件

数据库配置示例文件（Attention： 因为数据库要配置双主模式，所以在部署时主库和从库的server-id一定不能一样）：

$ cat 3314.cnf 
[mysqld3314]

lower_case_table_names=1
# 为PXC定制
binlog_format=ROW
default-storage-engine         = InnoDB
innodb_large_prefix=on
innodb_autoinc_lock_mode=2


# 绑定地址之后只能通过内部网络访问
# 不能使用: 10.*.*.*
bind-address=10.215.33.23

auto-increment-increment = 2
auto-increment-offset = 2

# 使用 xtrabackup-v2 必须制定datadir, 因为操作可能直接对 datadir进行
datadir=/home/mysql/3314/data

# http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/privileges.html#permissions-and-privileges-needed
# 权限的配置
# xtrapbackup在Donor上执行，因此只需localhost的权限

# 约定:
server-id = 72


# wsrep模式不依赖于GTID
# 开启GTID
# enforce_gtid_consistency=1
# gtid_mode=on

# 即便临时作为Slave，也记录自己的binlog
log-slave-updates=1

# master_info_repository=TABLE
# relay_log_info_repository=TABLE

# GENERAL #
user                           = mysql
port                           = 3314
socket                         = /home/mysql/3314/mysql.sock
pid-file                       = /home/mysql/3314/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

ft-min-word-len = 4
event-scheduler = 0

# SAFETY #
max-allowed-packet             = 16M

skip-name-resolve
max_connections = 2000
max_connect_errors = 30

back-log = 500

character-set-client-handshake = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

#character-set-client-handshake=1
#character-set-client=utf8
#character-set-server=utf8
#collation-server=utf8_general_ci

#key-buffer-size = 256M
table-open-cache = 2048
max-allowed-packet = 2048M
slave-skip-errors = all                       #Skip duplicated key
sort-buffer-size = 4M
join-buffer-size = 8M
thread-cache-size = 50
concurrent-insert = 2

thread-stack = 192K
net-buffer-length = 8K
read-buffer-size = 256K
read-rnd-buffer-size = 16M
bulk-insert-buffer-size = 64M

# 采用thread pool来处理连接
thread-handling=pool-of-threads
# 新线程创建等待时间，, 单位为10ms，50即为500ms
thread-pool-stall-limit = 50


sql-mode                       = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1


# BINARY LOGGING #
log-bin                        = /home/mysql/3314/data/mysql-bin
expire-logs-days               = 5
# LOGGING #
# log-output=file 默认就是FILE
log-error                      = /home/mysql/3314/data/mysql-error.log
long-query-time = 0.3
# log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /home/mysql/3314/data/mysql-slow.log

# 默认为0(MySQL不控制binlog的输出)
# sync-binlog                    = 1

# CACHES AND LIMITS #

tmp-table-size                 = 32M
max-heap-table-size            = 32M

# 频繁修改的表不适合做query-cache, 否则反而影响效率
query-cache-type               = 0
query-cache-size               = 0
# query-cache-limit = 2M
# query-cache-min-res-unit = 512

thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2

# innodb-file-per-table = 1设置之后， 下面的配置基本失效
innodb_data_file_path = ibdata1:10M:autoextend
innodb-thread-concurrency = 32
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-large-prefix            = on
# 内存: 全部内存*0.7
innodb-buffer-pool-size = 2G

performance-schema = 0
net-read-timeout = 60

# innodb-open-files 在MySQL5.6 auto-sized
# 来自May2
innodb-rollback-on-timeout
innodb-status-file = 1


# http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-multiple_io_threads.html
# http://zhan.renren.com/formysql?tagId=3942&checked=true
# 从MySQL 5.5
# innodb_file_io_threads = 4
innodb-read-io-threads = 16
innodb-write-io-threads = 8

innodb-io-capacity = 2000
# innodb-stats-update-need-lock = 0 # MySQL 5.6中无效
# innodb-stats-auto-update = 0
innodb-old-blocks-pct = 75
# innodb-adaptive-flushing-method = "estimate"
# innodb-adaptive-flushing = 1

# https://www.facebook.com/notes/mysql-at-facebook/repeatable-read-versus-read-committed-for-innodb/244956410932
# READ-COMMITTED 每次QUERY都要求调用: read_view_open_now, 而REPEATABLE-READ每次Transaction中只要求一次
# REPEATABLE-READ 会导致读写不同步
transaction-isolation = READ-COMMITTED

innodb-sync-spin-loops = 100
innodb-spin-wait-delay = 30

innodb-file-format = "Barracuda"
innodb-file-format-max = "Barracuda"

恢复数据到从库

如上mysql配置，在使用innobackupex恢复数据，会有报错：

Error: datadir must be specified.

解决上述报错（在配置文件中添加如下配置）：

$ vim /etc/mysql/3314.cnf
[mysqld]
# 使用 xtrabackup-v2 必须制定datadir, 因为操作可能直接对 datadir进行
datadir=/home/mysql/3314/data

恢复数据到从库：

$ cd /data2/backup/3314/

# 准备备份文件”--apply-log“
$ innobackupex --use-memory=1G --apply-log /data2/backup/3314/xxxx-xx-xx-xx
## --apply-log：根据Innodb日志文件中的更改来读取备份文件，并将更改应用到表空间，保证备份数据的一致性，应该在恢复备份之前执行。
## 参考：http://www.cnblogs.com/zhoujinyi/p/5893333.html
$ rm -rf /home/mysql/3314/data/*   # 清除从库中的脏数据

# 恢复数据到从库
$ innobackupex --defaults-file=/etc/mysql/3314.cnf --copy-back /data2/backup/3314/xxxx-xx-xx-xx

$ chown -R mysql:mysql /home/mysql/3314

# 启动mysql
$ systemctl start mysqld@3314

# 数据库登录脚本(使用ansible部署时会创建)：
# cat /home/mysql/3314/mysql_client.sh 
#!/bin/sh
# Do NOT modify this file by hand!
# Generate by ansible
mysql -u root -pxxxxxx -S /home/mysql/3314/mysql.sock $@

配置主从

主从复制原理

master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
slave将master的binary log拷贝到它自己的中继日志。
- 首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
- SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

注意几点：

--master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
--slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和 master数据保持一致了。
--Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
--Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
--master和slave两节点间时间需同步

mysql复制的模式：
- 主从复制： 主库授权从库远程连接，读取binlog日志并更新到本地数据库的过程；主库写数据后，从库会自动同步过来（从库跟着主库变）；
- 主主复制： 主从相互授权连接，读取对方binlog日志并更新到本地数据库的过程；只要对方数据改变，自己就跟着改变；
mysql主从复制模式
- 基于SQL语句的复制(statement-based replication, SBR)
- 基于行的复制(row-based replication, RBR)
- 混合模式复制(mixed-based replication, MBR)
参考： https://www.cnblogs.com/wade-lt/p/9008058.html

binlog

binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库的查询select或show等不会被binlog日志记录；主要用于数据库的主从复制（实时备份）以及增量恢复。

开启mysql的binlog： log-bin = /home/mysql/3314/mysql-bin
查看是否启用了日志： mysql> show variables like 'log_bin';
binlog的三种模式：
- Row level ：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
  - 优点：能清楚的记录每一行数据修改的细节
  - 缺点：数据量太大
- Statement level（默认）：每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
  - 优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能
  - 缺点：容易出现主从复制不一致
- Mixed（混合模式）：结合了Row level和Statement level的优点
参考：
- https://www.cnblogs.com/xhyan/p/6530861.html
- https://www.cnblogs.com/kevingrace/p/6065088.html

开始配置

主库授权： # 在主库创建主从同步要用到的用户，并授权（严格针对ip授权）： mysql> GRANT REPLICATION SLAVE ON *.* TO 'sstuser'@'10.xxx.xx.xx' IDENTIFIED BY 'sstuser_xxxxxxx'; mysql> flush privileges;
配置从库： $ systemctl start mysqld@3314 $ systemctl status mysqld@3314 # 获取备份文件binlog文件名、偏移量(搭建主从时用) $ cat /home/mysql/3314/data/xtrabackup_binlog_pos_innodb ## eg：mysql-bin.000028 145809329 # 登录并配置从库 $ sh mysql_client.sh
授权： ## 指定主库 mysql> CHANGE MASTER TO MASTER_HOST='10.xxx.xx.xx', MASTER_PORT=3314,MASTER_USER='sstuser', MASTER_PASSWORD='sstuser_xxxxxx', MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=145809329; ## 开启主从同步 mysql> start slave; ## 检查主从同步状态 mysql> show slave status\G # 验证： ## 在主库插入数据，在从库查看(简单操作：create database test_slave;) ## 如果配置了haproxy，也可以检测haproxy状态：http://vip:7901/status
主从重要状态信息：
- Slave_IO_Running: I/O线程是否被启动并成功地连接到主服务器上，Yes表示连接成功！
- **Slave_SQL_Running: ** SQL线程是否被启动，Yes表示启动成功！
- **Exec_Master_Log_Pos: ** 来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置（Relay_Master_Log_File）。
- **Relay_Log_Space: ** 所有原有的中继日志结合起来的总大小
- Slave_SQL_Running_State： 从库SQL线程运行状态，正常应该处于等待接收主库数据同步状态（ “ Slave has read all relay log; waiting for the slave I/O thread to update it“）

配置双主

Note：如果需要新从库和主库构建双主集群，从库必须不能接受任何写请求！！！

在从库服务器对主库服务器授权： mysql> GRANT REPLICATION SLAVE ON *.* TO 'sstuser'@'10.xxx.xx.xx2' IDENTIFIED BY 'sstuser_xxxxxx'; # 查看数据库binlog文件名、偏移量 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 2954215 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)
配置主库： # 停止原有的slave mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST='10.xxx.xx.xx2', MASTER_PORT=3314,MASTER_USER='sstuser', MASTER_PASSWORD='sstuser_xxxxxx', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=2954215; mysql> start slave; mysql> show slave status\G ## 搭建完成后用同样的方式(插入数据)检测双主状态！！！

问题处理

如果搭建过程中发现slave状态错误，应立刻停止slave（stop slave），检测操作步骤，排查错误，重建主从！
如果是恢复双主中的一个节点，必须先停止2个节点主从同步。不能让健康的节点一直同步错误的节点。
因mysql版本问题导致的问题： ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found 42. Created with MySQL 50552, now running 50637. Please use mysql_upgrade to fix this error
- 原因： 新部署的数据库和原有数据库版本不一致（检测发现小版本有升级）,错误是由于你曾经升级过数据库，升级完后没有使用 mysql_upgrade升级数据结构造成的。
- 解决办法：升级数据结构 $ mysql_upgrade -u root -pxxxxxx -S /home/mysql/3314/mysql.sock

(adsbygoogle = window.adsbygoogle || []).push({});

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2018/10/18 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据结构

云数据库 SQL Server

sql

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度