在企业中,数据库高可用一直是企业的重中之重,中小企业很多都是使用 mysql 主从方案,一主多从,读写分离等,但是单主存在单点故障,从库切换成主库需要作改动。因此,如果是双主或者多主,就会增加 mysql 入口,增加高可用。不过多主需要考虑自增长 ID 问题,这个需要特别设置配置文件,比如双主,可以使用奇偶,总之,主之间设置自增长 ID 相互不冲突就能完美解决自增长 ID 冲突问题。
在开始之前,我们先来了解主从同步复制原理。
复制分成三步:
下图描述了这一过程:
该过程的第一部分就是 master 记录二进制日志。在每个事务更新数据完成之前,master 在二日志记录这些改变。MySQL 将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,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 线程从中继日志读取事件,更新 slave 的数据,使其与 master 中的数据一致。只要该线程与 I/O 线程保持一致,中继日志通常会位于 OS 的缓存中,所以中继日志的开销很小。
此外,在 master 中也有一个工作线程:和其它 MySQL 的连接一样,slave 在 master 中打开一个连接也会使得 master 开始一个线程。
MySQL5.6 以前的版本复制过程有一个很重要的限制——复制在 slave 上是串行化的,也就是说 master 上的并行更新操作不能在 slave 上并行操作。 MySQL5.6 版本参数 slave-parallel-workers=1 表示启用多线程功能。
MySQL5.6 开始,增加了一个新特性,是加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。
官方文档: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html
这样做可以在一定程度上保证主库的高可用, 在一台主库 down 掉之后, 可以在极短的时间内切换到另一台主库上(尽可能减少主库宕机对业务造成的影响),减少了主从同步给线上主库带来的压力;
但是也有几个不足的地方:
架构的简易图如下:
1.CentOS 6.8 64 位 2 台:masterA(192.168.10.11),masterB(192.168.10.12)
2. 官方 Mysql5.6 版本
- yum - y install make gcc gcc - c++ncurses - devel bison openssl - devel
- groupadd -g 27 mysql
- adduser -u 27 -g mysql -s /sbin/nologin mysql
- mkdir -p /data/packages/src
- cd /data/packages/
- wget http://distfiles.macports.org/cmake/cmake-3.2.3.tar.gz
- wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.34.tar.gz
- mkdir - p / usr / local / mysql / data
- cd /data/packages/src
- tar -zxvf ../cmake-3.2.3.tar.gz
- cd cmake-3.2.3/
- ./bootstrap
- gmake
- make install
- cd ../
- tar xf mysql-5.6.34.tar.gz
- cd mysql-5.6.34
- cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DWITH_SSL=bundled -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_DATADIR=/usr/local/mysql/data
- make && make install
- cp support - files / mysql.server / etc / rc.d / init.d / mysqld
- [client]
- port = 3306
- socket = /tmp/mysql.sock
- [mysqld]
- basedir = /usr/local/mysql
- port = 3306
- socket = /tmp/mysql.sock
- datadir = /usr/local/mysql/data
- pid-file = /usr/local/mysql/data/mysql.pid
- log-error = /usr/local/mysql/data/mysql.err
- server-id = 1
- auto_increment_offset = 1
- auto_increment_increment = 2 #奇数ID
- log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
- binlog-format=ROW
- binlog-row-p_w_picpath=minimal
- log-slave-updates=true
- gtid-mode=on
- enforce-gtid-consistency=true
- master-info-repository=TABLE
- relay-log-info-repository=TABLE
- sync-master-info=1
- slave-parallel-workers=0
- sync_binlog=0
- binlog-checksum=CRC32
- master-verify-checksum=1
- slave-sql-verify-checksum=1
- binlog-rows-query-log_events=1
- #expire_logs_days=5
- max_binlog_size=1024M #binlog单文件最大值
- replicate-ignore-db = mysql #忽略不同步主从的数据库
- replicate-ignore-db = information_schema
- replicate-ignore-db = performance_schema
- replicate-ignore-db = test
- replicate-ignore-db = zabbix
- max_connections = 3000
- max_connect_errors = 30
- skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集
- init-connect='SET NAMES utf8' #连接时执行的SQL
- character-set-server=utf8 #服务端默认字符集
- wait_timeout=1800 #请求的最大连接时间
- interactive_timeout=1800 #和上一参数同时修改才会生效
- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式
- max_allowed_packet = 10M
- bulk_insert_buffer_size = 8M
- query_cache_type = 1
- query_cache_size = 128M
- query_cache_limit = 4M
- key_buffer_size = 256M
- read_buffer_size = 16K
- skip-name-resolve
- slow_query_log=1
- long_query_time = 6
- slow_query_log_file=slow-query.log
- innodb_flush_log_at_trx_commit = 2
- innodb_log_buffer_size = 16M
- [mysql]
- no-auto-rehash
- [myisamchk]
- key_buffer_size = 20M
- sort_buffer_size = 20M
- read_buffer = 2M
- write_buffer = 2M
- [mysqlhotcopy]
- interactive-timeout
- [mysqldump]
- quick
- max_allowed_packet = 16M
- [mysqld_safe]
- log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
- #masterA自增长ID
- auto_increment_offset = 1
- auto_increment_increment = 2 #奇数ID
- #masterB自增加ID
- auto_increment_offset = 2
- auto_increment_increment = 2 #偶数ID
- [client]
- port = 3306
- socket = /tmp/mysql.sock
- [mysqld]
- basedir = /usr/local/mysql
- port = 3306
- socket = /tmp/mysql.sock
- datadir = /usr/local/mysql/data
- pid-file = /usr/local/mysql/data/mysql.pid
- log-error = /usr/local/mysql/data/mysql.err
- server-id = 2
- auto_increment_offset = 2
- auto_increment_increment = 2 #偶数ID
- log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项
- binlog-format=ROW
- binlog-row-p_w_picpath=minimal
- log-slave-updates=true
- gtid-mode=on
- enforce-gtid-consistency=true
- master-info-repository=TABLE
- relay-log-info-repository=TABLE
- sync-master-info=1
- slave-parallel-workers=0
- sync_binlog=0
- binlog-checksum=CRC32
- master-verify-checksum=1
- slave-sql-verify-checksum=1
- binlog-rows-query-log_events=1
- #expire_logs_days=5
- max_binlog_size=1024M #binlog单文件最大值
- replicate-ignore-db = mysql #忽略不同步主从的数据库
- replicate-ignore-db = information_schema
- replicate-ignore-db = performance_schema
- replicate-ignore-db = test
- replicate-ignore-db = zabbix
- max_connections = 3000
- max_connect_errors = 30
- skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集
- init-connect='SET NAMES utf8' #连接时执行的SQL
- character-set-server=utf8 #服务端默认字符集
- wait_timeout=1800 #请求的最大连接时间
- interactive_timeout=1800 #和上一参数同时修改才会生效
- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式
- max_allowed_packet = 10M
- bulk_insert_buffer_size = 8M
- query_cache_type = 1
- query_cache_size = 128M
- query_cache_limit = 4M
- key_buffer_size = 256M
- read_buffer_size = 16K
- skip-name-resolve
- slow_query_log=1
- long_query_time = 6
- slow_query_log_file=slow-query.log
- innodb_flush_log_at_trx_commit = 2
- innodb_log_buffer_size = 16M
- [mysql]
- no-auto-rehash
- [myisamchk]
- key_buffer_size = 20M
- sort_buffer_size = 20M
- read_buffer = 2M
- write_buffer = 2M
- [mysqlhotcopy]
- interactive-timeout
- [mysqldump]
- quick
- max_allowed_packet = 16M
- [mysqld_safe]
- cd /usr/local/mysql
- scripts/mysql_install_db --user=mysql
- chmod +x /etc/rc.d/init.d/mysqld
- /etc/init.d/mysqld start
masterA 上:
- mysql > grant replication slave on * . * to'repl'@'192.168.10.12'identified by'123456';
- mysql > flush privileges;
masterB 上:
- mysql > grant replication slave on * . * to'repl'@'192.168.10.11'identified by'123456';
- mysql > flush privileges;
masterA 上:
- mysql > show master status;
- + ------------------+----------+--------------+------------------+-------------------+
- |File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+------------------+-------------------+
- |mysql - bin.000003 | 120 | |||
- +------------------+----------+--------------+------------------+-------------------+
- 1 row in set(0.00 sec)
masterB 上
- mysql > show master status;
- + ------------------+----------+--------------+------------------+-------------------+
- |File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+------------------+-------------------+
- |mysql - bin.000003 | 437 | |||
- +------------------+----------+--------------+------------------+-------------------+
- 1 row in set(0.00 sec)
masterA 上:
显示有如下状态则正常:
- mysql> change master to master_host='192.168.10.12',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=437;
- mysql> start slave;
- mysql> show slave status\G;
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
masterB 上:
#本人是测试环境,可以保证没数据写入,否则需要的步骤是:先 masterA 锁表 -->masterA 备份数据 -->masterA 解锁表 -->masterB 导入数据 -->masterB 设置主从 --> 查看主从
- mysql> change master to master_host='192.168.10.11',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;
- start slave;
- mysql> show slave status\G;
显示有如下状态则正常:
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- mysql > show databases;
- + --------------------+
- |Database |
- +--------------------+
- |information_schema |
- |mysql |
- |performance_schema |
- |test |
- +--------------------+
- 4 rows in set(0.00 sec)
- mysql > create database test01;
- Query OK,
- 1 row affected(0.00 sec)
- mysql > show databases;
- + --------------------+
- |Database |
- +--------------------+
- |information_schema |
- |mysql |
- |performance_schema |
- |test |
- |test01 |
- +--------------------+
- 5 rows in set(0.00 sec)
- mysql > quit
- Bye
- [root@masterA data]#
- mysql > show databases;
- + --------------------+
- |Database |
- +--------------------+
- |information_schema |
- |mysql |
- |performance_schema |
- |test |
- |test01 |
- +--------------------+
- 5 rows in set(0.00 sec)
- mysql > quit
- Bye
- [root@masterB data]#
masterA 和 masterB 分别执行如下命令:
- mysql> stop slave;
- Query OK, 0 rows affected (0.00 sec)
- mysql> change master to MASTER_AUTO_POSITION=1;
- Query OK, 0 rows affected (0.01 sec)
- mysql> start slave;
- Query OK, 0 rows affected (0.00 sec)
一种主从报错折腾了我半天:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not open log file'
后面修改主从同步相关 参数,确认原因是 my.cnf 增加了如下参数:
log-bin = mysql-bin
relay-log = mysql-bin
从正常主主同步时的二进制日志文件显示,有 2 套二进制日志。因此推断上面 2 个参数导致不能产生 2 套二进制文件,故导致二进制文件错乱和丢失。
来源: http://www.bubuko.com/infodetail-2440320.html