mysql双主配合keepalived可用简析

359025439

　　一 版本。
　　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>>　　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 用户名@登录主机>　　如果登陆主机设置成%表示任意主机都可以登录，(十分危险)

　　mysql> grant select,insert on swa.* to code@localhost>　　删除用户；（原理：删除 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

　　>　　replicate-wild-ignore-table=mysql.%
　　replicate-wild-ignore-table=test.%
　　replicate-wild-ignore-table=information_schema.%
　　简析 含义：
　　server-id = 1    #节点的识别符 ，主从的要不一致
　　log-bin=mysql-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'>　　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'>　　在新开启的主机上登录；
　　./bin/mysql -uroot -p -h 192.168.1.133
　　这里需要注意的是：-h  跟VIP 地址需要 keepalived服务成功启动。不然生成不了VIP。
　　mysql > show variables like '%hostname%'
　　mysql > show variables like '%hostname%';
　　可以查看是登录的是哪台mysql机器。