MySQL 高可用：主从配置或主主(双主)配置
来源：互联网 发布：mac电脑的安卓模拟器 编辑：程序博客网 时间：2024/05/22 17:46
mysql 的主从复制可以实现mysql的多种高可用性，如数据库的读写分离 ，在线热备，负载均衡，数据分布 等。
mysql 主从同步原理：
1. master 将操作记录到二进制日志(binary log)中；
2. slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
3. slave SQL进程读取中继日志，将重做记录数据到数据库中。
下面配置：【主从复制】
MySQL Version : 5.6.22
主库：centos152 / 192.168.1.152
从库：centos153 / 192.168.1.153
配置 mysql 文件：
【centos152 主库】
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 1
log-bin= mysql-bin
binlog_format = mixed
read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2
#添加后重启mysql服务
[root@centos152 ~]# service mysqld restart
【centos153 从库】
[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed
read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on
#auto-increment-offset=2
#auto-increment-increment=2
#添加后重启mysql服务
[root@centos153 ~]# service mysqld restart
说明：
log-bin ：需要启用二进制日志
server_id : 用于标识不同的数据库服务器
binlog-do-db : 需要记录到二进制日志的数据库
binlog-ignore-db : 忽略记录二进制日志的数据库
auto-increment-offset :该服务器自增列的初始值。
auto-increment-increment :该服务器自增列增量。
replicate-do-db ：指定复制的数据库
replicate-ignore-db ：不复制的数据库
relay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
log-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。
如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
如上面为双主的设置：
服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）
注意：文字图片来自《高性能 mysql》：
#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。
mysql> flush logs;mysql> show master status;+------------------+----------+--------------+---------------------------------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+---------------------------------------------+-------------------+| mysql-bin.000006 | 120 | | mysql,information_schema,performance_schema | |+------------------+----------+--------------+---------------------------------------------+-------------------+
File :当前正在记录的二进制日志文件
Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
Binlog_Ignore_DB ：不记录日志的数据库
Executed_Gtid_Set ：已执行的事务ID
二进制日志情况：
mysql> show variables like '%log_bin%';+---------------------------------+---------------------------------------+| Variable_name | Value |+---------------------------------+---------------------------------------+| log_bin | ON || log_bin_basename | /usr/local/mysql/data/mysql-bin || log_bin_index | /usr/local/mysql/data/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+---------------------------------------+
日志位置：
[root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin*-rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001-rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004-rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005-rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006-rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index
#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！
mysql> flush tables with read lock;
备份主库(执行以下一行脚本即可)
[root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql[root@centos152 ~]# mysqldump -u root -p --databases test --lock-all-tables --flush-logs> /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
查看当前日志记录的位置：
mysql> show master status \G;*************************** 1. row *************************** File: mysql-bin.000009 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schemaExecuted_Gtid_Set: 1 row in set (0.00 sec)
备份完后，可以解锁了
mysql> unlock tables;
在主服务器拷贝备份到从服务器：
[root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp#不行则先关闭主从服务器防火墙/etc/init.d/iptables status/etc/init.d/iptables stop
在从库中还原数据库：
[root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql
主库创建用于连接到本地的用户：
[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';
在从库中连接到主库：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.152', MASTER_USER='repl_user', MASTER_PASSWORD='slave@153', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=120;
重启从库mysql服务
[root@centos153 ~]# service mysqld restart
查看从库同步信息：
mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.152 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema
可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！
现在测试同步情况：
#主库创建测试表create table tabdemo( id int primary key auto_increment,value int default 0) auto_increment= 1 engine=innodb default charset=utf8; insert into tabdemo(value) values(1),(1),(1),(1),(1);select * from tabdemo;+----+-------+| id | value |+----+-------+| 1 | 1 || 3 | 1 || 5 | 1 || 7 | 1 || 9 | 1 |+----+-------+
主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~
==========================================================================
==========================================================================
下面配置：【主主复制】
更改配置文件：
【centos152 原主库】更改配置文件
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 1
log-bin= mysql-bin
binlog_format = mixed
read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2
#主主复制的从库设置（新增）
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on
【centos153 原从库】更改配置文件
[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed
read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on
#主主复制的主库设置（新增）
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=2
auto-increment-increment=2
添加后都重启mysql服务：
[root@centos153 ~]# service mysqld restart
原从库(153)中创建用于连接的用户：
[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';
原从库(153)查看当前日志记录的位置：
mysql> show master status \G;*************************** 1. row *************************** File: mysql-bin.000005 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schemaExecuted_Gtid_Set: 1 row in set (0.00 sec)
在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.153', MASTER_USER='repl_user', MASTER_PASSWORD='slave@152', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120;
重启从库(152)mysql服务：
[root@centos152 ~]# service mysqld restart
查看从库(152)的同步信息：
mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.153 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema
现在已经配置好双主了！现在相互在两个数据库中都操作数据：
#当前在原来的从库(153)操作数据。insert into tabdemo(value) values(2),(2),(2),(2),(2);#数据同步正常了！select * from tabdemo;+----+-------+| id | value |+----+-------+| 1 | 1 || 3 | 1 || 5 | 1 || 7 | 1 || 9 | 1 || 10 | 2 || 12 | 2 || 14 | 2 || 16 | 2 || 18 | 2 |+----+-------+在原来的主库(152)操作数据。insert into tabdemo(value) values(3),(3),(3),(3),(3);#也正常！select * from tabdemo;+----+-------+| id | value |+----+-------+| 1 | 1 || 3 | 1 || 5 | 1 || 7 | 1 || 9 | 1 || 10 | 2 || 12 | 2 || 14 | 2 || 16 | 2 || 18 | 2 || 19 | 3 || 21 | 3 || 23 | 3 || 25 | 3 || 27 | 3 |+----+-------+
至此，测试完毕！~注意参考：学一点 mysql 双机异地热备份—-mysql主从，主主备份原理及实践
- MySQL 高可用：主从配置或主主(双主)配置
- 《高可用MySQL》2 – 单机版MySQL主从配置
- mysql高可用双主配置
- 高可用架构-- MySQL主从复制的配置
- 高可用架构-- MySQL主从复制的配置
- 高可用架构-- MySQL主从复制的配置
- 高可用架构-- MySQL主从复制的配置
- 高可用架构-- MySQL主从复制的配置
- 高可用架构-- MySQL主从复制的配置
- 高可用架构-- MySQL主从复制的配置
- mongodb 主从配置、高可用配置
- redis主从配置及高可用哨兵
- Redis sentinel 高可用主从配置方案
- MySQL配置主主复制和高可用
- mha配置高可用mysql
- mysql高可用MHA配置
- mysql 主从同步 主主同步 高可用
- MySQL主主从复制+TomCat高可用实践案例
- iOS中AutoLayer自动布局流程及相关方法
- PCA数据降维
- Leetcode Subsets
- [UVA 247]Calling Circles[传递闭包][floyd]
- NSIndexSet无符号整数集合
- MySQL 高可用：主从配置或主主(双主)配置
- RecyclerView设置Item点击事件
- 关于微博的正文的整个思维逻辑 （含有只有图片数量计算图片九宫格算法）
- 百度云下载速度慢解决方案（仅供参考，大神请指导）
- 黑马程序员---成长之路-----OC之基础篇MRC内存管理
- 归并排序（Merge Sort）
- 关于学习编程
- 【我的问题】没解决的问题
- lintcode-中序遍历和后序遍历树构造二叉树-72
