linux-MYSQL 配置

Theshaye

已于 2022-09-12 15:57:58 修改

阅读量2.9k

点赞数

分类专栏： linux系统文章标签： mysql linux centos

于 2022-09-12 15:45:54 首次发布

本文链接：https://blog.csdn.net/m0_71514530/article/details/126817891

版权

linux系统专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一，mysql安装

基础操作

- DDL
- DML
- DCL
- 查询

运维：

分片mycat
- 垂直
- 水平
读写分离
主从辅助
MHA

关系型数据库

下载地址

官网不解释
http://mirrors.sohu.com/mysql/

下载5.6

安装

安装方式

rpm包安装

yum安装

源码编译安装

1.获取rpm包安装MySQL

需要手动解决依赖：

4个软件包
mysql-client
mysql-devel
mysql-server
mysql-shared

地址：wget下载安装即可
http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-shared-5.6.49-1.el7.x86_64.rpm
http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-devel-5.6.49-1.el7.x86_64.rpm
http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-server-5.6.49-1.el7.x86_64.rpm
http://mirrors.sohu.com/mysql/MySQL-5.6/MySQL-client-5.6.49-1.el7.x86_64.rpm

2. yum安装

最简单,但生产环境基本不用：

[root@mysql ~]# systemctl stop firewalld
[root@mysql ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@mysql ~]# sed -i s@SELINUX=enforcing@SELINUX=disabled@ /etc/selinux/config
[root@mysql ~]# setenforce 0
[root@mysql ~]# getenforce 
Permissive
[root@mysql ~]# yum install mariadb-server mariadb -y
[root@mysql ~]# systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

3.编译安装

源码编译三部曲

1.获取mysql源码包，解压缩，源码目录下有个makefile

2.进入mysql源码目录下，执行make命令，自动读取makefile

3.在make编译完成之后，执行make install，安装mysql到制定位置

需要提前安装gcc

3.1获取源码包

wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.49.tar.gz

编译安装一般要求2颗CPU，4G内存

[root@mysql ~]# free -h
total used free shared buff/cache available
Mem: 1.8G 211M 944M 9.5M 664M 1.4G
Swap: 2.0G 0B 2.0G

[root@mysql ~]# cat /proc/cpuinfo #看cpu

3.2准备环境

前提安装编译环境

[root@mysql ~]# yum install ncurses-devel livaio-devel gcc make cmake openssl-devel

[root@mysql ~]# rpm -qa libaio ncurses
ncurses-5.9-14.20130511.el7_4.x86_64
libaio-0.3.109-13.el7.x86_64

本次使用cmake安装，因此：

[root@mysql ~]# yum install cmake

创建mysql用户，用于授权目录

[root@mysql ~]# useradd -s /sbin/nologin -M mysql

-s指定shell -M不自动创建用户目录

