屌炸天实战 Mysql 系列教程（五）多实例、高可用生产环境实战

第一篇：屌炸天实战 MySQL 系列教程（一）生产标准线上环境安装配置案例及棘手问题解决

第二篇：屌炸天实战 MySQL 系列教程（二）史上最屌、你不知道的数据库操作

第三篇：屌炸天实战 MySQL 系列教程（三）你不知道的视图、触发器、存储过程、函数、事物、索引、语句

第四篇：屌炸天实战 MySQL 系列教程（四）主从复制、读写分离、模拟宕机、备份恢复方案生产环境实战

第五篇：屌炸天实战 Mysql 系列教程（五）多实例、高可用生产环境实战

本章内容：

MySQL 多实例
简介
安装前的配置
生产环境屌炸天的脚步
初始化登录实现多实例
要再增加一个多实例怎么办？解决
MySQL 高可用
部署配置heardbeat
部署配置drbd
heardbeat与drbdde 联合配置
部署MySQL
联合heardbeat配置MySQL

一、MySQL 多实例

1、简介

前几年一个朋友跟我说他们公司一台物理服务器上跑 3 到 5 个实例，我说你们公司得多穷啊，他说这个是合理规划，发挥服务器最大性能，你说呢？

别问小编公司跑几个，小编公司有钱不在乎，一台小型器最多跑 3 个实例，当然，每天的数据量还是很大的。

那什么使多实例呢，为什么用它？

多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307）；

运行多个MySQL服务进程，这些进程通过不同的socket监听不同的服务端口来提供各自的服务。

MySQL多实例共用一个 MySQL 的安装程序，使用不同（也可相同）的 my.cnf 配置文件，启动程序和数据文件。

在提供服务时，多实例 MySQL 在逻辑上是各自独立的，多个实例的本身是根据配置文件对应的设定值，来获得服务器的相关硬件资源多少。

2、安装配置前操作

安装配置前的一些配置少不了

文件的目录最好也规矩，这样才专业

#杀掉进程，避免冲突，删掉启动命令。
pkill mysqld
ps -ef|grep mysql
rm -f /etc/init.d/mysqld
# 创建目录,授权
mkdir -p /data/{3306,3307}/data
chown -R mysql.mysql /data/
tree /data/
    /data/      #总的多实例根目录
├── 3306        #3306实例的目录
│   └── data    #3306实例的数据文件目录
└── 3307        #3307实例的目录
    └── data    #3307实例的数据文件目录
    4 directories, 0 files

3、配置文件

话不多说，直接上生产环境线上的配置文件：

[client]
port            = 3306
socket          = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /data/3306/error.log
#log-slow-queries = /data/3306/slow.log
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql3306.err
pid-file=/data/3306/mysqld.pid

3306_my.cnf

#!/bin/sh
#Nick Suo
#email:630571017@qq.com
#blog:http://www.cnblogs.com/suoning
 
 
#init
port=3306
mysql_user="root"
mysql_pwd="suoning"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
#stop function
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
   fi
}
#restart function
function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}
case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

3306_mysql

[client]
port            = 3307
socket          = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user    = mysql
port    = 3307
socket  = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error = /data/3307/error.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
#log-bin = /data/3307/mysql-bin
relay-log = /data/3307/relay-bin
relay-log-info-file = /data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 3
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3307/mysql3307.err
pid-file=/data/3307/mysqld.pid

3307_my.cnf

#!/bin/sh
#Nick Suo
#email:630571017@qq.com
#blog:http://www.cnblogs.com/suoning
 
#init
port=3307
mysql_user="root"
mysql_pwd="suoning"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
#stop function
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
   fi
}
#restart function
function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}
case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

3307_mysql

#上传及拷贝配置文件
cd /home/oldsuo/tools/
rz data.zip
unzip data.zip
    Archive:  data.zip
           creating: data/
           creating: data/3306/
          inflating: data/3306/my.cnf        
          inflating: data/3306/mysql         
           creating: data/3307/
          inflating: data/3307/my.cnf        
          inflating: data/3307/mysql
cp data/3306/my.cnf /data/3306/
cp data/3307/my.cnf /data/3307/
tree /data/

