percona mysql 同步_使用percona做mysql5.7的在线主从同步

1、配置mysql5.7社区版yum仓库

[root@localhost rpm-gpg]# cat /etc/yum.repos.d/mysql-community.repo

[mysql-connectors-community]

name=MySQL Connectors Community

baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]

name=MySQL Tools Community

baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.5

[mysql55-community]

name=MySQL 5.5 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6

[mysql56-community]

name=MySQL 5.6 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql57-community]

name=MySQL 5.7 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]

name=MySQL 8.0 Community Server

baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]

name=MySQL Tools Preview

baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

2、yum安装mysql5.7社区版

yum install -y mysql-community-server.x86_64

3、安装后第一次使用会有个临时登陆密码，要使用临时登陆密码登陆后修改root密码

[root@localhost rpm-gpg]# grep "password" /var/log/mysqld.log

2020-11-27T09:00:48.521464Z 1 [Note] A temporary password is generated for root@localhost: **l#ORFKqjH7.C**

[root@localhost rpm-gpg]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> alter user user() identified by "123456";

4、master配置/etc/my.cnf，简单配置server-id和bin-log

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

log_bin=mysql-bin

server-id=1

5、下载percona-xtrabackup并备份

wget http://repo.percona.com/tools/yum/release/7/RPMS/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

1.主库中备份，将mysql数据库整个备份到/opt/目录下

# innobackupex --defaults-file="/etc/my.cnf" --user=root -p'pass' /opt

2.预处理，进行事物检查(最好直接在主库中备份完成后检查)

innobackupex --defaults-file="/etc/my.cnf" --user=root -p'Hczd365!!' --apply-log --use-memory=2G /opt/2020-11-27_17-47-45/

3.将备份出来的数据库scp拷贝到从库

scp -r /opt/2020-11-27_18-04-02 10.1.6.102:/opt/

4.关闭从库，清理从库数据，恢复数据到从库

systemctl stop mysqld

删除从库的数据和日志信息

rm -rf /data/mysql_data/*

5.在从库上执行(将数据恢复到数据库中)

innobackupex --defaults-file="/etc/my.cnf" --user=root --copy-back /opt/2020-11-27_18-04-02

6.修改权限启动从库

chown -R mysql.mysql /var/lib/mysql

查看主库中master位置，方便后面配置主从同步点

cat /opt/2020-11-27_18-04-02/xtrabackup_binlog_info

7.主库中创建建salve同步用户

grant replication slave,reload,super on *.* to rep@172.30.0.52 identified by 'wsdb123';

FLUSH PRIVILEGES;

8.从库执行同步

mysql> change master to master_host='f_crack_check.prod.mysql.eus',master_user='rep',master_password='wsdb123',master_log_file='mysql-bin.000004',master_log_pos=660;

mysql> start slave;

9.从上查看同步状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.2.205

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 772

Relay_Log_File: mysql-master-relay-bin.000009

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes