mysql5.6.30安装部署(mysql+keeplived)

来源：互联网发布：wps文字软件下载编辑：程序博客网时间：2024/05/21 17:01

安装MySQL 5.6 主

mysql必须装在系统最大分区,此文档以最大分区为 /data 为例

主数据库 134.96.247.47

从数据库 134.96.247.48

安装二进制包mysql。

mysql必须装在系统最大分区

主数据库 134.96.247.47

从数据库 134.96.247.48

mysql同步帐户replusr /password

1、检查并卸载系统中低版本的mysql

# rpm -qa |grep mysql

# yum remove mysql mysql-server mysql-libs

# rpm -e mysql-libs-5.1.71-1.el6.x86_64--nodeps

2添加用户和组:

添加用户和组:

# groupadd mysql

# useradd -g mysql mysql

3创建目录及授权

# mkdir -pv /data/mysql/data

# mkdir -pv /data/mysql/log/iblog

# mkdir -pv /data/mysql/log/binlog

# mkdir -pv /data/mysql/log/relaylog

# mkdir -pv /data/mysql/run

# mkdir -pv /data/mysql/tmp

# chown -R mysql:mysql /data/mysql

# chmod -R 755 /data/mysql

上传解压

上传mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz到/opt

# cd /opt

# tar zxf mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz

# cd mysql-5.6.30-linux-glibc2.5-x86_64
# cp -R * /data/mysql/
# chown -R mysql:mysql /data/mysql

4修改配置文件my.cnf参数

# cp/data/mysql/support-files/my-default.cnf /data/mysql/my.cnf

A、----------主数据库配置---------
# vi /data/mysql/my.cnf

# For advice on how to change settingsplease see

#http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's atemplate which will be copied to the

# *** default location during install, andwill be replaced if you

# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount ofRAM for the most important data

# cache in MySQL. Start at 70% of total RAMfor dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a veryimportant data integrity option: logging

# changes to the binary log betweenbackups.

# log_bin

# These are commonly set, remove the # andset as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainlyuseful for reporting servers.

# The server defaults are faster fortransactions and fast SELECTs.

# Adjust sizes as needed, experiment tofind the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]

port = 3306

socket =/data/mysql/run/mysql.sock

default-character-set=gbk

[mysql]

no-auto-rehash

[mysqld]

## enable autocommit

autocommit=1

general_log=off

explicit_defaults_for_timestamp=true

character_set_server = gbk

auto_increment_increment= 2

auto_increment_offset = 1

lower_case_table_names=1

# system

basedir=/data/mysql

datadir=/data/mysql/data/

max_allowed_packet=134217728

max_connections=8192

max_user_connections=8000

open_files_limit=65535

pid_file=/data/mysql/run/mysqld.pid

port=3306

server_id=128

skip_name_resolve=ON

socket=/data/mysql/run/mysql.sock

tmpdir=/data/mysql/tmp

# binlog

binlog_cache_size=32768

binlog_format=row

expire_logs_days=15

log-bin=/data/mysql/log/binlog/master-bin

log-bin-index=/data/mysql/log/binlog/master-bin.index

log_slave_updates=ON

max_binlog_cache_size=2147483648

max_binlog_size=524288000

sync_binlog=100

#relay

# LOGGING #

log_error =/data/mysql/log/alert.log

log_queries_not_using_indexes = 1

slow_query_log = 1

slow_query_log_file = /data/mysql/log/slow.log

log_slave_updates=ON

log_slow_admin_statements=1

long_query_time=1

#slave#

slave_skip_errors=OFF

log_slave_updates=ON

# innodb #

innodb_log_group_home_dir=/data/mysql/log/iblog

innodb_data_home_dir=/data/mysql/log/iblog

innodb_adaptive_flushing=1

innodb_additional_mem_pool_size=20M

innodb_buffer_pool_instances=8

innodb_change_buffering=inserts

innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend

innodb_flush_method = O_DIRECT

innodb_log_files_in_group = 4

innodb_log_file_size = 100M

innodb_flush_log_at_trx_commit = 1

innodb_file_per_table = 1

innodb_buffer_pool_size = 128M

innodb_file_format=Barracuda

innodb_file_io_threads=4

innodb_flush_neighbors=0

innodb_io_capacity=200

innodb_lock_wait_timeout=5

innodb_log_buffer_size=64M

innodb_lru_scan_depth=2048

innodb_max_dirty_pages_pct=60

innodb_old_blocks_time=1000

innodb_online_alter_log_max_size=200M

innodb_open_files=200

innodb_print_all_deadlocks=1

innodb_purge_threads=4