#拷贝启动文件，加执行权限
cp data/3306/mysql /data/3306/
cp data/3307/mysql /data/3307/
chmod +x /data/3306/mysql
chmod +x /data/3307/mysql
tree /data/           
/data/
├── 3306
│   ├── data
│   ├── my.cnf
│   └── mysql
└── 3307
    ├── data
    ├── my.cnf
└── mysql
# 多实例启动文件的启动mysql服务实质：
mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &
mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
# 多实例启动文件的停止mysql服务实质：
mysqladmin -u root -poldsuo -S /data/3306/mysql.sock shutdown
mysqladmin -u root -poldsuo -S /data/3307/mysql.sock shutdown

4、初始化启动登录

此时就算完成了，可以初始化并启动了，so easy

#初始化数据库，存放数据路径，自己放自己的目录下
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql
#启动mysql,并检查端口
/data/3306/mysql start
/data/3307/mysql start
netstat -lntup|grep 330[6-7]
    tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      27896/mysqld        
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      27174/mysqld
#授权，并检查（批量）
find /data/ -type f -name "mysql"
/data/3306/mysql
/data/3307/mysql
find /data/ -type f -name "mysql" -exec chmod 700 {} \;
find /data/ -type f -name "mysql" -exec chown root.root {} \;
find /data/ -type f -name "mysql" -exec ls -l {} \;
#设置mysql登陆密码，为nick。
mysqladmin -u root -S /data/3306/mysql.sock password 'nick'
mysqladmin -u root -S /data/3307/mysql.sock password 'nick'
#修改密码
mysqladmin -uroot -pnick password 'oldsuo' -S /data/3306/mysql.sock
#多实例数据库的登陆方法，需要参数-S、并指定sock。
mysql -S /data/3306/mysql.sock        #不设置密码登陆
mysql -S /data/3307/mysql.sock
mysql -uroot -p -S /data/3306/mysql.sock        #设置密码登陆
mysql -uroot -p -S /data/3307/mysql.sock 
mysql -uroot -p –hlocalhost -S /data/3306/mysql.sock        #远程登陆
mysql -uroot -p –hlocalhost -S /data/3307/mysql.sock
mysql -uroot -p –h 192.168.200.98 -P 3306
mysql -uroot -p –h 192.168.200.98 -P 3307
 
#创建库，名为d3306、d3307。并检查。
create database d3306;
create database d3307;
show databases;

5、增加一个mysql实例

问题又来了，要增加一个实例怎么弄呢？

其实不难，只需按上面步骤，创建目录，在修改相应配置文件，初始化就ok

#增加一个mysql实例
mkdir -p /data/3308/data
cp /data/3306/my.cnf /data/3308/
cp /data/3306/mysql /data/3308/ 
chown -R mysql.mysql /data/3308/
cd /data/3308/
vim my.cnf
:g/3306/s//3308/g        #批量替换命令
vi mysql
:g/3306/s//3308/g
#初始化数据库
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data --user=mysql
#启动数据库，并检查端口
/data/3308/mysql start
netstat -lntup|grep 330
#设置密码为nick。
mysqladmin -u root -S /data/3308/mysql.sock password 'nick'
#登陆mysql
mysql -uroot -p -S /data/3308//mysql.sock

二、MySQL 高可用双机热备

小编啥也不说了，直接把公司得环境搬上来吧

本文采用 heartbeat + drbd + MySQL 实现MySQL的高可用。

其原理图如下：

1、部署heartbeat3.0

heartbeat 是一个高可用集群架构，它最核心的两个部分为：心跳监测和资源接管，心跳监测是通过HA心跳实时监测，机器之间相互发送报文来告诉对方自己当前的状态，如果在指定的时间内未收到对方发送的报文，那就认为对方以宕机等，这时启动资源接管，使备份服务器变为主服务器，使服务器正常运行。

1.1.配置主机名与hosts

一惯的，需先改主机名与hosts文件

1>    data-1-1
hostname data-1-1
sed -i 's#HOSTNAME=localhost.localdomain#HOSTNAME=data-1-1#g' /etc/sysconfig/network
tail -1 /etc/sysconfig/network
/sbin/route add -host 192.168.1.5 dev eth1
echo '/sbin/route add -host 192.168.1.5' >>/etc/rc.local
route -n
#退出重新登录查看修改后主机名
2>    data-1-2
hostname data-1-2
sed -i 's#HOSTNAME=localhost.localdomain#HOSTNAME=data-1-2#g' /etc/sysconfig/network
tail -1 /etc/sysconfig/network
/sbin/route add -host 192.168.1.3 dev eth1
echo '/sbin/route add -host 192.168.1.3' >>/etc/rc.local
route -n