[root@mysql ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

3.3 安装

解压缩：

[root@mysql ~]# ls
anaconda-ks.cfg  mysql-5.6.49.tar.gz
[root@mysql ~]# tar zxvf mysql-5.6.49.tar.gz
[root@mysql ~]# tar zxvf mysql-5.6.49.tar.gz^C
[root@mysql ~]# ls
anaconda-ks.cfg  mysql-5.6.49  mysql-5.6.49.tar.gz
[root@mysql ~]# cd mysql-5.6.49
[root@mysql mysql-5.6.49]# pwd
/root/mysql-5.6.49

执行cmake，生成makefile：

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.49 \
-DMYSQL_DATADIR=/application/mysql-5.6.49/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.49/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \   #这里导致出错、应改成-DWITH_SSL=system
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

-DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.49 设定mysql安装目录
-DMYSQL_DATADIR=/application/mysql-5.6.49/data 设定mysql数据文件目录
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.49/tmp/musql.sock 设定mysql.sock路径
-DDEFAULT_CHARSET=utf8 设定默认字符集为utf8
-DDEFAULT_COLLATION=utf8_general_ci 设定默认排序规则

-DEXTRA_CHARSET=gbk,gb2312,utf8,ascii 启用额外的字符集类型

-DWITH_EXTRA_CHARSETS=all
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ 启用引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ 禁用引擎
-DWITH_ZLIB=bundled \ 启用zlib库支持
-DWITH_SSL=bundled
-DENABLED_LOCAL_INFILE=1 \ 启用本地数据导入支持
-DWITH_EMBEDDED_SERVER=1 \ 编译嵌入式服务器支持
-DENABLE_DOWNLOADS=1
-DWITH_DEBUG=0 禁用debug

cmake .   -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.49 \
-DMYSQL_DATADIR=/application/mysql-5.6.49/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.49/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=system \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

此时查看目录，出现makefile：

[root@mysql mysql-5.6.49]# ls
BUILD                CMakeLists.txt           CTestTestfile.cmake  info_macros.cmake  make_dist.cmake  packaging         sql            tests        zlib
client               cmd-line-utils           dbug                 INSTALL            Makefile         plugin            sql-bench      unittest
cmake                config.h.cmake           Docs                 libmysql           man              README            sql-common     VERSION
CMakeCache.txt       configure.cmake          Doxyfile-perfschema  libmysqld          mysql-test       regex             storage        VERSION.dep
CMakeFiles           CPackConfig.cmake        extra                libservices        mysys            scripts           strings        vio
cmake_install.cmake  CPackSourceConfig.cmake  include              LICENSE            mysys_ssl        source_downloads  support-files  win

执行make命令，直至进度为100%：（这个耗时比较长）

[root@mysql mysql-5.6.49]# make
[100%] Built target my_safe_process

执行make install 安装：

[root@mysql mysql-5.6.49]# make install

验证：

安装完成后，设定的路径生成相关目录：
[root@mysql mysql-5.6.49]# ls /application/
mysql-5.6.49
[root@mysql mysql-5.6.49]# cd /application/mysql-5.6.49/
[root@mysql mysql-5.6.49]# ls
bin  data  docs  include  lib  LICENSE  man  mysql-test  README  scripts  share  sql-bench  support-files

bin:二进制程序 	data:数据 	docs：帮助文档

为了方便以后调用（升级维护等），为目录创建一个软链接：
[root@mysql application]# ln -s /application/mysql-5.6.49/   /application/mysql
[root@mysql application]# ls
mysql  mysql-5.6.49
[root@mysql application]# ll
total 0
lrwxrwxrwx.  1 root root  26 Aug 21 04:24 mysql -> /application/mysql-5.6.49/
drwxr-xr-x. 13 root root 191 Aug 21 04:19 mysql-5.6.49

二.MySQL数据初始化操作

1.找到脚本路径

[root@mysql application]# cd /application/mysql/scripts/
[root@mysql scripts]# ls
mysql_install_db

此文件可以用于数据初始化。
同时观察数据目录，发现几乎没有什么东西：
[root@mysql scripts]# ls /application/mysql/data/  -R
/application/mysql/data/:
test

/application/mysql/data/test:
db.opt
必须要进行初始化之后才会产生数据，才能使用MySQL

2.初始化

[root@mysql scripts]# /application/mysql/scripts/mysql_install_db  --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql

出现两个OK，表示完成。

再看data目录：
[root@mysql scripts]# ls /application/mysql/data/
ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

yum 安装mysql5.7


cd /opt

wget  https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm

yum install    mysql80-community-release-el7-7.noarch.rpm   -y

yum installl yum-utils -y

yum-config-manager --disable mysql80-community

yum-config-manager --enable mysql57-community

yum install mysql-community-server   -y

3.启动MySQL

安装时有自带的脚本，这里直接利用。

[root@mysql ~]# ls /application/mysql/support-files/
 binary-configure  magic  my-default.cnf  mysqld_multi.server  mysql-log-rotate  mysql.server

mysql.server即配置脚本

备份、去除注释和空行：

[root@mysql ~]# cp mysql.server mysql.server.bak
[root@mysql ~]# grep -v '^\s*#' mysql.server|grep -v '^$' >111
[root@mysql ~]# ls /application/mysql/support-files/
111  binary-configure  magic  my-default.cnf  mysqld_multi.server  mysql-log-rotate  mysql.server  mysql.server.bak

将文件拷贝到系统自带的脚本目录并授权：

[root@mysql ~]# cp /application/mysql/support-files/111 /etc/init.d/mysqld
[root@mysql ~]# chmod 700 /etc/init.d/mysqld
[root@mysql ~]# ll /etc/init.d/mysqld 
-rwx------. 1 root root 6675 Aug 21 04:55 /etc/init.d/mysqld

启动MySQL（注意排错）：

启动需要的配置文件准备：将初始化时自动生成的配置文件拷贝一下即可:
[root@mysql ~]# cp /application/mysql/support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? yes

[root@mysql ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/application/mysql-5.6.49/data/mysql.err'.
220821 05:05:55 mysqld_safe Directory '/application/mysql-5.6.49/tmp' for UNIX socket file don't exists.
 ERROR! The server quit without updating PID file (/application/mysql-5.6.49/data/mysql.pid).
提示没有/application/mysql-5.6.49/tmp目录，创建目录后再次执行：
[root@mysql ~]# mkdir /application/mysql/tmp
[root@mysql ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/application/mysql-5.6.49/data/mysql.err'.
.... ERROR! The server quit without updating PID file (/application/mysql-5.6.49/data/mysql.pid).
依然出错，是因为权限的问题：
[root@mysql ~]# ll /application/mysql/
total 248
drwxr-xr-x.  2 root  root    4096 Aug 21 04:19 bin
drwxr-xr-x.  5 mysql mysql    143 Aug 21 05:06 data		#这个目录归属不一样
drwxr-xr-x.  2 root  root      55 Aug 21 04:19 docs
drwxr-xr-x.  3 root  root    4096 Aug 21 04:19 include
drwxr-xr-x.  3 root  root    4096 Aug 21 04:19 lib
-rw-r--r--.  1 root  root  219891 Jun  2  2020 LICENSE
drwxr-xr-x.  4 root  root      30 Aug 21 04:19 man
-rw-r--r--.  1 root  root     943 Aug 21 04:35 my.cnf
drwxr-xr-x. 10 root  root    4096 Aug 21 04:19 mysql-test
-rw-r--r--.  1 root  root     587 Jun  2  2020 README
drwxr-xr-x.  2 root  root      30 Aug 21 04:19 scripts
drwxr-xr-x. 28 root  root    4096 Aug 21 04:19 share
drwxr-xr-x.  4 root  root    4096 Aug 21 04:19 sql-bench
drwxr-xr-x.  2 root  root     171 Aug 21 04:50 support-files
drwxr-xr-x.  2 root  root       6 Aug 21 05:06 tmp

那么继续解决:
[root@mysql ~]# chown -R mysql.mysql /application/mysql/
[root@mysql ~]# ll /application/mysql/
total 248
drwxr-xr-x.  2 mysql mysql   4096 Aug 21 04:19 bin
drwxr-xr-x.  5 mysql mysql    143 Aug 21 05:06 data
drwxr-xr-x.  2 mysql mysql     55 Aug 21 04:19 docs
drwxr-xr-x.  3 mysql mysql   4096 Aug 21 04:19 include
drwxr-xr-x.  3 mysql mysql   4096 Aug 21 04:19 lib
-rw-r--r--.  1 mysql mysql 219891 Jun  2  2020 LICENSE
drwxr-xr-x.  4 mysql mysql     30 Aug 21 04:19 man
-rw-r--r--.  1 mysql mysql    943 Aug 21 04:35 my.cnf
drwxr-xr-x. 10 mysql mysql   4096 Aug 21 04:19 mysql-test
-rw-r--r--.  1 mysql mysql    587 Jun  2  2020 README
drwxr-xr-x.  2 mysql mysql     30 Aug 21 04:19 scripts
drwxr-xr-x. 28 mysql mysql   4096 Aug 21 04:19 share
drwxr-xr-x.  4 mysql mysql   4096 Aug 21 04:19 sql-bench
drwxr-xr-x.  2 mysql mysql    171 Aug 21 04:50 support-files
drwxr-xr-x.  2 mysql mysql      6 Aug 21 05:06 tmp

再启动：
[root@mysql ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@mysql ~]# netstat -tunlp |grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      48128/mysqld

三.基本操作

1.登录MySQL数据库

方才MySQL服务端（mysql-server）已经正确启动，需要使用客户端登录，进行数据的增删改查。```

[root@mysql ~]# mysql -uroot -p
-bash: mysql: command not found
缺乏path变量修改:
在/etc/profile最下方加入：
export PATH=/application/mysql/bin:$PATH 

[root@mysql docs]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH

source一下
[root@mysql docs]# source /etc/profile
[root@mysql docs]# mysql -uroot -p
Enter password: 

可以输入密码了
默认空密码，直接回车进入数据库

2.数据库操作(简单sql语句使用)

显示数据库：

show databases;注意“;”结束

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.31 sec)
这里有4个数据库

删除数据库：

mysql> drop database test;
Query OK, 0 rows affected (0.30 sec)

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

四.MySQL安全配置

没有密码不安全，首先要设置密码：

首先退出mysql：
mysql> exit;
Bye

Linux中mysqladmin命令，用来修改数据库信息
[root@mysql docs]# mysqladmin -uroot -p password		#-p提示输入密码
Enter password: 			#原密码，这里为空，直接回车
New password: 				#新密码
Confirm new password: 		#重复新密码

用户root
新密码123456
[root@mysql docs]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.49 Source 
······
mysql>

五，案例：多实例部署方式

基于一个MySQL应用程序

初始化3次，生成三个独立的MySQL数据目录、3个配置文件、3个启动程序，即生成三个实例。

第一步：二进制安装准备

新玩法：源码已经被编译过，下载解压后，可以直接在对应的系统平台上运行。包大，但使用简单。

1.下载二进制包

二进制包下载地址，这一次用阿里的镜像：
[root@mysql ~]# wget https://mirrors.aliyun.com/mysql/MySQL-5.6/mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz?spm=a2c6h.25603864.0.0.b80c6944SOFEKj


[root@mysql ~]# ls
anaconda-ks.cfg  mysql-5.6.51  mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz?spm=a2c6h.25603864.0.0.d3ec69441701Xl  mysql-5.6.51.tar.gz
[root@mysql ~]# mv mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz?spm=a2c6h.25603864.0.0.d3ec69441701Xl mysql-5.6.51-linux-glibc2.12-x86_64.tar
[root@mysql ~]# ls
anaconda-ks.cfg  mysql-5.6.51  mysql-5.6.51-linux-glibc2.12-x86_64.tar  mysql-5.6.51.tar.gz

2.安装依赖

[root@mysql ~]# yum install -y ncurses-devel libaio-devel gcc make cmake

3.环境清理

清空MySQL有关的PATH：

[root@mysql ~]# vim /etc/profile
``````
#export PATH=/application/mysql/bin:$PATH

退出当前shell重新登录一次，再查看PATH，已经清除了
[root@mysql ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

停止当前MySQL的运行：

[root@mysql ~]# netstat -tunlp |grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      16529/mysqld      

[root@mysql ~]# /etc/init.d/mysqld stop
Shutting down MySQL.... SUCCESS!

4.创建MySQL用户

useradd -s /sbin/nologin -M mysql 

由于之前已经创建过，可以直接使用：
[root@mysql ~]# id mysql 
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

5.准备好多实例的数据目录

[root@mysql ~]# mkdir -p /my_mysql/{3306,3307}
[root@mysql ~]# tree /my_mysql/
/my_mysql/
├── 3306
└── 3307

6.解压二进制包

[root@mysql ~]# tar zxvf mysql-5.6.51-linux-glibc2.12-x86_64.tar -C /application/
-C 指定解压目录
[root@mysql ~]# ls /application/
mysql  mysql-5.6.51  mysql-5.6.51-linux-glibc2.12-x86_64

7.准备二进制MySQL运行所需环境

解压完之后，可以看到所有编译过的文件：

[root@mysql ~]# ls /application/mysql-5.6.51-linux-glibc2.12-x86_64/
bin  data  docs  include  lib  LICENSE  man  mysql-test  README  scripts  share  sql-bench  support-files
[root@mysql ~]# ls /application/mysql-5.6.51-linux-glibc2.12-x86_64/bin/
innochecksum       mysql             mysql_client_test           mysqld_multi          mysqlhotcopy               mysqltest            replace
msql2mysql         mysqlaccess       mysql_client_test_embedded  mysqld_safe           mysqlimport                mysqltest_embedded   resolveip
myisamchk          mysqlaccess.conf  mysql_config                mysqldump             mysql_plugin               mysql_tzinfo_to_sql  resolve_stack_dump
myisam_ftdump      mysqladmin        mysql_config_editor         mysqldumpslow         mysql_secure_installation  mysql_upgrade
myisamlog          mysqlbinlog       mysql_convert_table_format  mysql_embedded        mysql_setpermission        mysql_waitpid
myisampack         mysqlbug          mysqld                      mysql_find_rows       mysqlshow                  mysql_zap
my_print_defaults  mysqlcheck        mysqld-debug                mysql_fix_extensions  mysqlslap                  perror

直接使用即可，二进制安装确实方便。

环境准备：

各个配置文件
各个启动脚本
数据初始化，生成初始化data数据

8.安装多个实例

1.准备多个实例的配置文件

准备3306的配置文件：

[root@mysql ~]# vim /my_mysql/3306/my.cnf
[client]


[mysqld]
port=3306
socket=/my_mysql/3306/mysql.sock
basedir=/application/mysql-5.6.51-linux-glibc2.12-x86_64/
datadir=/my_mysql/3306/data/
log-bin=/my_mysql/3306/mysql-bin
server-id=3306

[mysqld_safe]
log-error=/my_mysql/3306/mysql_3306_error.log
pid-file=/my_mysql/3306/mysqld_3306.pid

准备3307的配置文件：

[root@mysql 3306]# vim /my_mysql/3307/my.cnf 

[client]


[mysqld]
port=3307
socket=/my_mysql/3307/mysql.sock
basedir=/application/mysql-5.6.51-linux-glibc2.12-x86_64/
datadir=/my_mysql/3307/data/
log-bin=/my_mysql/3307/mysql-bin
server-id=3307

[mysqld_safe]
log-error=/my_mysql/3307/mysql_3307_error.log
pid-file=/my_mysql/3307/mysqld_3307.pid

2.准备MySQL启停脚本

因为二进制包中的安装脚本都是默认路径（与源码安装根据环境生成的不一样），所以不能直接使用了，最好自己配置脚本容易一些：

[root@mysql 3306]# vim /my_mysql/3306/mysql_3306

port=3306
mysql_user="mysql"
Cmdpath="/application/mysql-5.6.51-linux-glibc2.12-x86_64/bin"
mysql_sock="/my_mysql/3306/mysql.sock"
mysqld_pid_file_path="/my_mysql/3306/mysqld_3306.pid"

start(){
if [ ! -e "$mysql_sock" ];then
    printf "Starting MySQL...\n"
    /bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/my_mysql/3306/my.cnf --pid-file=/my_mysql/3306/mysqld_3306.pid 2>&1 > /dev/null &
    sleep 3
else
    printf "MySQL is running...\n"
    exit 1
fi
}

stop(){
if [ ! -e "$mysql_sock" ];then
    printf "MySQL is stopped...\n"
    exit 1
else
    printf "Stoping MySQL...\n"
    mysqld_pid=`cat "$mysqld_pid_file_path"`
    if (kill -0 $mysqld_pid 2>/dev/null)
        then
        kill $mysqld_pid
        sleep 2
    fi
fi
}

restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}

case "$1" in
start)
    start
;;
stop)
    stop
;;
restart)
    restart
;;
*)
    printf "Usage: /my_mysql/3306/mysqld_3306{start|stop|restart}\n"
esac

对于3307的，只需要修改文件目录及端口就行。

[root@mysql 3306]# vim /my_mysql/3307/mysql_3307

port=3307
mysql_user="mysql"
Cmdpath="/application/mysql-5.6.51-linux-glibc2.12-x86_64/bin"
mysql_sock="/my_mysql/3307/mysql.sock"
mysqld_pid_file_path="/my_mysql/3307/mysqld_3307.pid"

start(){
if [ ! -e "$mysql_sock" ];then
    printf "Starting MySQL...\n"
    /bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/my_mysql/3307/my.cnf --pid-file=/my_mysql/3307/mysqld_3307.pid 2>&1 > /dev/null &
    sleep 3
else
    printf "MySQL is running...\n"
    exit 1
fi
}

stop(){
if [ ! -e "$mysql_sock" ];then
    printf "MySQL is stopped...\n"
    exit 1
else
    printf "Stoping MySQL...\n"
    mysqld_pid=`cat "$mysqld_pid_file_path"`
    if (kill -0 $mysqld_pid 2>/dev/null)
        then
        kill $mysqld_pid
        sleep 2
    fi
fi
}
restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}

case "$1" in
start)
    start
;;
stop)
    stop
;;
restart)
    restart
;;
*)
    printf "Usage: /my_mysql/3307/mysqld_3307{start|stop|restart}\n"
esac

为脚本添加执行权限：

[root@mysql 3306]# chmod +x /my_mysql/3306/mysql_3306 
[root@mysql 3306]# chmod +x /my_mysql/3307/mysql_3307

3.用户组授权

[root@mysql ~]# ll /my_mysql/
total 0
drwxr-xr-x 2 root root 57 Aug 22 00:46 3306
drwxr-xr-x 2 root root 38 Aug 22 00:46 3307

[root@mysql ~]# chown -R mysql.mysql /my_mysql/
[root@mysql ~]# ll /my_mysql/
total 0
drwxr-xr-x 2 mysql mysql 57 Aug 22 00:46 3306
drwxr-xr-x 2 mysql mysql 38 Aug 22 00:46 330

4.PATH配置

[root@mysql ~]# vim /etc/profile
.......
export PATH=/application/mysql-5.6.51-linux-glibc2.12-x86_64/bin:$PATH

保存退出

logout
重进
[root@mysql ~]# echo $PATH
/application/mysql-5.6.51-linux-glibc2.12-x86_64/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@mysql ~]# which mysql
/application/mysql-5.6.51-linux-glibc2.12-x86_64/bin/mysql

5.创建多个实例对应的数据目录

[root@mysql ~]# mkdir -p /my_mysql/3306/data
[root@mysql ~]# mkdir -p /my_mysql/3307/data
[root@mysql ~]# tree /my_mysql/
/my_mysql/
├── 3306
│   ├── data
│   ├── my.cnf
│   └── mysql_3306
└── 3307
    ├── data
    ├── my.cnf
    └── mysql_3307

4 directories, 4 files

6.多实例初始化

初始化3306

此时3306下data目录是空的：
[root@mysql ~]# ls /my_mysql/3306/data/

执行初始化：
[root@mysql ~]# /application/mysql-5.6.51-linux-glibc2.12-x86_64/scripts/mysql_install_db --defaults-file=/my_mysql/3306/my.cnf --basedir=/application/mysql-5.6.51-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3306/data/ --user=mysql

完毕之后再查看
[root@mysql ~]# ls /my_mysql/3306/data/
ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

再初始化3307

[root@mysql ~]# /application/mysql-5.6.51-linux-glibc2.12-x86_64/scripts/mysql_install_db --defaults-file=/my_mysql/3307/my.cnf --basedir=/application/mysql-5.6.51-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3307/data/ --user=mysql


[root@mysql ~]# ls /my_mysql/3307/data/
ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

7.分别启动多实例

环境检查，看看有没有mysql在执行

[root@mysql ~]# netstat -tunlp |grep mysql

空的

启动3306数据库

[root@mysql ~]# /my_mysql/3306/mysql_3306 start
Starting MySQL...
Logging to '/usr/local/mysql/data/mysql.err'.
[root@mysql ~]# netstat -tunlp |grep 3306
启动失败

进入排错流程

思考一下整理流程

准备配置文件

准备安装脚本

授权

数据目录初始化

应挨个步骤检查

经过排查，找到问题了：

在配置文件中：

datadir=/my_mysql/3306/ #初始化数据放置目录

初始化过程选中的路径则是/my_mysql/3306/data

修改一下配置文件吧：datadir=/my_mysql/3306/data

[root@mysql ~]# /my_mysql/3306/mysql_3306 start
Starting MySQL...
Logging to '/usr/local/mysql/data/mysql.err'.

出现这个问题，应该是没有关闭防火墙和selinux,关闭之后提示的错误日志路径应该是配置文件配置的，此时手动创建错误日志，并重新赋权：
[root@mysql 3306]# chmod +x /my_mysql/3306/mysql_3306 
[root@mysql 3306]# chmod +x /my_mysql/3307/mysql_3307
另外，mysql解压后的目录最好也赋权为mysql

[root@mysql ~]# mysql -S /my_mysql/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.51-log MySQL Community Server (GPL)

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

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>

3307数据库也是用自己的sock文件登录，分别创建新的数据库，查看区别。结束。

Theshaye

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
linux-MYSQL 配置

DMYSQL_UNIX_ADDR=/application/mysql-5.6.49/tmp/musql.sock 设定mysql.sock路径。-DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.49 设定mysql安装目录。-DMYSQL_DATADIR=/application/mysql-5.6.49/data 设定mysql数据文件目录。datadir=/my_mysql/3306/ #初始化数据放置目录。
复制链接

扫一扫