innodb_read_ahead_threshold=0

innodb_read_io_threads=8

innodb_rollback_on_timeout=0

innodb_sort_buffer_size=2M

innodb_spin_wait_delay=6

innodb_stats_on_metadata=0

innodb_strict_mode=1

innodb_sync_array_size=256

innodb_sync_spin_loops=30

innodb_thread_concurrency=64

innodb_use_native_aio=0

innodb_write_io_threads=8

innodb_support_xa=1

[mysqld_safe]

datadir=/data/mysql/data/

---------------------------------------------------------------------------------------------------------------------------------

5执行MySQL安装脚本

# cd /data/mysql/

# ./scripts/mysql_install_db --defaults-file=/data/mysql/my.cnf --user=mysql --datadir=/data/mysql/data

# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

6修改系统环境变量

------------------

# vi /etc/profile

PATH=/data/mysql/bin:$PATH:/sbin

export MYSQL_HOME=/data/mysql
export PATH=$MYSQL_HOME/bin:$PATH

-------------

# source /etc/profile

7自启动脚本

# cp /data/mysql/my.cnf /etc/my.cnf

# cp /data/mysql/support-files/mysql.server/etc/init.d/mysql

# vi /etc/init.d/mysql

修改mysqld_pid_file_path=/data/mysql/run/mysqld.pid

basedir=/data/mysql
datadir=/data/mysql/data

# chkconfig --add mysql

# /etc/init.d/mysql start

# /etc/init.d/mysql stop

# service mysql start

8删除默认匿名账户
# mysql -h127.0.0.1 -uroot -P3306

mysql> DELETE FROM mysql.user WHEREUser='';

mySQL双主复制+keepalived

3.2.10.1 MySQL 双主复制设置

功能说明：
MySQL双主复制和keepalived组建高可用集群，保证2台服务器中，任一台故障不影响整个系统。

MySQL双主复制：2台互为主备. 即 A相对于B ,A为主，B为从; B相对于A, B为主，A为从。其中任一台数据发生改变会同步到另一台, 为了避免同时写入产生问题，只有一台处于活动状态，接受读写请求，另一台处于等待状态，不接受读写请求，只同步复制。

keepalived: 可以为linux提供高可用功能。使用vrrp协议，产生一个虚拟ip (vip), 可以在多个节点切换。

设置MySQL 双主：
1.修改2个数据库所在服务器主机名
若已经设置可跳过这步，

以修改主数据库主机名为例说明：
# vim /etc/sysconfig/network

NETWORKING=yes

HOSTNAME=database1

GATEWAY=134.96.247.1

即时生效：

# hostname database1

2.修改从数据库UUID
如果mysql安装时是通过批量复制安装的，则有可能数据库的UUID一致，进入数据库的datadir目录，修改auto.cnf。随意修改下uuid中的值，重启mysqld服务即可。
# vim /data/mysql/data/auto.cnf

随便修改最后一位字符即可

3 修改主数据库文件，启用中继日志
# vim /data/mysql/my.cnf

找到 #relay
在下面添加：

relay-log=/data/mysql/log/relaylog/slave-relay-bin

relay-log-index=/data/mysql/log/relaylog/slave-relay-bin.index

若没有 /data/mysql/log/relaylog/ 目录，创建即可：
# mkdir -pv /data/mysql/log/relaylog/

# cp /data/mysql/my.cnf /etc/
复制覆盖/etc下配置文件

# service mysql restart

4.在从服务器上创建同步账号
在从服务器上（134.96.247.48）登陆数据库，并配置数据库

创建复制账号，replusr，给予最小权限replication slave

mysql -h127.0.0.1 -uroot -P3306

mysql> set old_passwords=0;

mysql> create user 'replusr'@'%'identified by 'password';

mysql> grant replication slave on *.* to'replusr'@'%';

5.主服务器上配置同步
登录从服务器查看二进制日志状态：
mysql> SHOW MASTER STATUS;

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

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

| slave-bin.000003| 333| | | |

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

1 row in set (0.00 sec)

需要用到上面标注红色的两个参数

登录主服务器：
mysql>CHANGE MASTER TO MASTER_HOST='134.96.247.48',MASTER_USER='replusr',MASTER_PASSWORD='password',MASTER_LOG_FILE='slave-bin.000003',MASTER_LOG_POS=333;

mysql> start slave;

mysql> show slave status \G;

在打印出来的状态下，查看以下两个参数的值：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果这两个参数的值都是yes，则运行正常，可以在master数据库上添加一个数据库或者添加一张表，检查slave数据库上是否存在。

