percona-toolkit 安装使用
搭配主从
安装:yum -y install mariadb mariadb-server(主从都需要安装)
MySQL主从
###############################################################################
在主(Master主机上的操作)
1.关闭防火墙
service iptables stop
setenforce 0
2.安装MySQL客户端和服务端
yum -y install mysql mysql-server
3.启动MySQL服务
service mysqld start
chkconfig mysqld on
4.更改配置文件,开启二进制日志
vim /etc/my.cnf
在mysqld下增加内容:
server-id=1
log-bin=mysql-bin
server-id=1
log-bin=mysql-bin
binlog-ignore-db=infoomation_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
保存退出后重启MySQL
service mysqld restart
systemctl restart mariadb(主从都得配置)
5.登录到本地mysql上,给从(slave)主机授权 同步日志的权限
grant replication slave on *.* to 'tom'@'192.168.189.%' identified by '123';
flush privileges;
MariaDB [(none)]> grant all on *.* to 'slave'@'192.168.91.130' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
6.查看二进制日志和位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 331 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
主(master)配置完毕
###############################################################################
从(slave)主机的操作
1.关闭防火墙
service iptables stop
setenforce 0
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
2.安装MySQL客户端和服务端
yum -y install mysql mysql-server
yum -y install mariadb mariadb-server
3.启动MySQL服务
service mysqld start
chkconfig mysqld on
systemctl restart mariadb
4.更改配置文件,开启中继日志
vim /etc/my.cnf
在mysqld下增加内容:
server-id=2
relay-log=mysql-relay
保存退出后重启MySQL
service mysqld restart
登录本地mysql上,执行同步master二进制日志操作
mysql> stop slave; \\停止slave同步
mysql> change master to
-> master_host='192.168.189.120', \\master主机的ip地址
-> master_user='tom', \\登录master主机的账号
-> master_password='123', \\登录的密码
-> master_log_file='mysql-bin.000001', \\二进制日志文件
-> master_log_pos=331; \\二进制日志文件的位置
mysql> start slave; \\开启slave同步
查询slave状态
mysql> show slave status \G;
注意以下两个线程的状态都为Yes,说明配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
###############################################################################
测试
在主上创建一个数据库,在从查看能够成功同步既是成功!
下载地址:
1.wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/redhat/7/x86_64/percona-toolkit-3.1.0-2.el7.x86_64.rpm
也可以手动上传主从都要做
2.[root@localhost ~]# yum -y install percona-toolkit-3.1.0-2.el7.x86_64.rpm
3 验证 安装完成后会生成以下命令:
[root@localhost ~]# pt
pt-align pt-ioprofile pt-slave-delay
pt-archiver pt-kill pt-slave-find
ptaskset pt-mext pt-slave-resta