MySQL--Linux搭建双主/主从集群

MySQL–Linux搭建双主/主从集群

一、前置条件

1.1MySQL安装

1. 下载官方 Mysql 包

yum install wget && wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

2. 安装MySQL包

yum -y install mysql57-community-release-el7-10.noarch.rpm

3. 安装 MySQL

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 && yum -y install mysql-community-server

4. 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

5. 启动 Mysql 服务

systemctl start mysqld.service

6. 查看 Mysql 运行状态

service mysqld status

7. 进入数据库

#查看初始密码
sudo grep 'temporary password' /var/log/mysqld.log
mysql -u root -p
#重置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Wo12345678@';
#修改权限并且允许外部登录
CREATE USER 'root'@'%' IDENTIFIED BY 'Wo12345678@';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

1.2Supervisor

1.安装Supervisor

yum install -y epel-release && yum install -y supervisor

2.配置Supervisor

supervisor的配置文件内容有很多，不过好多都不需要修改就行使用，我这里只修改了以下两项

mkdir /etc/supervisord.d
#修改socket文件的mode，默认是0700
sed -i 's/;chmod=0700/chmod=0766/g' /etc/supervisord.conf   
#在配置文件最后添加以下两行内容来包含/etc/supervisord目录
sed -i '$a [include] \files = /etc/supervisord.d/*.conf' /etc/supervisord.conf

3.Supervisor配置systemctl服务

• 新建service文件

vi  /usr/lib/systemd/system/supervisor.service

• 加入如下内容

[Unit]
Description=supervisor
After=network.target

[Service]
Type=forking
ExecStart=/usr/bin/supervisord -c /etc/supervisord.conf
ExecStop=/usr/bin/supervisorctl $OPTIONS shutdown
ExecReload=/usr/bin/supervisorctl $OPTIONS reload
KillMode=process
Restart=on-failure
RestartSec=42s

[Install]
WantedBy=multi-user.target

4.使用systemctl启动

systemctl daemon-reload
systemctl start supervisor.service
systemctl status supervisor.service

5.自启动

systemctl enable supervisor.service

6.编写Supervisor管理的子进程

想要我们的应用被Supervisor管理，就需要在/etc/supervisord目录下编写配置文件，案例如下：

vi /etc/supervisord.d/mysql.ini
[program:mysql]
command=/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
;directory=  ; 执行前要不要先cd到目录去，一般不用
priority=1                    ;数字越高，优先级越高
numprocs=1                    ; 启动几个进程
autostart=true                ; 随着supervisord的启动而启动
autorestart=true              ; 自动重启。。当然要选上了
startretries=10               ; 启动失败时的最多重试次数
exitcodes=0                   ; 正常退出代码（是说退出代码是这个时就不再重启了吗？待确定）
stopsignal=KILL               ; 用来杀死进程的信号
stopwaitsecs=10               ; 发送SIGKILL前的等待时间
redirect_stderr=true          ; 重定向stderr到stdout
user=mysql
stdout_logfile_maxbytes = 1024MB
stdout_logfile_backups  = 10
stdout_logfile          = /var/run/log/mysql.log

子进程管理

supervisorctl status 子进程名                             #子进程状态
supervisorctl stop 子进程名                               #停止子进程
supervisorctl start 子进程名                            #启动子进程
supervisorctl restart 子进程名                            #重启子进程
supervisorctl reload 子进程名 

7.重启

systemctl daemon-reload
systemctl restart supervisor.service

二、配置集群主从关系

复制四台完成了前置步骤的虚拟机

2.1双主集群

1.IP汇总

10.8.16.125 master1

10.8.16.126 master2

2.修改UUID

/var/lib/mysql/auto.cnf

修改server-uuid

集群中uuid不能一样,下面是生成uuid的脚本（linux直接执行）

uuidgen

3.配置二进制日志

/etc/my.cnf

添加如下配置，server-id不重复（如maser1设置1，matser2设置2）

server-id =1
relay_log = mysql-relay-bin
log_bin = mysql-bin
binlog_format = ROW

4.重启

#安装了supervisor
supervisorctl restart mysql
#未安装
systemctl restart mysqld

5.创建slave账号

master1/2都需要创建

