CentOS 7安装MySQL8.0配置主从复制

CentOS 7安装MySQL8.0配置主从复制

留下评论 , ,

一.下载安装MySQL

下载地址：https://dev.mysql.com/downloads/mysql/

按照图中选择下载适用与CentOS7 64位MySQL安装包

解压安装包压缩文件

tar -xf mysql-8.0.22-1.el8.x86_64.rpm-bundle.tar

解压后看到下面图中必要文件

按照顺序执行安装命令安装MySQL

1. rpm -ivh mysql-community-common-8.0.22-1.el8.x86_64.rpm
2. rpm -ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
3. rpm -ivh mysql-community-libs-8.0.22-1.el7.x86_64.rpm
4. rpm -ivh mysql-community-client-8.0.22-1.el7.x86_64.rpm
5. rpm -ivh mysql-community-server-8.0.22-1.el7.x86_64.rpm

启动MySQL

启动MySQL：systemctl start mysqld
停止MySQL：systemctl stop mysqld
重启MySQL：systemctl restart mysqld

查看MySQL默认密码

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

修改root密码为root

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

如需远程访问执行下列命令添加root用户远程访问

create user 'root'@'%' identified with mysql_native_password by 'root';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

如果不能远程连接需检查firewall防火墙是否开启，可直接关闭firewall

systemctl stop firewalld.service;
systemctl disable firewalld.service;

二. 配置主节点Master

编辑主服务器Master my.cnf配置文件

nano /etc/my.cnf

加入以下内容

[mysqld]
log-bin=mysql-bin   //启用二进制日志(必填)
server-id=1         //唯一ID，一般取IP最后

编辑从服务器Slave my.cnf配置文件

[mysqld]
log-bin=mysql-bin   //启用二进制日志(选填)
server-id=2         //唯一ID，一般取IP最后（不能重复）

在主节点创建一个用户sync，从服务器同步数据使用

mysql> CREATE USER 'sync'@'192.168.89.5' IDENTIFIED WITH mysql_native_password BY 'Qa1256>!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.89.5';
mysql> flush privileges;

获取主节点当前mysql-bin.log文件名和位置（Position）

mysql> SHOW MASTER STATUS;

在从节点（Slave）设置主节点参数

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.89.4',
MASTER_USER='sync',
MASTER_PASSWORD='Qa1256>!',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=156;

开启同步

mysql> start slave;

查看主从同步状态

mysql> show slave status\G;

查看Master/Slave服务器数据库（创建一个新数据库测试同步）

mysql> show databases;
Master/Slave节点数据一致

创建测试数据库

mysql> create database sync_test charset='utf8';
Master节点

查看Slave节点数据库

Slave节点

三. 配置过程遇到问题

1. 由于是克隆虚拟机导致主从节点UUID相同，解决办法
错误信息
The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

删除一台数据库的auto.cnf后重启MySQL

rm -rf /var/lib/mysql/auto.cnf
systemctl restart mysqld

发表评论