mysql ha_MYSQL HA 部署手册

分手后的思念是犯贱 2023-01-11 01:14 111阅读 0赞

1 MySQL启用主主双写复制

1.1 卸载系统默认的数据库mariadb

安装mysql出现安装包不兼容问题，首先卸载掉系统自带mariadb

查看已经安装的mariadb

rpm -qa|grep mariadb

删除mariadb

rpm -e --nodeps mariadb-libs-5.5.33a-3.el7.x86\_64

rpm -e --nodeps mariadb-server-5.5.44-2.el7.x86\_64

rpm -e mariadb-devel --nodeps

rpm -e mariadb --nodeps

直到rpm -qa|grep mariadb命令输出为空

1.2 卸载旧版安装包

检查MySQL及相关RPM包，是否安装，如果有安装，则移除(rpm –e 名称)

rpm -qa | grep -i mysql

yum -y remove MySQL-\*

1.3 安装MySQL

1.3.1 解压安装MySQL

tar xvf MySQL-5.5.52-1.el7.x86\_64.rpm-bundl.tar

MySQL-client-5.5.52-1.el7.x86\_64.rpm

MySQL-devel-5.5.52-1.el7.x86\_64.rpm

MySQL-embedded-5.5.52-1.el7.x86\_64.rpm

MySQL-server-5.5.52-1.el7.x86\_64.rpm

MySQL-shared-5.5.52-1.el7.x86\_64.rpm

MySQL-shared-compat-5.5.52-1.el7.x86\_64.rpm

MySQL-test-5.5.52-1.el7.x86\_64.rpm

安装MySQL-client-5.5.52-1.el7.x86\_64.rpm，MySQL-server-5.5.52-1.el7.x86\_64.rpm，MySQL-devel-5.5.52-1.el7.x86\_64.rpm即可

rpm -ivh MySQL-server-5.5.52-1.el7.x86\_64.rpm

rpm -ivh MySQL-client-5.5.52-1.el7.x86\_64.rpm

rpm -ivh MySQL-devel-5.5.52-1.el7.x86\_64.rpm

1.3.2 修改配置文件位置

cp /usr/share/mysql/my-default.cnf /etc/my.cnf

1.3.3 初始化MySQL及设置密码

初始化数据库并启动mysql

/usr/bin/mysql\_install\_db

service mysql start

查看root账号初始密码

cat /root/.mysql\_secret

The random password set for the root user at Wed Dec 11 23:32:50 2013 (local time): qKTaFZnl

修改root密码为111111

mysql -uroot –pqKTaFZnl

mysql> SET PASSWORD = PASSWORD('111111');

mysql> exit

或者

mysql> use mysql;

mysql> update user set password=password("111111") where user='root';

mysql> flush privileges;

mysql> exit;

验证登录

mysql -uroot -p111111

1.3.4 安装MySQL JDBC驱动

yum -y install mysql-connector-java

1.3.5 允许远程登陆

mysql> use mysql;

mysql> select host,user,password from user;

\+-----------+------+-------------------------------------------+

| host | user | password |

\+-----------+------+-------------------------------------------+

| localhost | root | \*FD571203974BA9AFE270FE62151AE967ECA5E0AA |

| node1a146 | root | \*F99E6AEA8DF26B30DA7A57F92F3FABC0E4949F12 |

| 127.0.0.1 | root | \*F99E6AEA8DF26B30DA7A57F92F3FABC0E4949F12 |

| ::1 | root | \*F99E6AEA8DF26B30DA7A57F92F3FABC0E4949F12 |

\+-----------+------+-------------------------------------------+

mysql> update user set password=password('111111') where user='root';

mysql> update user set host='%' where user='root' and host='localhost';

mysql> flush privileges;

mysql> exit

1.3.6 设置开机自启动

chkconfig mysql on

chkconfig --list | grep mysql

1.3.7 MySQL的默认安装位置

/var/lib/mysql/ \#数据库目录

/usr/share/mysql \#配置文件目录

/usr/bin \#相关命令目录

/etc/init.d/mysql \#启动脚本

1.3.8 修改字符集和数据存储路径

查看初始字符集

mysql> show variables like '%collation%';

\+----------------------+-------------------+

| Variable\_name | Value |

\+----------------------+-------------------+

| collation\_connection | utf8\_general\_ci |

| collation\_database | latin1\_swedish\_ci |

| collation\_server | latin1\_swedish\_ci |

\+----------------------+-------------------+

3 rows in set (0.00 sec)

mysql> show variables like '%char%';

\+--------------------------+----------------------------+