[root@localhost ~]# hostname data-1-2
[root@localhost ~]# sed -i 's#HOSTNAME=localhost.localdomain#HOSTNAME=data-1-2#g' /etc/sysconfig/network
[root@localhost ~]# tail -1 /etc/sysconfig/network
HOSTNAME=data-1-2
[root@localhost ~]# /sbin/route add -host 192.168.1.6 dev eth1
[root@localhost ~]# echo '/sbin/route add -host 192.168.1.6' >>/etc/rc.local 
[root@localhost ~]# route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
192.168.1.6     0.0.0.0         255.255.255.255 UH    0      0        0 eth1
192.168.1.0     0.0.0.0         255.255.255.0   U     0      0        0 eth1
192.168.200.0   0.0.0.0         255.255.255.0   U     0      0        0 eth0
169.254.0.0     0.0.0.0         255.255.0.0     U     1002   0        0 eth0
169.254.0.0     0.0.0.0         255.255.0.0     U     1003   0        0 eth1
0.0.0.0         192.168.200.6   0.0.0.0         UG    0      0        0 eth0

View Code

1.2.下载安装epel包

yum 安装前需先安装 epel 包

mkdir -p /home/oldsuo/tools
cd /home/oldsuo/tools/
sed -i 's#keepcache=0#keepcache=1#g' /etc/yum.conf
wget http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
rpm -ivh epel-release-6-8.noarch.rpm
rpm -qa |grep epel

1.3.安装heartbeat

yum 安装，简单快捷

yum install heartbeat –y

1.4.配置heartbeat

安装完配置，以下是快速配置文档

cd /etc/ha.d/
cp ha.cf ha.cf.ori
cp authkeys authkeys.ori
cp haresources haresources.ori
chmod 600 authkeys
vim ha.cf
debugfile      /var/log/ha-debug
logfile         /var/log/ha-log
logfacility      local1
keepalive      2
deadtime      30
warntime      10
initdead       60
 
#bcast  eth1
mcast   eth1    225.0.0.1 694 1 0
auto_failback       on
node            data-1-1
node            data-1-2
crm             no
vim authkeys
auth 1
1 sha1  sui1bian2xie3duo4dian5suibianxieduodian
vim haresources
#192.168.1.3    
data-1-1       IPaddr::192.168.200.91/24/eth0
#data-1-1      IPaddr::192.168.200.91/24/eth0    httpd
 
#192.168.1.5
data-1-2       IPaddr::192.168.200.94/24/eth0
#mysql master
#picdata-1-1    IPaddr::192.168.1.3/24/eth1     mysqld
#picdata-1-1    IPaddr::192.168.1.3/24/eth1
 
#mysql slave
#picdata-1-2    IPaddr::192.168.1.5/24/eth1

1.5.配置hosts

检查 hosts，一定要做

#！！！主机名必须与uname -n 显示一致！
cat >>/etc/hosts<<eof
192.168.1.3 data-1-1
192.168.1.5 data-1-2
eof
tail -2 /etc/hosts
ping data-1-1
ping data-1-2

[root@data-1-2 heartbeat-3.0.4]# cat >>/etc/hosts<<eof
> 192.168.1.3 data-1-1
> 192.168.1.5 data-1-2
> eof
[root@data-1-2 heartbeat-3.0.4]# tail -2 /etc/hosts
192.168.1.6 data-1-1
192.168.1.7 data-1-2
[root@data-1-2 heartbeat-3.0.4]# ping data-1-1
64 bytes from data-1-1 (192.168.1.3): icmp_seq=1 ttl=64 time=1.98 ms
[root@data-1-2 heartbeat-3.0.4]# ping data-1-2
64 bytes from data-1-2 (192.168.1.5): icmp_seq=1 ttl=64 time=0.040 ms

View Code

1.6.启动heartbeat服务

可以启动啦，啦

/etc/init.d/iptables stop
setenforce 0
/etc/init.d/heartbeat start
ps -ef |grep hear

