当前位置：首页 > news >正文

22 mysql数据库主从搭建

news 来源：原创 2024/9/20 13:41:30

上午
1、为mysql添加开机启动chkconfig
[root@mysql1 ~]# chkconfig --list //列出系统服务在不同运行级别下的启动状态
注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。
要列出 systemd 服务，请执行 'systemctl list-unit-files'。
查看在具体 target 启用的服务请执行
'systemctl list-dependencies [target]'。
netconsole 0:关 1:关 2:关 3:关 4:关 5:关 6:关
network 0:关 1:关 2:开 3:开 4:开 5:开 6:关
[root@mysql1 ~]# chkconfig --add mysql8 //将名为 mysql8 的服务添加到系统的服务管理列表中，以便通过 chkconfig 工具来管理它在不同运行级别的启动状态
[root@mysql ~]# chkconfig mysql8 on //设置 mysql8 服务在特定运行级别（或多个运行级别）自动启动
2、编辑配置文件my.cnf
[root@mysql1 ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql/ #指明了 MySQL 的安装根目录
datadir=/usr/local/mysql/data #指明了数据文件的存储目录
socket=/tmp/mysql.sock #定义了 MySQL 服务器用于本地连接的套接字文件路径
[root@mysql1 ~]# service mysql8 restart
[root@mysql1 ~]# /usr/local/mysql/bin/mysql_secure_installation //增强 MySQL 服务器安全性
3、修改环境变量
将mysql的bin也添加到$PATH
[root@mysql1 ~]# sed -i '$a export PATH=/usr/local/mysql/bin/:$PATH' /etc/profile
[root@mysql1 ~]# source /etc/profile
4、mysql角色授权
[root@mysql1 ~]# mysql -uroot -p'Root@123456'
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
4 rows in set (0.01 sec)
mysql> create user 'aaa'@'%' identified by 'aaa';
Query OK, 0 rows affected (0.11 sec)
[root@mysql1 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent //防火墙打开3306端口
success
[root@mysql1 ~]# firewall-cmd --reload //重新加载防火墙
success
mysql> create role a;
Query OK, 0 rows affected (0.06 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | a |
| % | aaa |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
6 rows in set (0.00 sec)
mysql> show grants for a;
+-------------------------------+
| Grants for a@% |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)
mysql> grant all on *.* to a;
Query OK, 0 rows affected (0.02 sec)
mysql> grant a to aaa;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for aaa;
+---------------------------------+
| Grants for aaa@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%` |
| GRANT `a`@`%` TO `aaa`@`%` |
+---------------------------------+
2 rows in set (0.00 sec)

角色不生效

在配置文件中不添加activate_all_roles_on_login=on

glibc安装，my.cnf在项目目录之下

rpm安装，my.cnf文件在/etc/my.cnf

[root@mysql1 ~]# vim /usr/local/mysql/my.cnf
activate_all_roles_on_login=on #在登录时激活所有的角色
[root@mysql1 ~]# service mysql8 restart
Shutting down MySQL... SUCCESS!
Starting MySQL........ SUCCESS!

5、自动安装mysql脚本（主）
#!/bin/bash
yum list installed |grep libaio #在已安装的软件包列表中搜索包含 libaio 字样的软件包
if [ $? -ne 0 ]; then
yum -y install libaio #如果没有安装这个软件包就安装这个软件
fi
echo libaio yes
rm -rf /etc/my.cnf #删除/etc/my.cnf文件，后续要创建到项目中（/usr/local/mysql/）
echo remo my.cnf yes

#tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar
#echo tar yes
tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz #解压软件包
echo tar xz yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql #将root家目录下的已经解压过的软件包下的内容复制到/usr/local/mysql目录下
echo copy file to /usr/local/mysql yes

mkdir /usr/local/mysql/mysql-files #在/usr/local/mysql目录下创建mysql-files目录
echo mysql-files yes

grep mysql /etc/passwd #查看是否有mysql用户
useradd -r -s /sbin/nologin mysql #创建mysql用户（-r 选项表示创建一个系统用户（通常系统用户的 UID 范围在 1 到 499 之间））

chown mysql:mysql /usr/local/mysql/mysql-files #使mysql-files目录的所属主和所属组都为mysql
chmod 750 /usr/local/mysql/mysql-files #设置mysql-files目录的权限为750

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ #初始化 MySQL 数据库服务器
/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data #为 MySQL 服务器设置 SSL（Secure Sockets Layer，安全套接层）和 RSA（Rivest–Shamir–Adleman，一种非对称加密算法）相关的配置
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8 #将/usr/local/mysql/support-files/mysql.server文件复制到/etc/init.d/目录下并改名为mysql8，方便后续启服务

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile #修改环境变量，方便后续进入mysql
source /etc/profile #激活配置
--initialize 选项会执行初始化操作，包括生成随机的初始密码、创建必要的数据库结构等。

--user=mysql 指定以 mysql 用户来运行数据库服务。

--basedir=/usr/local/mysql/ 指明了 MySQL 的安装根目录。

执行此命令后，您可以在输出中找到生成的初始密码，请注意记录好这个密码，以便后续登录和修改密码使用。

--datadir=/usr/local/mysql/data 选项指定了 MySQL 数据文件的存储目录。

执行此命令可以帮助确保 MySQL 服务器在通信过程中的安全性，通过使用 SSL 加密连接来保护数据传输。

6、数据库时间同步
[root@slave-mysql ~]# yum -y install ntpdate.x86_64
[root@slave-mysql ~]# ntpdate cn.ntp.org.cn
6 Aug 11:21:15 ntpdate[1165]: adjust time server 203.107.6.88 offset 0.000143 sec
[root@mysql ~]# yum -y install ntpdate.x86_64
[root@mysql ~]# ntpdate cn.ntp.org.cn
6 Aug 11:21:15 ntpdate[1165]: adjust time server 203.107.6.88 offset 0.000143 sec
下午
环境准备：主从服务器关闭防火墙和selinux，时间同步

1、使用脚本重新搭建主服务器
[root@mysql ~]# sh mysql.sh
[root@mysql ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
activate_all_roles_on_login=on
port=3306 #指定了 MySQL 服务器监听的端口为 3306，这是 MySQL 的默认端口，客户端连接时将使用这个端口进行通信
log-error=/usr/local/mysql/data/db01-master.err #定义了错误日志的存储路径为 /usr/local/mysql/data/db01-master.err，服务器运行过程中的错误信息将被记录到这个文件中
log-bin=/usr/local/mysql/data/binlog #启用了二进制日志，并指定其存储位置为 /usr/local/mysql/data/binlog，二进制日志可用于数据备份、主从复制等场景
server-id=10 #为服务器分配了一个唯一的标识符 10，在主从复制架构中用于区分不同的服务器
character_set_server=utf8mb4 #设置了服务器的默认字符集为 utf8mb4，确保在处理数据时使用这种更广泛支持 Unicode 字符的字符集
[root@mysql ~]# service mysql8 restart
Shutting down MySQL. SUCCESS!
Starting MySQL... SUCCESS!
[root@mysql-zhu ~]# mysql -uroot -p'y-Hqum4J)X!='
mysql> alter user 'root'@'localhost' identified by '123'; //修改root密码
Query OK, 0 rows affected (0.07 sec)
2、使用脚本重新搭建从服务器
[root@mysql-slave ~]# vim mysql.sh
#!/bin/bash
yum list installed |grep libaio
if [ $? -ne 0 ]; then
yum -y install libaio
fi
echo libaio yes
rm -rf /etc/my.cnf
echo remo my.cnf yes
#tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar
#echo tar yes
tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
echo tar xz yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
echo copy file to /usr/local/mysql yes

mkdir /usr/local/mysql/mysql-files
echo mysql-files yes

grep mysql /etc/passwd
useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files
chmod 750 /usr/local/mysql/mysql-files

#/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ 从服务器不初始化mysql
#/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data 也就没有data目录
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
source /etc/profile
[root@mysql-slave ~]# source mysql.sh
[root@mysql-slave ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3310 #指定了 MySQL 服务器监听的端口为 3310
log-error=/usr/local/mysql/data/db01-slave.err
relay-log=/usr/local/mysql/data/relaylog
server-id=11 #为服务器分配了一个唯一的标识符 11，在主从复制架构中用于区分不同的服务器
character_set_server=utf8mb4
3、数据同步
[root@mysql-zhu ~]# service mysql8 stop //停止主mysql服务
Shutting down MySQL. SUCCESS!
[root@mysql-zhu ~]# ls /usr/local/mysql/data/
auto.cnf //主要用于存储一些服务器自动生成的配置信息，其中可能包括服务器的 UUID（Universally Unique Identifier，通用唯一标识符）等重要的标识信息
[root@mysql-zhu ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@mysql-zhu ~]# yum -y install rsync //下载同步软件
[root@mysql-slave ~]# yum -y install rsync //下载同步软件
[root@mysql-zhu ~]# rsync -av /usr/local/mysql/data root@10.0.0.60:/usr/local/mysql/ //将主中的data目录同步到从中
[root@mysql-slave ~]# ls /usr/local/mysql/data/
binlog.000001 client-key.pem #innodb_redo public_key.pem
binlog.000002 db01-master.err #innodb_temp server-cert.pem
binlog.index #ib_16384_0.dblwr mysql server-key.pem
ca-key.pem #ib_16384_1.dblwr mysql.ibd sys
ca.pem ib_buffer_pool performance_schema undo_001
client-cert.pem ibdata1 private_key.pem undo_002
[root@mysql-slave ~]# service mysql8 start //启动从的mysql服务
Starting MySQL.... SUCCESS!
[root@mysql-slave ~]# mysql -P3310 -p123 //可以登录到从的mysql中了
4、主从复制实现
replication slave：指定授予的权限类型为复制从服务器的权限

[root@mysql-zhu ~]# service mysql8 start //启动主的mysql服务
Starting MySQL.... SUCCESS!
[root@mysql-zhu ~]# mysql -uroot -p'123' //登录到主的mysql中
mysql> create user 'slave'@'%' identified by '123'; //创建一个slave用户，%表示在每个主机上都可以使用slave登录到该主机上的mysql
Query OK, 0 rows affected (0.06 sec)
mysql> grant replication slave on *.* to 'slave'@'%'; //将所有的数据库的所有表的replication slave操作权限给到slave用户
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges; //重新加载权限表
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock; //刷新所有表并施加一个全局读锁
Query OK, 0 rows affected (0.00 sec)
mysql> create database if not exists abc charset utf8;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> show master status; //显示主服务器当前二进制日志的状态信息,
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 1067 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
File：当前正在写入的二进制日志文件的名称。
Position：当前写入的位置（偏移量）在二进制日志文件中的位置。
[root@mysql-slave ~]# mysql -uslave -p123 -h10.0.0.51 -P3306 --get-server-public-key //用于以指定的用户 slave 和密码 123 连接到主机地址为 10.0.0.51 、端口为 3306 的 MySQL 服务器，并获取服务器的公共密钥
[root@mysql-slave ~]# mysql -P3310 -p123 //登录到从的数据库中
mysql> change master to
-> master_host='10.0.0.51', //指定主服务器的主机地址
-> master_user='slave', //指定用于连接主服务器进行复制的用户名
-> master_password='123', //指定对应的密码
-> master_port=3306, //指定主服务器的端口
-> master_log_file='binlog.000003', //指定从哪个二进制日志文件开始复制
-> master_log_pos=1067; //指定在指定的二进制日志文件中的起始位置
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave; //启动从服务器的复制进程
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show slave status\G //查看从服务器的复制状态详细信息

5、同步测试
主服务器

mysql> unlock tables; //释放之前通过 LOCK TABLES 命令施加的表锁
Query OK, 0 rows affected (0.00 sec)
mysql> create database if not exists test charset utf8mb4; //创建数据库test
Query OK, 1 row affected (0.02 sec)
mysql> use test; //使用test数据库
Database changed
mysql> create table user(id int primary key,username varchar(45) not null,passwordvarchar(45) not null); //创建user表
Query OK, 0 rows affected (0.03 sec)
mysql> insert into user values(1,'zhangsan','abc'); //向user表中插入数据
Query OK, 1 row affected (0.03 sec)
从服务器