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

记一次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的缓存中,所以中继日志的开销很小。

注意几点:

  1. --master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
  2. --slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和 master数据保持一致了。
  3. --Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
  4. --Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)
  5. --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的优点
  • 参考:

开始配置

  • 主库授权: # 在主库创建主从同步要用到的用户,并授权(严格针对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({});

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券