[root@data-1-1 ha.d]# /etc/init.d/iptables stop
[root@data-1-1 ha.d]# setenforce 0
setenforce: SELinux is disabled
[root@data-1-1 ha.d]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO:  Resource is stopped
INFO:  Resource is stopped
Done.
[root@data-1-1 ha.d]# ps -ef |grep hear
root      5282     1  0 14:17 ?        00:00:00 heartbeat: master control process
root      5286  5282  0 14:17 ?        00:00:00 heartbeat: FIFO reader        
root      5287  5282  0 14:17 ?        00:00:00 heartbeat: write: mcast eth1  
root      5288  5282  0 14:17 ?        00:00:00 heartbeat: read: mcast eth1   
root      5298  1763  0 14:18 pts/0    00:00:00 grep hear

View Code

2、部署 DRBD

DRBD 是一个用软件实现、不共享、服务器之间镜像块块的存储复制解决方案。

在高可用(HA)环境中使用 DRBD，可以代替一个共享的盘阵。

2.1.环境准备

#！！！双网卡，双硬盘

2.2.对磁盘分区

超过2T的磁盘，一般是 parted 分区，支持更好

#parted分区
##data-1-1和data-1-2操作一样！
fdisk –l
parted /dev/xvdb mklabel gpt
parted /dev/xvdb mkpart primary 0 1024
parted /dev/xvdb p
parted /dev/xvdb mkpart primary 1025 2146
parted /dev/xvdb p

[root@data-1-1 ~]# fdisk -l
Disk /dev/xvdb: 2147 MB, 2147483648 bytes
[root@data-1-1 ~]# parted /dev/xvdb mklabel gpt
信息: You may need to update /etc/fstab.                                  
[root@data-1-1 ~]# parted /dev/xvdb mkpart primary 0 1024
警告: The resulting partition is not properly aligned for best performance.
忽略/Ignore/放弃/Cancel? Ignore                                           
信息: You may need to update /etc/fstab.                                  
[root@data-1-1 ~]# parted /dev/xvdb p
Number  Start   End     Size    File system  Name     标志
 1      17.4kB  1024MB  1024MB               primary
[root@data-1-1 ~]# parted /dev/xvdb mkpart primary 1025 2146
警告: The resulting partition is not properly aligned for best performance.
忽略/Ignore/放弃/Cancel? Ignore                                           
信息: You may need to update /etc/fstab.                                  
[root@data-1-1 ~]# parted /dev/xvdb p
1      17.4kB  1024MB  1024MB               primary
2      1025MB  2146MB  1121MB               primary

View Code

2.3.安装配置DRBD

以下是快速安装配置文档

1>    安装epel包
mkdir -p /home/oldsuo/tools
cd /home/oldsuo/tools/
sed -i 's#keepcache=0#keepcache=1#g' /etc/yum.conf
wget http://mirror.ventraip.net.au/elrepo/elrepo/el6/x86_64/RPMS/elrepo-release-6-5.el6.elrepo.noarch.rpm
rpm -ivh elrepo-release-6-5.el6.elrepo.noarch.rpm
2>    安装drbd,并加载到内核
yum install drbd kmod-drbd84 -y
rpm -qa |grep drbd
cd /home/oldsuo/tools/
export LC_ALL=C
lsmod |grep drbd
modprobe drbd
lsmod |grep drbd
echo "modprobe drbd >/dev/null 2>&1" >/etc/sysconfig/modules/drbd.modules
tail -1 /etc/sysconfig/modules/drbd.modules
#!!!报错FATAL: Module drbd not found.
http://blog.sina.com.cn/s/blog_702bd5360101h84d.html
[root@data-1-2 ~]# modprobe drbd
FATAL: Module drbd not found.
解决方法：yum install kernel* -y        重启服务器！

2.4.配置drbd.conf文件

一惯需改配置文件

vim /etc/drbd.conf
global {
 usage-count no;
}
common {
 syncer {
   rate 330M;
   verify-alg crc32c;
 }
}
resource data {
 protocol C;
 disk   {
   on-io-error  detach;
 }
 on data-1-1 {
    device /dev/drbd0;
    disk /dev/xvdb1;
    address 192.168.1.6:7788;
    meta-disk /dev/xvdb2;
    }
 on data-1-2 {
    device /dev/drbd0;
    disk /dev/xvdb1;
    address 192.168.1.7:7788;
    meta-disk /dev/xvdb2;
    }
}

2.5.初始化

启动前需初始化

