Drbd+heartbeat+mysql: Configuring High Availability for Mysql

Drbd+heartbeat+mysql: Configuring High Availability for Mysql

Description

Today, the second application of DRBD, using drbd+heartbeat+mysql: Configuring Mysql's High Availability

Environment:

[Email protected] ~]# Cat/etc/issuecentos release 6.4 (Final) Kernel \ r on an \m[[email protected] ~]# uname-r2.6.32-358 . el6.i686

dbm137	192.168.186.137	Dbm137.51.com	Primary	Drbd+heartbeat+mysql
dbm138	192.168.186.138	Dbm138.51.com	Secondary	Drbd+heartbeat+mysql
Vip	192.168.186.200

Prepare for Work and install DRBD:

http://732233048.blog.51cto.com/9323668/1665979

Install the configuration MySQL:

Install MySQL: (dbm137,dbm138)

# #安装依赖包: [[email protected] ~]# yum-y install gcc gcc-c++ autoconf automake Bison ncurses-devel libtool-ltdl-devel* CMA Ke make

[[email protected] ~]# mkdir -p /opt/mysql/data      # Create a temporary data directory for the database [[email protected] ~]# cd /usr/local/src/[[email protected] src]#  wget http://mirrors.sohu.com/mysql/mysql-5.6/mysql-5.6.22.tar.gz[[email protected] src]#  tar -zxf mysql-5.6.22.tar.gz[[email protected] src]# cd mysql-5.6.22[[ email protected] mysql-5.6.22]# cmake  -dcmake_install_prefix=/usr/local/mysql - dmysql_datadir=/opt/mysql/data -dsysconfdir=/usr/local/mysql -dwith_myisam_storage_engine=1 - Dwith_innobase_storage_engine=1 -dwith_memory_storage_engine=1 -dwith_partition_storage_engine=1   -dmysql_unix_addr=/var/lib/mysql/mysql.sock  -ddefault_charset=utf8 -ddefault_ collation=utf8_general_ci  -dextra_charsets:string=utf8,gbk  -dwith_debug=0# #注意-dcmake_ Install_prefix,-dmysql_datadir and-DThe value of the SYSCONFDIR option [[email protected] mysql-5.6.22]# make                #这个过程可能会很久 [[Email protected] mysql-5.6.22]# make  install[[email protected] ~]# groupadd mysql              #创建mysql用户组 [[email protected] ~]# useradd -s / Sbin/nologin -g mysql mysql[[email protected] ~]# chown -r mysql.mysql  /opt/mysql     #授权数据目录 [[email protected] ~]# /usr/local/mysql/scripts/ mysql_install_db --basedir=/usr/local/mysql --datadir=/opt/mysql/data --defaults-file=/usr/ local/mysql/my.cnf --user=mysql       #初始化数据库

Configure MySQL:

dbm137 (Primary)

