安装MySQL及优化

kk起个名吧
789 阅读6分钟

一、 yum安装

1. 安装mysql

[root@node7 ~]# tee /etc/yum.repos.d/mysql.repo <<EOF    //官方源
> [mysql57-community]
> name=MySQL 5.7 Community Server
> baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
> enabled=1
> gpgcheck=0
> EOF

[root@node7 ~]# yum -y install mysql-community-server    //yum安装
[root@node7 ~]# systemctl start mysqld    //开启服务
[root@node7 ~]# ss -ntap |grep 3306
LISTEN     0      80          :::3306                    :::*                   users:(("mysqld",pid=59796,fd=21))

[root@node7 ~]# grep password /var/log/mysqld.log   //查看默认密码
2024-06-28T04:09:01.772990Z 1 [Note] A temporary password is generated for root@localhost: <AZoDFYCu4rA
[root@node7 ~]# mysql -u root -p'<AZoDFYCu4rA' //使用默认密码登录

mysql> set global validate_password_policy=0;  //修改密码策略
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;  //修改密码策略
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' identified by 'abc123';  //修改密码为abc123
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@node7 ~]# mysql -u root -p'abc123'   //用自定义密码登录

2. 安装 mariadb

[root@node7 ~]# yum install mariadb-server -y
[root@node7 ~]# systemctl start mariadb.service
[root@node7 ~]# mysql
[root@node7 ~]# mysql_secure_installation 
//初始化设置  先输入密码 一路回车

二、 编译安装

  • 安装依赖包添加mysql用户
[root@node7 ~]# systemctl stop firewalld
[root@node7 ~]# setenforce 0
[root@node7 ~]# yum install -y ncurses-devel autoconf cmake
[root@node7 ~]# useradd -s /sbin/nologin  mysql
  • 解压软件包并切换到编译目录，并编译安装
[root@node7 ~]# cd /data
[root@node7 data]# tar xf mysql-boost-5.7.20.tar.gz
[root@node7 data]# cd mysql-5.7.20/
[root@node7 mysql-5.7.20]# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
> -DSYSCONFDIR=/etc \
> -DSYSTEMD_PID_DIR=/usr/local/mysql \
> -DDEFAULT_CHARSET=utf8  \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
> -DMYSQL_DATADIR=/usr/local/mysql/data \
> -DWITH_BOOST=boost \
> -DWITH_SYSTEMD=1
  • 编译安装
[root@node7 mysql-5.7.20]# make -j4 && make install  
//建议分两步做
  • 数据库目录进行权限调整
