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

转载

草根追逐 2020-11-30 10:49:14 博主文章分类：数据库|缓存

文章标签 mysql percona xtrabackup 主从同步 文章分类 运维

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