| Variable\_name | Value |

\+--------------------------+----------------------------+

| character\_set\_client | utf8 |

| character\_set\_connection | utf8 |

| character\_set\_database | latin1 |

| character\_set\_filesystem | binary |

| character\_set\_results | utf8 |

| character\_set\_server | latin1 |

| character\_set\_system | utf8 |

| character\_sets\_dir | /usr/share/mysql/charsets/ |

\+--------------------------+----------------------------+

8 rows in set (0.00 sec)

配置/etc/my.cnf文件,修改数据存放路径、mysql.sock路径以及默认编码utf-8.

\[client\]

password = 111111

port = 3306

default-character-set=utf8

\[mysqld\]

port = 3306

character\_set\_server=utf8

character\_set\_client=utf8

collation-server=utf8\_general\_ci

(注意linux下mysql安装完后是默认：表名区分大小写，列名不区分大小写； 0：区分大小写，1：不区分大小写)

lower\_case\_table\_names=1

(设置最大连接数，默认为 151，MySQL服务器允许的最大连接数16384; )

max\_connections=1000

\[mysql\]

default-character-set = utf8

重启使生效

service mysql restart

1.3.9 异常处理

问题1：

如果执行sql时报下面错误是由于你升级过数据库，升级完后没有使用mysql\_upgrade升级数据结构造成的。

ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found 42. Created with MySQL 50544, now running 50637. Please use mysql\_upgrade to fix this error.

使用mysql\_upgrade命令

root@localhost ~\]\# mysql\_upgrade -u root -p 111111

1.4 配置MySQL主主同步

主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步。

1.4.1 创建同步账号

分别在两台机器上配置同步账号

master1机器上：

mysql> grant all privileges on . to 'repl'@'%' identified by '123456';

mysql> flush privileges;

master2机器上：

mysql> grant all privileges on . to 'repl'@'%' identified by '123456';

mysql> flush privileges;

1.4.2 配置数据库参数

master1机器上：

1、关闭

service mysql stop

2、更改配置文件/etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=1

log-bin=mysql-bin

relay\_log=mysql-relay-bin

binlog\_format=ROW

report-host = master2

report-user = repl

report-password = 123456

report-port = 3306

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

其中标红为新增内容：

l Log-bin：启动二进制日志文件

l Server-id：本机数据库ID(和)

l Binlog\_format:一共有三种复制方式：ROW\\STATEMENT\\MIXED，默认是STATEMENT

l report-host，report-user，report-password，report-port为master复制的主机名，用户，密码以及端口号。

3、启动mysql

service mysql start

Master2机器上：

1、关闭

service mysql stop

2、更改配置文件/etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=1

log-bin=mysql-bin

relay\_log=mysql-relay-bin

binlog\_format=ROW

report-host = master2

report-user = repl

report-password = 123456

report-port = 3306

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

