1. MySQL在金融互联网行业的企业级安装部署

数哥

已于 2023-04-04 21:09:24 修改

阅读量1.2k

点赞数 1

分类专栏： MySQL性能优化+原理+实战_已完结文章标签： mysql 数据库 sql

于 2023-02-20 17:27:59 首次发布

本文链接：https://blog.csdn.net/wangzhicheng987/article/details/129005574

版权

MySQL性能优化+原理+实战_已完结专栏收录该内容

22 篇文章 12 订阅 ¥9.90 ¥99.00

订阅专栏

1. 版本介绍

安装开发版本还是通用版本（GA）。

开发版本具有最新功能，但生成环境不能使用。
GA版本，也称为生产版本或稳定版本。

MySQL命名方案使用的发行版本名称由三个数字和一个可选的后缀组成。例如 mysql-5.7.1-m1。版本名称中的数字解释如下：

第一个数字（5）是主版本号。
第二个数字（7）是次要版本号。总而言之，主要和次要数字构成发行版本号。
第三个数字（30）是发行系列中的版本号。对于每个新的错误修正版本，此值均递增。在通常最新版本是最佳选择。
里程碑
后缀用于指示版本的稳定性。可能的后缀是：m N（例如， m1， m2， m3，…）表示里程碑编号。
rc表示发布候选（RC）。通过MySQL的所有内部测试后，发布候选版本被认为是稳定的。
没有后缀表示正式版。生产环境中的选型。

源代码发行版比二进制发行版包含更多的测试和示例。

本专栏使用的数据库版本是：

 mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
 mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz

2.安装MySQL规范

2.1 安装方式

MySQL安装方式：二进制安装

2.2 安装用户

MySQL运行用户： mysql:用户建议虚拟用户，只用于mysql进程运行使用，不允许登录、不创建家目录。
useradd -s /sbin/nologin -M mysql

2.3 目录规范

系统目录 /opt/mysql/my-xx.xx
软连接 ln -s /opt/mysql/my-xx.xx /usr/local/mysql
数据目录 /data/mysql/my+port/{data,logs}
配置文件 /data/mysql/my+port/my+port.cnf

3. MySQL 5.7 安装部署

3.1 操作系统配置

[root@sdns ~]# cat /etc/redhat-release 
CentOS Linux release 7.9.2009 (Core)

[root@sdns ~]# systemctl status firewalld.service  // 关闭防火墙
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

[root@sdns ~]# getenforce   // 禁用selinux。 如果不是disabled，敲setenforce 0  / sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/sysconfig/selinux将其禁用
Disabled

3.2 创建用户

创建MySQL虚拟用户

useradd -s /sbin/nologin -M mysql

3.3 创建目录

[root@sdns data]# mkdir -p /data/mysql/my3306
[root@sdns data]# mkdir -p /data/mysql/my3307  //用于后续的多实例案例
[root@sdns data]# mkdir -p /data/mysql//my3306/{data,logs}
[root@sdns data]# mkdir -p /data/mysql//my3307/{data,logs}  //用于后续的多实例案例

更改用户和组
数据目录通常与软件目录分开放，设置对应的用户和组和适当的目录权限：

[root@sdns data]# chown -R mysql.mysql /data/mysql/
[root@sdns data]# chmod -R 750 /data/mysql/

3.4 安装

解压包

[root@sdns ~]# tar -zxvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

[root@sdns ~]# mkdir -p /opt/mysql
[root@sdns ~]# mv mysql-5.7.33-linux-glibc2.12-x86_64 /opt/mysql/

创建软连接

[root@sdns ~]# ln -s /opt/mysql/mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/mysql

删除mariadb

rpm -e --nodeps mariadb-libs

3.5 配置文件

配置文件整理（my.cnf 后面章节详解）

more /data/mysql/my3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

3.6 安装依赖包

安装MySQL依赖包

yum install libaio-devel -y
yum install numactl -y

3.7 配置环境变量

加入环境变量

