mysql 主主复制

1.环境准备：

主机IP：192.168.1.4, 192.168.1.5

操作系统：centos 7.3

mysql数据库版本：mysql 5.7.13

浮动IP:192.168.1.24

mysql 采用rpm包安装，依赖关系依次为common→libs→client→serveràdevel

rpm -ivhmysql-community-common-5.7.20-1.el7.x86_64.rpm

rpm -ivhmysql-community-libs-5.7.20-1.el7.x86_64.rpm

rpm -ivhmysql-community-client-5.7.20-1.el7.x86_64.rpm

rpm -ivhmysql-community-server-5.7.20-1.el7.x86_64.rpm

rpm -ivhmysql-community-devel-5.7.20-1.el7.x86_64.rpm

master 主机：

vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

character_set_server=utf8

max_connections=2000

log_timestamps=SYSTEM

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#主主复制配置:

auto_increment_offset=1

auto_increment_increment=2

#启用二进制日志注意：日志的目录需要先建立，并将所有者该为mysql

log-bin=mysql-bin

binlog_format=row

log_bin_trust_function_creators=1

#超过30天的binlog删除

expire_logs_days=30

server-id=31

#配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步

log-slave-updates

sync_binlog=1

#中继日志文件

relay_log = mysql-relay-bin

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

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

[client]

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

slave 主机：

[mysqld]

datadir=/var/lib/mysql

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

character_set_server=utf8

max_connections=2000

log_timestamps=SYSTEM

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Recommended in standard MySQL setup

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#主主复制配置:

auto_increment_offset=2

auto_increment_increment=2

#启用二进制日志注意：日志的目录需要先建立，并将所有者该为mysql

log-bin=mysql-bin

binlog_format=row

log_bin_trust_function_creators=1

expire_logs_days=30

server-id=32

#配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步

log-slave-updates

sync_binlog=1

relay_log = mysql-relay-bin

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

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

[client]

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

配置主主复制

---- 在 4 上执行：

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'192.168.1.5' IDENTIFIED BY 'Xsjy%123';

---- 在 5 上执行：

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'192.168.1.4' IDENTIFIED BY 'Xsjy%123';

--查看5 mysql master 状态

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

---按照5 master的状态设置 slave 4 mysql--

change master to

master_host='192.168.1.5',

master_user='replication',

master_password='Xsjy%123',

master_log_file='mysql-bin.000005',

master_log_pos=654; #对端状态显示的值

start slave;

-查看4 mysql master 状态

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

---按照4 master的状态设置 slave 5 mysql--

change master to

master_host='192.168.1.4',

master_user='replication',

master_password='Xsjy%123',

master_log_file='mysql-bin.000008',

master_log_pos=483; #对端状态显示的值

start slave;

启动mysql

#bin/mysqld_safe --user=mysql & 或者service mysqld start

#bin/mysql --user=root –p

--输入 /var/log/mysqld.log 中生成的临时密码

重新设置密码：

mysql>set global validate_password_policy=0;

mysql>set global validate_password_length=1;

mysql> set password=password('123456');

允许远程登录：

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

mysql> flush privileges;

查看用户信息

mysql> use mysql;

mysql> select host,user from user where user='root';

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

| host | user |

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

| % | root |

| localhost | root |

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

2 rows in set (0.00 sec)

keepalived配置：

#yum install keepalived -y

keepalived的日志默认是输出到/var/log/messages中，这样不便于查看。

如何自定义keepalived的日志输出文件呢？

# vim /etc/sysconfig/keepalived

KEEPALIVED_OPTIONS="-D -d -S 0"

# vim /etc/rsyslog.conf在文件最后添加：

#keepalived -S 0

local0.* /var/log/keepalived.log

重新启动日志

# systemctl restart rsyslog

最后使用以下命令进行验证

# systemctl restart keepalived

# tail -f /var/log/keepalived.log

主节点： vim /etc/keepalived/keepalived.conf

vrrp_script check_mysql {

script "/usr/sbin/check_mysql.sh"

interval 2

weight 2

}

vrrp_instance MYSQL_HA {

#state MASTER

state BACKUP

interface eth0

virtual_router_id 50

priority 100

nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass password123

}

track_script {

check_mysql

}

virtual_ipaddress {

192.168.1.24/24 dev eth0

}

keepalive从节点配置：

vrrp_script check_mysql {

script "/usr/sbin/check_mysql.sh"

interval 2

weight 2

}

vrrp_instance MYSQL_HA {

#state MASTER

state BACKUP

interface eth0

virtual_router_id 50

priority 90

#nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass password123

}

track_script {

check_mysql

}

virtual_ipaddress {

192.168.1.24/24 dev eth0

}

vim /usr/sbin/check_mysql.sh

#!/bin/sh

nc localhost 3306 -w 1 < /dev/null &>/dev/null || systemctl stop keepalived

vim /usr/sbin/check_keepalived.sh

#!/bin/bash

DATE=`date +"%Y-%m-%d %H:%M:%S"`

Process_count=`ps -C keepalived --noheader|wc -l`

if [ $Process_count -eq 3 ]

then

echo "$DATE keepalived service is running" >> /tmp/check_ka.log

else

echo "$DATE keepalived service is not running" >> /tmp/check_ka.log

systemctl start keepalived >> /tmp/check_ka.log

该脚本用来检测keepalived 服务。可以写到 crontab 里面调用没分钟检查一次。

*/1 * * * * /etc/keepalived/check_keepalived.sh