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机器。

本文转自 swallow_zys  51CTO博客，原文链接：http://blog.51cto.com/12042068/1897045，如需转载请自行联系原作者