如果这两个参数有任何一个不是 Yes，则说明存在问题。可以查看slave上的数据库错误日志文件查看错误原因。

6.测试：

登录主数据库:
mysql > create database testdb;

mysql > use testdb;

Database changed

mysql > CREATE TABLE users (id INTAUTO_INCREMENT,

name VARCHAR(30),

datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(id));

Query OK, 0 rows affected (0.50 sec)

mysql > INSERT INTO users(name) VALUES('Marko');

Query OK, 1 row affected (0.06 sec)

mysql > select * from users;

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

| id | name | datum |

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

| 1| Marko | 2016-04-26 01:01:11 |

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

1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------------------------------------------

登录从数据库：
mysql > use testdb

Database changed

mysql > select * from users;

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

| id | name | datum |

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

| 1| Marko | 2016-04-26 01:01:11 |

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

1 row in set (0.00 sec)

mysql > INSERT INTO users(name)VALUES('John');

Query OK, 1 row affected (0.39 sec)

mysql > select * from users;

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

| id | name | datum |

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

| 1| Marko | 2016-04-26 01:01:11 |

| 2| John | 2016-04-26 01:04:32 |

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

2 rows in set (0.01 sec)

再登录主数据库：
mysql > select * from users;

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

| id | name | datum |

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

| 1| Marko | 2016-04-26 01:01:11 |

| 2 |John | 2016-04-26 01:04:32 |

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

2 rows in set (0.01 sec)

现在看到所有的表和行都已经同步

安装keepalived
.登录主数据库服务器
上传keepalived rpm 包
上传 keepalived-1.2.13-5.el6_6.x86_64.rpm
lm_sensors-libs-3.1.1-17.el6.x86_64.rpm
net-snmp-libs-5.5-54.el6_7.1.x86_64.rpm
3个包到 /opt
安装3个rpm包:
# rpm -ivh lm_sensors-libs-3.1.1-17.el6.x86_64.rpm
# rpm -ivh net-snmp-libs-5.5-54.el6_7.1.x86_64.rpm
# rpm -ivh keepalived-1.2.13-5.el6_6.x86_64.rpm
登录从数据库服务器，重复以上操作

配置 keepalived
1.设置一个虚拟ip (vip), 此ip 要和主、从数据库在同一网段。

2.登录主数据库服务器
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

# notification_email {

# alexzeng@wordpress.com

# }

# notification_email_from alexzeng@wordpress.com

# smtp_server mx.wordpress.com

# smtp_connect_timeout 30

router_id mysql-ha

}

vrrp_script check_mysql {

script "/data/mysql/keepalived_check.sh 134.96.247.48"
# 此 ip为从数据库ip

interval 2

weight 2

}

vrrp_instance VI_1 {

state BACKUP #被动模式

interface eth0 # ifconfig 检查ip对应的网卡

virtual_router_id 51

priority 100

advert_int 1

nopreempt # only needed onhigher priority node 不主动抢占资源

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysql

}

virtual_ipaddress {

134.96.247.xxx/24dev eth0 label eth0:1 # ifconfig检查ip对应的网卡

}

notify_master /data/mysql/keepalived_master.sh

notify_backup /data/mysql/keepalived_backup.sh

}
---------------------------------------------------------------------------------------------------------------------------------

3.登录从数据库服务器
# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

# notification_email {

# alexzeng@wordpress.com

# }

# notification_email_from alexzeng@wordpress.com

# smtp_server mx.wordpress.com

# smtp_connect_timeout 30

router_id mysql-ha

}

vrrp_script check_mysql {

script "/data/mysql/keepalived_check.sh 134.96.247.47"

# 此 ip为主数据库ip

interval 2

weight 2

}

vrrp_instance VI_1 {

state BACKUP #被动模式

interface eth0 #ifconfig 检查ip对应的网卡

virtual_router_id 51

priority 100

advert_int 1

nopreempt # only needed on higher priority node不主动抢占资源

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysql

}

virtual_ipaddress {

134.96.247.xxx/24 dev eth0 label eth0:1 # ifconfig检查ip对应的网卡

}

notify_master /data/mysql/keepalived_master.sh

notify_backup /data/mysql/keepalived_backup.sh

}
---------------------------------------------------------------------------------------------------------------------------------

4.上传安装包\scripts\keepalived\node1
keepalived_check.sh
keepalived_master.sh
keepalived_backup.sh
3个脚本到主数据库 /data/mysql
# cd /data/mysql

# chmod +x *.sh

上传安装包\scripts\keepalived\node2

keepalived_check.sh

keepalived_master.sh

keepalived_backup.sh

