1. 首页
  2. 文章中心
  3. 数据库

Mysql+Keepalived双主热备高可用操作记录1.配置server-id并开启bin-log功能2.检查：3.创建复制账号4.检查创建的rep复制账号1.查看位置信息2.数据库12同步114.11同步12数据库三.配置keepalived1.b11主节点：2.b12从节点：3.在主从节点授权root用户远程登陆登陆测试b11主节点创建数据库同步到从节点b12b11主节点创建数据库同步到从节点b12

56 阅读 0 评论
我是靠谱客的博主 满意萝莉，最近开发中收集的这篇文章主要介绍Mysql+Keepalived双主热备高可用操作记录1.配置server-id并开启bin-log功能2.检查：3.创建复制账号4.检查创建的rep复制账号1.查看位置信息2.数据库12同步114.11同步12数据库三.配置keepalived1.b11主节点：2.b12从节点：3.在主从节点授权root用户远程登陆登陆测试b11主节点创建数据库同步到从节点b12b11主节点创建数据库同步到从节点b12，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

架构图
1332343-20180211184043326-61199446.png

一． 两台机器都要配置

1.配置server-id并开启bin-log功能

[root@linux-node1 ~]# grep '^[a-Z]' /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=11
log-bin=/var/lib/mysql/mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
symbolic-links=0
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

2.检查：

MariaDB [(none)]> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11 |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]>

3.创建复制账号

MariaDB [(none)]> grant replication slave on . to 'rep'@'192.168.56.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4.检查创建的rep复制账号

MariaDB [(none)]> select user,host from mysql.user;
+--------+-------------------------+
| user | host |
+--------+-------------------------+
| root | 127.0.0.1 |
| rep | 192.168.56.% |
| root | ::1 |
| | linux-node1.example.com |
| root | linux-node1.example.com |
| | localhost |
| root | localhost |
| zabbix | localhost |
+--------+-------------------------+
8 rows in set (0.00 sec)

二．测试

1.查看位置信息

b11:
MariaDB [db1]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 950 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [db1]>

2.数据库12同步11

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| salt |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.11',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=950;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]>

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 792
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 792
Relay_Log_Space: 825
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>

4.11同步12数据库

b12:
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [db1]> CHANGE MASTER TO
-> MASTER_HOST='192.168.56.12',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.12
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 825
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
1 row in set (0.00 sec)

ERROR: No query specified

三.配置keepalived

1.b11主节点：

[root@linux-node1 mysql]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
notification_email {
xiaolong.xu@maixunbytes.com
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id master-ha
}

vrrp_instance VI_1 {
state MASTER
interface eth0
mcast_src_ip 192.168.56.11
virtual_router_id 50
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.14/24 dev eth0 label eth0：1
}
}

2.b12从节点：

[root@linux-node2 mysql]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
notification_email {
xiaolong.xu@maixunbytes.com
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id master-ha
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
mcast_src_ip 192.168.56.12
virtual_router_id 50
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.14/24 dev eth0 label eth0：1
}
}

3.在主从节点授权root用户远程登陆

MariaDB [(none)]> grant all on . to root@'192.168.56.%' identified by "123456";
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> select user,host from mysql.user;
+--------+-------------------------+
| user | host |
+--------+-------------------------+
| root | % |
| root | 127.0.0.1 |
| rep | 192.168.56.% |
| root | 192.168.56.% |
| root | ::1 |
| | linux-node1.example.com |
| root | linux-node1.example.com |
| | localhost |
| root | localhost |
| zabbix | localhost |
+--------+-------------------------+
10 rows in set (0.00 sec)

MariaDB [(none)]>

登陆测试

b11主节点创建数据库同步到从节点b12

1332343-20180211184327935-461979715.png

b11主节点创建数据库同步到从节点b12

1332343-20180211184351138-862374633.png

转载于:https://www.cnblogs.com/fengmeng1030/p/8442986.html

最后

以上就是满意萝莉为你收集整理的Mysql+Keepalived双主热备高可用操作记录1.配置server-id并开启bin-log功能2.检查：3.创建复制账号4.检查创建的rep复制账号1.查看位置信息2.数据库12同步114.11同步12数据库三.配置keepalived1.b11主节点：2.b12从节点：3.在主从节点授权root用户远程登陆登陆测试b11主节点创建数据库同步到从节点b12b11主节点创建数据库同步到从节点b12的全部内容，希望文章能够帮你解决Mysql+Keepalived双主热备高可用操作记录1.配置server-id并开启bin-log功能2.检查：3.创建复制账号4.检查创建的rep复制账号1.查看位置信息2.数据库12同步114.11同步12数据库三.配置keepalived1.b11主节点：2.b12从节点：3.在主从节点授权root用户远程登陆登陆测试b11主节点创建数据库同步到从节点b12b11主节点创建数据库同步到从节点b12所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(37)

相关文章

yum源配置
yum源配置
nodeJs操作mysql数据库
nodeJs操作mysql数据库
python mysql库下载_mysql python下载
python mysql库下载_mysql python下载
MySQL数据库安全配置
MySQL数据库安全配置
Mysql+Keepalived双主热备高可用操作记录1.配置server-id并开启bin-log功能2.检查：3.创建复制账号4.检查创建的rep复制账号1.查看位置信息2.数据库12同步114.11同步12数据库三.配置keepalived1.b11主节点：2.b12从节点：3.在主从节点授权root用户远程登陆登陆测试b11主节点创建数据库同步到从节点b12b11主节点创建数据库同步到从节点b12
Mysql+Keepalived双主热备高可用操作记录1.配置server-id并开启bin-log功能2.检查：3.创建复制账号4.检查创建的rep复制账号1.查看位置信息2.数据库12同步114.11同步12数据库三.配置keepalived1.b11主节点：2.b12从节点：3.在主从节点授权root用户远程登陆登陆测试b11主节点创建数据库同步到从节点b12b11主节点创建数据库同步到从节点b12
MySQL-Python下载网址分享MySQL-Python下载网址分享
MySQL-Python下载网址分享MySQL-Python下载网址分享
JDBC操作Mysql数据库的基本步骤_jdbc操作数据库的基本流程详解
JDBC操作Mysql数据库的基本步骤_jdbc操作数据库的基本流程详解
mysql python 1.2.3_MySQL-python-1.2
mysql python 1.2.3_MySQL-python-1.2

评论列表共有 0 条评论

立即
投稿
返回
顶部