mysql主从复制及失败切换

4353

软件环境：
mysql软件包：mysql-5.6.17.tar.gz
cmake软件包：cmake-2.8.12.2.tar.gz @  wget http://www.cmake.org/files/v2.8/cmake-2.8.12.2.tar.gz
系统版本：
[iyunv@db src]# uname -a
Linux db.jrzj.com 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

[iyunv@backup yum.repos.d]# uname -a
Linux backup.jrzj.com 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
开始安装：
==============
1:安装cmake：
[iyunv@db cmake-2.8.12.2]# yum install gcc
[iyunv@db cmake-2.8.12.2]# yum install gcc-C++
[iyunv@db cmake-2.8.12.2]# yum install make
[iyunv@db cmake-2.8.12.2]# ./configure
[iyunv@db cmake-2.8.12.2]# make && make install
2:安装mysql
为mysql创建用户
[iyunv@db mysql-5.6.17]# useradd mysql
创建mysql数据文件存放目录
[iyunv@db mysql-5.6.17]# mkdir /data/
开始安装mysql
[iyunv@db mysql-5.6.17]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/ -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=GBK -DDEFAULT_COLLATION=gbk_chinese_ci
[iyunv@db mysql-5.6.17]# make && make install
error for cmake  
CMake Error at cmake/readline.cmake:85 (MESSAGE):
Curses library not found.  Please install appropriate package,
     remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readline.cmake:128 (FIND_CURSES)
cmake/readline.cmake:202 (MYSQL_USE_BUNDLED_EDITLINE)
CMakeLists.txt:411 (MYSQL_CHECK_EDITLINE)
[iyunv@db mysql-5.6.17]# yum install ncurses-devel
[iyunv@db mysql-5.6.17]# rm CMakeCache.txt
rm：是否删除普通文件 "CMakeCache.txt"？y
初始化mysql
[iyunv@db /]#  chown -R mysql.mysql /usr/local/mysql
[iyunv@db /]#  chown -R mysql.mysql /data/
[iyunv@db /]#  cd /usr/local/mysql/scripts
[iyunv@db scripts]#  ./mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/ --collation-server=gbk_chinese_ci
[iyunv@db scripts]# cd ../support-files/
[iyunv@db support-files]# cp mysql.server /etc/rc.d/init.d/mysqld
[iyunv@db support-files]# chkconfig --add mysqld
[iyunv@db support-files]# chkconfig mysqld on
[iyunv@db support-files]# echo 'PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile
[iyunv@db support-files]# source /etc/profile
[iyunv@db support-files]# mysql_secure_installation
[iyunv@db support-files]# service  mysqld start
Starting MySQL. SUCCESS!
[iyunv@db support-files]# mysqladmin -u root password 1234qwer
[iyunv@db support-files]# mysql -u root -p
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using  EditLine wrapper

Connection id:7
Current database:
Current user:root@localhost
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.6.17 Source distribution
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:gbk
Db     characterset:gbk
Client characterset:gbk
Conn.  characterset:gbk
UNIX socket:/tmp/mysqld.sock
Uptime:4 min 33 sec

Threads: 1  Questions: 19  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.069
--------------

============
单节点mysql安装完成
============
重复以上操作安装mysql_backup
=============
设置root用户可远程登录
grant all privileges  on *.* to root@'%' identified by "root";
mysql -h 192.168.0.176-u root -p
============
设置主从：
主DB：
[iyunv@db etc]# mysql -u root -p
Enter password:
mysql>  create user jrzj IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to 'jrzj'@'192.168.0.176' identified by '1234qwer';
Query OK, 0 rows affected (0.00 sec)
[iyunv@db etc]# vim /etc/my.cnf
server_id=177
log-bin=jrzj-bin
[iyunv@db etc]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
查看日志偏移量：
mysql> show master status \G
*************************** 1. row ***************************
            File: jrzj-bin.000002
        Position: 120
    Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> flush tables with read lock;