3个脚本到从数据库 /data/mysql

# cd /data/mysql

# chmod +x *.sh

5.修改主、从数据库服务器上 keepalived_check.sh
# cd /data/mysql

# vim keepalived_check.sh

#!/bin/bash

# monitor mysql status

# if this node mysql is dead and its slavedelay less than 120 seconds, then stop its keepalived. The other node will bindthe IP.

export MYSQL_HOME=/data/mysql

export PATH=$MYSQL_HOME/bin:$PATH

mysql="$MYSQL_HOME/bin/mysql"

delay_file="$MYSQL_HOME/log/slave_delay_second.log"

slave_host=$1

#$mysql -u root --connect_timeout=3--execute="select version();"

netstat -tunlp | grep 3306

if [ $? -ne 0 ]; then

delayseconds=`cat $delay_file`

if [$delayseconds -le 120 ]; then

/etc/init.d/keepalived stop

exit1 #bad

# Get slave delay time and save it

$mysql -usk-ppassword -h$slave_host --connect_timeout=3 -e"select version();"

if [ $? -eq 0 ]; then

delayseconds=`$mysql -usk -ppassword -h$slave_host --connect_timeout=3-e"show slave status\G"|grep Seconds_Behind_Master|awk '{print \$2}'`

if[[ "$delayseconds" =~ ^[0-9]+$ ]] ; then

echo "$delayseconds" > $delay_file

else

echo "9999" > $delay_file

exit 0 #good
---------------------------------------------------------------------------------------------------------------------------------

在主数据库服务器上测试运行脚本：

结尾ip为从数据库ip
# ./keepalived_check.sh 134.96.247.48
报错：
ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) theSUPER, REPLICATION CLIENT privilege(s) for this operation
数据库账户 sk 需要 SUPER 权限

登录主数据库：

mysql> grant super on *.* to 'sk'@'%';
此时在运行
# ./keepalived_check.sh 134.96.247.48
tcp 0 0 :::3306 :::* LISTEN 19330/mysqld

Warning: Using a password on the commandline interface can be insecure.

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

| version() |

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

| 5.6.30-log |

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

Warning: Using a password on the commandline interface can be insecure.

在从数据库服务器上测试运行脚本：

结尾ip为主数据库ip

# ./keepalived_check.sh 134.96.247.47

tcp 0 0 :::3306 :::* LISTEN 19330/mysqld

Warning: Using a password on the commandline interface can be insecure.

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

| version() |

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

| 5.6.30-log |

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

Warning: Using a password on the commandline interface can be insecure.

6 修改keepalived_master.sh
# cd /data/mysql
# vim keepalived_master.sh

#!/bin/bash

my_host=`hostname`

current_date=`/bin/date +"%b %d%H:%M:%S"`

From="$my_host"

#mail_list=alexzeng@wordpress.com

#Subject="$my_host is MASTER"

Msgboday="$current_date : mysql isonline at $my_host"

#echo "$Msgboday" |/usr/bin/mailx -s "$Subject""$mail_list"

echo "$Msgboday" >> /data/mysql/log/notify.log
---------------------------------------------------------------------------------------------------------------------------------

在主、从数据库服务器上测试运行脚本：

# cd /data/mysql
# ./keepalived_master.sh
keepalived vip 切换时，会显示通过vip连接到哪个数据库
日志写入到 /data/mysql/log/notify.log

7.修改 keepalived_backup.sh
# vim keepalived_backup.sh

#!/bin/bash

my_host=`hostname`

current_date=`/bin/date +"%b %d%H:%M:%S"`

From="$my_host"

#mail_list=alexzeng@wordpress.com

#Subject="$my_host is BACKUP"

Msgboday="$current_date : mysql is offlineat $my_host"

#echo "$Msgboday" |/usr/bin/mailx -s "$Subject""$mail_list"

echo "$Msgboday" >> /data/mysql/log/notify.log
---------------------------------------------------------------------------------------------------------------------------------

测试运行脚本：

分别在主、从数据库服务器上测试运行
keepalived vip 切换时，会显示通过vip连接到哪个数据库

日志写入到/data/mysql/log/notify.log

启动keepalived

先在主数据库服务器上启动 keepalived,然后再在从数据库服务器上启动·
# service keepalived start

# /etc/init.d/keepalived start

检测日志文件 /var/log/messages

测试：
分别在主、从数据库运行
# service keepalived start

由于主数据库先启动 keepalived ，所以默认 vip 在主数据库服务器上