\`\`\`shell

3、启动mysql

service mysql start

\#\#\# 1.4.3 备份数据库

备份两个需要同步的数据库数据以防同步失败时数据丢失。

备份mysql

mysqldump --all-databases -uroot -p > mysql.sql

\#\#\# 1.4.4 设置master2同步master1

\*\*master1机器上:\*\*

\`\`\`shell

mysql> flush tables with read lock;

mysql> show master status;

\+---------------+----------+--------------+------------------+

\+---------------+----------+--------------+------------------+

| binlog.000003 | 365 | | |

\+---------------+----------+--------------+------------------+

1 row in set (0.03 sec)

mysql> unlock tables;

master2机器上：

设置master2同步master1数据

mysql> change master to master\_host='master1IP', master\_port=3306, master\_user='repl',master\_password='123456', master\_log\_file='binlog.000003',master\_log\_pos=365;

开启同步

mysql> start slave;

执行这命令后 注意观察下面这两个参数，必须要都是yes才行

mysql> show slave status \\G

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

master\_host表示是master1的IP

master\_user表示主库master1上允许同步的用户

maser\_password表示同步用户的密码

master\_log\_file表示从哪个binlog文件开始同步

master\_log\_pos表示从该binlog文件的那个pos节点位置开始同步

我们查看同步是否主要是查看Slave\_IO\_Running与Slave\_SQL\_Running选项。如果正常同步，这两选必须同时为YES。

如果Slave\_IO\_Running为NO，说明可能是从库与主库的网络不通。

如果Slave\_SQL\_Running为NO，说明很可能是从库与主库的数据不一致。

如果Slave\_IO\_Running: Connecting，可能是同步的用户密码写错了。

同样的 反过来做相同操作

1.4.5 设置master1同步master2

master2机器上:

mysql> flush tables with read lock;

mysql> show master status;

\+---------------+----------+--------------+------------------+

\+---------------+----------+--------------+------------------+

| binlog.000004 | 207 | | |

\+---------------+----------+--------------+------------------+

1 row in set (0.03 sec)

mysql> unlock tables;

master1\*\*\*\*机器上：

mysql> change master tomaster\_host='10.1.1.21', master\_port=3306, master\_user='repl',master\_password='123456', master\_log\_file='binlog.000004',master\_log\_pos=207;

mysql> start slave;

mysql> show slave status \\G

执行这命令后 注意观察下面这两个参数，必须要都是yes才行

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

介此，主主同步打造完成，可以简单测试一下，分别在两个机器上写数据 看看会不会同步到另一台机器上

PS：如果报错Slave\_IO\_Running: NO 可以检查同步的账号是否创建正常！

1.5 安装keepalived 并设置监控

keepalived是安装在两台MySQL服务器上的

首先安装keepalived 过程不解释就正常解压安装就好

安装后配置 vim /etc/keepalived/keepalived.conf 内容如下

10.1.1.20的配置文件

! Configuration File for keepalived

global\_defs \{

notification\_email \{

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

notification\_email\_from Alexandre.Cassen@firewall.loc

smtp\_server 127.0.0.1

smtp\_connect\_timeout 30

router\_id LVS\_DEVEL

vrrp\_instance VI\_1 \{

state backup \#两台配置此处均是BACKUP

interface eth0

virtual\_router\_id 51

priority 100 \#优先级，另一台改为90

advert\_int 1

nopreempt \#不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

10.1.1.25

virtual\_server 10.1.1.25 3306 \{

delay\_loop 6

lb\_algo wrr

lb\_kind DR

persistence\_timeout 50 \#会话保持时间

protocol TCP

real\_server 10.1.1.20 3306 \{

weight 3

notify\_down /tmp/nimei.sh \#检测到mysql服务挂了就执行这个脚本(脚本要自己写哈)

TCP\_CHECK \{

connect\_timeout 10 \#连接超时时间

nb\_get\_retry 3 \#重连次数

delay\_before\_retry 3 \#重连间隔时间

connect\_port 3306 \#健康检查端口

10.1.1.21的配置文件

! Configuration File for keepalived

global\_defs \{

notification\_email \{

acassen@firewall.loc

failover@firewall.loc

sysadmin@firewall.loc

notification\_email\_from Alexandre.Cassen@firewall.loc

smtp\_server 127.0.0.1

smtp\_connect\_timeout 30

router\_id LVS\_DEVEL

vrrp\_instance VI\_1 \{

state backup

interface eth0

virtual\_router\_id 51

priority 90

advert\_int 1

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

10.1.1.25

virtual\_server 10.1.1.25 3306 \{

delay\_loop 6

lb\_algo wrr

lb\_kind DR

persistence\_timeout 50

protocol TCP

real\_server 10.1.1.21 3306 \{

weight 3

notify\_down /tmp/nimei.sh

TCP\_CHECK \{

connect\_timeout 10

nb\_get\_retry 3

delay\_before\_retry 3

connect\_port 3306

编写监控mysql服务是否挂了的脚本，按照上面配置文件的位置编写脚本。

vim /tmp/nimei.sh

\#!/bin/sh

pkill keepalived

脚本很简单就一句，目的是当keepalived检测到mysql服务挂了之后触发这个脚本，杀死keepalived进程，让另一台机器接管

好 修改后启动keeplived服务

介此整个集群搭建完成

1.6 测试

找一台机器用虚拟ip连接mysql

\[root@localhost html\]\# mysql -uab -h 10.1.1.25 -p123

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 736

Server version: 5.1.66-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql>

这样成功连上了，然后你可以任意关闭某台机器，或者某台机器的mysql服务，看看还能不能连上！！

mysql ha_MYSQL HA 部署手册

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 linux(centos)产品安装部署jdk、nginx、mysql、redis手册

相关 mysql ha_MYSQL HA 部署手册

相关 mysql中文安装手册_MySQL中文参考手册- 4 安装MySQL

相关 Redis主从复制的HA部署

相关 MySQL-Cluster三机集群+HA高可用+负载均衡配置手册

相关 hadoop2.6 HA部署

相关 MySQL HA

相关 Hadoop——HDFS HA部署

相关 Redis部署必备手册{{1026}}

相关 redis主从哨兵部署手册