mysql双主多从高可用配置_MySQL 高可用：主从配置或主主(双主)配置

最新推荐文章于 2024-08-14 15:42:25 发布

昌维

最新推荐文章于 2024-08-14 15:42:25 发布

阅读量306

点赞数

文章标签： mysql双主多从高可用配置

本文链接：https://blog.csdn.net/weixin_30896451/article/details/113605327

版权

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 ~] 操作几笔数据后，数据有些变化。

[plain] view plain copy

mysql> flush logs;

mysql> show master status;

+------------------+----------+--------------+---------------------------------------------+-------------------+

+------------------+----------+--------------+---------------------------------------------+-------------------+

+------------------+----------+--------------+---------------------------------------------+-------------------+

File :当前正在记录的二进制日志文件

Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。

Binlog_Do_DB ：要记录日志的数据库

Binlog_Ignore_DB ：不记录日志的数据库

Executed_Gtid_Set ：已执行的事务ID

二进制日志情况：

[plain] view plain copy

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 |

+---------------------------------+---------------------------------------+

日志位置：

[plain] view plain copy

[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

#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！

[plain] view plain copy

mysql> flush tables with read lock;

备份主库(执行以下一行脚本即可)

[plain] view plain copy

[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

查看当前日志记录的位置：

[plain] view plain copy

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)

备份完后，可以解锁了

[plain] view plain copy

mysql> unlock tables;

在主服务器拷贝备份到从服务器：

[plain] view plain copy

[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

在从库中还原数据库：

[plain] view plain copy

[root@centos153 ~]# mysql -u root -p test

主库创建用于连接到本地的用户：

[plain] view plain copy

[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';

在从库中连接到主库：

[plain] view plain copy

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;

change master to master_host='139.21x.1x.xx', master_user='xxx', master_password='xxxx*',master_port=3306, master_log_file='mysql-bin.000005', master_log_pos=28387862;

重启从库mysql服务

[root@centos153 ~]# service mysqld restart

查看从库同步信息：

[plain] view plain copy

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，表示正常执行！

现在测试同步情况：

[plain] view plain copy

#主库创建测试表

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)中创建用于连接的用户：

[plain] view plain copy

[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';

原从库(153)查看当前日志记录的位置：

[plain] view plain copy

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)，之前的从库现在也将是主库：

[plain] view plain copy

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)的同步信息：

[plain] view plain copy

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

现在已经配置好双主了！现在相互在两个数据库中都操作数据：

[plain] view plain copy

#当前在原来的从库(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 |

+----+-------+

昌维

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