# ifconfig | grep eth0:1
eth0:1 Link encap:Ethernet HWaddr 00:0C:29:76:56:8B
分别在主、从数据库服务器上通过mysql客户端连接vip：
# mysql -usk -ppassword -h134.96.247.xxx -e"select@@hostname;"
Warning: Using a password on the command line interface can be insecure.

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

| @@hostname |

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

| database1 |
结果应该都一样，都连接到主数据库，从而验证了 vip 在主数据库。

主数据库故障演练：
停止主数据库
# service mysql stop
分别在主、从数据库服务器上运行：
# ifconfig | grep eth0:1
发现vip 切换到从数据库服务器
在从数据库服务器运行：
# mysql -usk -ppassword -h134.96.247.xxx -e"select @@hostname;"

Warning: Using a password on the commandline interface can be insecure.

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

| @@hostname |

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

| database2|
也验证了vip 切换到了从数据库服务器

VIP 恢复回到主数据库：

在主数据库上：
# service mysql start

# service keepalived start

在从数据库上：
# service keepalived stop

在主数据库上：
# ifconfig | grep eth0:1

eth0:1 Link encap:Ethernet HWaddr00:0C:29:76:56:8B

发现vip 切换回来了

在从数据库上
# service keepalived start

分别在主、从数据库服务器上运行：
# service keepalived status
keepalived (pid 28260) 正在运行...
# service mysql status
MySQL running (28174)

正常运行要保持主、从keepalived 处于运行状态
# service keepalived status

开机自动启动keepalived
在主、从数据库服务器上都运行
# chkconfig keepalived off

在主、从数据库服务器上都修改 /etc/rc.d/rc.local
# vim /etc/rc.d/rc.local
在最后添加：

/etc/init.d/keepalived start
重启后有可能vip不在主数据库上，此时停止从数据库上keepalived, vip就会切回主数据库，再启动从数据上keepalived,最后验证2台服务器keepalived都处于启动状态。

3.2.11 MySQL备份

备份工具：Percona XtraBackup

Xtrabackup是由percona提供的mysql数据库备份工具，
特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快；

为什么不使用mysqldump:

Mysqldump是mysql自带的，支持逻辑备份，将需要备份内容存为SQL语句保存文件扩展名为.sql. 由于mysqldump恢复时是执行.sql文件的sql语句，在数据库容量大时恢复非常慢。

安装：
在主数据库所在服务器：
将percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz
MySQL-shared-compat-5.6.30-1.linux_glibc2.5.x86_64.rpm
perl-DBD-MySQL-4.013-3.el6.x86_64.rpm 上传到 /opt
# cd /opt
# rpm -ivh MySQL-shared-compat-5.6.30-1.linux_glibc2.5.x86_64.rpm
# rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpm
# tar -xf percona-xtrabackup-2.4.2-Linux-x86_64.tar.gz
# mv percona-xtrabackup-2.4.2-Linux-x86_64 xtrabackup
# cd /opt/xtrabackup/bin
# ln -sv /opt/xtrabackup/bin/* /usr/local/sbin/

创建最小权限备份用户 bkpuser

mysql> CREATE USER 'bkpuser'@'localhost'IDENTIFIED BY 's3cret';

mysql> REVOKE ALL PRIVILEGES, GRANTOPTION FROM 'bkpuser'@'localhost';

mysql> GRANT RELOAD, LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';

mysql> FLUSH PRIVILEGES;

创建备份目录
# mkdir -pv /data/backup

备份脚本：
将mysqlbak.sh 上传到/app/scripts目录
# chmod +x /app/scripts/mysqlbak.sh
# vim /app/scripts/mysqlbak.sh

脚本内容：
---------------------------------------------------------------------------------------------------------------------------------
#/bin/bash

#使用percona-xtrabackup备份数据库

#备份存放路径

bk_dir=/data/backup

#备份数据库文件并打包

/usr/local/sbin/innobackupex --user=bkpuser--password=s3cret --stream=tar $bk_dir | gzip > $bk_dir/`date+%F_%H-%M-%S`.tar.gz

# 删除5天之后的备份

find $bk_dir/* -mtime +6 -exec rm -fr {} \;> /dev/null 2>&1

---------------------------------------------------------------------------------------------------------------------------------

添加到crontab里，每晚1点运行
# crontab -e

0 1 * * * sh /app/scripts/mysqlbak.sh >/dev/null2>&1

在从数据库上执行以上相同的操作，由于开启了双主复制，从数据库会自动创建备份用户bkpuser

测试脚本运行：

# sh /app/scripts/mysqlbak.sh
全程未报错且最后一行显示：
160430 15:42:41 completed OK!
在 /data/backup 目录产生以日期命名的文件

阅读全文

0 0