MYSQL(主从备份、GTID备份、半同步复制、MHA实现主从复制高可用）

实验环境 rhel7.3

	主机			server1				server2
	ip			172.25.47.1			172.25.47.2
	服务			主服务master			从属

一、数据库的主从备份

1、master侧

1）安装

[root@server1 ~]# cd mysql/
[root@server1 mysql]# ls
master_ip_failover       mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar      sysbench
master_ip_online_change  mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
MHA-7                    send_report
[root@server1 mysql]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server1 mysql]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm

2）配置mysql

[root@server1 mysql]# vim /etc/my.cnf
 29 log-bin=mysql-bin		##添加plugins插件
 30 server-id=1
[root@server1 mysql]# systemctl start mysqld
[root@server1 mysql]# cat /var/log/mysqld.log |grep password
2019-04-23T09:14:18.235905Z 1 [Note] A temporary password is generated for root@localhost: 0I1-V!l!-qlk					##查看系统生成密码
[root@server1 mysql]# mysql_secure_installation
修改密码					#Westos+001

3）授权

[root@server1 mysql]# mysql -uroot -pWestos+001
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000002 | 398 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

mysql> grant replication slave on . to repl@‘172.25.47.%’ identified by ‘Westos+001’; ##授权
mysql> show plugins; ##查看plugins插件
±---------------------------±---------±-------------------±---------------------±--------+
| Name | Status | Type | Library | License |
±---------------------------±---------±-------------------±---------------------±--------+
| binlog | ACTIVE | STORAGE ENGINE | NULL

4） 添加数据库信息

mysql> create database westos;
	Query OK, 1 row affected (0.01 sec)
mysql> use westos;
Database changed
mysql> create table usertb (
    -> username varchar(10) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.04 sec)

2、从属端

1）安装

server2

[root@server2 ~]# cd mysql/
[root@server2 mysql]# ls
	master_ip_failover       mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar      sysbench
	master_ip_online_change  mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
	MHA-7                    send_report
[root@server2 mysql]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server2 mysql]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm
[root@server2 mysql]# vim /etc/my.cnf
 28 server-id=2
[root@server2 mysql]# systemctl start mysqld
[root@server2 mysql]# cat /var/log/mysqld.log |grep password		##查看系统生成密码
2019-04-23T09:17:58.871656Z 1 [Note] A temporary password is generated for root@localhost: ve;glkRnt4KS
[root@server2 mysql]# mysql_secure_installation

2）从master备份数据

[root@server2 mysql]# mysql -uroot -pWestos+001

mysql> change master to master_host='172.25.47.1',master_user='repl',master_password='Westos+001',master_log_file='mysql-bin.000002',master_log_pos=398;		##备份

mysql> start slave;		##开启slave
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G	##查看slave状态，要IO和SQL都yes

	    Slave_IO_Running: Yes
	    Slave_SQL_Running: Yes

3）查看数据

mysql> show databases; 查看 数据已经从server1上备份过来

server1

server2