drbdadm create-md data
[root@data-1-2 tools]# drbdadm create-md data
initializing activity log
NOT initializing bitmap
Writing meta data...
New drbd meta data block successfully created.

2.6.启动、关闭及查看状态

启动啦，啦，啦，检查状态

drbdadm up data
cat /proc/drbd
drbdadm down data    关闭
[root@data-1-1 tools]# drbdadm up data
[root@data-1-1 tools]# cat /proc/drbd
version: 8.4.6 (api:1/proto:86-101)
GIT-hash: 833d830e0152d1e457fa7856e71e11248ccf3f70 build by phil@Build64R6, 2015-04-09 14:35:00
 0: cs:Connected ro:Secondary/Secondary ds:Inconsistent/Inconsistent C r-----
    ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:999984

2.7.同步

配置同步

#！！！主上操作！
drbdadm -- --overwrite-data-of-peer primary data
cat /proc/drbd
Primary/Secondary

[root@data-1-1 ~]# drbdadm -- --overwrite-data-of-peer primary data
[root@data-1-1 ~]# cat /proc/drbd
version: 8.4.4 (api:1/proto:86-101)
GIT-hash: 74402fecf24da8e5438171ee8c19e28627e1c98a build by root@data-1-1, 2015-12-11 20:06:46
 0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
    ns:58112 nr:0 dw:0 dr:58784 al:0 bm:3 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:490464
        [=>..................] sync'ed: 11.2% (490464/548576)K
        finish: 0:00:08 speed: 58,112 (58,112) K/sec
[root@data-1-1 ~]# cat /proc/drbd
version: 8.4.4 (api:1/proto:86-101)
GIT-hash: 74402fecf24da8e5438171ee8c19e28627e1c98a build by root@data-1-1, 2015-12-11 20:06:46
 0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
    ns:517248 nr:0 dw:0 dr:517920 al:0 bm:31 lo:0 pe:146 ua:0 ap:0 ep:1 wo:f oos:34420
        [=================>..] sync'ed: 94.1% (34420/548576)K
        finish: 0:00:00 speed: 36,724 (36,724) K/sec
[root@data-1-1 ~]# cat /proc/drbd
version: 8.4.4 (api:1/proto:86-101)
GIT-hash: 74402fecf24da8e5438171ee8c19e28627e1c98a build by root@data-1-1, 2015-12-11 20:06:46
 0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
    ns:548576 nr:0 dw:0 dr:549248 al:0 bm:34 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
[root@data-1-1 ~]# cat /proc/drbd
version: 8.4.4 (api:1/proto:86-101)
GIT-hash: 74402fecf24da8e5438171ee8c19e28627e1c98a build by root@data-1-1, 2015-12-11 20:06:46
 0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
    ns:548576 nr:0 dw:0 dr:549248 al:0 bm:34 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

View Code

2.8.挂载磁盘

挂载磁盘

#主！！！
mkfs.ext4 /dev/drbd0
mkdir /data
mount /dev/drbd0 /data/
df -HT
mount /dev/xvdb2 /mnt/
#从！！！
mkdir /data

[root@data-1-1 tools]# mkfs.ext4 /dev/drbd0         
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
62592 inodes, 249995 blocks
12499 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=260046848
8 block groups
32768 blocks per group, 32768 fragments per group
7824 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376
Writing inode tables: done                            
Creating journal (4096 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 23 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@data-1-1 tools]# mkdir /md1
[root@data-1-1 tools]# mount /dev/drbd0 /md1/
[root@data-1-1 tools]# df -HT
Filesystem                   Type   Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root ext4    49G  6.4G   40G  14% /
tmpfs                        tmpfs  187M     0  187M   0% /dev/shm
/dev/xvda1                   ext4   500M   85M  389M  18% /boot
/dev/drbd0                   ext4   992M  1.3M  939M   1% /data
[root@data-1-1 data]# mount /dev/xvdb2 /mnt/
mount: you must specify the filesystem type

View Code

3：配合heartbeat调试drbd服务配置

联合 heartbeat 调试配置 DRBD 服务，两者结合

#两台操作！
/etc/init.d/heartbeat stop
#后面加入内容，不能换行
vim /etc/ha.d/haresources
data-1-1       IPaddr::192.168.200.91/24/eth0 drbddisk::data Filesystem::/dev/drbd0::/data::ext4
/etc/init.d/heartbeat start
###！！！/etc/ha.d/resource.d/IPaddr！！！
###！！！/etc/ha.d/resource.d/drbddisk！！！