自己设置slave账号的账号和密码，并授权REPLICATION

CREATE USER 'replication_user'@'%' identified by 'Wo195271.';
GRANT REPLICATION SLAVE on *.* TO 'replication_user'@'%'identified by 'Wo195271.';
FLUSH PRIVILEGES;

6.连接主服务器

• master1获取连接参数

#master2执行命令
SHOW MASTER STATUS;

• master1切换主服务器为master2

MASTER_HOST=‘<主服务器IP地址>’,
MASTER_PORT=‘<端口>’,
MASTER_USER=‘<复制用户>’
MASTER_PASSWORD=‘<复制用户密码>’
MASTER_LOG_FILE=‘<主服务器的二进制日志文件>’
MASTER_LOG_POS=‘<主服务器的二进制日志位置>’

#salve执行命令，填入master2查询到的参数
CHANGE MASTER TO MASTER_HOST='10.8.16.126',
MASTER_PORT=3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='Wo195271.',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=350;
START SLAVE;

• master1查看状态

确保输出中的Slave_IO_Running和Slave_SQL_Running都是Yes，这意味着从服务器的复制进程正在运行且正常工作。还要确保输出中的Last_IO_Error和Last_SQL_Error都为空，这表示没有发生错误。

show slave status;

• master2获取连接参数

#master1执行命令
SHOW MASTER STATUS;

• master2切换主服务器为master1

MASTER_HOST=‘<主服务器IP地址>’,
MASTER_PORT=‘<端口>’,
MASTER_USER=‘<复制用户>’
MASTER_PASSWORD=‘<复制用户密码>’
MASTER_LOG_FILE=‘<主服务器的二进制日志文件>’
MASTER_LOG_POS=‘<主服务器的二进制日志位置>’

#salve执行命令，填入master1查询到的参数
CHANGE MASTER TO MASTER_HOST='10.8.16.125',
MASTER_PORT=3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='Wo195271.',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=350;
START SLAVE;

• master2查看状态

确保输出中的Slave_IO_Running和Slave_SQL_Running都是Yes，这意味着从服务器的复制进程正在运行且正常工作。还要确保输出中的Last_IO_Error和Last_SQL_Error都为空，这表示没有发生错误。

show slave status;

2.2主从集群

1.IP汇总

10.8.16.127 master

10.8.16.128 slave

2.修改UUID

/var/lib/mysql/auto.cnf

修改server-uuid

集群中uuid不能一样,下面是生成uuid的脚本（linux直接执行）

uuidgen

3.配置二进制日志

/etc/my.cnf

添加如下配置，server-id不重复（如maser1设置1，matser2设置2）

server-id =1
relay_log = mysql-relay-bin
log_bin = mysql-bin
binlog_format = ROW

4.重启

#安装了supervisor
supervisorctl restart mysql
#未安装
systemctl restart mysqld

5.创建slave账号

master设置slave账号的账号和密码，并授权REPLICATION

CREATE USER 'replication_user'@'%' identified by 'Wo195271.';
GRANT REPLICATION SLAVE on *.* TO 'replication_user'@'%'identified by 'Wo195271.';

6.连接主服务器

• master获取连接参数

#master执行命令
SHOW MASTER STATUS;

• salve切换主服务器为master

MASTER_HOST=‘<主服务器IP地址>’,
MASTER_PORT=‘<端口>’,
MASTER_USER=‘<复制用户>’
MASTER_PASSWORD=‘<复制用户密码>’
MASTER_LOG_FILE=‘<主服务器的二进制日志文件>’
MASTER_LOG_POS=‘<主服务器的二进制日志位置>’

#salve执行命令，填入master查询到的参数
CHANGE MASTER TO MASTER_HOST='10.8.16.127',
MASTER_PORT=3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='Wo195271.',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=350;
START SLAVE;

• slave节点查看状态

确保输出中的Slave_IO_Running和Slave_SQL_Running都是Yes，这意味着从服务器的复制进程正在运行且正常工作。还要确保输出中的Last_IO_Error和Last_SQL_Error都为空，这表示没有发生错误。

show slave status;

三、创建库，创建用户语句，赋权，创建表的几种类型？

