mysql主从配置

一、环境描述

利用两台虚拟机centos7.6 配置 mysql5.7主从服务器

centos7.6-2 （主服务器master）

ip地址10.0.0.137

远程登录账号llw

远程登录密码llw@2023

centos7.6-3（从服务器slave）

ip地址10.0.0.135

远程登录账号liuliu

远程登录密码liuliu@2023

二、克隆出来的虚拟机无法正常上网

centos7.6-3由centos7.6-2克隆而来（克隆时确保centos7.6-2是关机状态）

克隆出来的centos7.6-3可能无法正常上网，需要先移除配置，然后再重新添加

2.修改克隆机的server-id

克隆出来的centos7.6-3和centos7.6-2的server-uuid是一样的，需要进行更改避免slave-IO-Runing是NO状态

(1)查看server-uuid

方法一:cat /var/lib/mysql/auto.cnf

方法二:show variables like 'server_uuid';

(2)修改server-uuid
1、利用uuid函数生成新的uuid
mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| b33057ff-bec6-11eb-ad94-000c29af6856 |
+--------------------------------------+
1 row in set (0.00 sec)

2、查看配置文件目录
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.03 sec)

3、编辑配置文件目录

vi /var/lib/mysql/auto.cnf

4、uuid修改新生成的uuid【把刚刚生成的uuid复制过来】
server-uuid=b33057ff-bec6-11eb-ad94-000c29af6856

5、重启服务
service mysqld restart

三、配置master和salve的准备工作

1.关闭防火墙或者开启mysql端口

开启mysql端口（推荐）

1.查看mysql服务器的端口号
show variables like 'port';

2.查看防火墙是否开启
systemctl status firewalld
 
显示active说明是开启状态，如果是关闭状态需要开启防火墙
systemctl start firewalld

3.查看开放的端口号
firewall-cmd --list-all 或者 firewall-cmd --list-ports
 
4.设置开放的端口号
firewall-cmd --permanent --add-port=3306/tcp 
 
5.重启防火墙
firewall-cmd --reload


关闭防火墙并且禁用防火墙的开机自启动
systemctl stop firewalld
systemctl disable firewalld

查看mysql，如果不是开启状态需要开启

查看
systemctl status mysqld
开启
systemctl start mysqld

四、配置master

（1）修改配置文件/etc/my.cnf

mysql服务ID，保证整个集群环境中唯一。取值范围1~【（2的32次方）-1】，默认为1

read-only当前服务器的只读状态，“1”只读,"0"读写

vim /etc/my.cnf
server-id=1
read-only=0

（2）重启mysql服务

systemctl restart mysqld

（3）登录mysql，创建远程连接账号，授予主从复制权限

登录mysql
mysql -u用户名 -p密码

创建远程连接账号
create user '创建远程账号名'@'%' identified with mysql_native_password by '设置远程登录密码';

授予主从复制权限
grant replication slave on *.* to '创建远程账号名'@'%';

（4）查看二进制日志坐标

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

如果File和Position为空，解决方法如下

（1）查看log_bin文件是否为on
show variables like 'log_bin';

（2）如果为off，需要开启
vim /etc/my.cnf
log_bin=mysql.bin

（3）重启mysqld
systemctl restart mysqld

五、配置slave

（1）修改配置文件/etc/my.cnf

vim /etc/my.cnf
server-id=2
read-only=1

（2）重启mysql服务

systemctl restart mysqld

（3）登录mysql

mysql8.0.23版之前的

change master to
master_host='主库ip',
master_user='在主库设置的远程登陆账号',
master_password='远程登陆账号对应的密码',
master_log_file='查看主库状态的二进制文件名File'
master_log_pos=查看主库的Position；

mysql8.0.23版本

change replication source to
source_host='主库ip',
source_user='在主库设置的远程登陆账号',
source_password='远程登陆账号对应的密码',
source_log_file='查看主库状态的二进制文件名File'
source_log_pos=查看主库的Position；

（4）启动slave，开始同步操作

8.0.22版本之后
start replica;

8.0.22版本之前
start start;

（5）查看主从同步状态

8.0.22版本之后
start replica status\G;

8.0.22版本之前
start start status\G;

六、mysql主从测试

（1）先关闭从库

stop slave;

（2）创建sql文件夹

cd /root
mkdir sql

（3）利用XFTP软件将模拟数据从windows导入到centos7.6中

（4）创建数据库testdb，创建表tb_sku

mysql> create databases testdb;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql> CREATE TABLE `tb_sku` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
    ->   `sn` varchar(100) NOT NULL COMMENT '商品条码',
    ->   `name` varchar(200) NOT NULL COMMENT 'SKU名称',
    ->   `price` int(20) NOT NULL COMMENT '价格（分）',
    ->   `num` int(10) NOT NULL COMMENT '库存数量',
    ->   `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
    ->   `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
    ->   `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
    ->   `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
    ->   `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    ->   `update_time` datetime DEFAULT NULL COMMENT '更新时间',
    ->   `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
    ->   `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
    ->   `spec` varchar(200) DEFAULT NULL COMMENT '规格',
    ->   `sale_num` int(11) DEFAULT '0' COMMENT '销量',
    ->   `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
    ->   `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
    ->   PRIMARY KEY (`id`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb_sku           |
+------------------+
1 row in set (0.00 sec)

（5）导入sql文件

mysql> load data local infile '/root/sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

mysql> load data local infile '/root/sql/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

也可以使用Navicat软件导入sql文件

（6）打开从库

start slave;

（7）带从库查看数据库，会比之前多一个testdb数据库

show databases;