ceshimd

二进制安装

下载解压 MySQL

链接：MySQL 下载提取码：bc52

将下载的 mysql 通过文件传输工具移到 /usr/local
解压 : tar -xvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
删除源码包 : rm -f mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
修改文件名 : mv mysql-5.7.21-linux-glibc2.12-x86_64/ mysql
进入mysql创建 data 文件夹 和 log 文件夹 : cd mysql/ && mkdir data && mkdir log

清除历史环境

[root@bogon mysql]# rpm -qa |grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64

[root@bogon mysql]# yum remove mariadb-libs.x86_64 -y

创建 mysql 用户组和 mysql 用户

#查看有没有mysql组
[root@localhost mysql]# cat /etc/group | grep mysql
#查看有没有mysql用户
[root@localhost mysql]# cat /etc/passwd |grep mysql
#创建mysql用户组
[root@localhost mysql]# groupadd mysql
#创建mysql用户并添加到mysql用户组中(//useradd -r参数表示mysql用户是系统用户，不可用于登录系统；第一个mysql是用户组，第二个mysql是用户)
[root@localhost mysql]# useradd -r -g mysql mysql
#检查用户组是否创建成功
[root@localhost mysql]# groups mysql
mysql : mysql

设置 mysql 目录访问权限，用户组

#将mysql目录访问权限赋为myql用户
[root@localhost mysql]# chown -R mysql /usr/local/mysql
#改变mysql目录的用户组属于mysql组
[root@localhost mysql]# chgrp -R mysql /usr/local/mysql
#查看mysql目录下所有的目录及文件夹所属组合用户
[root@bogon mysql]# cd /usr/local/mysql && ll
总用量 40
drwxr-xr-x.  2 mysql mysql  4096 7月  17 11:23 bin
-rw-r--r--.  1 mysql mysql 17987 12月 28 2017 COPYING
drwxr-xr-x.  2 mysql mysql     6 7月  19 15:47 data
drwxr-xr-x.  2 mysql mysql    52 7月  17 11:24 docs
drwxr-xr-x.  3 mysql mysql  4096 7月  17 11:23 include
drwxr-xr-x.  5 mysql mysql  4096 7月  17 11:24 lib
drwxr-xr-x.  2 mysql mysql     6 7月  19 15:47 log
drwxr-xr-x.  4 mysql mysql    28 7月  17 11:23 man
-rw-r--r--.  1 mysql mysql  2478 12月 28 2017 README
drwxr-xr-x. 28 mysql mysql  4096 7月  17 11:24 share
drwxr-xr-x.  2 mysql mysql    86 7月  17 11:24 support-files

修改环境变量

#配置环境变量
[root@bogon mysql]# vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
#生效配置
[root@bogon mysql]# source /etc/profile
#验证mysql
[root@bogon mysql]# mysql -V
mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

初始化 mysql

5.7 版本

[root@localhost mysql]# cd /usr/local/mysql/ && bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2020-02-24T15:20:51.505520Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-02-24T15:21:16.611890Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-02-24T15:21:20.895476Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-02-24T15:21:21.075717Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4facfa2a-5719-11ea-9d4a-000c291546c6.
2020-02-24T15:21:21.119139Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-02-24T15:21:21.162719Z 1 [Note] A temporary password is generated for root@localhost: Rzm%WwopR0yp

可能会报错: bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

原因：yum安装的是libnuma.so.1，但安装时默认安装的是32位的，而db2需要的是64位的

#如果已经安装了libnuma.so.1，先yum remove libnuma.so.1
yum remove libnuma.so.1
#安装依赖包
yum -y install numactl.x86_64

5.6 版本

