MySQL双主（主主）架构方案
企业中数据库的高可用是必不可缺的。中小企业使用mysql主从方案，一主多从，读写分离等，但单主出现故障时，从库需要切换主库作改动。而如果是双主或多主，就会增加mysql入口，增加高可用。多主需要考虑到自增长id问题。需要特别设置配置文件。总之，主之间设置自增长ID相互不冲突就能完美解决自增长ID冲突问题。
主从同步复制原理
1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
2. slave将master的binary log events拷贝到它的中继日志(relay log)；
3. slave重做中继日志中的事件，将改变反映它自己的数据。
MySQL双主（主主）架构方案思路是:
1.两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；
2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；
3.两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）；
4.所有提供服务的从服务器与masterB进行主从同步（双主多从）;
5.建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；
但是也有几个不足的地方:
1.masterB可能会一直处于空闲状态（可以用它当从库，负责部分查询）；
2.主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据，这样可能会造成一定程度的同步延时；
架构简易图：
主主环境（这里只介绍2台主的配置方案）:
1.CentOS 6.8 64位 2台：masterA（192.168.10.11），masterB（192.168.10.12）
2.官方Mysql5.6版本
搭建过程：
1.安装MySQL服务（建议源码安装）
1.1 yum安装依赖包
yum -y install make gcc gcc-c++ ncurses-devel bison openssl-devel
1.2 添加MySQL所需要的用户和组
groupadd -g 27 mysql
adduser -u 27 -g mysql -s /sbin/nologin mysql
1.3 下载MySQL源码包
mkdir -p /data/packages/src
cd /data/packages/wget http://distfiles.macports.org/cmake/cmake-3.2.3.tar.gzwget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.34.tar.gz
1.4 创建mysql数据目录
mkdir -p /usr/local/mysql/data
1.5 解压编译安装cmake、MySQL
cd /data/packages/srctar -zxvf ../cmake-3.2.3.tar.gz
cd cmake-3.2.3/
./bootstrap
gmakemake 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/datamake && make install
1.6 添加开机启动脚本
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
1.7 添加masterA配置文件/etc/my.cnf
[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=TABLEsync-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]
1.8 特别参数说明
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
1.9 添加masterB配置文件/etc/my.cnf
[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=TABLEsync-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]
1.10 初始化MySQL
cd /usr/local/mysql
scripts/mysql_install_db --user=mysql
1.11 为启动脚本赋予可执行权限并启动MySQL
chmod +x /etc/rc.d/init.d/mysqld/etc/init.d/mysqld start
2. 配置主从同步
2.1 添加主从同步账户
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;
2.2 查看主库的状态
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)
2.3 配置同步信息：
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
3.测试主从同步
3.1 在masterA上创建一个数据库测试同步效果
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]#
3.2 到masterB查看是否已经同步创建数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test01 |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye
[root@masterB data]#
4. 开启MySQL5.6的GTID功能
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)
5. 遇到的问题
一种主从报错折腾了我半天：
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套二进制文件，故导致二进制文件错乱和丢失。
配置Mysql-proxy，实现读写分离
环境描述:
操作系统：CentOS6.5 64 位
mysql主服务器 Master：172.25.17.19
mysql从服务器 Slave1：172.25.17.20
mysql从服务器 Slave2：172.25.17.21
调度服务器 MySQL-Proxy：172.25.17.7
1、安装 mysql-proxy
实现读写分离是有 lua 脚本实现的，现在 mysql-proxy 里面已经集成，无需再安装
下载：http://dev.mysql.com/downloads/mysql-proxy/ 一定要下载对应的版本
[root@proxy ~]# rpm -ivh mysql-proxy-0.8.1-1.el6.x86_64.rpm
2、将rw-splitting.lua 脚本复制到/lib 下
[root@proxy ~]# cp rw-splitting.lua /lib
3、修改mysql-proxy主配置文件/etc/sysconfig/mysql-proxy
[root@proxy ~]# vim /etc/sysconfig/mysql-proxy
ADMIN_USER="proxy" #主从 mysql 共有的用户
ADMIN_PASSWORD="123456" #用户的密码
ADMIN_LUA_SCRIPT="/lib/rw-splitting.lua" #指定管理脚本
PROXY_USER="root" #运行 mysql-proxy用户
#添加如下代码:[以下代码需写在一行内]
PROXY_OPTIONS="--proxy-address=172.25.17.7:4040
--proxy-read-only-backend-addresses=172.25.17.20:3306--proxy-read-only-backend-addresses=172.25.17.21:3306--proxy-backend-addresses=172.25.17.19:3306--proxy-lua-script=/lib/rw-splitting.lua
--daemon"
#mysql-proxy 运行 ip 和端口,不加端口,默认 4040
4、修改读写分离配置文件
[root@proxy ~]# vim /lib/rw-splitting.luaif not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, #默认超过 4个连接数时,才开始读写分离,改为 1
max_idle_connections = 1, #默认 8,改为1
is_debug = false}
end
#这里的 4 、8 是指定链接数，做测试时调整为 1
5、设置mysql-proxy 开机启动,并启动mysql-proxy
[root@proxy ~]# chkconfig mysql-proxy on[root@proxy ~]# service mysql-proxy start[root@proxy ~]# netstat -tnlp | grep mysql-proxy
6、在master数据库授权mysql-proxy访问
[root@master ~]# mysql -puplooking
grant all on *.* to 'proxy'@'172.25.17.7' identified by '123456';
grant all on *.* to 'proxy'@'rhel6' identified by '123456';
flush privileges;
7 、测试读写分离
测试读写分离,要让两台服务器数据有误差才行，所以暂停slave1(172.25.17.20)上的从服务:
mysql -puplooking > stop slave;
在 master(172.25.17.19) 服务器上修改数据:
mysql> create tabletest.t1(id int);
mysql> insert intotest.t1 values (1);mysql> insert into test.t1 values (3);
mysql> insert intotest.t1 values (2);mysql> insert into test.t1 values (4);
在 slave1(172.25.17.20) 服务器上修改数据:
mysql> create tabletest.t1(id int);
mysql> insert into test.t1values (5);
mysql> insert intotest.t1 values (6);
在 调度服务器mysql-proxy 上打开若干个终端远程访问数据库,读取数据:
[root@proxy ~]# yum -y install mysql #安装数据库添加mysql启动命令
[root@proxy ~]# mysql -uproxy -p123456 -h172.25.17.7 -P4040
#远程访问数据库
mysql> select * from test1.t1;
+------+| id |
+------+| 1 || 2 || 3 || 4 |
+------+
4 rows in set (0.00 sec)
mysql> select * from test01.t1;
+------+| id |
+------+| 5 || 6 |
+------+
2 rows in set (0.00 sec)
显示结果不一样，证明配置成功。多次尝试以上两行代码直到显示不同结果。
试验结束在slave1服务器上slave start恢复主从同步。
读写分离，延迟是个大问题
在 slave 服务器上执行 show slave status,
可以看到很多同步的参数,要注意的参数有：
Master_Log_File:slave 中的 I/O 线程当前正在读取的 master 服务器二进制式日志文件名.
Read_Master_Log_Pos:在当前的 master 服务器二进制日志中，slave 中的 I/O 线程已经读取的位置
Relay_Log_File:SQL 线程当前正在读取与执行中继日志文件的名称
Relay_Log_Pos:在当前的中继日志中，SQL 线程已读取和执行的位置
Relay_Master_Log_File:由 SQL 线程执行的包含多数近期事件的 master 二进制日志文件的名称
Slave_IO_Running:I/O 线程是否被启动并成功连接到 master
Slave_SQL_Running:SQL 线程是否被启动
Seconds_Behind_Master:slave 服务器 SQL 线程和从服务器 I/O 线程之间的差距，单位为秒计
slave 同步延迟情况出现：
1.Seconds_Behind_Master 不为了，这个值可能会很大
2.Relay_Master_Log_File 和 Master_Log_File 显示 bin-log 的编号相差很大，说明 bin-log 在 slave 上
没有及时同步，所以近期执行的 bin-log 和当前 I/O 线程所读的 bin-log 相差很大
3.mysql 的 slave 数据库目录下存在大量的 mysql-relay-log 日志，该日志同步完成之后就会被系统自动
删除，存在大量日志，说明主从同步延迟很厉害
mysql 主从同步原理
主库针对读写操作，顺序写 binlog，从库单线程去主库读"写操作的 binlog",从库取到 binlog 在本地原
样执行(随机写),来保证主从数据逻辑上一致.
mysql 的主从复制都是单线程的操作，主库对所有 DDL 和 DML 产生 binlog，binlog 是顺序写，所以
效率很高，slave 的 Slave_IO_Running 线程到主库取日志，效率比较高，下一步问题来 了，slave 的
slave_sql_running 线程将主库的 DDL 和 DML 操作在 slave 实施。DML，DDL 的 IO 操作是随即的，
不能顺序的，成本高很多，还有可能 slave 上的其他查询产生 lock，由于 slave_sql_running 也是单线
程的，所以 一个 DDL 卡住了，需求需求执行一段时间，那么所有之后的 DDL 会等待这个 DDL 执行完
才会继续执行，这就导致了延迟.由于 master 可以并发，Slave_sql_running 线程却不可以，所以主库执
行 DDL 需求一段时间，在 slave 执行相同的 DDL 时，就产生了延迟.
主从同步延迟产生原因
当主库的 TPS 并发较高时，产生的 DDL 数量超过 Slave 一个 sql 线程所能承受的范围，那么延迟就产
生了，当然还有就是可能与 slave 的大型 query 语句产生了锁等待
首要原因：数据库在业务上读写压力太大，CPU 计算负荷大，网卡负荷大，硬盘随机 IO 太高
次要原因：读写 binlog 带来的性能影响，网络传输延迟
主从同步延迟解决方案
架构方面
1.业务的持久化层的实现采用分库架构，mysql 服务可平行扩展分散压力
2.单个库读写分离，一主多从，主写从读，分散压力。
3.服务的基础架构在业务和 mysql 之间加放 cache 层
4.不同业务的 mysql 放在不同的机器
5.使用比主加更了的硬件设备作 slave
主要是让mysql压力变小，延迟自然就会变小。
硬件方面：
采用好的服务器
mysql 主从同步加速
1、sync_binlog 在 slave 端设置为 0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3、直接禁用 slave 端的 binlog
4、slave 端，如果使用的存储引擎是 innodb，innodb_flush_log_at_trx_commit =2
从文件系统本身属性角度优化
master 端
修改 linux、Unix 文件系统中文件的 etime 属性，可以 通过设置文件系统的 mount 属性，组织操作系统写 atime 信息。
打开/etc/fstab，加上 noatime 参数
/dev/sdb1 /data reiserfs noatime 1 2
然后重新 mount 文件系统
#mount -oremount /data
主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的
而 slave 则不需要这么高的数据安全，完全可以讲 sync_binlog 设置为 0 或者关闭
binlog，innodb_flushlog 也可以设置为 0 来提高 sql 的执行效率
1、sync_binlog=1 o
MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上去。
默认，sync_binlog=0，表示 MySQL 不控制 binlog 的刷新，
2、innodb_flush_log_at_trx_commit （这个很管用）
默认值 1 的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的，设成 2 对于很多运用，特别是从 MyISAM 表转过来的是可以的，它的意思是不 写入硬盘而是写入系统缓存。值 2 只会在整个操作系统 挂了时才可能丢数据。
3.进行分库分表处理，这样减少数据量的复制同步操作
123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