vi ~/.bash_profile
export PATH="/usr/local/mysql/bin:$PATH"  //顺序性文件添加mysql可执行命令
source ~/.bash_profile

3.8 初始化数据库

初始化数据库

mysqld --defaults-file=/data/mysql/my3306/my3306.cnf --initialize --user=mysql

--defaults-file: 如果参数文件没有在默认的位置，需要手工指定my.cnf的位置
--initialize：相对应的参数是---initialize-insecure。 “ 默认安全 ”安装（即包括生成随机初始的 root密码）。在这种情况下，密码被标记为已过期，您将需要选择一个新密码。使用--initialize-insecure，不会root生成密码。这是不安全的；可以直接登录进入。
--basedir=/usr/local/mysql  可选参数，如果my.cnf当中没有指定路径，需要手工指定,mysql软件位置。
--datadir=/opt/mysql/mysql/data 可选参数，如果my.cnf当中没有指定路径，需要手工指定，mysql数据目录位置。
--user=mysql  数据初始化后，所属的用户

查看密码

[root@sdns logs]# grep -i 'password' /data/mysql/my3306/logs/error.log 
2023-02-20T08:10:56.002819Z 1 [Note] A temporary password is generated for root@localhost: xocqK)rn29jp

启动MySQL

[root@sdns ~]# nohup mysqld --defaults-file=/data/mysql/my3306/my3306.cnf  &

查看是否启动成功

[root@sdns ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1908 mysql   22u  IPv6  30649      0t0  TCP *:mysql (LISTEN)

3.9 重置密码

重置root密码

除了重置初始root密码的动作以外，其它全部默认即可。初始的root密码在错误日志当中。

[root@sdns my3306]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3306/mysql.sock
Enter password: 

Securing the MySQL server deployment.


The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :  

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done!

连接mysql

[root@sdns my3306]# mysql  -uroot -p --socket=/data/mysql/my3306/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql>

4. MySQL8 安装

几乎同mysql 5.7的安装一模一样

创建MySQL虚拟用户

useradd -s /sbin/nologin -M mysql

[root@sdns data]# mkdir -p /data/mysql/my3306
[root@sdns data]# mkdir -p /data/mysql/my3307  //用于后续的多实例案例
[root@sdns data]# mkdir -p /data/mysql//my3306/{data,logs}
[root@sdns data]# mkdir -p /data/mysql//my3307/{data,logs}  //用于后续的多实例案例

更改用户和组
数据目录通常与软件目录分开放，设置对应的用户和组和适当的目录权限：

[root@sdns data]# chown -R mysql.mysql /data/mysql/
[root@sdns data]# chmod -R 750 /data/mysql/

解压包

[root@sdns ~]# xz -d mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
[root@sdns ~]# tar -xf mysql-8.0.27-linux-glibc2.12-x86_64.tar

[root@sdns ~]# mkdir -p /opt/mysql
[root@sdns ~]# mv mysql-8.0.27-linux-glibc2.12-x86_64 /opt/mysql/

创建软连接

[root@sdns ~]# ln -s /opt/mysql/mysql-8.0.27-linux-glibc2.12-x86_64 /usr/local/mysql

删除mariadb

rpm -e --nodeps mariadb-libs

配置文件整理（my.cnf 后面章节详解）

more /data/mysql/my3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_replica_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
binlog_expire_logs_seconds=36000
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

安装MySQL依赖包

yum install libaio-devel -y
yum install numactl -y

加入环境变量

vi ~/.bash_profile
export PATH="/usr/local/mysql/bin:$PATH"  //顺序性文件添加mysql可执行命令
source ~/.bash_profile

初始化数据库

mysqld --defaults-file=/data/mysql/my3306/my3306.cnf --initialize --user=mysql

--defaults-file: 如果参数文件没有在默认的位置，需要手工指定my.cnf的位置
--initialize：相对应的参数是---initialize-insecure。 “ 默认安全 ”安装（即包括生成随机初始的 root密码）。在这种情况下，密码被标记为已过期，您将需要选择一个新密码。使用--initialize-insecure，不会root生成密码。这是不安全的；可以直接登录进入。
--basedir=/usr/local/mysql  可选参数，如果my.cnf当中没有指定路径，需要手工指定,mysql软件位置。
--datadir=/opt/mysql/mysql/data 可选参数，如果my.cnf当中没有指定路径，需要手工指定，mysql数据目录位置。
--user=mysql  数据初始化后，所属的用户

查看密码

[root@sdns logs]# grep -i 'password' /data/mysql/my3306/logs/error.log 
2023-02-20T08:10:56.002819Z 1 [Note] A temporary password is generated for root@localhost: xocqK)rn29jp

