mysql 主从和备份

1.由于centOS7中默认安装了MariaDB,需要先进行卸载

rpm -qa | grep -i mariadb
rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64

查询下本机mysql是否卸载干净，若有残留也需要卸载

rpm -qa | grep mysql

2.下载MySQL仓库并安装

wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
yum -y install mysql80-community-release-el7-7.noarch.rpm

若 GPG Keys are configured as: …异常则更新GPG钥

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

3.安装MySQL数据库

yum -y install mysql-community-server

4.修改my.conf 且开启mysql服务

主数据库

[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 the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

bind-address=0.0.0.0
#character config
character_set_server=utf8mb4
log_bin_trust_function_creators=1
symbolic-links=0
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
lower_case_table_names=1
default-time-zone='+8:00'


server-id=1
log-bin=mysql-bin
#log过期时间
expire_logs_days = 7
max_binlog_size=2G

# 启用全局事务标识符(GTID)，用于标识复制拓扑结构中多个服务器上的事务
gtid_mode=ON
# 使用基于GTID的复制时强制执行严格一致性检查，防止数据不一致性
enforce_gtid_consistency=ON
# 必须参数
# 设置多级复制拓扑结构(包括双master环境)，其中一个从属方作为向下游的其他从属方的主节点，则这很有用
# 控制slave数据库 是否把 从master 接受到的log 合并在 本slave 执行的内容记录到slave的二进制日志中
log_slave_updates=1

从数据库

[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 the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#>
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


bind-address=0.0.0.0
port=3306
#character config

bind-address=0.0.0.0
#character config
character_set_server=utf8mb4
log_bin_trust_function_creators=1
symbolic-links=0
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
lower_case_table_names=1
default-time-zone='+8:00'



server-id=2
log-bin=mysql-bin
#log过期时间
expire_logs_days = 7
max_binlog_size=2G


# 启用全局事务标识符(GTID)，用于标识复制拓扑结构中多个服务器上的事务
gtid_mode=ON
# 使用基于GTID的复制时强制执行严格一致性检查，防止数据不一致性
enforce_gtid_consistency=ON
# 必须参数
# 设置多级复制拓扑结构(包括双master环境)，其中一个从属方作为向下游的其他从属方的主节点，则这很有用
# 控制slave数据库 是否把 从master 接受到的log 合并在 本slave 执行的内容记录到slave的二进制日志中
log_slave_updates=1

启动

systemctl start mysqld.service

5.查看mysql默认密码并登陆

cat /var/log/mysqld.log | grep password

更改密码

alter user 'root'@'localhost' identified by 'yourpassword';
flush privileges;

6. 创建主库用于同步的账号

-- 配置从库连接到主库，准备执行复制数据
CHANGE MASTER TO
MASTER_HOST = 'master.example.com',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION = 1;

7.启动主从复制

-- 启动从库复制进程：
START SLAVE;

-- 检查从库的状态
SHOW SLAVE STATUS\G;

8. 物理备份
   xtrabackup80

#RHEL/Centos 6以上使用
#（1）yum下载软件包
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

#（2）测试软件包是否安装好
yum list | grep percona

#（3）安装xtrabackup 80系列
percona-release setup ps80 && yum -y --skip-broken install percona-xtrabackup-80.x86_64


#（4）【核验】
xtrabackup --version