[root@node7 mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/
  • 建立调整配置文件，并修改配置文件权限
[root@node7 mysql-5.7.20]# vi /etc/my.cnf  //删除文件内原有内容，添加以下内容

[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[root@node7 mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
  • 设置环境变量
[root@node7 mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@node7 mysql-5.7.20]# echo 'export PATH' >> /etc/profile
[root@node7 mysql-5.7.20]# source /etc/profile
  • 初始化 数据库
[root@node7 mysql-5.7.20]# cd /usr/local/mysql/
[root@node7 mysql]# bin/mysqld \
> --initialize-insecure \
> --user=mysql \
> --basedir=/usr/local/mysql \
> --datadir=/usr/local/mysql/data
  • 准备systemctl配置文件
[root@node7 mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@node7 mysql]# systemctl daemon-reload
[root@node7 mysql]# systemctl start mysqld
[root@node7 mysql]# systemctl status mysqld
  • 修改数据库密码
[root@node7 mysql]# mysqladmin -u root -p password "abc123"  
//刚开始没密码是空的直接回车
  • 登录数据库
mysql -u root -p     
//这个命令敲下，提示要输入密码,这个就是刚才设置的密码abc123

三、 rpm包安装

//安装相关依赖包
yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel   ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel
//卸载 mariadb的安装联系
yum remove  mysql-libs   
//从官网下载rpm包
rpm -ivh mysql-community-common-5.7.44-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-5.7.44-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.44-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.44-1.el7.x86_64.rpm

四、 二进制安装

  • 准备用户
groupadd -r  -g  306 mysql
useradd  -r  -g  306 -u 306 -d /data/mysql mysql
  • 准备数据目录，建议使用逻辑卷
//可选做，后面的脚本mysql_install_db可自动生成此目录
mkdir /data/mysql   -p
chown mysql:mysql /data/mysql
  • 准备二进制程序
//官网下载二进制包
tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 
ln -s /data/mysql-5.7.44-linux-glibc2.12-x86_64   /usr/local/mysql
  • 准备配置文件
cp /etc/my.cnf{,.bak}

vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
  • 创建数据库文件 生成root空密码
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

//生成随机密码
mysqld --initialize   --user=mysql --datadir=/data/mysql
  • 准备服务脚本，并启动服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

systemctl  start  mysqld
  • PATH路径
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
  • 安全初始化
/usr/local/mysql/bin/mysql_secure_installation
  • 测试登录
mysql -uroot -p

五、 多实例

  • 什么是数据库多实例

多实例类似微信双开，端口号类比微信账号，数据库类比聊天窗口，表类比聊天记录MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306、3307、3308等），同时运行多个MySQL服务进程，这些服务进程通过不同的Socket监听不同的服务端口来提供服务。多实例可能是MySQL的不同版本,也可能是MySQL的同一版本实现。

  • 多实例的好处

可有效利用服务器资源。当单个服务器资源有剩余时，可以充分利用剩余资源提供更多的服务，且可以实现资源的逻辑隔离节约服务器资源。例如公司服务器资源紧张，但是数据库又需要各自尽量独立的提供服务，并且还需要到主从复制等技术，多实例就是最佳选择。

  • 多实例弊端

存在资源互相抢占的问题。比如：当某个数据库实例并发很高或者SQL查询慢时，整个实例会消耗大量的CPU、磁盘I/O等资源，导致服务器上面其他的数据库实例在提供服务的质量也会下降,所以具体的需求要根据自己的实际情况而定。

[root@node7 ~]# yum -y install mariadb-server
[root@node7 ~]# mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@node7 ~]# chown -R mysql.mysql /mysql
[root@node7 ~]# mysql_install_db --user=mysql --datadir=/mysql/3306/data  //生成数据库文件
[root@node7 ~]# mysql_install_db --user=mysql --datadir=/mysql/3307/data
[root@node7 ~]# mysql_install_db --user=mysql --datadir=/mysql/3308/data


[root@node7 ~]# vim /mysql/3306/etc/my.cnf  //编辑配置文件
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid
[root@node7 ~]# sed 's/3306/3307/' /mysql/3306/etc/my.cnf > /mysql/3307/etc/my.cnf
[root@node7 ~]# sed 's/3306/3308/' /mysql/3306/etc/my.cnf > /mysql/3308/etc/my.cnf


[root@node7 ~]# vim /mysql/3306/bin/mysqld    //准备启动脚本

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

[root@node7 ~]# vim /mysql/3307/bin/mysqld
[root@node7 ~]# vim /mysql/3308/bin/mysqld
//重复上述过程，分别建立3307，3308的启动脚本   注意修改端口号

[root@node7 ~]# chmod +x /mysql/3306/bin/mysqld
[root@node7 ~]# chmod +x /mysql/3307/bin/mysqld
[root@node7 ~]# chmod +x /mysql/3308/bin/mysqld
//添加执行权限

//启动服务
[root@node7 ~]# /mysql/3306/bin/mysqld start
Starting MySQL...
[root@node7 ~]# /mysql/3307/bin/mysqld start
Starting MySQL...
[root@node7 ~]# /mysql/3308/bin/mysqld start
Starting MySQL...
[root@node7 ~]# ss -natp | grep 330
LISTEN     0      50           *:3307                     *:*                   users:(("mysqld",pid=62019,fd=14))
LISTEN     0      50           *:3308                     *:*                   users:(("mysqld",pid=62179,fd=14))
LISTEN     0      50           *:3306                     *:*                   users:(("mysqld",pid=61859,fd=14))
多实例.png 
//登录实例
[root@node7 ~]# /mysql/3308/bin/mysqld start
[root@node7 ~]# mysql -h127.0.0.1 -P3308  

//关闭数据库，需要手动输入root的密码
[root@node7 ~]# /mysql/3308/bin/mysqld stop
Stoping MySQL...
Enter password:
多实例2.png 
//修改密码 
[root@node7 ~]# mysqladmin -uroot -S /mysql/3307/socket/mysql.sock password '123123' 

//测试连接
[root@node7 ~]# mysql -uroot -p -S /mysql/3307/socket/mysql.sock
多实例3.png

六、 安装mycli 插件

[root@localhost data]# yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel libffi-devel
[root@localhost data]# tar zxvf Python-3.7.7_.tgz
[root@localhost Python-3.7.7]# cd Python-3.7.7/
[root@localhost Python-3.7.7]# ./configure --prefix=/usr/local/Python-3.7.7/
[root@localhost Python-3.7.7]# make  
[root@localhost Python-3.7.7]# make install
[root@localhost Python-3.7.7]# ln -s  /usr/local/Python-3.7.7/bin/python3.7  /usr/bin/python37
[root@localhost Python-3.7.7]# ln -s /usr/local/Python-3.7.7/bin/pip3.7 /usr/bin/pip37

[root@localhost Python-3.7.7]# pip37 install --upgrade pip -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com
[root@localhost Python-3.7.7]# pip37 install mycli -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com
[root@localhost Python-3.7.7]# ln -s /usr/local/Python-3.7.7/bin/mycli /usr/bin/mycli
[root@localhost Python-3.7.7]# mycli -u root -p abc123

[root@localhost ~]#vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
auto-rehash

七、 优化操作

1. 生成提示符

//查看mysql版本
[root@centos7 ~]#mysql -V

//修改提示符
[root@localhost ~]#vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_

2. 自动补全

[mysql]
prompt=(mysql) [\\d]>\\_
auto-rehash
//自动补全 只能补全敲过的命令
avatar
文章
阅读
粉丝