一、mysql简介
MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
二、mysql复制
关于mysql复制，我是这样理解的：将某一台主机上的Mysql数据复制到其它主机(slaves)上，并重新执行一遍从而实现当前主机上的mysql数据与(master)主机上数据保持一致的过程我们可以称为复制。
复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
三、mysql复制能解决什么问题
1、数据的分布(Data Distribution)
2、负载均衡(Load balancing) | mysql读写分离
3、可以实现数据的备份(Backups)，但是不能当真正意义上数据备份来用
4、高可用性和容错行(比如双主模型中的互为主从能实现高可用)
四、mysql的复制原理
1、工作原理图
从上图来看，Mysql的复制就三个步骤：
1)在Master服务器将改变的数据记录到二进制日志(binary log)中(这些记录叫做二进制日志事件)
2)Slave服务器将Master服务器上的二进制日志拷贝到自己的中继日志(relay-log)中
3)Slave服务器读取中继日志中的事件，然后将改变的数据写入到自己的数据库中
2、说明三步流程
第一步：是在Master服务器上记录二进制日志。在每个更新数据的事务完成之前，Master服务器都会将数据更改记录到二进制日志中。即使事务在执行期间是交错的，Mysql也会串行地将事务写入到二进制日志中。在把事件写入二进制日志之后，Master服务器告诉存储引擎可以提交事务了
第二步：是Slave服务器把主服务器的二进制日志拷贝到自己的硬盘上，进入所谓的“中继日志”中。首先，它启动一个工作线程，叫I/O线程，这个I/O线程开启一个普通的客户端连接，然后启动一个特殊的二进制日志转储进程（它没有相应的SQL命令）。这个转储进程Master服务器的二进制日志中读取数据。它不会对事件进行轮询。如果3跟上了Master服务器，就会进入休眠状态并等待有新的事件发生时Master服务器发出的信号。I/O线程把数据写入Slave服务器的中继日志中
第三步：SQL线程读取中继日志，并且重放其中的事件，然后更新Slave服务器的数据。由于这个线程能跟上I/O线程，中继日志通常在操作系统的缓存中，所以中继日志的开销很低。SQL线程执行事件也可以被写入Slave服务器自己的二进制日志中，它对于有些场景很实用
上图中显示了在Slave服务器有两个运行的线程，在Master服务器上也有一个运行的线程：和其他普通连接一样，由Slave服务器发起的连接，在Master服务器上同样拥有一个线程
3、配置注意事项
1、Master服务器必须开启二进制日志
2、Master和Slave的Server-id不能相同
3、同一个Master的多个Slave，Server-id也不能相同
4、Binlog_format最好相同
5、在Slave服务器上配置log-slave-updates=1时，也需要开启二进制日志；如果可以推荐使用read_only选项，该选项会阻止没有权限的线程修改数据
五、mysql主从异步复制
1、软件版本
系统版本：CentOS6.4 64bit
Mysql版本：mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz
2、拓扑图规划
3、准备工作
3.1 修改主机名
###MASTER执行 [root@localhost ~]# sed -i 's@\(HOSTNAME=\).*@\1master.allentuns.com@g' /etc/sysconfig/network [root@localhost ~]# hostname master.allentuns ###SLAVE执行 [root@localhost ~]# sed -i 's@\(HOSTNAME=\).*@\1slave.allentuns.com@g' /etc/sysconfig/network [root@localhost ~]# hostname slave.allentuns
3.2 配置主机名解析，这里修改hosts文件来实现
###MASTER执行 [root@master ~]# cat >> /etc/hosts << EOF > 192.168.2.100 master.allentuns.com > 192.168.2.200 slave.allentuns.com > EOF ###SLAVE执行 [root@slave ~]# cat >> /etc/hosts << EOF > 192.168.2.100 master.allentuns.com > 192.168.2.200 slave.allentuns.com > EOF
3.3 同步两台服务器的时间，保持时间一致
###MASTER执行 [root@master ~]# crontab -l */5 * * * * /usr/sbin/ntpdate -u asia.pool.ntp.org ###SLAVE执行 [root@slave ~]# crontab -l */5 * * * * /usr/sbin/ntpdate -u asia.pool.ntp.org
4、安装Mysql
4.1 在Master与Slave服务器上分别安装Mysql
###MASTER执行 1、解压mysql并建立软链接 tar xf mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ cd /usr/local/ ln -sv mysql-5.6.12-linux-glibc2.5-x86_64 mysql 2、创建mysql用户 groupadd -r mysql useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql 3、赋予mysql程序目录的属主和属组为mysql用户 chown -R mysql.mysql /usr/local/mysql/ 4、创建mysql数据存储目录并赋予此目录的属主和属组为mysql用户 mkdir -pv /mydata/data chown -R mysql:mysql /mydata/data/ 5、初始化mysql依赖libaio yum -y install libaio 6、初始化mysql /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data --user=mysql 7、为了mysql数据库的安全，改回mysql程序目录的属主 chown -R root /usr/local/mysql/* 8、修改mysql的默认配置文件，并优化相关参数 # vim /usr/local/mysql/my.cnf 添加内容是： [mysqld] log-bin = master-bin server-id = 1 port = 3306 basedir = /usr/local/mysql/ datadir = /mydata/data socket = /var/lib/mysql/mysql.sock innodb_file_per_table = 1 9、为mysql提供sysv服务脚本并启动服务 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 10、将mysql加入到服务列表并设置开机自启动 chkconfig --add mysqld chkconfig mysqld on 11、mysql命令添加到环境变量中 # vim /etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin # source /etc/profile 12、输出mysql的man手册到man命令的查找路径 # yum -y install man # vim /etc/man.config +54 加入到54行后 MANPATH /usr/local/mysql/man 13、输出mysql的库文件 echo "/usr/local/mysql/lib/" > /etc/ld.so.conf.d/mysql.conf ldconfig -v 14、输出mysql的头文件到系统头文件 ln -sv /usr/local/mysql/include/ /usr/include/mysql 15、启动mysql服务 service mysqld start 16、测试mysql[root@mysql ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> \q Bye
###SLAVE执行 1、解压mysql并建立软链接 tar xf mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ cd /usr/local/ ln -sv mysql-5.6.12-linux-glibc2.5-x86_64 mysql 2、创建mysql用户 groupadd -r mysql useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql 3、赋予mysql程序目录的属主和属组为mysql用户 chown -R mysql.mysql /usr/local/mysql/ 4、创建mysql数据存储目录并赋予此目录的属主和属组为mysql用户 mkdir -pv /mydata/data chown -R mysql:mysql /mydata/data/ 5、初始化mysql依赖libaio yum -y install libaio 6、初始化mysql /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data --user=mysql 7、为了mysql数据库的安全，改回mysql程序目录的属主 chown -R root /usr/local/mysql/* 8、修改mysql的默认配置文件，并优化相关参数 # vim /usr/local/mysql/my.cnf 添加内容是： [mysqld] log-bin = master-bin server-id = 1 port = 3306 basedir = /usr/local/mysql/ datadir = /mydata/data socket = /var/lib/mysql/mysql.sock innodb_file_per_table = 1 9、为mysql提供sysv服务脚本并启动服务 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 10、将mysql加入到服务列表并设置开机自启动 chkconfig --add mysqld chkconfig mysqld on 11、mysql命令添加到环境变量中 # vim /etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin # source /etc/profile 12、输出mysql的man手册到man命令的查找路径 # yum -y install man # vim /etc/man.config +54 加入到54行后 MANPATH /usr/local/mysql/man 13、输出mysql的库文件 echo "/usr/local/mysql/lib/" > /etc/ld.so.conf.d/mysql.conf ldconfig -v 14、输出mysql的头文件到系统头文件 ln -sv /usr/local/mysql/include/ /usr/include/mysql 15、启动mysql服务 service mysqld start 16、测试mysql[root@mysql ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> \q Bye [root@mysql ~]#
4.2 主从复制配置
4.2.1 在Master服务器上建立用于Slave服务器复制数据的帐户
[root@master ~]# mysql mysql> grant replication slave,replication client on *.* to 'allentuns'@'192.168.2.200' identified by '1234@6@'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'allentuns'@'192.168.2.200'; #查看授权用户 +------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for allentuns@192.168.2.200 | +------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'allentuns'@'192.168.2.200' IDENTIFIED BY PASSWORD '*7B8E3D52A612E2CB04E31B43FCDC20A07317E332' | +------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4.2.2 在Slave服务器上使用授权用户连接测试
[root@slave ~]# mysql -uallentuns -p1234@6@ -h 192.168.2.100 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
4.2.3 修改Master服务器上的Mysqld主配置文件如下：
[root@master ~]# vim /usr/local/mysql/my.cnf log-bin = master-bin #二进制日志文件 binlog_format = mixed #二进制日志文件索引 server-id = 1 #用于识别的ID port = 3306 #Mysql的默认端口号 basedir = /usr/local/mysql/ #Mysql源程序目录 datadir = /mydata/data #数据存储目录路径 socket = /var/lib/mysql/mysql.sock #套接字文件路径 innodb_file_per_table = 1 #每表一个文件 [root@master ~]# service mysqld restart
4.2.4 修改Slave服务器上的Mysqld主配置文件如下：
[root@slave ~]# vim /usr/local/mysql/my.cnf #log-bin = master-bin #注释二进制日志文件，如果当其它从服务器的主服务器，否则关闭 #binlog_format = mixed #注释此行 skip_slave_start = 1 #启动服务时不自动启动从服务线程 read_only = 1 #设置Slave服务器为只读 server-id = 10 relay_log = relay_log relay_log_index = relay_log.index port = 3306 basedir = /usr/local/mysql/ datadir = /mydata/data socket = /var/lib/mysql/mysql.sock innodb_file_per_table = 1 [root@slave ~]# service mysqld restart
4.2.5 查看Master服务器的二进制日志及二进制日志事件位置用于Slave服务器复制
mysql> RESET MASTER; #清空二进制日志 Query OK, 0 rows affected (0.05 sec) mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000003 | 120 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 注释： File：表示从此日志开始复制 Position：表示从这个事件开始复制 偏移位
4.2.6 在Slave服务器上同步Master服务器上面的数据如下：
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.2.100', -> MASTER_USER='allentuns', -> MASTER_PASSWORD='1234@6@', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=120; Query OK, 0 rows affected, 2 warnings (0.38 sec) **************************************************** mysql> help change master to #获取帮助信息 Name: 'CHANGE MASTER TO' Description: Syntax: CHANGE MASTER TO option [, option] ... CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
4.2.7 启动Slave服务器的复制线程并查看状态
mysql> start slave; #启动Slave服务器线程 Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.100 #Master服务器地址 Master_User: allentuns #连接Master服务器用户名 Master_Port: 3306 #Master服务器的监听端口 Connect_Retry: 60 #重试时间间隔 Master_Log_File: master-bin.000003 #I/O线程读取的二进制日志文件 Read_Master_Log_Pos: 120 #I/O线程读取的二进制日志文件事件位置 Relay_Log_File: relay_log.000002 #SQL线程正在读取的中继日志文件 Relay_Log_Pos: 284 #SQL线程读取和执行的中继日志文件事件位置 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes #Slave服务器的IO线程状态 Slave_SQL_Running: Yes #Slave服务器的SQL线程状态 Replicate_Do_DB: #下面Replicate开头的表示用来指明哪些库或者表在复制时不需要同步 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 #SQL线程读取日志参数的错误数量 Last_Error: #SQL线程读取日志参数的错误消息 Skip_Counter: 0 #最近被用于SQL_SLAVE_SKIP_COUNTER的值 Exec_Master_Log_Pos: 120 Relay_Log_Space: 451 Until_Condition: None #所有原有中继日志的总大小 Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No #是否允许对Master服务器进行SSL连接 Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 #落后于Master服务器的时间 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: 1 Master_UUID: 84774a86-3ee8-11e4-a268-000c29ad35d7 Master_Info_File: /mydata/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 1 row in set (0.00 sec) ERROR: No query specified
4.2.8 在Slave服务器查看启动的线程
[root@slave ~]# mysql -e "show processlist;" +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 3 | system user | | NULL | Connect | 533 | Waiting for master to send event | NULL | | 4 | system user | | NULL | Connect | 533 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 5 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
4.2.9 在Master服务器创建数据库并在Slave服务器上验证是否存在
###在Master服务器创建数据库并查看 [root@master ~]# mysql -e 'create database mydbtest;' [root@master ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mydbtest | | mysql | | performance_schema | | test | +--------------------+ ###在Slave服务器查看是否有'mydbtest'数据库 [root@slave ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mydbtest | #数据库已经成功同步到slave服务器 | mysql | | performance_schema | | test | +--------------------+
4.2.10 在Master与Slave服务器查看二进制日志事件位置已更新
###查看Master服务器 [root@master ~]# mysql -e 'show master status;' +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000003 | 226 | | | | +-------------------+----------+--------------+------------------+-------------------+ ###查看Slave服务器 [root@slave ~]# mysql -e 'show slave status\G;' |grep 'Read_Master_Log_Pos' Read_Master_Log_Pos: 226
六、mysql主从监控
1、Linux系统sendmail发邮件到139外部邮箱
1、安装sendmail和mailx # yum -y install sendmail mailx 2、修改配置文件 # cp /etc/mail.rc /etc/mail.rc.bak # cat >> /etc/mail.rc << EOF set from=13260071987@139.com smtp=smtp.139.com set smtp-auth-user=13260071987@139.com smtp-auth-password=yi15093547036 smtp-auth=login EOF 3、重新启动服务 # service sendmail restart 4、发送测试邮件 echo "I Love You" |mail -s "邮件主题:MIS you" 13260071987@139.com
2、分别在从服务器(Slave)上创建登陆用户，只限定本地运行，保证安全
mysql> grant all privileges on *.* to "zhengyansheng"@"127.0.0.1" identified by "password123"; Query OK, 0 rows affected (0.04 sec) mysql> grant all privileges on *.* to "zhengyansheng"@"localhost" identified by "password123"; Query OK, 0 rows affected (0.00 sec)
3、Mysql监控脚本
#!/bin/bash
#check MySQL_Slave Status
#crontab time 00:10
MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'`
MYSQLIP=`ifconfig eth1|grep "inet addr" | awk -F[:" "]+ '{print $4}'`
STATUS=$(/usr/local/mysql/bin/mysql -u zhengyansheng -ppassword123 -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running")
IO_env=`echo $STATUS | grep IO | awk ' {print $2}'`
SQL_env=`echo $STATUS | grep SQL | awk '{print $2}'`
DATA=`date +"%y-%m-%d %H:%M:%S"`
if [ "$MYSQLPORT" == "3306" ]
then
echo "mysql is running"
else
mail -s "warn!server: $MYSQLIP mysql is down" 13260071987@139.com
fi
if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]
then
echo "Slave is running!"
else
echo "####### $DATA #########">> /mydata/check_mysql_log/check_mysql_slave.log
echo "Slave is not running!" >> /mydata/check_mysql_log/check_mysql_slave.log
echo "Slave is not running!" | mail -s "warn! $MYSQLIP MySQL Slave is not running" 13260071987@139.com
fi
4、定时执行监控脚本
[root@slave ~]# crontab -l */1 * * * * root /bin/sh /mydata/check_mysql_health.sh
5、测试：停止slave进程，看是否能收到邮件
mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.2.100 Master_User: allentuns Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 226 Relay_Log_File: relay_log.000005 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: No #IO线程已经停止 Slave_SQL_Running: No #SQL线程已经停止 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: 226 Relay_Log_Space: 615 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: NULL 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: 1 Master_UUID: 84774a86-3ee8-11e4-a268-000c29ad35d7 Master_Info_File: /mydata/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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 1 row in set (0.00 sec)
C成功收到邮件！哈哈
七、mysql主从半同步复制
1、什么是同步复制
同步复制：同步复制可以定义为数据在同一时刻被提交到一台或多台机器，通常这是通过众所周知的“两阶段提交”做到的。
虽然这确实给你在多系统中保持一致性，但也由于增加了额外的消息交换而造成性能下降。使用MyISAM或者InnoDB存储引擎的MySQL本身并不支持同步复制，然而有些技术，例如分布式复制块设备(简称DRBD)，可以在下层的文件系统提供同步复制，允许第二个MySQL服务器在主服务器丢失的情况下接管
(使用第二服务器的复本)。
2、什么是半同步复制
MYSQL 5.5开始，支持半同步复制。之前版本的MySQL Replication都是异步(asynchronous)的，主库在执行完一些事务后，是不会管备库的进度的。
如果备库不幸落后，而更不幸的是主库此时又出现Crash(例如宕机)，这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务Semisynchronous Replication(半同步复制)则一定程度上保证提交的事务已经传给了至少一个备库。Semi synchronous中，仅仅保证事务的已经传递到备库上，但是并不确保已经在备库上执行完成了。
此外，还有一种情况会导致主备数据不一致。在某个session中，主库上提交一个事务后，会等待事务传递给至少一个备库，如果在这个等待过程中主库Crash，那么也可能备库和主库不一致，这是很致命的。如果主备网络故障或者备库挂了，主库在事务提交后等待10秒(rpl_semi_sync_master_timeout的默认值)后，就会继续。这时，主库就会变回原来的异步状态。
MySQL在加载并开启Semi-sync插件后，每一个事务需等待备库接收日志后才返回给客户端。如果做的是小事务，两台主机的延迟又较小，则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。
3、异步与半同步的区别
默认情况下MySQL的复制是异步的，Master上所有的更新操作写入Binlog之后并不确保所有的更新都被复制到Slave之上。异步操作虽然效率高，但是在Master/Slave出现问题的时候，存在很高数据不同步的风险，甚至可能丢失数据。
MySQL5.5引入半同步复制功能的目的是为了保证在master出问题的时候，至少有一台Slave的数据是完整的。在超时的情况下也可以临时转入异步复制，保障业务的正常使用，直到一台salve追赶上之后，继续切换到半同步模式。
默认情况下我们的主从复制是异步进行的，导致我们的从服务器有可能是落后于主服务器的，这在一定程度上是不安全的，如果我们的主服务器瞬间挂掉，从服务器将来不及复制数据。为了解决这个问题，我们可以打上google的一个补丁，使主从服务器实现半同步。
为什么叫半同步呢？
因为开启这个功能之后，主服务器只等待多个从服务器中的指定的一台从服务器复制成功，然后才进行其他写操作，使这个从服务器和主服务器上的数据完全同步，而并不管其他的从服务器。这在一定程度上就保证了我们数据的安全性。当然主服务器是不能一直等待从服务器复制成功的，因为万一从服务器挂掉，那么主服务器将一直处于等待状态而不提供写服务，这就需要我们定义一个超时时间，防止等待从服务器时间太长，单位是ms。如果超过定义的时间，从服务器还没有响应，则把指定的从服务器自动降级到异步模式，在选定一个从服务器做同步。
4、查看半同步插件
注意：Mysql5.5+半同步插件是由谷歌提供的，具体的位置/usr/local/mysql/lib/plugin/下
[root@master ~]# cd /usr/local/mysql/lib/plugin/ [root@master plugin]# ll semisync*.so -rwxr-xr-x 1 root mysql 415322 6月 20 2013 semisync_master.so #用于Master服务器安装的半同步插件 -rwxr-xr-x 1 root mysql 250750 6月 20 2013 semisync_slave.so #用于Slave服务器安装的半同步插件
5、在Master与Slave服务器分别安装半同步插件
###在Master上安装半同步插件 mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步插件 Query OK, 0 rows affected (0.03 sec) mysql> set global rpl_semi_sync_master_enabled = 1; #开启Master半同步功能 Query OK, 0 rows affected (0.00 sec) mysql> set global rpl_semi_sync_master_timeout = 1000; #设置超时时间 Query OK, 0 rows affected (0.00 sec) ###在Slave上安装半同步插件 mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步插件 Query OK, 0 rows affected (0.08 sec) mysql> set global rpl_semi_sync_slave_enabled = 1; #开启Slave半同步功能 Query OK, 0 rows affected (0.00 sec) mysql> stop slave io_thread; #停止IO线程 Query OK, 0 rows affected (0.00 sec) mysql> start slave io_thread; #启动IO线程 Query OK, 0 rows affected (0.00 sec)
6、查看半同步开启状态
#在Master服务器上查看 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | #已经有一个客户端连接 | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | #表明Master已经开启了半同步功能 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec) mysql> show global variables like '%rpl%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | #Master半同步功能已经开启 | rpl_semi_sync_master_timeout | 1000 | #超时时间 | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) #在Slave服务器上查看 mysql> show global status like 'rpl_semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | #表明Slave已经开启了半同步功能 +----------------------------+-------+ 1 row in set (0.04 sec) mysql> show global variables like '%rpl%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | #Slave半同步功能已经开启 | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec)
7、查看Slave线程是否为"Yes"
[root@slave ~]# mysql -e 'show slave status\G;' |grep 'Running:' Slave_IO_Running: Yes Slave_SQL_Running: Yes
8、验证半同步功能
###在Master服务器上删除数据库 [root@master ~]# mysql -e 'drop database mydbtest;' [root@master ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ ###在Slave服务器查看数据库 [root@slave ~]# mysql -e 'show databases;' +--------------------+ | Database | #删除数据库mydbtest已经成功同步到Slave数据库上 +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
9、已经配置都是临时的，如果要永久生效，需要将参数加到my.cnf配置文件并重启服务就可以了
八、mysql的ssl加密认证
1、简述
由于Mysql的主从复制是明文传送的，但如果在生产环境中跨网络我们使用主从还是明文传送的话，就保证不了数据的安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据
2、将Master服务器自己做成CA服务器
[root@master ~]# cd /etc/pki/CA/ [root@master CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048) Generating RSA private key, 2048 bit long modulus ........+++ ...................+++ e is 65537 (0x10001) [root@master CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:BJ Locality Name (eg, city) [Default City]:HaiDian Organization Name (eg, company) [Default Company Ltd]:Allentuns Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server's hostname) []:master.allentuns.com Email Address []: [root@master CA]# touch index.txt [root@master CA]# echo 01 > serial
3、为Master创建证书申请并由CA服务器签发证书
[root@master ~]# mkdir /usr/local/mysql/ssl [root@master ~]# cd /usr/local/mysql/ssl [root@master ssl]# (umask 077;openssl genrsa -out master.key 2048) Generating RSA private key, 2048 bit long modulus ........................+++ ......+++ e is 65537 (0x10001) [root@master ssl]# openssl req -new -key master.key -out master.csr -days 365 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:BJ Locality Name (eg, city) [Default City]:HaiDian Organization Name (eg, company) [Default Company Ltd]:Allentuns Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server's hostname) []:master.allentuns.com Email Address []:master@allentuns.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@master ssl]# openssl ca -in master.csr -out master.crt -days 365 Using configuration from /etc/pki/tls/openssl.cnf Check that the request matches the signature Signature ok Certificate Details: Serial Number: 1 (0x1) Validity Not Before: Sep 18 13:12:43 2014 GMT Not After : Sep 18 13:12:43 2015 GMT Subject: countryName = CN stateOrProvinceName = BJ organizationName = Allentuns organizationalUnitName = Tech commonName = master.allentuns.com emailAddress = master@allentuns.com X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: FB:20:AF:6D:64:AB:46:AB:83:E5:71:BA:D9:A3:42:BF:2F:3D:6E:CD X509v3 Authority Key Identifier: keyid:C5:1A:5C:09:E1:A7:49:03:BC:55:C8:9F:EB:32:3A:6A:16:3D:FD:1B Certificate is to be certified until Sep 18 13:12:43 2015 GMT (365 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data Base Updated
4、为Slave创建证书申请
[root@slave ~]# mkdir /usr/local/mysql/ssl [root@slave ~]# cd /usr/local/mysql/ssl [root@slave ssl]# (umask 077;openssl genrsa -out slave.key 2048) Generating RSA private key, 2048 bit long modulus .............................................................................................+++ ...........+++ e is 65537 (0x10001) [root@slave ssl]# openssl req -new -key slave.key -out slave.csr -days 365 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:BJ Locality Name (eg, city) [Default City]:HaiDian Organization Name (eg, company) [Default Company Ltd]:Allentuns Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server's hostname) []:slave.allentuns.com Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
5、为Slave服务器签署证书
[root@slave ssl]# scp slave.csr master.allentuns.com:/tmp/ #在Slave主机上将证书申请文件拷贝到Master的CA服务器上 The authenticity of host 'master.allentuns.com (192.168.2.100)' can't be established. RSA key fingerprint is 46:b9:7c:11:db:75:93:ad:f1:26:f0:a7:4d:00:40:20. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'master.allentuns.com,192.168.2.100' (RSA) to the list of known hosts. root@master.allentuns.com's password: slave.csr 100% 1013 1.0KB/s 00:00 [root@master ~]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365 #CA服务器签署Slave的证书申请 Using configuration from /etc/pki/tls/openssl.cnf Check that the request matches the signature Signature ok Certificate Details: Serial Number: 2 (0x2) Validity Not Before: Sep 18 13:18:06 2014 GMT Not After : Sep 18 13:18:06 2015 GMT Subject: countryName = CN stateOrProvinceName = BJ organizationName = Allentuns organizationalUnitName = Tech commonName = slave.allentuns.com X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: 7C:51:D9:CD:DC:28:B3:3D:5D:F7:20:83:FA:0F:3D:2B:1A:4B:F3:8B X509v3 Authority Key Identifier: keyid:C5:1A:5C:09:E1:A7:49:03:BC:55:C8:9F:EB:32:3A:6A:16:3D:FD:1B Certificate is to be certified until Sep 18 13:18:06 2015 GMT (365 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Data Base Updated [root@master ~]# scp /tmp/slave.crt slave.allentuns.com:/usr/local/mysql/ssl/ #将签署好的证书拷贝到Slave服务器上 The authenticity of host 'slave.allentuns.com (192.168.2.200)' can't be established. RSA key fingerprint is 46:b9:7c:11:db:75:93:ad:f1:26:f0:a7:4d:00:40:20. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'slave.allentuns.com,192.168.2.200' (RSA) to the list of known hosts. root@slave.allentuns.com's password: slave.crt 100% 4477 4.4KB/s 00:00
6、将CA证书拷贝到Slave服务器并为Master拷贝一份
[root@master ~]# scp /etc/pki/CA/cacert.pem slave.allentuns.com:/usr/local/mysql/ssl/ root@slave.allentuns.com's password: cacert.pem 100% 1338 1.3KB/s 00:00 [root@master ~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
7、修改Master与Slave服务器证书属主、属组为"mysql"用户
###在Master服务器上执行 [root@master ~]# chown -R mysql.mysql /usr/local/mysql/ssl [root@master ~]# ll /usr/local/mysql/ssl 总用量 20 -rw-r--r-- 1 mysql mysql 1338 9月 18 21:22 cacert.pem -rw-r--r-- 1 mysql mysql 4564 9月 18 21:12 master.crt -rw-r--r-- 1 mysql mysql 1066 9月 18 21:12 master.csr -rw------- 1 mysql mysql 1679 9月 18 21:11 master.key ###在Slave服务器上执行 [root@slave ssl]# chown -R mysql.mysql /usr/local/mysql/ssl [root@slave ssl]# ll /usr/local/mysql/ssl/ 总用量 20 -rw-r--r-- 1 mysql mysql 1338 9月 18 21:22 cacert.pem -rw-r--r-- 1 mysql mysql 4477 9月 18 21:18 slave.crt -rw-r--r-- 1 mysql mysql 1013 9月 18 21:14 slave.csr -rw------- 1 mysql mysql 1679 9月 18 21:14 slave.key 注意：Master与Slave服务器上的证书属主、属组必须为mysql用户及组
8、在Master与Slave服务器修改主配置文件开启SSL加密功能
###在Master服务器上执行 [root@master ~]# vim /usr/local/mysql/my.cnf #添加如下参数 ssl #开启SSL功能 ssl_ca = /usr/local/mysql/ssl/cacert.pem #指定CA文件位置 ssl_cert = /usr/local/mysql/ssl/master.crt #指定证书文件位置 ssl_key = /usr/local/mysql/ssl/master.key #指定密钥所在位置 [root@master ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.... SUCCESS! ###在Slave服务器上执行 [root@slave ~]# vim /usr/local/mysql/my.cnf #添加如下参数 ssl #开启SSL功能 ssl_ca = /usr/local/mysql/ssl/cacert.pem #指定CA文件位置 ssl_cert = /usr/local/mysql/ssl/master.crt #指定证书文件位置 ssl_key = /usr/local/mysql/ssl/master.key #指定密钥所在位置 [root@slave ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.... SUCCESS!
9、在Master服务器查看SSL加密是否开启；然后创建授权一个基于密钥认证的用户
mysql> show variables like "%ssl%"; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /usr/local/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /usr/local/mysql/ssl/master.crt | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /usr/local/mysql/ssl/master.key | +---------------+---------------------------------+ 9 rows in set (0.00 sec) mysql> grant replication client,replication slave on *.* to 'userssl'@'192.168.2.%' identified by '1234@6@' require ssl; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
10、查看Master服务器二进制日志文件和事件位置用于Slave服务器连接从这个位置开始复制
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000005 | 442 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
11、测试使用加密用户指定密钥连接Master服务器
[root@slave ssl]# mysql -uallentuns -p1234@6@ -h 192.168.2.100 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
12、查看Slave服务器SSL是否开启并连接Master服务器
这里有个疑问Slave中到底是否要开启SSL功能？？？？？？
mysql> show variables like '%ssl%'; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | /usr/local/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /usr/local/mysql/ssl/master.crt | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /usr/local/mysql/ssl/master.key | +---------------+---------------------------------+ mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.2.100', -> MASTER_USER='userssl', -> MASTER_PASSWORD='1234@6@', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000005', -> MASTER_LOG_POS=442, -> MASTER_SSL = 1, -> MASTER_SSL_CA = '/usr/local/mysql/ssl/cacert.pem', -> MASTER_SSL_CERT = '/usr/local/mysql/ssl/slave.crt', -> MASTER_SSL_KEY = '/usr/local/mysql/ssl/slave.key'; Query OK, 0 rows affected, 2 warnings (0.06 sec)
13、查看Slave服务器状态
mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.100 Master_User: userssl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000005 Read_Master_Log_Pos: 442 Relay_Log_File: relay_log.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000005 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: 442 Relay_Log_Space: 451 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /usr/local/mysql/ssl/slave.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: 1 Master_UUID: 84774a86-3ee8-11e4-a268-000c29ad35d7 Master_Info_File: /mydata/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: /usr/local/mysql/ssl/cacert.pem Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified
14、基于SSL的结果验证
###在Master服务器上创建数据库并查看 [root@master ~]# mysql -e 'create database mydbtestt1;' [root@master ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mydbtestt1 | | mysql | | performance_schema | | test | +--------------------+ ###在Slave服务器上查看是否同步 [root@slave ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mydbtestt1 | | mysql | | performance_schema | | test | +--------------------+
九、mysql主主复制
1、主主复制或者互为主从的思路
1、在两台服务器上各自建立一个具有复制权限的用户 2、修改配置文件 3、重新启动数据库 4、查看两台服务器当前的二进制日志文件和事件位置 5、互为CHANGE MASTER TO 6、先后启动Slave功能
2、配置主主复制
###在Master上执行 1、创建具有复制权限的用户 mysql> grant replication slave,replication client on *.* to 'master1'@'192.168.2.200' identified by '1234@6@'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 2、修改配置文件 [root@master ~]# vim /usr/local/mysql/my.cnf [mysqld] server-id = 1 log-bin = master-node1-bin relay-log = relay-mysql relay-log-index = relay-mysql.index auto-increment-increment = 2 auto-increment-offset = 1 3、重新启动数据库 [root@master ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! 4、查看当前的二进制日志文件和事件位置 mysql> show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | master-node1-bin.000001 | 120 | | | | +-------------------------+----------+--------------+------------------+-------------------+ 5、两台服务器分别指定另一台服务器为自己的主服务器： mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.2.200', -> MASTER_USER='master2', -> MASTER_PASSWORD='1234@6@', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-node2-bin.000001', -> MASTER_LOG_POS=120; Query OK, 0 rows affected, 2 warnings (0.01 sec) 6、启动Slave并查看是否同步 mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.200 Master_User: master2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-node2-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: relay-mysql.000002 Relay_Log_Pos: 290 Relay_Master_Log_File: master-node2-bin.000001 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: 459 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: 10 Master_UUID: 89726004-3ee8-11e4-a268-000c29c32576 Master_Info_File: /mydata/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 1 row in set (0.00 sec) ERROR: No query specified ###在Slave上执行 1、创建具有复制权限的用户 mysql> grant replication slave,replication client on *.* to 'master2'@'192.168.2.100' identified by '1234@6@'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 2、修改配置文件 [root@master ~]# vim /usr/local/mysql/my.cnf [mysqld] server-id = 10 log-bin = master-node2-bin relay-log = relay-mysql relay-log-index = relay-mysql.index auto-increment-increment = 2 auto-increment-offset = 2 3、重新启动数据库 [root@master ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! 4、查看当前的二进制日志文件和事件位置 mysql> show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | master-node1-bin.000001 | 120 | | | | +-------------------------+----------+--------------+------------------+-------------------+ 5、两台服务器分别指定另一台服务器为自己的主服务器 mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.2.100', -> MASTER_USER='master1', -> MASTER_PASSWORD='1234@6@', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-node1-bin.000001', -> MASTER_LOG_POS=120; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; #报错 ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository mysql> reset slave; #网上解决方案 Query OK, 0 rows affected (0.00 sec) 6、启动Slave并查看是否同步 mysql> start slave; Query OK, 0 rows affected (0.07 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.100 Master_User: master1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-node1-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: relay-mysql.000005 Relay_Log_Pos: 290 Relay_Master_Log_File: master-node1-bin.000001 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: 459 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /usr/local/mysql/ssl/slave.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: 1 Master_UUID: 84774a86-3ee8-11e4-a268-000c29ad35d7 Master_Info_File: /mydata/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: /usr/local/mysql/ssl/cacert.pem Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified
3、验证主主复制是否同步
分为两种情况
第一、在主1上创建数据库、创建表、插入数据、删除部分数据；并在主2上查看是否同步
#创建数据库、创建表、插入数据、删除部分数据 [root@master ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database mydbs; Query OK, 1 row affected (0.00 sec) mysql> use mydbs; Database changed mysql> CREATE TABLE t1 (id int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> delete from t1 where id > 7; Query OK, 3 rows affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +------+ 7 rows in set (0.00 sec) mysql> \q Bye #查看是否同步成功 [root@slave ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydbs | | mydbtestt1 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> use mydbs; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_mydbs | +-----------------+ | t1 | +-----------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +------+ 7 rows in set (0.00 sec) mysql> \q Bye
第二、在主2上创建数据库、创建表、插入数据、删除部分数据；并在主1上查看是否同步
#创建数据库、创建表、插入数据、删除部分数据 [root@slave ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database mydbs222; Query OK, 1 row affected (0.00 sec) mysql> use mydbs222; Database changed mysql> CREATE TABLE t1 (id int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 value (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> delete from t1 where id > 7; Query OK, 3 rows affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +------+ 7 rows in set (0.00 sec) #查看是否同步成功 [root@master ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydbs | | mydbs222 | | mydbtestt1 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> use mydbs222; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------------+ | Tables_in_mydbs222 | +--------------------+ | t1 | +--------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +------+ 7 rows in set (0.00 sec)
到此，主主复制就完成了！是不是So Easy 啊！呵呵
十、mysql的高可用ha
1、简介
Mysql的高可用是基于Mysql的主主复制的基础，在这里主主复制就不在累述。
2、安装依赖包
yum -y install pcre-devel openssl-devel popt-devel
3、安装keepalived服务并添加系统服务
tar zxvf keepalived-1.2.7.tar.gz cd keepalived-1.2.7 ./configure --prefix=/usr/local/keepalived make && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ mkdir /etc/keepalived/ cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
4、配置keepalived服务
ON Master1
[root@master keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
zhengyansheng@cdvcloud.com #故障联系人
}
notification_email_from admin@cdvcloud.com #故障发送人
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-MASTER #定义router_id的名称
}
vrrp_instance VI_1 {
state BACKUP #都要修改成BACKUP
interface eth1 #监听的本地网卡
virtual_router_id 51 #默认为51，主从都选择默认
priority 100 #优先级高的为主节点，从节点修改成80
advert_int 1 #检查间隔，默认为1秒
nopreempt #不抢占资源，意思就是它活了之后也不会再把主抢回来
authentication {
auth_type PASS #认证方式，可以是PASS或AH两种认证方式
auth_pass 1111 #认证密码
}
virtual_ipaddress {
192.168.2.88 #这里设置的就是VIP，也就是虚拟IP地址，他随着state的变化而增加删除，
}
}
virtual_server 192.168.2.88 3306 {
delay_loop 2 #每个2秒检查一次real_server状态；即服务轮询的时间间隔
lb_algo rr #LVS调度算法 rr|wrr|lc|wlc|lblc|sh|dh
lb_kind DR #LVS模式 NAT|DR|TUN
nat_mask 255.255.255.0
persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP #健康检查用的是TCP还是UDP
real_server 192.168.2.100 3306 { #检测本地mysql，backup也要写检测本地mysql是否存活
weight 3
notify_down /usr/local/keepalived/mysql.sh #当mysql服务down时，执行此脚本，杀死keepalived实现VIP切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
}
ON Master2
[root@slave keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
zhengyansheng@cdvcloud.com
}
notification_email_from admin@cdvcloud.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-BACKUP
}
vrrp_instance VI_1 {
state BACKUP
interface eth1
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.2.88
}
}
virtual_server 192.168.2.88 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.2.200 3306 {
weight 3
notify_down /usr/local/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
5、建立授权登陆用户(两台服务器都要建立授权登陆用户)
mysql> grant all privileges on *.* to root@"192.168.2.%" identified by "123456"; mysql> flush privileges
6、启动keepalived服务(两台服务器都要启动keepalived服务)
service keepalived start
7、查看系统日志信息和vip地址
###查看输出系统日志信息 [root@slave ~]# tail -f /var/log/messages Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: TCP connection to [192.168.2.200]:3306 failed !!! Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Removing service [192.168.2.200]:3306 from VS [192.168.2.88]:3306 Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Executing [/usr/local/keepalived/mysql.sh] for service [192.168.2.200]:3306 in VS [192.168.2.88]:3306 Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Lost quorum 1-0=1 > 0 for VS [192.168.2.88]:3306 Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Remote SMTP server [127.0.0.1]:25 connected. Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: SMTP alert successfully sent. Sep 18 23:41:55 slave Keepalived[8952]: Stopping Keepalived v1.2.7 (09/18,2014) Sep 18 23:41:55 slave Keepalived_vrrp[8955]: VRRP_Instance(VI_1) sending 0 priority Sep 18 23:41:55 slave Keepalived_vrrp[8955]: VRRP_Instance(VI_1) removing protocol VIPs. ###查看vip地址 无 [root@slave ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:c3:25:76 brd ff:ff:ff:ff:ff:ff inet 192.168.2.200/24 brd 192.168.2.255 scope global eth1 inet6 fe80::20c:29ff:fec3:2576/64 scope link valid_lft forever preferred_lft forever
###查看输出系统日志信息 [root@master ~]# tail -f /var/log/messages Sep 18 23:41:55 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Transition to MASTER STATE Sep 18 23:41:56 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Entering MASTER STATE Sep 18 23:41:56 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) setting protocol VIPs. Sep 18 23:41:56 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.2.88 Sep 18 23:41:56 master Keepalived_healthcheckers[5328]: Netlink reflector reports IP 192.168.2.88 added Sep 18 23:42:01 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.2.88 #查看vip地址 有 [root@master ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:ad:35:d7 brd ff:ff:ff:ff:ff:ff inet 192.168.2.100/24 brd 192.168.2.255 scope global eth1 inet 192.168.2.88/32 scope global eth1 inet6 fe80::20c:29ff:fead:35d7/64 scope link valid_lft forever preferred_lft forever
8、keepalived主从切换的脚本(分别在两台服务器上运行监控脚本)
#!/bin/bash while : do mysqldpid=`ps -C mysqld --no-header | wc -l` if [ $mysqldpid -eq 0 ];then /etc/init.d/mysqld restart sleep 5 mysqldpid=`ps -C mysqld --no-header | wc -l` echo $mysqldpid if [ $mysqldpid -eq 0 ];then /etc/init.d/keepalived stop fi fi sleep 5 done
nohup /usr/local/mysql/mysql_keepalived.sh &
没有测试这个监控脚本
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD="1234"
CHECK_TIME=3
#mysql is working MYSQL_OK is 0 , mysql down MYSQL_OK is 1
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p $MYSQL_PASSWORD -e "show status;" > /dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=0
else
MYSQL_OK=1
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 0 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 1 ] && [ $CHECK_TIME -eq 1 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done
9、测试高可用
2>、停止master这台mysql服务，是否能正常切换过去，可通过ip addr命令来查看VIP在哪台服务器上
[root@master ~]# killall -9 mysqld [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 You have new mail in /var/spool/mail/root [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# killall -9 mysqld mysqld: 没有进程被杀死 [root@master ~]# service mysqld status SUCCESS! MySQL running (6462) [root@master ~]# service keepalived status keepalived 已停 [root@master ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:ad:35:d7 brd ff:ff:ff:ff:ff:ff inet 192.168.2.100/24 brd 192.168.2.255 scope global eth1 inet6 fe80::20c:29ff:fead:35d7/64 scope link valid_lft forever preferred_lft forever
3>、可通过查看/var/log/messges日志，看出主备切换过程
[root@master ~]# tail -f /var/log/messages Sep 18 23:41:55 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Transition to MASTER STATE Sep 18 23:41:56 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Entering MASTER STATE Sep 18 23:41:56 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) setting protocol VIPs. Sep 18 23:41:56 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.2.88 Sep 18 23:41:56 master Keepalived_healthcheckers[5328]: Netlink reflector reports IP 192.168.2.88 added Sep 18 23:42:01 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.2.88 Sep 18 23:53:28 master Keepalived_healthcheckers[5328]: TCP connection to [192.168.2.100]:3306 failed !!! Sep 18 23:53:28 master Keepalived_healthcheckers[5328]: Removing service [192.168.2.100]:3306 from VS [192.168.2.88]:3306 Sep 18 23:53:28 master Keepalived_healthcheckers[5328]: Executing [/usr/local/keepalived/mysql.sh] for service [192.168.2.100]:3306 in VS [192.168.2.88]:3306 Sep 18 23:53:28 master Keepalived_healthcheckers[5328]: Lost quorum 1-0=1 > 0 for VS [192.168.2.88]:3306 Sep 18 23:53:28 master Keepalived_healthcheckers[5328]: Remote SMTP server [127.0.0.1]:25 connected. Sep 18 23:53:28 master Keepalived_healthcheckers[5328]: SMTP alert successfully sent. Sep 18 23:53:37 master Keepalived[5326]: Stopping Keepalived v1.2.7 (09/18,2014) Sep 18 23:53:37 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) sending 0 priority Sep 18 23:53:37 master Keepalived_vrrp[5329]: VRRP_Instance(VI_1) removing protocol VIPs.
[root@slave ~]# tail -f /var/log/messages Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: TCP connection to [192.168.2.200]:3306 failed !!! Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Removing service [192.168.2.200]:3306 from VS [192.168.2.88]:3306 Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Executing [/usr/local/keepalived/mysql.sh] for service [192.168.2.200]:3306 in VS [192.168.2.88]:3306 Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Lost quorum 1-0=1 > 0 for VS [192.168.2.88]:3306 Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: Remote SMTP server [127.0.0.1]:25 connected. Sep 18 23:41:46 slave Keepalived_healthcheckers[8954]: SMTP alert successfully sent. Sep 18 23:41:55 slave Keepalived[8952]: Stopping Keepalived v1.2.7 (09/18,2014) Sep 18 23:41:55 slave Keepalived_vrrp[8955]: VRRP_Instance(VI_1) sending 0 priority Sep 18 23:41:55 slave Keepalived_vrrp[8955]: VRRP_Instance(VI_1) removing protocol VIPs. Sep 18 23:53:02 slave Keepalived[10202]: Starting Keepalived v1.2.7 (09/18,2014) Sep 18 23:53:02 slave Keepalived[10203]: Starting Healthcheck child process, pid=10205 Sep 18 23:53:02 slave Keepalived[10203]: Starting VRRP child process, pid=10206 Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Interface queue is empty Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Netlink reflector reports IP 192.168.2.200 added Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Netlink reflector reports IP fe80::20c:29ff:fec3:2576 added Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Registering Kernel netlink reflector Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Registering Kernel netlink command channel Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Registering gratuitous ARP shared channel Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Opening file '/etc/keepalived/keepalived.conf'. Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Interface queue is empty Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Netlink reflector reports IP 192.168.2.200 added Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Netlink reflector reports IP fe80::20c:29ff:fec3:2576 added Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Registering Kernel netlink reflector Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Registering Kernel netlink command channel Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Opening file '/etc/keepalived/keepalived.conf'. Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Configuration is using : 63030 Bytes Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Configuration is using : 11601 Bytes Sep 18 23:53:02 slave Keepalived_vrrp[10206]: Using LinkWatch kernel netlink reflector... Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Using LinkWatch kernel netlink reflector... Sep 18 23:53:02 slave Keepalived_healthcheckers[10205]: Activating healthchecker for service [192.168.2.200]:3306 Sep 18 23:53:02 slave Keepalived_vrrp[10206]: VRRP_Instance(VI_1) Entering BACKUP STATE Sep 18 23:53:02 slave Keepalived_vrrp[10206]: VRRP sockpool: [ifindex(2), proto(112), fd(11,12)] Sep 18 23:53:37 slave Keepalived_vrrp[10206]: VRRP_Instance(VI_1) Transition to MASTER STATE Sep 18 23:53:38 slave Keepalived_vrrp[10206]: VRRP_Instance(VI_1) Entering MASTER STATE Sep 18 23:53:38 slave Keepalived_vrrp[10206]: VRRP_Instance(VI_1) setting protocol VIPs. Sep 18 23:53:38 slave Keepalived_vrrp[10206]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.2.88 Sep 18 23:53:38 slave Keepalived_healthcheckers[10205]: Netlink reflector reports IP 192.168.2.88 added Sep 18 23:53:43 slave Keepalived_vrrp[10206]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.2.88
4>、master服务器故障恢复后，是否主动抢占资源，成为活动服务器。
[root@master ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:ad:35:d7 brd ff:ff:ff:ff:ff:ff inet 192.168.2.100/24 brd 192.168.2.255 scope global eth1 inet6 fe80::20c:29ff:fead:35d7/64 scope link valid_lft forever preferred_lft forever [root@master ~]# service mysqld status SUCCESS! MySQL running (6462) [root@master ~]# service keepalived status keepalived 已停 [root@master ~]# service keepalived start 正在启动 keepalived： [确定] [root@master ~]# service keepalived status keepalived (pid 6761) 正在运行... [root@master ~]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:ad:35:d7 brd ff:ff:ff:ff:ff:ff inet 192.168.2.100/24 brd 192.168.2.255 scope global eth1 inet6 fe80::20c:29ff:fead:35d7/64 scope link valid_lft forever preferred_lft forever
*******2014-11-12补充内容************
介绍修改mysql密码的三种方法
解决mysql数据库中忘记root密码的的两种方法
*******2014-11-13补充内容************
mysqldump导出数据时排除某些表
mysqldump -u*** -p*** database --ignore-table=database.table1 --ignore-table=database.table2 > backup.sql
*******2015-09-14补充内容************
mysql主从复制中允许哪些库复制，忽略哪些库复制
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000007 | 205 | t | tt | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
在从库上有两个文件分别是master.info和relay-log.info文件
这两个文件分别对应着从库的两个线程 io线程 对应master.info； sql线程对应relay-log.info文件
转载于:https://blog.51cto.com/467754239/1548094
421
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
