首页 > 代码库 > mysql 主从复制 && 失败切换

mysql 主从复制 && 失败切换

软件环境:

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

系统版本:

[root@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

[root@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:

[root@db cmake-2.8.12.2]# yum install gcc
[root@db cmake-2.8.12.2]# yum install gcc-C++
[root@db cmake-2.8.12.2]# yum install make
[root@db cmake-2.8.12.2]# ./configure 
[root@db cmake-2.8.12.2]# make && make install

2:安装mysql

为mysql创建用户

[root@db mysql-5.6.17]# useradd mysql

创建mysql数据文件存放目录

[root@db mysql-5.6.17]# mkdir /data/

开始安装mysql

[root@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

[root@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)

[root@db mysql-5.6.17]# yum install ncurses-devel

[root@db mysql-5.6.17]# rm CMakeCache.txt 

rm:是否删除普通文件 "CMakeCache.txt"?y

初始化mysql

[root@db /]#  chown -R mysql.mysql /usr/local/mysql 
[root@db /]#  chown -R mysql.mysql /data/
[root@db /]#  cd /usr/local/mysql/scripts
[root@db scripts]#  ./mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/ --collation-server=gbk_chinese_ci

[root@db scripts]# cd ../support-files/

[root@db support-files]# cp mysql.server /etc/rc.d/init.d/mysqld

[root@db support-files]# chkconfig --add mysqld

[root@db support-files]# chkconfig mysqld on

[root@db support-files]# echo ‘PATH=$PATH:/usr/local/mysql/bin/‘ >> /etc/profile

[root@db support-files]# source /etc/profile

[root@db support-files]# mysql_secure_installation

[root@db support-files]# service  mysqld start

Starting MySQL. SUCCESS! 

[root@db support-files]# mysqladmin -u root password 1234qwer

[root@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 -192.168.0.176 -u root -p

============

设置主从:

主DB:

[root@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)
[root@db etc]# vim /etc/my.cnf
server_id=177
log-bin=jrzj-bin 
[root@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;

导出数据库:

[root@db tmp]# mysqldump  -p3306 -uroot -p  -S /tmp/mysqld.sock  --all-databases > /tmp/mysql.sql

从DB配置

[root@db etc]# vim /etc/my.cnf
server_id=176

[root@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

[root@db tmp]# yum install ipvsadm keepalived
[root@db tmp]# chkconfig keepalived on

[root@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

    }

}


[root@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

[root@db tmp]#service mysqld start

[root@db tmp]#service keepalibed start

[root@db tmp]# nohup ./check_mysql.sh &

 


本文出自 “疯狂的蚂蚁” 博客,请务必保留此出处http://crazyants.blog.51cto.com/3402098/1594605

mysql 主从复制 && 失败切换