MySQL 高可用：主从配置或主主(双主)配置

mysql 的主从复制可以实现mysql的多种高可用性，如数据库的读写分离，在线热备，负载均衡，数据分布等。mysql 主从同步原理：1. master 将操作记录到二进制日志(binary log)中；2. slave IO 线程将master的binary log events读写到它的中继日志(relay log)；3. slave SQL进程读取中继日志

薛定谔的DBA

18581人浏览 · 2015-10-19 01:19:47

薛定谔的DBA · 2015-10-19 01:19:47 发布

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_schema
Executed_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_schema
Executed_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主从，主主备份原理及实践

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。使用场景和优势当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整