启动MySQL

[root@sdns ~]# nohup mysqld --defaults-file=/data/mysql/my3306/my3306.cnf  &

查看是否启动成功

[root@sdns ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1908 mysql   22u  IPv6  30649      0t0  TCP *:mysql (LISTEN)

重置root密码

除了重置初始root密码的动作以外，其它全部默认即可。初始的root密码在错误日志当中。

[root@sdns my3306]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3306/mysql.sock
Enter password: 

Securing the MySQL server deployment.


The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :  

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done!

连接mysql

[root@sdns my3306]# mysql  -uroot -p --socket=/data/mysql/my3306/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql>

5 源码安装（了解）

源码安装的方式时间非常久，不推荐使用这种方式，如果是想了解mysql引擎源码，可以使用这种方式进行安装。

5.1 安装依赖包

安装gcc

[root@node1 cmake-3.18.2]# mount /dev/cdrom /mnt/cdrom/
[root@node1 cmake-3.18.2]# yum install gcc

安装gcc++

[root@node1 cmake-3.18.2]# yum install gcc-c++

安装ssl相关软件

yum install openssl
yum install openssl-devel
yum install ncurses
yum install ncurses-devel
yum install bison
yum install make

安装cmake

[root@node1 cmake-3.18.2]# tar -zxvf cmake-3.18.2.tar.gz 
[root@node1 ~]# cd cmake-3.18.2
[root@node1 cmake-3.18.2]# ./
Auxiliary/ configure  Licenses/  Packaging/ Templates/ Utilities/ 
bootstrap  Help/      Modules/   Source/    Tests/     
[root@node1 cmake-3.18.2]# ./bootstrap 
---------------------------------------------
CMake 3.18.2, Copyright 2000-2020 Kitware, Inc. and Contributors
---------------------------------------------
Error when bootstrapping CMake:
Cannot find appropriate C compiler on this system.
Please specify one using environment variable CC.
See cmake_bootstrap.log for compilers attempted.

---------------------------------------------
Log of errors: /root/cmake-3.18.2/Bootstrap.cmk/cmake_bootstrap.log
---------------------------------------------

gmake
gmake install

5.2 生成源码包

生成源码包

[root@node1 ~]# rpm -ivh mysql-community-5.7.31-1.el7.src.rpm
[root@node1 ~]# ls
anaconda-ks.cfg  mysql-community-5.7.31-1.el7.src.rpm  rpmbuild
[root@node1 ~]# cd rpmbuild/
[root@node1 rpmbuild]# ls
SOURCES  SPECS
[root@node1 rpmbuild]# cd SOURCES/
[root@node1 SOURCES]# ls
boost_1_59_0.tar.bz2  filter-requires.sh   mysql-5.7.31
filter-provides.sh    mysql-5.6.45.tar.gz  mysql-5.7.31.tar.gz

5.3 创建用户

创建用户

useradd -s /sbin/nologin -M mysql

5.4 编译安装

编译安装

[root@node1 mysql-5.7.31]# tar -zxvf mysql-5.7.31.tar.gz

[root@node1 SOURCES]# cd mysql-5.7.31
cp  boost_1_59_0.tar.bz2  ./
tar -jxvf boost_1_59_0.tar.bz2


 cmake ./ -DWITH_BOOST=boost_1_59_0/ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DMYSQL_DATADIR=/usr/local/mysql/data -DINSTALL_MANDIR=/usr/share/man -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DEXTRA_CHARSETS=all -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1

cmake . \
-DWITH_BOOST=boost_1_59_0/ \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \    // 软件位置
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \		//数据目录
-DINSTALL_MANDIR=/usr/share/man \
-DMYSQL_TCP_PORT=3306 \						//端口
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \			// socket文件
-DDEFAULT_CHARSET=utf8 \					// 系统字符集
-DEXTRA_CHARSETS=all \
-DDEFAULT_COLLATION=utf8_general_ci \		// 排序规则，用于是否区分大小写
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1

如果缓存失败，清除缓存

make clean
shell> rm CMakeCache.txt

make (四个小时)

make install

5.5 配置数据库

配置文件整理

more /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket = /tmp/mysql.sock
server_id = 1
port = 3306
log_error=/usr/local/mysql/data/error.log
log_bin=/usr/local/mysql/data/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/usr/local/mysql/data/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

加入环境变量

vi ~/.bash_profile
export PATH="/usr/local/mysql/bin:$PATH"  //顺序性文件添加mysql可执行命令
source ~/.bash_profile

初始化数据库

mysqld --initialize --user=mysql  //使用/etc/my.cnf ，则不需要指定参数文件

查看密码

[root@sdns logs]# grep -i 'password' /usr/local/mysql/data/error.log 
2023-02-20T08:10:56.002819Z 1 [Note] A temporary password is generated for root@localhost: xocqK)rn29jp

