mysql安装

上传安装包到服务器上

解压安装包，配置运行环境

[root@mysql01 ~]# tar -xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /opt/   

[root@mysql01 ~]# 
[root@mysql01 ~]# cd /opt/
[root@mysql01 opt]# ls
mysql-5.7.28-linux-glibc2.12-x86_64
[root@mysql01 opt]# ln -s mysql-5.7.28-linux-glibc2.12-x86_64/ mysql5728
[root@mysql01 opt]# ls -l
total 0
lrwxrwxrwx 1 root root  36 Jul 27 14:51 mysql5728 -> mysql-5.7.28-linux-glibc2.12-x86_64/
drwxr-xr-x 9 root root 129 Jul 27 14:50 mysql-5.7.28-linux-glibc2.12-x86_64
[root@mysql01 opt]# 

配置PATH

[root@mysql01 opt]# echo 'export PATH=$PATH:/opt/mysql5728/bin'  >> /etc/profile
[root@mysql01 opt]# source /etc/profile

查看mysql版本

[root@mysql01 bin]# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

删除mariadb的依赖，删除默认的配置文件

[root@mysql01 bin]# yum remove mariadb-libs.x86_64  -y
[root@mysql01 bin]# rm -f /etc/my.cnf

安装装mysql5.7特有的依赖包

[root@mysql01 bin]# yum install libaio-devel -y

创建数据目录

#准备mysql的数据目录，授权用户
[root@mysql01 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql01 ~]# mkdir -p /mysql57
[root@mysql01 ~]# mkdir -p /mysql57/mysql_3306/
[root@mysql01 ~]# 

#授权目录
[root@mysql01 ~]# chown -R mysql.mysql /mysql57/
[root@mysql01 ~]# chown -R mysql.mysql /mysql57/mysql_3306/
[root@mysql01 ~]# chown -R mysql.mysql /opt/mysql*

#检查目录权限
root@mysql01 ~]# ls -ld /mysql57/ /mysql57/mysql_3306/ /opt/mysql5728/
drwxr-xr-x 3 mysql mysql  24 Jul 27 15:05 /mysql57/
drwxr-xr-x 2 mysql mysql   6 Jul 27 15:05 /mysql57/mysql_3306/
drwxr-xr-x 9 mysql mysql 129 Jul 27 14:50 /opt/mysql5728/
[root@mysql01 ~]# 

# 现在自建mysql57目录，没有输入数据，mysql无法使用，初始化生成mysql默认库的数据源，用户等信息，即可启动成功
# mysqld 服务端命令，启动，初始化，都用这个命令
# --basedir mysql二进制命令装在哪里了，主程序目录
# --datadir 数据目录初始到哪

