mysql笔记（二进制安装+使用+多实例）

1. Mysql

1.1 二进制安装

下载

yum -y install wget

wget https://mirrors.aliyun.com/mysql/MySQL-5.7/mysql-5.7.36-el7-x86_64.tar.gz

添加用户

groupadd -g 27 mysql && useradd -g 27 -u 27 -M -s /sbin/nologin mysql

id mysql           #uid=27(mysql) gid=27(mysql) groups=27(mysql)

解压-移动-授权

tar xzvf mysql-5.7.36-el7-x86_64.tar.gz

mv /root/mysql-5.7.36-el7-x86_64    /usr/local/mysql

chown -R mysql:mysql /usr/local/mysql

创建日志文件并授权

touch /var/log/mysql.log && chown mysql:mysql /var/log/mysql.log

编辑配置文件

vim /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
port=3306
default-storage-engine = innodb
innodb_large_prefix=on
innodb_file_per_table = on
max_connections = 10000
collation-server = utf8_general_ci
character_set_server=utf8
user=mysql
[client]　　　　　　　　　　
port = 3306
socket = /usr/local/mysql/data/mysql.sock
default-character-set = utf8
[mysqld_safe]
log-error=/var/log/mysql.log

注释：

[mysqld]    //mysql服务相关的配置
basedir=/usr/local/mysql    #MySQL安装目录
datadir=/usr/local/mysql/data    #数据库数据文件存放目录
socket=/usr/local/mysql/data/mysql.sock  #MySQL使用的Unix socket文件路径；mysql socket的作用：用于应用程序间的通信。让本地客户端优先使用socket文件连接，获得更好的性能。限制只有有权限访问该socket文件的进程才能连接MySQL，提高安全性
port=3306   #MySQL监听的TCP/IP 端口号
default-storage-engine = innodb  #默认的存储引擎，这里使用的是Innodb
innodb_large_prefix=on      #启用Innodb的大VARCHAR和TEXT列，开启后支持更长的列和索引；不开启此参数时，InnoDB支持的VARCHAR最大长度为255字节，TEXT最大长度为65535字节。开启此参数后，InnoDB支持的VARCHAR最大长度提升到65535字节，TEXT最大长度提升到163830字节。
innodb_file_per_table = on   #开启每个表使用单独的表空间文件
max_connections = 10000     #最大允许的并发连接数
collation-server = utf8_general_ci  #设置MySQL服务器(全局)的默认字符集和校对规则。utf8：表示字符集为utf8，可以表示绝大部分的国际字符；_general_ci:表示校对规则为utf8_general_ci, 其中：general：表示最常用的比较规则，ci：表示忽略字符大小写(case insensitive)
character_set_server=utf8    #设置了MySQL服务器(整个实例)的默认字符集。
//MySQL支持很多种字符集，例如：
    utf8:多字节字符集,支持绝大部分国际字符
    latin1:单字节字符集,只支持西欧语言字符
    gbk:支持简体中文字符
    big5:支持繁体中文字符
user=mysql   //MySQL服务运行的用户

[client]　   //客户端配置

port = 3306     #（在[client]部分）表示客户端连接MySQL时使用的端口
socket = /usr/local/mysql/data/mysql.sock    #（在[client]部分）表示客户端连接MySQL时使用的Unix socket文件路径
default-character-set = utf8    #(在[client]部分) 表示客户端连接时使用的默认字符集

[mysqld_safe]    //[mysqld_safe]部分是针对MySQL的mysqld_safe启动脚本的配置
log-error=/var/log/mysql.log    #指定mysqld_safe脚本的日志文件路径为/var/log/mysql.log。

添加环境变量

cat >>/etc/profile<<EOF

export PATH=\${PATH}:/usr/local/mysql/bin

EOF

echo 'export PATH=${PATH}:/usr/local/mysql/bin'>>/etc/profile 

source /etc/profile

初始化数据库，产生随机初始登录密码，在最后一行

mysqld --initialize --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql

添加启动脚本

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

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000  
#设置操作系统默认允许mysql进程最大打开文件的数量

更新自启动服务，查看工作端口

systemctl daemon-reload

systemctl enable --now mysql.service

systemctl  restart mysql

systemctl enable mysql

ss -ntl |grep 3306

1.2 使用

登录

mysql -u root -p

修改登录密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

设置密码永不过期

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

重新加载授权表

flush privileges;

忘记密码

如果你在 CentOS 系统上忘记了 MySQL（或 MariaDB，CentOS 的默认数据库系统）的密码，你可以按照以下步骤来重置它：

1. 停止 MySQL 服务

sudo systemctl stop mysql

2. 启动 MySQL 服务并进入安全模式

sudo mysqld_safe --skip-grant-tables &

3. 无密码登录 MySQL

mysql -u root

4. 重置密码

FLUSH PRIVILEGES;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');

5. 退出 MySQL 并重启服务

EXIT;

常用命令

1.3 多实例

1. MySQL多实例的概念：
   MySQL多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307）运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务。

这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件、启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。

2. MySQL多实例的特点：
   有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务
   节约服务器资源
   缺点：资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降

   当前mysql在/usr/local下

1.3.1 停止mysql

mysql  -uroot -p

shutdown;

上可用

service mysqld stop

查看是否关闭

ss -antl

1.3.2 部署

mkdir -p /opt/data/{3306,3307,3308}

chown -R mysql.mysql /opt/data/

初始化3306-3308实例，密码在最后一行

mysqld --initialize --datadir=/opt/data/3306 --user=mysql

mysqld --initialize --datadir=/opt/data/3307 --user=mysql

mysqld --initialize --datadir=/opt/data/3308 --user=mysql

安装perl

yum install -y perl

配置/etc/my.cnf

vi /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log                          

启动三个实例

mysqld_multi start 3306

mysqld_multi start 3307

mysqld_multi start 3308

初始化三个实例并修改密码

mysql -uroot -p'g*/ASjBPi1(O' -S /tmp/mysql3306.sock

mysql -uroot -p'q_dO?tt#,5Y%' -S /tmp/mysql3307.sock

mysql -uroot -p'3xEsjjXq<jC-' -S /tmp/mysql3308.sock

set password = password('123456');    #设置密码

查看端口

ss -antl

1.3.3 登录实例

mysql -uroot -p -S /tmp/mysql3306.sock