[[email protected] ~]# mount /dev/drbd0 /data/          #配置前先把DRBD设备挂载 [[email protected] ~]# df -hfilesystem             size  used avail use% mounted on /dev/mapper/volgroup-lv_root                        19G  3.4G   14G   20% /tmpfs                   58m     0   58m   0% /dev/ shm/dev/sda1             477m    43M  409M  10% /boot/dev/drbd0             8.8g    21m  8.3g   1% /data[[email protected] ~]# mkdir  -p /data/mysql/binlog        #创建二进制日志目录 [[email protected]  ~]# mkdir -p /data/mysql/data         # Create the data directory for the database [[email protected] ~]# cp -a /opt/mysql/data/* /data/mysql/data/        #拷贝数据到数据库目录 [[Email protected] ~]# chown -r mysql.mysql  /data/mysql/     #授权mysql目录 [[email protected] ~]# cd /data/mysql/ DATA/[[EMAIL PROTECTED] DATA]# LLTOTAL 110604-RW-RW---- 1 mysql mysql  12582912 JUL  2 12:00 IBDATA1-RW-RW---- 1 mysql mysql 50331648  JUL  2 12:00 IB_LOGFILE0-RW-RW---- 1 mysql mysql 50331648  jul  2 12:00 IB_LOGFILE1DRWX------ 2 mysql mysql     4096 Jul   2 12:00 MYSQLDRWX------ 2 mysql mysql     4096  JUL  2 12:00 PERFORMANCE_SCHEMADRWX------ 2 mysql mysql      4096 jul  2 12:00 test[[email protected] ~]# mv /usr/ local/mysql/my.cnf /usr/local/mysql/my.cnf.old[[email protected] ~]# vi /usr/local/ mysql/my.cnf          #修改配置文件 [mysqld]basedir = /usr/ local/mysql   #安装路径datadir  = /data/mysql/data   #数据目录port  = 3306server_ id = 1               # This value does not modify the pid-file = /data/mysql/data/mysql.pid   #进程文件socket  = /var/lib/mysql/ Mysql.sockdefault_storage_engine = innodblog-bin = /data/mysql/binlog/mysql-binlog   #二进制文件expire_logs_days  =  14max_binlog_size = 5Gbinlog_cache_size = 10Mmax_binlog_cache_size =  20mslow_query_loglong_query_time = 2slow_query_log_file = /data/mysql/data/slow.log    #slow日志文件open_files_limit  = 65535innodb = FORCEinnodb_buffer_pool_size =  100m    #注意这个参数innodb_log_file_size  = 1gquery_cache_size = 0thread_cache_ size = 64table_definition_cache = 512table_open_cache = 512max_connections  = 20sort_buffer_size = 10mmax_allowed_packet = 6msql_mode=no_engine_substitution, Strict_trans_tables[client] #socket  = /var/lib/mysql/mysql.sock# #注意: innodb_buffer_pool_size:## The main role is to cache the index of the InnoDB table, data, buffer when inserting data # #默认值: 128m# #专用mysql服务器设置此值的大小: System memory 70%-80% Best # #如果你的系统内存不大, view this parameter, set its value smaller (if the value is set large , the boot will be error)

