mysql主从配置

MySQL主从复制原理

1. 基于语句的复制；——基于SQL语言命令的复制形式，使用SQL命令执行复制，效率高

2. 基于行的复制；——复制数据库变化的内容，不是执行命令

3. 混合类型的复制；——默认采用语句类型的复制，如果发现有不确定问题等其他原因造成无法复制的情况再进行基于行的复制

主机规划：

192.168.223.134 master

192.168.223.135 slave

版本：

centos7、MySQL5.7

一、建立互信

1、#hostnamectl set-hostname master (主)//修改主机名

#bash

#hostnamectl set-hostname slave(从)

#bash

2、#systemctl stop firewalld.service //关闭防火墙

3、#systemctl status firewalld.service //查看状态

4、#setenforce 0

5、#ssh-keygen -t rsa //生成密钥

6、#ssh-copy-id 192.168.223.135（从IP) //将公钥分发到其他 节点，从库同上

二、时间同步

1、[root@master ~]#yum install ntp -y //下载ntp

2、[root@master ~]#systemctl start ntpd //打开ntp（从关闭）

3、[root@master ~]#systemctl enable ntpd

4、[root@slave ~]#vim /etc/hosts 在从机添加以下内容：

192.168.223.134(主IP) master

192.168.223.135(从) slave

5、[root@slave ~]#ntpdate master

三、 安装MySQL（主从都需操作）

1、卸载mariadb

[root@master ~]# rpm -qa | grep mariadb //查看包

[root@master ~]# rpm -e --nodeps mariadb-libs

2、安装mysql5.7

[root@master ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

[root@master ~]# yum localinstall mysql57-community-release-el7-8.noarch.rpm -y

[root@master ~]# 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

[root@master ~]# systemctl start mysqld

[root@master ~]# systemctl enable mysqld

[root@master ~]# systemctl status mysqld

[root@master ~]# grep 'temporary password' /var/log/mysqld.log //查看原始密码

[root@master ~]#mysql -uroot -p //进入mysql

[root@master ~]# systemctl start mysqld

[root@master ~]# systemctl enable mysqld

[root@master ~]# systemctl status mysqld

[root@master ~]# grep 'temporary password' /var/log/mysqld.log //查看原始密码

mysql> set password for 'root'@'localhost'=password('Ren123456.'); //修改密码

mysql> grant all privileges on *.* to 'root'@'%' identified by 'Ren123456.' with grant option; //开放远程登录

mysql> flush privileges; //保存然后退出

四、Master配置文件

[root@master ~]# cat /etc/my.cnf

[root@master ~]# 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配置文件

[root@slave ~]# cat /etc/my.cnf

[root@slave ~]# 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 'Ren123456.'; //同步账号

七、重启数据库服务

[root@master ~]# systemctl restart mysqld

[root@slave ~]# systemctl restart mysqld

八、从库连接主库

mysql> CHANGE MASTER TO MASTER_HOST='192.168.223.134', MASTER_USER='myslave', MASTER_PASSWORD='Ren123456.', master_auto_position=1;start slave;

Query OK, 0 rows affected, 2 warnings (0.01 sec) //GTID模式下同步连接方式（更简化）

九、查看集群状态

主库：

mysql> show master status \G;

从库：

mysql> show slave status \G;

十、测试主从

1、主服务器上创建库

1.1 备份mysql库

# mysqldump -uroot -p'Ren123456.' mysql > /tmp/mysql.sql

1.2 创建一个库保存数据

# mysql -uroot -p'Ren123456.' -e "create database lfb"

1.3 将mysql库恢复成新建的库，作为测试数据

# mysql -uroot -p'Ren123456.' lfb < /tmp/mysql.sql

解决方法：

1.4 登录主数据库并切换库

#mysql -uroot -p

mysql>use lfb;

mysql>select count(*) from db;

2、从服务器上查看库

在从上进入数据库

#mysql -uroot -p

mysql>use lfb;

mysql>select count(*) from db;(空）

3、主服务器上清空库内容

#mysql -uroot -p

mysql>use lfb;

mysql>truncate table db;