mysql雙主配合keepalived可用簡析

一 版本。
    mysql數據庫版本：Preconna Server ,MariaDB還是MYSQL
    1.mysql三種存儲引擎
        mysql提供了兩種存儲引擎，MyISAM,InnoDB.xtraDB
        MyISAM沒有日志和事物支持。所以I/0性能非常好
        InnoDB有日志支持提供事務支持。（通過日志記錄，方便恢復。增強了mysql的健壯性）mysql5.5版本默認為InnoDB
        xtraDB是InnoDB存儲引擎的增強版本，提供了對更高性能計算機的支持。
    2.Percona Server 分支
        由 Percona 公司（mysql咨詢公司）發布的mysql版本。提供xtraDB引擎，附帶了Percona-toolkit等管理工具。
        不過percona Server 是閉源的。
    3.MariaDB
        MariaDB10.0.9版本使用xtraDB引擎。有mysql創始人開發的開源版本。避免甲骨文公司閉源mysql的風險。
二 mysql 命令操作
    1.連接mysql
        #cd /usr/local/mysql
        小技巧；隱藏密碼 。在命令行下輸入 # HISTCONTROL=ignorespace  下面的輸入總先輸入空格就不會記錄內容
        #./bin/mysql -h 主機名稱 -u 用戶名 -p 密碼 -h 主機名 -A （可以切換庫名）
        #./bin/mysql -uroot -p
        mysql>  show dattabases; (查看數據庫名)
        mysql> use database  (切換數據庫)
        mysql> show tables (查看database庫的表)
        mysql> desc table (查看表的結構)
        mysql> create database name (創建庫)
        mysql> create table swa (a int,b varchar(200)); （創建表swa)
        mysql> desc swa;
        +-------+--------------+------+-----+---------+-------+
        | Field | Type         | Null | Key | Default | Extra |
        +-------+--------------+------+-----+---------+-------+
        | a     | int(11)      | YES  |     | NULL    |       |
        | b     | varchar(200) | YES  |     | NULL    |       |
        +-------+--------------+------+-----+---------+-------+
        mysql> alter table swa add id int(4) default '0';(增加表的字段)
        mysql>rename table 原表名 to 新表名;(修改表的名稱)
        mysql> insert into swa values(11,'aasdfsdfsf'); (插入數據)
        mysql> select * from swa;
        +------+------------+
        | a    | b          |
        +------+------------+
        |   11 | aasdfsdfsf |
        +------+------------+
        mysql> insert into swa values(12,'123424'); (寫入內容到swa表)
        mysql> select * from swa where a=11;
        +------+------------+
        | a    | b          |
        +------+------------+
        |   11 | aasdfsdfsf |
        +------+------------+
        mysql> select * from swa where a=12;
        +------+--------+
        | a    | b      |
        +------+--------+
        |   12 | 123424 |
        +------+--------+
        mysql> drop table name; （刪除表）
        mysql> drop database  name; (刪除庫)
        如果是刪除沒有的數據庫會報錯。解決方法：
        mysql> drop database if exists name; (在 database  后面 判斷是否含有數據庫)
    
    2.修改密碼：在shell下（不在mysql里面）使用/bin/mysqladmin 命令：
      格式 mysqladmin -u 用戶名 -p 舊密碼 password 新密碼
     # ./bin/mysqladmin -uroot -proot password root123
    Warning: Using a password on the command line interface can be insecure.
    3.增加新用戶/授權用戶
        格式 grant 權限 on 數據庫.數據表 to 用戶名@登錄主機 identified by '密碼'；
        如果登陸主機設置成%表示任意主機都可以登錄，(十分危險)
        mysql> grant select,insert on swa.* to code@localhost identified by '123';
        刪除用戶；（原理：刪除 mysql庫下的user表的數據）
        mysql> drop user code@localhost;
    4.備份數據庫：在shell下（不在mysql里面）使用/bin/mysqldump 命令
        # ./bin/mysqldump -u 用戶名 -p 數據庫名稱 > 路徑.sql
        # ./bin/mysqldump -u 用戶名 -p 數據庫名稱 表名稱 > 路徑.sql (備份表)
    5.還原數據庫。
        1.在shell命令行下：
            ./bin/mysql -u 用戶名 -p -A 數據庫名稱< 路徑.sql
        2.進入數據庫
                mysql> use swa;
                mysql> source 路徑.sql;
三 mysql的高可用構架 mysql主從復制 .
        123:作為主庫mysql:DB1    124：作為從庫mysql:DB2
        DB1的配置：
        默認配置文件/etc/my.cnf
        [mysqld]
        server-id = 1
        log-bin=mysql-bin
        relay-log = mysql-relay-bin
        replicate-wild-ignore-table=mysql.%
        replicate-wild-ignore-table=test.%
        replicate-wild-ignore-table=information_schema.%
        簡析 含義：
        server-id = 1    #節點的識別符 ，主從的要不一致
        log-bin=mysql-bin    # 二進制文件的命名格式
        relay-log = mysql-relay-bin   #從服務器中繼日志的格式
        replicate-wild-ignore-table   #可以忽略同步的數據庫名
        DB2:slave 的配置 也是在/etc/my.cnf的[mysqld]字段