[root@localhost mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

配置文件设置

[root@bogon mysql]# vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server_id=1
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

配置启动脚本

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

启动 mysql

[root@bogon mysql]# service mysql start
Starting MySQL.Logging to '/usr/local/mysql/data/bogon.err'.
 SUCCESS!

#查看mysql状态
/etc/init.d/mysql status 或者 service mysql status
#启动mysql
/etc/init.d/mysql start 或者 service mysql start
#停止mysql
/etc/init.d/mysql stop 或者 service mysql stop
#重新启动mysql
/etc/init.d/mysql restart 或者 service mysql restart
#查看mysql服务说明启动成功
ps -ef|grep mysql

配置 mysql 到环境变量

[root@bogon ~]# vi /root/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export MYSQL_HOME=/usr/local/mysql/bin
PATH=$PATH:$HOME/bin::${MYSQL_HOME}

export PATH

#设置环境变量立即生效
[root@localhost tmp]# source /root/.bash_profile

重置密码

mysql> ALTER USER USER() IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

退出使用设置的密码重新登录

添加远程访问账号

mysql> grant all privileges on *.* to 'root'@"%" identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

开放3306端口

[root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost mysql]# firewall-cmd --reload
success

多实例

准备多个`数据`和`日志`目录

[root@bogon /]# mkdir -p /data/330{7,8,9}/data
[root@bogon /]# mkdir -p /log/330{7,8,9}

准备配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

授权目录权限

[root@bogon data]# chown -R mysql.mysql /data/* /log/*

将原有配置文件设置为备份文件

[root@bogon log]# mv /etc/my.cnf /etc/my.cnf.bak

初始化三套数据

[root@bogon /]# cd /usr/local/mysql/

[root@bogon mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/3307/data
=/data/3309/data2020-07-19T13:04:19.622157Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-19T13:04:19.911293Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-07-19T13:04:19.965577Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-07-19T13:04:20.033509Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5bdcfe6e-c9c0-11ea-b3cf-000c297d79ef.
2020-07-19T13:04:20.034703Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-07-19T13:04:20.038381Z 1 [Note] A temporary password is generated for root@localhost: AyLafdD>7+?U
[root@bogon mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/3308/data
2020-07-19T13:04:23.193064Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-19T13:04:23.670815Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-07-19T13:04:23.744085Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-07-19T13:04:23.807425Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5e1cd94b-c9c0-11ea-b528-000c297d79ef.
2020-07-19T13:04:23.808725Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-07-19T13:04:23.812885Z 1 [Note] A temporary password is generated for root@localhost: (C+(Inkut6A&
[root@bogon mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/3309/data
2020-07-19T13:04:40.336162Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-19T13:04:41.433808Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-07-19T13:04:41.582865Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-07-19T13:04:41.643617Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 68be6f77-c9c0-11ea-b64c-000c297d79ef.
2020-07-19T13:04:41.645015Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-07-19T13:04:41.646244Z 1 [Note] A temporary password is generated for root@localhost: ew6f;)7b(ygF

systemd 管理多实例

[root@bogon systemd]# cat > /etc/systemd/system/mysqld3307.service <<EOF
> [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=/data/3307/my.cnf
> LimitNOFILE = 5000
> EOF
ttp://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=/data/3308/my.cnf
LimitNOFILE = 5000
E[root@bogon systemd]#
[root@bogon systemd]# cat > /etc/systemd/system/mysqld3308.service <<EOF
> [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=/data/3308/my.cnf
> LimitNOFILE = 5000
> EOF
[root@bogon systemd]#
[root@bogon systemd]# cat > /etc/systemd/system/mysqld3309.service <<EOF
> [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=/data/3309/my.cnf
> LimitNOFILE = 5000
> EOF

启动

[root@bogon system]# systemctl start mysqld3307.service && systemctl start mysqld3308.service && systemctl start mysqld3309.service

验证多实例

[root@bogon system]# netstat -lnp|grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      46793/mysqld
tcp6       0      0 :::3308                 :::*                    LISTEN      46799/mysqld
tcp6       0      0 :::3309                 :::*                    LISTEN      46805/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     176408   46793/mysqld         /tmp/mysql3307.sock
unix  2      [ ACC ]     STREAM     LISTENING     176411   46805/mysqld         /tmp/mysql3309.sock
unix  2      [ ACC ]     STREAM     LISTENING     176405   46799/mysqld         /tmp/mysql3308.sock

登陆多实例

#多实例的登录一定要指定特定的socket
[root@bogon system]# mysql -uroot -p'AyLafdD>7+?U'  -S /tmp/mysql3307.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.21-log

Copyright (c) 2000, 2018, 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>

ceshimd

二进制安装

下载解压 MySQL

清除历史环境

创建 mysql 用户组和 mysql 用户

设置 mysql 目录访问权限，用户组

修改环境变量

初始化 mysql

5.7 版本

5.6 版本

配置文件设置

配置启动脚本

启动 mysql

配置 mysql 到环境变量

重置密码

添加远程访问账号

开放3306端口

多实例

准备多个数据和日志目录

准备配置文件

授权目录权限

将原有配置文件设置为备份文件

初始化三套数据

systemd 管理多实例

启动

验证多实例

登陆多实例

准备多个`数据`和`日志`目录