本文详细介绍了在CentOS系统中配置MySQL主从复制的过程，包括建立互信、时间同步、安装MySQL、主从配置文件、主从部署等步骤，并提供了具体的命令行操作。通过基于语句和行的复制方式，确保数据一致性。最后，验证了主从配置的正确性并通过创建和清空数据库来测试集群状态。
MySQL主从复制原理
1. 基于语句的复制；——基于SQL语言命令的复制形式，使用SQL命令执行复制，效率高
2. 基于行的复制；——复制数据库变化的内容，不是执行命令
3. 混合类型的复制；——默认采用语句类型的复制，如果发现有不确定问题等其他原因造成无法复制的情况再进行基于行的复制
master(主) :192.168.223.130
slave(从):192.168.223.131
建立互信
master
修改主机名
#hostnamectl set-hostname master
#bash
关闭防火墙
#systemctl stop firewalld.service
#setenforce 0
[root@master ~]# ssh-keygen -t rsa #生成密钥
[root@master ~]# ssh-copy-id 192.168.223.131 #将公钥分发到其他节点
slave
修改主机名
#hostnamectl set-hostname slave
#bash
关闭防火墙
#systemctl stop firewalld.service
#setenforce 0
[root@slave ~]# ssh-keygen -t rsa #生成密钥
[root@slave ~]# ssh-copy-id 192.168.223.130 #将公钥分发到其他节点
时间同步
master
# yum install ntp -y
# systemctl start ntpd // 开启ntpd
# systemctl enable ntpd
slave
# yum install ntp -y (从库不用开启ntpd）
# ntpdate master // 时间同步
出现如下错误
解决方法
#vim /etc/hosts
再次输入#ntpdate master就成功了
安装MySQL （主从都需操作）
卸载mariadb
# rpm -qa | grep mariadb
# rpm -e --nodeps mariadb-libs
安装mysql5.7
# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
# yum localinstall mysql57-community-release-el7-8.noarch.rpm -y
# yum install mysql-community-server -y
若出现GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 报错信息，需要更新MySQL的GPG，需要更新rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld
# grep 'temporary password' /var/log/mysqld.log //查看初始密码
#mysql -uroot -p
mysql> set password for 'root'@'localhost'=password('Jxy123456.');//修改密码 (密码需包含大小写字母符号)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%'identified by 'Jxy123456.' with grant option; //开放远程登录
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; //保存
Query OK, 0 rows affected (0.00 sec)
mysql> quit; // 退出
Bye
Master配置文件
cat /etc/my.cnf //查看
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
vim /etc/my.cnf //修改
[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin = master-bin #二进制日志文件 master-bin可以自己设置
server-id = 1 #服务器的id号，用于区别
log-slave-updates=true #开启从服务器更新日志功能（结合复制流程连接）
max_connections = 1000
max_connect_errors = 1000
gtid_mode=on #开启GITD复制模式，gtid在mysql5.6之后才有，但是mysql5.6上gtid默认是不可用的，5.7支持动态修改gtid
enforce-gtid-consistency=true
log-slave-updates=on
binlog_format=row #使用GITD复制模式官方建议使用row复制模式,具有最高性能
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
slave配置
cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
vim /etc/my.cnf
[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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2 #开启二进制日志
log_bin=master-bin #使用中继日志进行同步
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
master_info_repository=TABLE
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
relay_log_info_repository=TABLE
relay_log_recovery=ON
skip-name-resolve
gtid_mode=on #开启GITD复制模式
enforce-gtid-consistency=true
log-slave-updates=on
binlog_format=row #使用GITD复制模式官方建议使用row复制模式,具有最高性能.
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
主从部署
创建同步账号及授权
mysql> grant replication slave on *.* to 'myslave'@'192.168.223.%' identified by 'Jxy123456.'; //同步账号
重启数据库服务
master
#systemctl restart mysqld
slave
#systemctl restart mysqld
从库连接主库
mysql> CHANGE MASTER TO MASTER_HOST='192.168.223.130', MASTER_USER='myslave', MASTER_PASSWORD='Jxy123456.', MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=154;start slave; //#传统主从配置连接方式
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.223.130', MASTER_USER='myslave', MASTER_PASSWORD='Jxy123456.', master_auto_position=1;start slave; // #GTID模式下同步连接方式（更简化）
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看集群状态
主库：
mysql> show master status \G;
从库：
mysql> show slave status \G;
Slave_IO_Running与Slave_SQL_Running同时为yes才算成功
验证主从配置
主服务器上创建一个名为 test 的数据库
# mysql -u root -p
create database `test`;
show databases;
登陆从节点数据库并查看数据库实例
# mysql -u root -p
show databases;
测试集群
主库创建及删除，同步在从库查看
主服务器上创建库
创建一个库保存数据
# mysql -uroot -p'Jxy123456.' -e "create database lfb"
备份mysql库
# mysqldump -uroot -p'Jxy123456.' mysql>/tmp/mysql.sql
将mysql库恢复成新建的库，作为测试数据
# mysql -uroot -p'Jxy123456.' lfb < /tmp/mysql.sql
登陆主数据库并切换库
# mysql -uroot -pJxy123456.
use lfb;
select count(*) from db;
从服务器上查看库
在从库上进入数据库
# mysql -uroot -pJxy123456.
use lfb;
select count(*) from db; (表是空的)
主服务器上清空库内容并查看库
# mysql -uroot -pJxy123456.
use lfb;
truncate table db;
select count(*) from db;
扫一扫
1929
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
