mysql主从搭建从零开始

mysql主从搭建

主从复制原理

当主服务更新数据完成时，更新记录将保存在二进制日志文件中，主服务器开启dump线程通知从服务器。从服务器io线程向主服务器接收二进制日志，并保存在中继日志中。从服务器sql线程从中继日志中读取数据写入本地完成同步。

主从复制相关线程

主节点：

• dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

从节点：

• I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
• SQL Thread：从中继日志中读取日志事件，在本地完成重放

一、（主服务器配置）

1、主服务器数据备份（冷备）

----------------------安装数据库，修改配置---------------------------
#主服务器ip
[root@rocky86-100 ~]# hostname -I
10.0.0.100 
#安装mysql数据库，mysql8.0默认没有用户名与密码，可修改
[root@rocky86-100 ~]# yum -y install mysql-server
##修改mysql配置文件，指定服务器ID、二进制日志存放路径
[root@rocky86-100 ~]# vim /etc/my.cnf
[mysqld]
server-id=100
log_bin=/data/logbin/mysql-bin100
#创建二进制日志备份文件夹
[root@rocky86-100 ~]# mkdir -p /data/logbin/
#修改文件夹所有者所有组
[root@rocky86-100 ~]# chown -R mysql.mysql /data/

-------------------------模拟生产环境------------------------------
#模拟生产环境，主服务器数据已经使用多年
#启动数据库服务（开机启动）
[root@rocky86-100 ~]# systemctl enable --now mysqld
#准备好hellodb数据库、测试快速插入10000表记录的存储过程文件（测试使用的，没有的手动创建，能同步即环境搭建没有问题）
[root@rocky86-100 ~]# ls
hellodb_innodb.sql  testlog.sql
#导入数据库
[root@rocky86-100 ~]# mysql < hellodb_innodb.sql
#导入测试存储过程，可创建10000张表
[root@rocky86-100 ~]# mysql hellodb < testlog.sql

-------------------------备份数据（冷备）------------------------------
#停止数据库服务（冷备），拷贝到从库
[root@rocky86-100 ~]# systemctl stop mysqld;
#压缩data
[root@rocky86-100 ~]# tar cf mysql.tar.gz /var/lib/mysql/*
[root@rocky86-100 ~]# ls
hellodb_innodb.sql  mysql.tar.gz  testlog.sql
#拷贝文件到从库
[root@rocky86-100 ~]# scp mysql.tar.gz root@10.0.0.101:
#启动数据库服务
[root@rocky86-100 ~]# systemctl start mysqld

2、查看二进制日志状态

后面要用到此信息

#登录数据库
[root@rocky86-100 ~]# mysql -uroot -p

#查看二进制文件和位置
mysql> show master logs;
+---------------------+-----------+-----------+
| Log_name            | File_size | Encrypted |
+---------------------+-----------+-----------+
| mysql-bin100.000001 |       179 | No        |
| mysql-bin100.000002 |       156 | No        |
+---------------------+-----------+-----------+

3、创建拥有同步权限的用户

后面要用到此信息

# 创建同步的用户repluser，密码123456，并授权所有库的同步权限，10.0.0.%表示只允许此网段的网络可远程登录服务器
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

二、（从服务器配置）

1、从库环境准备

----------------------安装数据库，修改配置---------------------------
#从服务器的ip
[root@rocky86-101 ~]# hostname -I
10.0.0.101 
#安装数据库
[root@rocky86-101 ~]# yum -y install mysql-server
#修改mysql配置文件，指定服务器ID、二进制日志存放路径
[root@rocky86-101 ~]# vim /etc/my.cnf
[mysqld]
server-id=101
log_bin=/data/logbin/mysql-bin101

#创建二进制日志备份文件夹
[root@rocky86-101 ~]# mkdir -p /data/logbin/
#修改文件夹所有者所有组
[root@rocky86-101 ~]# chown -R mysql.mysql /data/

-----------模拟旧机器环境，测试修改配置文件后数据库能否启动-----------------
#启动数据库服务，如果可以启动说明配置文件没有错
[root@rocky86-101 ~]# systemctl enable --now mysqld
#停止数据库
[root@rocky86-101 ~]# systemctl stop mysqld;
#删除从库文件,
[root@rocky86-101 ~]# rm -rf /var/lib/mysql/*


-------------------------导入数据------------------------------
#主服务传过来的文件
[root@rocky86-101 ~]# ls
mysql.tar.gz
#解压文件
[root@rocky86-101 ~]# tar xf mysql.tar.gz
#进入解压缩文件夹里的mysql文件夹
[root@rocky86-101 ~]# cd ./var/lib/mysql
#将文件夹里的文件剪切到mysql的data目录
[root@rocky86-101 ~]# mv * /var/lib/mysql/
#删除/var/lib/mysql/auto.cnf   与主服务器的uuid不能一样  启动数据库服务后重新生成server-uuid
[root@rocky86-101 ~]# rm -f /var/lib/mysql/auto.cnf
#启动数据库，此时主从服务器的数据是一样的
[root@rocky86-101 ~]# systemctl start mysqld

2、配置从服务器

#登录数据库
[root@rocky86-101 ~]# mysql -uroot -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-------------------------主服务器需要用到的信息-----------------------------------------------------

# 主服务器ip：10.0.0.100，用户名repluser密码123456二进制日志记录mysql-bin100.000003名记录数据段开始30488946

-------------------------配置从服务器--------------------------------------------------------------
#可查看帮助
mysql> help change master to
CHANGE MASTER TO
  MASTER_HOST='source2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='source2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

#记得修改成对应的信息为自己电脑上的
mysql> change master to 
	master_host='10.0.0.100',
    master_user='repluser',
    master_password='123456',
    master_log_file='mysql-bin100.000002',
    master_log_pos=156,
    master_delay=10;
    
#查看状态
mysql> show slave status\G;
#开启SQL线程和IO线程 Slave_IO_Running=yes、Slave_SQL_Running=yes
mysql> start slave;

（三）主从测试

在主服务器增加数据，在从库中查看是否同步了数据，能查看到数据，主从搭建成功

1、测试工具

模拟大量表记录新增，没有数据库的自己新增数据库

[root@rocky86-100 ~]# ls
hellodb_innodb.sql  mysql.tar.gz  testlog.sql
#此文件可向表里追加10000条数据
[root@rocky86-100 ~]# cat testlog.sql 
create table testlog (id int auto_increment primary key,name char(10),salary int default 20);

delimiter $$
create procedure  sp_testlog() 
begin  
declare i int;
set i = 1; 
while i <= 100000 
do  insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000)); 
set i = i +1; 
end while; 
end$$

delimiter ;

2、主：

[root@rocky86-100 ~]# mysql -uroot -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
#进入数据库
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
#执行，大量表新增
mysql> call sp_testlog;

#另开一个终端，查看所有表
mysql> show tables;
#查看新增的数据
mysql> select count(*) from testlog;
+----------+
| count(*) |
+----------+
|   200000 |
+----------+
1 row in set (0.04 sec)

3、从

[root@rocky86-101 ~]# mysql -uroot -p

mysql> use hellodb;
mysql> select count(*) from testlog;
+----------+
| count(*) |
+----------+
|   177230 |
+----------+

数据正在同步过来…

#查看从状态
mysql> show slave status\G;