###############slave####################
server_id = 2
#log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
#########################################
     配置主從；
        1.同步數據：
            DB1：mysql>FLUSH TABLES WITH READ LOCK; #鎖表
            > UNLOCK TABLES;#解鎖
            將DB1的數據拷貝到DB2的位置上。
        2.在DB1上給DB2賦予權限：
            mysql> grant replication slave on *.* to 'rep_user'@'192.168.1.124' identified by 'rep_user';
            mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      334 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
        3. 在DB2上配置slave，并開啟slave
            mysql> change master to master_host='192.168.1.123',master_user='rep_user',master_password='rep_user',master_log_file='mysql-bin.000001',master_log_pos=334;
            mysql> start slave;
        錯誤信息：
        Last_SQL_Error: Error 'Can't drop database 'swa1'; database doesn't exist' on query. Default database: 'swa1'. Query: 'drop database swa1'

        DB2:  mysql> stop slave;            
            mysql> set global sql_slave_skip_counter=1;
            msyql > start slave;
        也可以反過來做。DB2為主。DB1為從。DB2的my.cnf開啟log-bin日志。在DB2上給DB1權限。DB1配置從信息。
    如果出現錯誤：請檢查幾個地方：
        change master to master_host='192.168.1.123',master_user='rep_user',master_password='rep_user',master_log_file='mysql-bin.000001',master_log_pos=334;
        這條語句中所有的參數都是","相連的。還有就是這些參數不要寫錯。
        在這些的基礎上，再檢查:DB1和DB2 是否可以通信 ping.查看3306端口是否開啟。安全機制如果不會配的話就關閉。
為了實現高可用：下面安裝keepalived實現高可用
        下載軟件： wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz
        安裝軟件：
         tar xf
         cd keepalived-1.2.12
        ./configure   --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.5.1.el6.x86_64
        錯誤提示：!!! Can not include OpenSSL headers files.
             configure: error: ! OpenSSL is not properly installed on your system
        解決方法： # yum install openssl-devel perl-DBI perl-DBD-MySQL -y
        （我的監測腳本是perl語言的所以安裝了perl的擴展模塊）
         ./configure   --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.5.1.el6.x86_64
         make && make install
         兩臺機器做keepalived：
         123：master  124 backup
         123的配置
         vim keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id MySQLHA_DEVEL
}
vrrp_script check_mysqld {
    script "/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1"  #這里的腳本一定要有執行權限
    interval 2
    }
vrrp_instance HA_1 {
    state MASTER           # 如果主機出現問題，會切換到備機上。等到主機恢復了。切換回主機。
    interface eth0
    virtual_router_id 80
    priority 100
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass qweasdzxc
    }
    track_script {
    check_mysqld
    }
    virtual_ipaddress {
        192.168.1.133/24 dev eth0
    }
}
        124的配置：vim keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id MySQLHA_DEVEL
}
vrrp_script check_mysqld {
    script "/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1" #檢測mysql復制狀態的腳本
    interval 2
#    weight 21              #如果是多臺BACKUP的時候開啟。2臺不開啟。容易導致解析錯誤
    }
vrrp_instance HA_1 {
    state BACKUP                #在DB1和DB2上均配置為BACKUP
    interface eth0
    virtual_router_id 80
    priority 90                 #這里的數值不同
    advert_int 2
#    nopreempt  #不搶占模式，只在優先級高的機器上設置即可，優先級低的機器不設置
    authentication {
        auth_type PASS
        auth_pass qweasdzxc
    }
    track_script {
    check_mysqld
    }
    virtual_ipaddress {
        192.168.1.133/24 dev eth0       #mysql的對外服務IP，即VIP
    }
}
        下面是check_slave.pl
#!/usr/bin/perl -w
use DBI;
use DBD::mysql;

# CONFIG VARIABLES
$SBM = 120;
$db = "";
$host = $ARGV[0];
$port = 3306;
$user = "root";
$pw = "";

# SQL query
$query = "show slave status";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });

if (!defined($dbh)) {
    exit 1;
}

$sqlQuery = $dbh->prepare($query);
$sqlQuery->execute;

$Slave_IO_Running =  "";
$Slave_SQL_Running = "";
$Seconds_Behind_Master = "";

while (my $ref = $sqlQuery->fetchrow_hashref()) {
    $Slave_IO_Running = $ref->{'Slave_IO_Running'};
    $Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
    $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
}
$sqlQuery->finish;
$dbh->disconnect();

if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
    exit 1;
} else {
    if ( $Seconds_Behind_Master > $SBM ) {
        exit 1;
    } else {
        exit 0;
    }
}
    這里遇到的問題是腳本沒有權限的話。keepalived啟動不了VIP，此外這個腳本還需要perl擴展支持。前面已經yum了。
         
         測試； 開啟另一臺同網段的主機。
         在123 和124 上分別賦予權限。
         # grant  all privileges on *.* to 'root'@'新開啟的主機ip' identified by 'root';
         在新開啟的主機上登錄；
         ./bin/mysql -uroot -p -h 192.168.1.133
         這里需要注意的是：-h  跟VIP 地址需要 keepalived服務成功啟動。不然生成不了VIP。
         mysql > show variables like '%hostname%'
         mysql > show variables like '%hostname%';
         可以查看是登錄的是哪臺mysql機器。