启动MySQL

[root@sdns ~]# nohup mysqld & 	    //使用/etc/my.cnf ，则不需要指定参数文件

查看是否启动成功

[root@sdns ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1908 mysql   22u  IPv6  30649      0t0  TCP *:mysql (LISTEN)

重置root密码

除了重置初始root密码的动作以外，其它全部默认即可。初始的root密码在错误日志当中。

mysql_secure_installation -uroot -p   //sock如果在/tmp/mysql.sock 则不需要指定
Enter password: 

Securing the MySQL server deployment.


The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :  

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done!

5.7 连接mysql

连接mysql

mysql  -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql>

6.多实例部署及注意事项

6.1 多实例概念

如果一个系统有多个数据库端口（例如有3306 ，也有3307 ）则这个服务器上是多实例。

MySQL实例构成
由进程+线程+预分配内存组成，mysql是单进程多线程的结构
进程：正在运行的程序
查看mysql进程：ps -ef |grep mysql
杀死mysql进程：kill 进程号、 pkill 进程名称
线程：Master thread、purge thread 、page cleaner thread、io thread
预分配内存：buffer pool、insert buffer、change buffer、double write
什么是多实例
在一台机器上由多个实例（进程+线程+预分配内存）构成
注意事项
系统共用，实例之间具有不同的datadir、server_id、port、日志文件、socket文件
innodb_buffer_pool_size分配的总量要小于物理内存的80%

6.2 多实例安装

创建MySQL虚拟用户

useradd -s /sbin/nologin -M mysql

[root@sdns data]# mkdir -p /data/mysql/my3306
[root@sdns data]# mkdir -p /data/mysql/my3307  //用于后续的多实例案例
[root@sdns data]# mkdir -p /data/mysql//my3306/{data,logs}
[root@sdns data]# mkdir -p /data/mysql//my3307/{data,logs}  //用于后续的多实例案例

更改用户和组
数据目录通常与软件目录分开放，设置对应的用户和组和适当的目录权限：

[root@sdns data]# chown -R mysql.mysql /data/mysql/
[root@sdns data]# chmod -R 750 /data/mysql/

解压包

[root@sdns ~]# tar -zxvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

[root@sdns ~]# mkdir -p /opt/mysql
[root@sdns ~]# mv mysql-5.7.33-linux-glibc2.12-x86_64 /opt/mysql/

创建软连接

[root@sdns ~]# ln -s /opt/mysql/mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/mysql

创建配置文件
实例之间具有不同的datadir、server_id、port、日志文件、socket文件

[root@sdns ~]# more /data/mysql/my3306/my3306.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

more /data/mysql/my3307/my3307.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3307/data
socket = /data/mysql/my3307/mysql.sock
server_id = 2
port = 3307
log_error=/data/mysql/my3307/logs/error.log
log_bin=/data/mysql/my3307/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3307/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

加入环境变量

vi ~/.bash_profile
export PATH="/usr/local/mysql/bin:$PATH"  //顺序性文件添加mysql可执行命令
source ~/.bash_profile

初始化数据库

mysqld --defaults-file=/data/mysql/my3306/my3306.cnf --initialize --user=mysql

mysqld --defaults-file=/data/mysql/my3307/my3307.cnf --initialize --user=mysql

查看密码

[root@sdns logs]# grep -i 'password' /data/mysql/my3306/logs/error.log 
2023-02-20T08:10:56.002819Z 1 [Note] A temporary password is generated for root@localhost: xocqK)rn29jp

