1.编写脚本,支持让用户自主选择,使用mysqldump还是xtraback全量备份。  

PS3="please select your backup type:";
select type in mysqldump xtraback;do
case $REPLY in
1)
[ -e /data/backup ] || mkdir /data/backup
mysqldump -A --single-transaction -F |gzip > /data/backup/mysqldump_full_backup-$(date '+%Y%m%d-%H%M').sql.gz || (echo 'backup failed!' && exit 2 )
echo 'mysqldump  full-backup successfully !'
break
;;
2)
xtrabackup --backup --target-dir=/data/backup/xtra_full_backup-$(date '+%Y%m%d-%H%M') &> /dev/null || (echo 'backup failed!' && exit 2 )
echo 'xtrabackup full-backup successfully !'
break
;;
*)
echo "please input number for your backup type "                                                                                            
esac
done

image

2.配置Mysql主从同步  

主服务器配置(192.168.47.154):
1.修改配置文件
vim /etc/my.cnf
    server-id=1
    log-bin=/var/lib/mysql/logbin_tao

2.systemctl restart mariadb

3.创建用户
grant replication slave on *.* to 'repluser'@'192.168.47.%' identified by 'tao'

4.查看二进制日志
MariaDB [mysql]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| logbin_tao.000001 |       404 |
+-------------------+-----------+
1 row in set (0.00 sec)


从服务器配置(192.168.47.101 )
1.修改配置文件
vim /etc/my.cnf
    server-id=2
    read-only

2.CHANGE MASTER TO
        MASTER_HOST='192.168.47.154',
        MASTER_USER='repluser',
        MASTER_PASSWORD='tao',
        MASTER_PORT=3306,
        MASTER_LOG_FILE='logbin_tao.000001,
        MASTER_LOG_POS=404;

3.show slave status\G

4.start slave;

3.使用MHA实现Mysql高可用

1.准备:

1.)4台主机

192.168.47.100  (maha-manager)

192.168.47.101 (主服务器)

192.168.47.102 (从服务器)

192.168.47.103 (从服务器)

2.)时间同步,防火墙关闭/selinux关闭

2.安装  

centos7_2 [主服务器 192.168.47.101]  

#1.安装数据库

yum install mariadb-server -y

#2.修改配置文件

/etc/my.cnf

server-id=2  

log-bin  

ship_name-resolve  

systemctl restart mariadb

#3.创建账号

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.47.%' identified by 'tao';

MariaDB [(none)]> grant all on *.* to mhauser@'192.168.47.%' identified by 'tao';

        查看二进制日志

        MariaDB [(none)]> show master logs;

                +--------------------+-----------+

                | Log_name           | File_size |

                +--------------------+-----------+

                | mariadb-bin.000001 |       534 |

                +--------------------+-----------+

                1 row in set (0.00 sec)

#4.安装mha4mysql-node-0.56-0.el6.noarch.rpm

yum install mha*.rpm

centos7_3 、centos7_4[从服务器192.168.47.102 、192.168.47.103]  

#1.安装数据库

yum install mariadb-server -y

#2.修改配置文件

/etc/my.cnf

server-id=3  

log-bin  

read-only  

skip_name_resolve  

relay_log_purge=0  

server-id=4  

log-bin  

read-only  

skip_name_resolve  

relay_log_purge=0  

systemctl restart mariadb

#3.执行命令

MariaDB [(none)]> CHANGE MASTER TO

MASTER_HOST='192.168.47.101',

MASTER_USER='repluser',

MASTER_PASSWORD='tao',

MASTER_PORT=3306,

MASTER_LOG_FILE='mariadb-bin.000001',

MASTER_LOG_POS=245;

查看状态

MariaDB [(none)]> show slave status\G

启动

MariaDB [(none)]> start slave;

从节点有只读属性

MariaDB [(none)]> show variables like 'read_only';

        +---------------+-------+

        | Variable_name | Value |

        +---------------+-------+

        | read_only     | ON    |

        +---------------+-------+

        1 row in set (0.06 sec)

#4.安装mha4mysql-node-0.56-0.el6.noarch.rpm

yum install mha*.rpm

maha-manager[192.168.47.100]  

#1.准备文件

mha4mysql-manager-0.56-0.el6.noarch.rpm  

mha4mysql-node-0.56-0.el6.noarch.rpm

#2.安装软件

yum install mha*.rpm  [开启epel源]

#3.基于key的验证

        (1)ssh-keygen

        [root@mha-manager ~]#ls -a .ssh

         .  ..  id_rsa  id_rsa.pub

        (2)ssh-copy-id 192.168.47.100 (本机电脑的IP)

        cd .ssh

        [root@mha-manager .ssh]#ls

        authorized_keys  id_rsa  id_rsa.pub  known_hosts

        (3)拷贝

        scp -r .ssh 192.168.47.101:/root/

        scp -r .ssh 192.168.47.102:/root/

        scp -r .ssh 192.168.47.103:/root/

        (4)测试:

        ssh 192.168.47.101

        ssh 192.168.47.102

        ssh 192.168.47.103

#4.建立配置文件

mkdir /etc/mha/

vim /etc/mha/app1.cnf

------------------------

[server default]

user=mhauser

password=tao

manager_workdir=/data/mastermha/app1/

manager_log=/data/mastermha/app1/manager.log

remote_workdir=/data/mastermha/app1/

ssh_user=root

repl_user=repluser

repl_password=tao

ping_interval=1

[server1]

hostname=192.168.47.101

candidate_master=1

[server2]

hostname=192.168.47.102

candidate_master=1

[server3]

hostname=192.168.47.103  

#5.检查

masterha_check_ssh   --conf=/etc/mha/app1.cnf

image  

masterha_check_repl  --conf=/etc/mha/app1.cnf

image  

#6.启动

masterha_manager  --conf=/etc/mha/app1.cnf

image

测试

查看日志

image

主服务器

create databases tao;

create table testlog (id int auto_increment primary key,name char(10),age int default 20);

delimiter $$

create procedure  sp_testlog()
begin 
declare i int;
set i = 1;
while i <= 100000
do  insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$

delimiter ;

call  sp_testlog;

直接关闭主服务的电脑

image

查看日志

image

image

image

image

变为主服务器的电脑查看信息:【192.168.47.102】

image

最后把变成主服务器的配置文件/etc/my.cnf中的read_only去掉