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
```shell
[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机器上:

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

秒客网

MYSQL HA 部署手册

1 MySQL启用主主双写复制

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

1.2 卸载旧版安装包

1.3 安装MySQL

1.3.1 解压安装MySQL

1.3.2 修改配置文件位置

1.3.3 初始化MySQL及设置密码

1.3.4 安装MySQL JDBC驱动

1.3.5 允许远程登陆

1.3.6 设置开机自启动

1.3.7 MySQL的默认安装位置

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

1.3.9 异常处理

1.4 配置MySQL主主同步

1.4.1 创建同步账号

1.4.2 配置数据库参数

1.4.3 备份数据库

1.4.4 设置master2同步master1

1.4.5 设置master1同步master2

1.5 安装keepalived 并设置监控

1.6 测试

相关文章