MYSQL HA 部署手册

2022-12-03 22:16:49

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;

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

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |

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

| 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;

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

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |

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

| 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

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

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服务，看看还能不能连上！！

码农公寓