屌炸天實戰 Mysql 系列教程（五）多實例、高可用生產環境實戰

第一篇：屌炸天實戰 MySQL 系列教程（一）生產標準線上環境安裝配置案例及棘手問題解決第二篇：屌炸天實戰 MySQL 系列教程（二）史上最屌、你不知道的資料庫操作第三篇：屌炸天實戰 MySQL 系列教程（三）你不知道的視圖、觸發器、存儲過程、函數、事物、索引、語句第四篇：屌炸天實戰 M ...

本章內容：

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:[email protected]
#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:[email protected]
#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/m

屌炸天實戰 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.多實例配置

屌炸天實戰 Mysql 系列教程（五）多實例、高可用生產環境實戰

4：部署MySQL