一.主从复制
重点,在做主从复制之前,一定要做全备
[root@db01 ~]# mysqldump -uroot -p1 -S /opt/test.sock -A > /tmp/full.sql
从库:
mysql> source /tmp/full.sql
保证主从库数据一致
1.修改主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin
binlog_format=row
server_id=1
2.重启数据库,让配置文件生效
[root@db01 ~]# /etc/init.d/mysqld restart
3.连接数据库
[root@db01 ~]# mysql -uroot -p123
4.创建主从复制用户
mysql> grant replication slave on . to rep@'%' identified by '123';
5.记录binlog名字和binlog位置点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 317 | | | |
+------------------+----------+--------------+------------------+-------------------+
6.在从库上执行change master to语句
mysql > change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=317;
7.从库开启主从复制(IO线程,SQL线程)
mysql> start slave;
8.查看主从复制的状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
9.IO线程报错:
1.网络问题:ping 192.168.13.60
2.端口问题:telnet 192.168.13.60 3306
3.用户名密码问题:mysql -urep -p123 -h192.168.13.60
1)用户名密码输入错误:
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'10.0.0.52' (using password: YES)
2)跳过反向解析
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'db02' (using password: NO)
vim /etc/my.cnf
[mysqld]#mysql5.6
skip-name-resolve
skip-name-resolv
mysql5.7
skip_name_resolve
/etc/init.d/mysqld restart
从库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rep |
| rep1 |
| rep2 |
| test |
+--------------------+
主库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ZLS |
| mysql |
| nb |
| oldboy |
| performance_schema |
| rep |
| rep1 |
| rep2 |
| world |
| zls1 |
+--------------------+
11 rows in set (0.00 sec)
取消从库的身份
进入从库
mysql> stop slave;
mysql> reset slave all;
mysql> show slave status\G
二.使用binlog恢复任意时间点的数据
1.binlog的工作模式
1)STATEMENT语句模式:默认
mysql> create database binlog;
mysql> create table binlog(id int);
mysql> insert into binlog values(1),(2),(3);
查看binlog:
[root@db01 data]# mysqlbinlog mysql-bin.000002
2)ROW行级模式:mysql5.7默认
[root@db01 data]# vim /etc/my.cnf
binlog_format=row
[root@db01 data]# /etc/init.d/mysqld restart
mysql> create database binlog1;
mysql> use binlog1;
mysql> create table binlog1(id int);
mysql> insert into binlog1 values(1),(2),(3);
查看binlog:
[root@db01 data]# mysqlbinlog mysql-bin.000003
行级模式:记录的是SQL语句每一行的变化过程
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003
BEGIN
/*!*/;
# at 406
#190510 10:48:41 server id 1 end_log_pos 459 CRC32 0xc0c7e4bc Table_map: `binlog1`.`binlog1` mapped to number 70
# at 459
#190510 10:48:41 server id 1 end_log_pos 509 CRC32 0x51306b79 Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `binlog1`.`binlog1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `binlog1`.`binlog1`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `binlog1`.`binlog1`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 509
#190510 10:48:41 server id 1 end_log_pos 540 CRC32 0x010ed7db Xid = 22
COMMIT/*!*/;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
模拟数据变化:
mysql> update binlog1 set id=10 where id=1;
mysql> select * from binlog1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
mysql> delete from binlog1 where id=2;
mysql> select * from binlog1;
+------+
| id |
+------+
| 10 |
| 3 |
+------+
mysql> drop table binlog1;
mysql> drop database binlog1;
结束位置点:540
起始位置点:120
3)MIXED混合模式:将语句模式和行级模式 混合使用
2.如何查看binlog
[root@db01 data]# mysqlbinlog mysql-bin.000002
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003
三.部署MHA
提前准备环境
将所需的包全部导入
Atlas-2.2.1.el6.x86_64.rpm
mha4mysql-manager-0.56.tar.gz
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56.tar.gz
mha4mysql-node-0.56-0.el6.noarch.rpm
三台都执行:
db01,02,03:yum install perl-DBD-MySQL -y
只在第三台执行:
db03:
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
1.做主从复制的先决条件:
主库:
1)开启binlog
2)开启server_id
3)创建主从复制用户
从库:
1)必须开启binlog
2)从库开启server_id(与主库不相同)
3)从库必须要创建主从复制用户
4)开IO,SQl线程 start slave;
三个库都要运行:
vim /etc/my.cnf
db01:
[mysqld]
log_bin=mysql-bin
binlog_format=row
server_id=1
db02:
[mysqld]
log_bin=mysql-bin
binlog_format=row
server_id=2
db03:
[mysqld]
log_bin=mysql-bin
binlog_format=row
server_id=3
2.MHA工作原理
当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master。
db01
1.开启binlog
2.主从复制用户
3.server_id 不同
db02
1.开启binlog
2.主从复制用户
3.server_id 不同
db03
1.开启binlog
2.主从复制用户
3.server_id 不同
=============================================================
3.MHA的工具
解压manager
[root@db01 ~]# tar xf mha4mysql-manager-0.56.tar.gz
[root@db01 ~]# cd /root/mha4mysql-manager-0.56/bin
[root@db01 bin]# ll
Manager工具包主要包括以下几个工具：
masterha_check_ssh #检查MHA的ssh-key
masterha_check_repl #检查主从复制情况
masterha_manger #启动MHA
masterha_check_status #检测MHA的运行状态
masterha_master_monitor #检测master是否宕机
masterha_master_switch #手动故障转移
masterha_conf_host #手动添加server信息
masterha_secondary_check #建立TCP连接从远程服务器
masterha_stop #停止MHA
解压node
[root@db01 ~]# tar xf mha4mysql-node-0.56.tar.gz
[root@db01 ~]# cd /root/mha4mysql-node-0.56/bin
[root@db01 bin]# ll
Node工具包主要包括以下几个工具：
save_binary_logs #保存宕机的master的binlog
apply_diff_relay_logs #识别relay log的差异
filter_mysqlbinlog #防止回滚事件
purge_relay_logs #清除中继日志
MHA 是 C/S结构的服务
manager 服务端
node 客户端
4.主从配置
主库执行：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 829135 | | | |
+------------------+----------+--------------+------------------+-------------------+
记录file和position这两条信息
从库执行：
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=829135;
mysql> start slave;
mysql> show slave status\G
5.只读和禁用删除relay log功能
#禁用自动删除relay log 功能(3个库都执行)
mysql> set global relay_log_purge = 0;
#设置只读(只能在从库执行)
mysql> set global read_only=1;
#编辑配置文件（三个都添加）
[root@mysql-db02 ~]# vim /etc/my.cnf
#在mysqld标签下添加
[mysqld]
relay_log_purge = 0 （禁用自动删除relay log 永久生效）
6.安装node包（三台都要装）
[root@db01 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db02 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db03 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
7.安装manager包(避免装在主库上)
[root@db03 ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
8.创建命令软连接
[root@db01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
[root@db01 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
[root@db02 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
[root@db02 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
[root@db03 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/
[root@db03 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
9.创建mha工作目录
[root@db03 ~]# mkdir /etc/mha
10.编辑mha配置文件
[root@db03 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/usr/local/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
[server1]
hostname=10.0.0.51
port=3306
[server2]
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306
11.在mysql中创建一个mha管理用户(三台),只需要在主库上创建
mysql> grant all on . to mha@'%' identified by 'mha';
12.创建密钥对,做免密登录
[root@db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
[root@db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
[root@db02 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
[root@db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
[root@db03 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.53
[root@db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.52
[root@db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.51
13.测试免密登录
[root@db01 ~]# ssh root@10.0.0.51
[root@db01 ~]# ssh root@10.0.0.52
[root@db01 ~]# ssh root@10.0.0.53
[root@db02 ~]# ssh root@10.0.0.51
[root@db02 ~]# ssh root@10.0.0.52
[root@db02 ~]# ssh root@10.0.0.53
[root@db03 ~]# ssh root@10.0.0.51
[root@db03 ~]# ssh root@10.0.0.52
[root@db03 ~]# ssh root@10.0.0.53
14.使用mha工具检测ssh（db03执行）
[root@db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
成功：All SSH connection tests passed successfully.
15.使用mha工具检测主从复制（db03执行）
[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
报错：1045:Access denied for user 'mha'@'db03' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
反向解析问题
[root@db01 ~]# vim /etc/my.cnf
[root@db02 ~]# vim /etc/my.cnf
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve
每台都要加，加完重启
[root@db01 ~]# /etc/init.d/mysqld restart
报错：User rep does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.
从库没有rep用户
主库再创建一次
mysql> grant replication slave on *.* to rep@'%' identified by '123';
成功：MySQL Replication Health is OK.
16.启动mha（db03上启动）
[root@db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/manager.log 2>&1 &
17.检查MHA启动状态（db03上执行）
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:25635) is running(0:PING_OK), master:10.0.0.51
18.绑定vip需要一个脚本
①需要配置vip漂移
添加配置文件（MHA的配置文件，所以只能在db03上加）
[root@db03 ~]# vim /etc/mha/app1.cnf
master_ip_failover_script=/etc/mha/app1/master_ip_failover
[root@db03 ~]# cd /etc/mha/app1
②上传脚本（master_ip_failover）
[root@db03 app1~]# vim master_ip_failover
my $vip = '10.0.0.55/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
19.主库绑定vip
[root@db01 ~]# /sbin/ifconfig eth0:0 10.0.0.55/24
20.给脚本执行权限
[root@db03 app1]# chmod +x master_ip_failover
21.重启mha
[root@db03 app1]# masterha_stop --conf=/etc/mha/app1.cnf
[root@db03 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/manager.log 2>&1 &
如果mha启动不了？
1.脚本语法问题
2.脚本的格式问题
安装格式转换命令:
[root@db03 app1]# yum install -y dos2unix
[root@db03 app1]# dos2unix master_ip_failover
dos2unix: converting file master_ip_failover to Unix format ...
3.脚本的权限问题 chmod +x master_ip_failover
[root@db03 app1]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:26448) is running(0:PING_OK), master:10.0.0.52