[[email protected] ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/ init.d/mysqld     #拷贝启动脚本 [[email protected] ~]# /etc/init.d/mysqld  start          #启动mysql服务Starting  mysql ....  success! # #注意: # #千万不要, do not set up MySQL boot auto-boot, and do not start automatically, but do not configure the automatic starting of the startup. Because the MySQL service is started by Heartbeat unified management [[Email protected] ~]# ps -ef | grep mysqlroot      16284     1  0 12:46 pts/1     00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data  --pid-file=/data/mysql/data/mysql.pidmysql    16783 16284  6  12:46 pts/1    00:00:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/ mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lIb/plugin --user=mysql --log-error=/var/log/mysqld.log --open-files-limit=65535 --pid-file =/data/mysql/data/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306root      16878  1286  0 12:47 pts/1    00:00:00 grep  mysql[[email protected] ~]# vi /etc/profile             #修改PATH路径 # #在最后添加: export path= $PATH:/usr/local/mysql/bin[[email  protected] ~]# source /etc/profile        #立即生效

dbm138 (secondary)

# #下面的操作一定要看仔细 # #先进行DRBD设备的主备切换, switch dbm137 to seocndary, switch dbm138 to primary# #在dbm137 (primary) side Execution [[email protected] ~]#/ Etc/init.d/mysqld stop #将137的mysql服务stop掉Shutting down MySQL. success! [Email protected] ~]# umount/dev/drbd0 #卸载DRBD设备 [[email protected] ~]# Drbdadm secondary r0 #切换137为seco Ndary[[email protected] ~]# CAT/PROC/DRBD | grep roversion:8.3.16 (api:88/proto:86-97) 0:cs:connected ro:secondary/secondary ds:uptodate/uptodate C r-----

# #在dbm138 (secondary) Side Execution [[email protected] ~]# drbdadm  primary r0           #把138切换为primary [[email protected] ~]# cat /proc/ drbd | grep roversion: 8.3.16  (api:88/proto:86-97)  0: cs:connected ro: Primary/secondary ds:uptodate/uptodate c r-----[[Email protected] ~]# mount  /dev/drbd0 /data/      #挂载DRBD设备 [[EMAIL PROTECTED] ~]# DF  -hfilesystem            size  used  Avail Use% Mounted on/dev/mapper/VolGroup-lv_root                        19g   3.4G   14G  20% /tmpfs                   58m     0   58m   0%  /dev/shm/dev/sda1             477m    43M  409M  10% /boot/dev/drbd0             8.8g  2.1g  6.3g  25% /data[[email  protected] ~]# cd /data/mysql/data/        # View MySQL data [[EMAIL PROTECTED] DATA]# LLTOTAL 2109468-RW-RW---- 1 mysql mysql          56 Jul  2 12:46  AUTO.CNF-RW-RW---- 1 mysql mysql   12582912 jul  2 13:02  IBDATA1-RW-RW---- 1 mysql mysql 1073741824 jul  2 13:02 ib_ LOGFILE0-RW-RW---- 1 MYSQL MYSQL 1073741824 JUL  2 12:46 IB_LOGFILE1DRWX------ 2 mysql mysql        4096 JUL  2 12:00 MYSQLDRWX------ 2  mysql mysql       4096 jul  2 12:00  PERFORMANCE_SCHEMA-RW-RW---- 1 mysql mysql        185  JUL  2 12:46 SLOW.LOGDRWX------ 2 mysql mysql        4096 jul  2 12:00 test[[email protected] ~]# cd  /data/mysql/binlog/       #查看二进制文件 [[email protected] binlog]#  LLTOTAL 8-RW-RW---- 1 mysql mysql 285 Jul  2 13:02  MYSQL-BINLOG.000001-RW-RW---- 1 mysql mysql  39 jul  2 12:46  mysql-binlog.index[[email proteCted] ~]# mv /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.old[[email protected]  ~]# vi /usr/local/mysql/my.cnf      #修改配置文件 [mysqld]basedir =  /usr/local/mysql   #安装路径datadir  = /data/mysql/data   #数据目录port  =  3306server_id = 1                 #此值不要去修改 because the primary and standby MySQL only one at a time was started pid-file = /data/mysql/data/mysql.pid  # Process file socket = /var/lib/mysql/mysql.sockdefault_storage_engine = innodblog-bin = / data/mysql/binlog/mysql-binlog   #二进制文件expire_logs_days  = 14max_binlog_size =  5gbinlog_cache_size = 10mmax_binlog_cache_size = 20mslow_query_loglong_query_time =  2slow_query_log_file = /data/mysql/data/slow.log   #slow日志文件open_files_limit  =  65535innodb = forceinnodb_buffer_pool_size = 100m    #注意这个参数innodb_log_file_size  = 1gquery_cache_ size = 0thread_cache_size = 64table_definition_cache = 512table_open_cache  = 512max_connections = 20sort_buffer_size = 10mmax_allowed_packet = 6msql_ Mode=no_engine_substitution,strict_trans_tables[client] #socket  = /var/lib/mysql/mysql.sock# #注意: # # The primary and standby nodes are guaranteed to be identical on both sides of the configuration file my.cnf to avoid problems [[email protected] ~]# cp -a /usr/local/mysql/ support-files/mysql.server /etc/init.d/mysqld     #拷贝启动脚本 # #注意: # #千万不要, never set up MySQL boot automatically Because the startup of the MySQL service is managed by Heartbeat Unified [[email protected] ~]# /etc/init.d/mysqld start           #启动mysql服务Starting  mysql ......... ...................  SUCCESS! [[email protected] ~]# ps -ef | grep mysqlroot      16284     1  0 12:46 pts/1    00:00:00 /bin/sh  /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/ mysql.pidmysql    16783 16284  6 12:46 pts/1     00:00:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/ Data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log  --open-files-limit=65535 --pid-file=/data/mysql/data/mysql.pid --socket=/var/lib/mysql/ Mysql.sock --port=3306root     16878  1286  0 12:47  pts/1    00:00:00 grep mysql[[email protected] ~]# vi  /etc/profile            #修改PATH路径 # #在最后添加:export  Path= $PATH:/usr/local/mysql/bin[[email protected] ~]# source /etc/profile        #立即生效 

This article is from the "See" blog, please be sure to keep this source http://732233048.blog.51cto.com/9323668/1670068

Drbd+heartbeat+mysql: Configuring High Availability for Mysql