[root@mysql01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql5728 --datadir=/mysql57/mysql_3306/

创建配置文件

初始化完毕后，有配置文件即可正确启动，告诉my.conf
mysqld的数据源目录在哪，日志写入到哪

# /etc/my.conf 默认mysql会去读这个，不指定也会读
# [mysqld] 服务端会读取的配置
# [mysql] 再机器本地，执行mysql命令，客户端读取配置
# socket 本地进程套数字文件，用于mysql客户端再到本地链接

cat >/etc/my.cnf <<'EOF'

[mysqld]
port=3306
user=mysql
basedir=/opt/mysql5728
datadir=/mysql57/mysql_3306/
#socket=/tmp/mysql.sock   #最好不要放到tmp目录下
socket=/mysql57/mysql_3306/mysql.sock

[mysql]
socket=/mysql57/mysql_3306/mysql.sock
EOF

创建启动脚本

[root@mysql01 ~]# cp /opt/mysql5728/support-files/mysql.server /etc/init.d/mysqld
[root@mysql01 ~]# systemctl daemon-reload
[root@mysql01 ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:systemd-sysv-generator(8)
[root@mysql01 ~]# 

登录mysql

[root@mysql01 ~]# systemctl start mysql
[root@mysql01 ~]# netstat -ntclup |grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      11726/mysqld        
tcp6       0      0 :::3306                 :::*                    LISTEN      11726/mysqld        
tcp6       0      0 :::3306                 :::*                    LISTEN      11726/mysqld        

修改数据库密码

[root@mysql01 ~]# mysqladmin password -S /tmp/mysql.sock mysql5728
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@mysql01 ~]# mysql -uroot -pmysql5728

多实例管理

多实例就是在同一台主机上，跑多个数据库，数据库的端口、目录不同。
这几个数据库相当于几个房间，之间没有关系。
不同的端口、不用的数据目录、不同的配置文件、不同的mysql进程，不同的pid

创建多实例数据目录

mkdir -p /mysql57/mysql_3307/
mkdir -p /mysql57/mysql_3308/
chown -R mysql.mysql /mysql57/

初始化2个实例的数据

mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql5728  --datadir=/mysql57/mysql_3307/

mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql5728  --datadir=/mysql57/mysql_3308/

创建实例的配置文件

cat >/etc/mysql_3307.cnf <<'EOF'

[mysqld]
port=3307
user=mysql
basedir=/opt/mysql5728
datadir=/mysql57/mysql_3307/
socket=/mysql57/mysql_3307/mysql.sock
log_error=/mysql57/mysql_3307/mysql.log

[mysql]
socket=/mysql57/mysql_3307/mysql.sock
EOF

#————————————————————————————————————————————————————————————————

cat >/etc/mysql_3308.cnf <<'EOF'

[mysqld]
port=3308
user=mysql
basedir=/opt/mysql5728
datadir=/mysql57/mysql_3308/
socket=/mysql57/mysql_3308/mysql.sock
log_error=/mysql57/mysql_3308/mysql.log

[mysql]
socket=/mysql57/mysql_3308/mysql.sock
EOF

检查配置文件

[root@mysql01 ~]# ls /etc/my*
/etc/my.cnf  /etc/mysql_3307.cnf  /etc/mysql_3308.cnf
   

创建多实例脚本

cat > /mysql57/3308.sh <<'EOF'
port="3308"
mysql_user="mysql"
Cmdpath="/opt/mysql5728/bin/"
mysql_sock="/mysql57/mysql_${port}/mysql.sock"
mysqld_pid_file_path=/mysql57/mysql_${port}/mysqld_${port}.pid

start(){
if [ ! -e "$mysql_sock" ];then
    printf "Starting MySQL...\n"
    /bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/etc/mysql_${port}.cnf --pid-file=$mysqld_pid_file_path 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: /data/${port}/mysql{start|stop|restart}\n"
esac
EOF

启动数据库

[root@mysql01 mysql57]# sh 3307.sh start
Starting MySQL...
Logging to '/mysql57/mysql_3307/mysql.log'.

[root@mysql01 mysql57]# netstat -tunlp |grep mysql
tcp6       0      0 :::3307                 :::*                    LISTEN      2742/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      2551/mysqld         
tcp6       0      0 :::3306                 :::*                    LISTEN      1508/mysqld         

#检查本地socket
[root@mysql01 mysql57]# ls mysql*
mysql_3306:
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  jpress  mysql01.err  performance_schema  public_key.pem   server-key.pem
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mysql   mysql01.pid  private_key.pem     server-cert.pem  sys

mysql_3307:
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  mysql            mysql.log   mysql.sock.lock     private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mysqld_3307.pid  mysql.sock  performance_schema  public_key.pem   server-key.pem

mysql_3308:
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  mysql            mysql.log   mysql.sock.lock     private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mysqld_3308.pid  mysql.sock  performance_schema  public_key.pem   server-key.pem
[root@mysql01 mysql57]# 

配置多实例密码

#使用socket连接更稳定，不走网络，延迟小。

mysqladmin -S /mysql57/mysql_3307/mysql.sock password 
#生产环境不建议在password后面直接写密码，history可以看到
mysqladmin -S /mysql57/mysql_3308/mysql.sock password mysql5728
[root@mysql01 mysql57]# mysqladmin -S /mysql57/mysql_3308/mysql.sock password mysql5728
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

#生产环境不建议-p后面跟密码，因为history可以看到
mysql -uroot -pmysql5728 -S /mysql57/mysql_3307/mysql.sock -e "show global variables like 'port';"