MySQL创建库的语句是使用CREATE DATABASE命令，示例如下：

CREATE DATABASE mydatabase;

MySQL创建用户的语句是使用CREATE USER命令，示例如下：

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

MySQL赋权的语句是使用GRANT命令，示例如下：

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';

MySQL创建表的几种类型包括：

1. 普通表 (Table)：用于存储数据的一种基本表结构。
2. 临时表 (Temporary Table)：在当前会话中创建的临时表，会话结束后自动删除。
3. 视图 (View)：基于查询结果的虚拟表，可以简化复杂查询。
4. 分区表 (Partitioned Table)：将表数据按照特定规则分割到多个物理文件中，提高查询性能。
5. 全文索引表 (Full-Text Index Table)：用于支持全文搜索的表格，可以进行高效的文本搜索操作。
6. 内存表 (Memory Table)：将表数据存储在内存中的表格，适用于性能要求较高的场景。
7. 临时表 (Temporary Table)：在当前会话中创建的临时表，会话结束后自动删除。

四、mysql如何提高读写性能？

1. 合理设计数据库结构：合理设计数据库模式、表结构和索引，尽量避免过度规范化和冗余数据，以提高查询效率。
2. 使用适当的数据类型：选择合适的数据类型来存储数据，避免浪费存储空间或造成数据类型转换的额外开销。
3. 优化查询语句：编写高效的SQL查询语句，避免全表扫描和使用过多的关联查询，利用索引辅助查询过程，合理使用JOIN等操作。
4. 创建适当的索引：为经常被查询的列创建索引，可以加快查询速度。
5. 避免使用SELECT *：只选择需要的列，避免不必要的网络传输和数据处理开销。
6. 批量插入和更新：使用批量操作，如LOAD DATA INFILE、INSERT INTO … VALUES、UPDATE … SET … WHERE等语句，减少网络通信次数，提高效率。
7. 合理设置缓存：使用MySQL的查询缓存和InnoDB的缓冲池等机制，加速重复查询的处理。
8. 配置硬件和系统参数：调整数据库服务器的硬件配置和操作系统的参数，如内存、磁盘设置、文件系统等，以优化数据库性能。
9. 使用分区表：对于大型表，可以通过分区技术将数据分散存储到多个物理文件，提高查询和维护效率。
10. 使用主从复制：通过主从复制的方式，将读写分离，让主服务器处理写操作，从服务器处理读操作，提高并发处理能力。
11. 定期优化和维护：定期进行数据库优化和维护工作，如碎片整理、统计信息更新、重建索引等，以保持数据库的最佳性能状态。

五、主流的mysql搭建方式双主，主从的主要区别？底层原理是什么？

1. 双主 (Master-Master)：
   双主配置是指两个MySQL实例相互充当主服务器和从服务器的角色。每个实例都可以处理写操作和读操作，提供了双向的数据复制。双主配置可实现负载均衡和故障切换，但必须注意处理冲突和同步延迟的问题。

2. 主从 (Master-Slave)：
   主从配置是指一个MySQL实例作为主服务器 (Master)，负责处理写操作，而其他一个或多个MySQL实例作为从服务器 (Slave)，复制主服务器的数据。从服务器可以用于读取数据，提供了读写分离和数据冗余的功能。主从配置提供了数据复制和故障切换的功能，但主服务器故障时需要手动进行切换。

3. 底层原理：

   主从复制的底层原理是基于二进制日志 (Binary Log)。当启用二进制日志后，主服务器将所有的写操作记录到二进制日志中，从服务器通过读取主服务器的二进制日志，将其应用到自己的数据上，实现数据的复制。主服务器将写操作信息通过二进制日志传输给从服务器，从服务器接收并应用到自己的数据库。主从复制的过程基于MySQL的同步复制机制或半同步复制机制。在主从复制的过程中，主服务器和从服务器之间通过网络进行数据传输。主服务器将变更记录写入二进制日志，然后从服务器连接到主服务器并请求获取二进制日志，获取完之后应用到从服务器的数据库上。从服务器根据配置可以选择是异步复制还是同步复制，异步复制允许主服务器提交事务后立即返回给客户端，而同步复制需要等待从服务器确认成功才返回。