4：部署MySQL

快速安装 MySQL 文档

4.1.解压配置

mkdir -p /home/oldsuo/tools/
cd /home/oldsuo/tools/
rz 上传免编译安装包
ls mysql-5.5.47-linux2.6-x86_64.tar.gz
tar zxf mysql-5.5.47-linux2.6-x86_64.tar.gz
mkdir -p /application/
mv mysql-5.5.47-linux2.6-x86_64 /application/mysql-5.5.47
ln -s /application/mysql-5.5.47/ /application/mysql
ls -l /application/
#检查
[root@data-1-1 tools]# ls -l /application/
lrwxrwxrwx   1 root root        26 Dec 13 13:25 mysql -> /application/mysql-5.7.10/
drwxr-xr-x   9 7161 wheel     4096 Nov 30 03:54 mysql-5.7.10

4.2.创建用户

groupadd mysql
useradd -g mysql -M mysql

4.3.多实例配置

#杀掉进程，避免冲突，删掉启动命令。
pkill mysqld
ps -ef|grep mysql
rm -f /etc/init.d/mysqld
#上传及拷贝配置文件
cd /home/oldsuo/tools/
rz data.zip
unzip data.zip
    Archive:  data.zip
           creating: data/
           creating: data/3306/
          inflating: data/3306/my.cnf        
          inflating: data/3306/mysql         
           creating: data/3307/
          inflating: data/3307/my.cnf        
          inflating: data/3307/mysql
cp data/3306/my.cnf /data/3306/
cp data/3307/my.cnf /data/3307/
tree /data/
 
#拷贝启动文件，加执行权限
cp data/3306/mysql /data/3306/
cp data/3307/mysql /data/3307/
chmod +x /data/3306/mysql
chmod +x /data/3307/mysql
tree /data/           
/data/                #总的多实例根目录
├── 3306            #3306实例的目录
│   ├── my.cnf    #3306实例的配置文件
│   └── mysql        #3306实例的启动文件
└── 3307            #3307实例的目录
    ├── my.cnf    #3307实例的配置文件
└── mysql        #3307实例的启动文件

4.3.初始化数据库，启动单实例

#初始化数据库
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
[root@data-1-1 ~]# /application/mysql/bin/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
2015-12-13 13:53:25 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2015-12-13 13:53:33 [WARNING] The bootstrap log isn't empty:
2015-12-13 13:53:33 [WARNING] 2015-12-13T05:53:26.040946Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2015-12-13T05:53:26.076433Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2015-12-13T05:53:26.076465Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
#授权,并做替换
find /data –name mysql.mysql /data
sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe
#启动单实例，并检查
/data/3306/mysql start
sleep 6
lsof -i :3306

5：配合heartbeat调试MySQL

#两边操作，添加mysql。
cp /data/3306/mysql /etc/ha.d/resource.d/
chmod +x /etc/ha.d/resource.d/
vim /etc/ha.d/haresources
data-1-1       IPaddr::192.168.200.91/24/eth0 drbddisk::data Filesystem::/dev/drbd0::/md1::ext4 mysql

屌炸天实战 Mysql 系列教程（五） 多实例、高可用生产环境实战

一、MySQL 多实例

1、简介

2、安装配置前操作

3、配置文件

4、初始化启动登录

5、增加一个mysql实例

二、MySQL 高可用双机热备

1、部署heartbeat3.0

1.1.配置主机名与hosts

1.2.下载安装epel包

1.3.安装heartbeat

1.4.配置heartbeat

1.5.配置hosts

1.6.启动heartbeat服务

2、部署 DRBD

2.1.环境准备

2.2.对磁盘分区

2.3.安装配置DRBD

2.4.配置drbd.conf文件

2.5.初始化

2.6.启动、关闭及查看状态

2.7.同步

2.8.挂载磁盘

3：配合heartbeat调试drbd服务配置

4： 部署MySQL

4.1.解压配置

4.2.创建用户

4.3.多实例配置

4.3.初始化数据库，启动单实例

5： 配合heartbeat调试MySQL

屌炸天实战 Mysql 系列教程（五）多实例、高可用生产环境实战

4：部署MySQL

5：配合heartbeat调试MySQL