[root@sdns ~]# grep -i 'password' /data/mysql/my3307/logs/error.log 
2023-02-20T09:10:33.994071Z 1 [Note] A temporary password is generated for root@localhost: /ydbB6n<cKl?

启动MySQL

[root@sdns ~]# nohup mysqld --defaults-file=/data/mysql/my3306/my3306.cnf  &
[root@sdns ~]# nohup mysqld --defaults-file=/data/mysql/my3307/my3307.cnf  &

查看是否启动成功

[root@sdns ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1908 mysql   22u  IPv6  30649      0t0  TCP *:mysql (LISTEN)

[root@sdns ~]# lsof -i:3307
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  76327 mysql   23u  IPv6 192333      0t0  TCP *:opsession-prxy (LISTEN)

重置root密码

[root@sdns my3306]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3306/mysql.sock
[root@sdns my3306]# mysql_secure_installation -uroot -p --socket=/data/mysql/my3307/mysql.sock

连接

[root@sdns ~]# mysql -uroot -p --socket=/data/mysql/my3307/mysql.sock
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

6.3 mysqld_multi（多实例第二种安装方式）

添加多实例配置文件
[root@sdns ~]# more /etc/my_multi.cnf

[mysqld_multi]  
mysqld     = /usr/local/mysql/bin/mysqld_safe  
mysqladmin = /usr/local/mysql/bin/mysqladmin  
  
[mysqld1]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3306/data
socket = /data/mysql/my3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/my3306/logs/error.log
log_bin=/data/mysql/my3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
[mysqld2]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/my3307/data
socket = /data/mysql/my3307/mysql.sock
server_id = 2
port = 3307
log_error=/data/mysql/my3307/logs/error.log
log_bin=/data/mysql/my3307/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/my3307/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

启动全部实例

[root@sdns ~]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start

[root@sdns ~]# lsof -i:3307
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4200 mysql   34u  IPv6  33157      0t0  TCP *:opsession-prxy (LISTEN)

[root@sdns ~]# lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4210 mysql   25u  IPv6  33872      0t0  TCP *:mysql (LISTEN)

[root@sdns ~]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

启动指定实例

[root@sdns ~]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 2
[root@sdns ~]# lsof -i:3307
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  11329 mysql   15u  IPv6  49106      0t0  TCP *:opsession-prxy (LISTEN)
[root@sdns ~]# lsof -i:3306
[root@sdns ~]# mysqld_multi --defaults-extra-file=/etc/my_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running

数哥

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
1. MySQL在金融互联网行业的企业级安装部署

1. 版本介绍示例2.安装MySQL规范(建议二进制)2.1 安装方式2.2 安装用户2.3 目录规范3.二进制安装3.1 操作系统配置3.2 MySQL 5.7.33 安装部署2.3 MySQL8.0.27安装2.4 源码安装（了解）3.多实例部署及注意事项3.1 多实例概念3.2 多实例安装3.3 多实例第二种方式
复制链接

扫一扫