导出数据库：
[iyunv@db tmp]# mysqldump  -p3306 -uroot -p  -S /tmp/mysqld.sock  --all-databases > /tmp/mysql.sql
从DB配置
[iyunv@db etc]# vim /etc/my.cnf
server_id=176
[iyunv@backup tmp]# mysql -uroot -p < /tmp/mysql.sql
mysql> change master to
-> master_host='192.168.0.177',
-> master_user='jrzj',
-> master_password='1234qwer',
-> master_port=3306,
-> master_log_file='jrzj-bin.000002',
-> master_log_pos=120;
mysql>  show slave status\G;
***************************1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host:192.168.0.177
                 Master_User: jrzj
                 Master_Port:3306
               Connect_Retry:60
             Master_Log_File: jrzj-bin.000002
         Read_Master_Log_Pos:120
              Relay_Log_File: backup-relay-bin.000002
               Relay_Log_Pos:282
       Relay_Master_Log_File: jrzj-bin.000002
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB:
         Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
     Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                  Last_Errno:0
                  Last_Error:
                Skip_Counter:0
         Exec_Master_Log_Pos:120
             Relay_Log_Space:456
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos:0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
             Master_SSL_Cert:
           Master_SSL_Cipher:
              Master_SSL_Key:
       Seconds_Behind_Master:0
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno:0
               Last_IO_Error:
              Last_SQL_Errno:0
              Last_SQL_Error:
Replicate_Ignore_Server_Ids:
            Master_Server_Id:177
                 Master_UUID: e0fe5f94-c633-11e3-8f4e-0050568ea576
            Master_Info_File:/data/master.info
                   SQL_Delay:0
         SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
          Master_Retry_Count:86400
                 Master_Bind:
     Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Master_SSL_Crl:
          Master_SSL_Crlpath:
          Retrieved_Gtid_Set:
           Executed_Gtid_Set:
               Auto_Position:0
=============
主从配置完成
==========
将mysql配置为互为主从，开启slave的bin log 并授权访问
=========
高可用配置
=========
0.176/0.177
[iyunv@db tmp]# yum install ipvsadm keepalived
[iyunv@db tmp]# chkconfig keepalived on
[iyunv@db tmp]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
  notification_email {
    yangjob2013@126.com
  }
  notification_email_from yangjob2013@126.com
  smtp_server smtp.126.com
  smtp_connect_timeout 30
  router_id HA
}
vrrp_instance VI_1 {
   state MASTER
   interface eth0
   virtual_router_id 51
   priority 100
   advert_int 1
   preempt
   authentication {
       auth_type PASS
       auth_pass 1111
   }
   virtual_ipaddress {
       192.168.0.16
   }
}

[iyunv@db tmp]# cat /tmp/check_mysql.sh
MYSQL="/usr/local/mysql/bin/mysql"
MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASSWORD=1234qwer
LOG_FILE="/tmp/log/check_mysql.log"
MYSQL_OK=1 # mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
check_mysql_helth()
{
$MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p${MYSQL_PASSWORD} -e "show status" &>/dev/null
if [ $? = 0 ] ; then
   MYSQL_OK=1
else
   MYSQL_OK=0
fi
return $MYSQL_OK
}
check_keepalived()
{
  ps -ef | grep keepalived | grep -v grep > /dev/null
  if [ $? = 0 ];then
      keepalived_OK=1
  else        
      keepalived_OK=0
  fi
  return $keepalived_OK
}
while :
do
CHECK_TIME=3
while [ $CHECK_TIME -ne 0 ]
do
   let "CHECK_TIME -= 1"
   check_mysql_helth
   if [ $MYSQL_OK == 1 ];then
       check_keepalived
if [ $keepalived_OK == 1 ];then
break
else
service keepalived start >>$LOG_FILE
break
fi
   elif [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ];then
     service keepalived stop >> $LOG_FILE
     echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - mysql invaild. keepalived stop. >> $LOG_FILE
   fi
done
done
[iyunv@db tmp]#service mysqld start
[iyunv@db tmp]#service keepalibed start
[iyunv@db tmp]# nohup ./check_mysql.sh &