MySQL服务

MySQL官方网站

一、概述

1.常用版本

• 5.6：5.6.34 5.6.36 5.6.38 5.6.40
• 5.7：5.7.18 5.7.20 5.7.24 5.7.26
• 8.0

2.安装方式

• YUM安装
• 二进制安装(常用)
• 源码包编译安装

二、安装部署

1.创建所需目录

mkdir /application /data/mysql/3306/data -p

[root@db01 ~]# mkdir /application /data/mysql/3306/data -p
[root@db01 ~]# tree /application/ /data/
/application/
/data/
└── mysql
    └── 3306
        └── data

3 directories, 0 files

2.创建mysql用户

useradd -Ms /sbin/nologin/ mysql

[root@db01 ~]# useradd -Ms /sbin/nologin mysql
[root@db01 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

3.软件上传安装

mysql-5.7.26-二进制安装包

tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql

[root@db01 ~]# cd /application/
[root@db01 /application]# rz -E
rz waiting to receive.
[root@db01 /application]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@db01 /application]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 
[root@db01 /application]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql
[root@db01 /application]# rm -f mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 
[root@db01 /application]# ls
mysql

4.修改系统环境变量

/etc/profile export PATH=/application/mysql/bin:$PATH source /etc/profile

[root@db01 /application]# vim /etc/profile
......
export PATH=/application/mysql/bin:$PATH
"/etc/profile" 78L, 1952C written
[root@db01 /application]# source /etc/profile
[root@db01 /application]# echo $PATH
/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

5.权限设定(授权软件安装目录和数据目录)

chown -R mysql. /application/mysql /data/mysql/

[root@db01 /application]# chown -R mysql. /application/mysql/ /data/mysql/
[root@db01 /application]# ll -d /application/mysql/ /data/mysql/
drwxr-xr-x 9 mysql mysql 129 Aug 13 15:52 /application/mysql/
drwxr-xr-x 6 mysql mysql  54 Aug 13 15:46 /data/mysql

6.初始化系统数据

rpm -qa | grep mariadb yum remove -y mariadb-libs.x86_64 yum install -y libaio-devel mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/3306/data

[root@db01 /application]# rpm -qa| grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@db01 /application]# yum remove -y mariadb-libs.x86_64 
......

Removed:
  mariadb-libs.x86_64 1:5.5.60-1.el7_5                                  
......
Complete!

[root@db01 /application]# yum install -y libaio-devel
......
Installed:
  libaio-devel.x86_64 0:0.3.109-13.el7                                  

Dependency Installed:
  libaio.x86_64 0:0.3.109-13.el7                                        
Complete!
[root@db01 /application]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/3306/data
2019-08-13T08:08:45.260979Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-13T08:08:45.429804Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-13T08:08:45.453403Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-13T08:08:45.509006Z 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: 92668f31-bda1-11e9-a5f9-000c2938d306.
2019-08-13T08:08:45.509596Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-13T08:08:45.510008Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /application]# ls /data/mysql/3306/data/ -l
total 110628
-rw-r----- 1 mysql mysql       56 Aug 13 16:08 auto.cnf
-rw-r----- 1 mysql mysql      419 Aug 13 16:08 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 13 16:08 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 13 16:08 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 13 16:08 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Aug 13 16:08 mysql
drwxr-x--- 2 mysql mysql     8192 Aug 13 16:08 performance_schema
drwxr-x--- 2 mysql mysql     8192 Aug 13 16:08 sys

MySQL数据块初始化时，会产生警告；但没有报错，那么表示初始化成功；

7.编写配置文件

MySQL程序默认读取配置文件位置-/etc/my.cnf

/etc/my.cnf [mysqld] user=Linux用户 basedir=数据库软件目录 datadir=数据库数据目录 socket=socket文件 server_id=实例ID port=端口号 log_error=错误日志 [mysql] socket=socket文件

[root@db01 /application]# vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/data/mysql.log
[mysql]
socket=/tmp/mysql.sock

8.准备启动脚本

注意：两种脚本方式不能交替使用，即用sys-v方式启动的数据库不能通过systemctl管理，反之亦然
Cent OS 6(sys-v)

管理命令：service mysqld start|stop|status|restart 方式: cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@db01 /application]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@db01 /application]# service mysqld start 
Starting MySQL.Logging to '/data/mysql/3306/data/mysql.log'.
 SUCCESS! 
[root@db01 /application]# ps -ef|grep mysqld
root      10566      1  0 16:40 pts/0    00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --datadir=/data/mysql/3306/data --pid-file=/data/mysql/3306/data/db01.pid
mysql     10731  10566  6 16:40 pts/0    00:00:00 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/data/mysql/3306/data --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/data/mysql/3306/data/mysql.log --pid-file=/data/mysql/3306/data/db01.pid --socket=/tmp/mysql.sock --port=3306
root      10761   9803  0 16:40 pts/0    00:00:00 grep --color=auto mysqld
[root@db01 /application]# ss -lntup |grep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=10731,fd=16))
[root@db01 /application]# service mysqld stop 
Shutting down MySQL.. SUCCESS! 

Cent OS 7(systemd)

管理命令：systemctl start|stop|status|restart mysqld

/etc/systemd/system/mysqld.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=/目录/mysql/bin/mysqld --defaults-file=配置文件 LimitNOFILE = 5000

[root@db01 /application]# cat /etc/systemd/system/mysqld.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=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db01 /application]# systemctl start mysqld.service 
[root@db01 /application]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> ^DBye
[root@db01 /application]# systemctl stop mysqld.service 

三、mysql基础管理

1.用户和权限管理

MySQL安装完成后，默认可以使用无密码登录，但仅有本机可以无密码登录；

[root@db01 /application]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> ^DBye

用户管理

1. 设置/修改超级管理员密码(root@localhost)

mysqladmin -uroot -p password 密码

[root@db01 /application]# mysqladmin -uroot -p password '123456'
Enter password: 
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@db01 /application]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

修改完成后，登录数据库方式：

mysql -uroot -p密码

[root@db01 /application]# mysql -uroot -p123456
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 6
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> ^DBye

2. MySQL的用户管理

• 用户定义形式：用户名@'白名单'

  白名单形式 10.0.0.2 #表示特定主机 10.0.0.% #表示一个网段 10.0.0.5% #表示50-59网段 10.0.0.0/255.255.254.0 #表示一个网段(该形式支持VLSM和CIDR) % #表示所有地址(非常危险，实际生产环境中禁用) 域名(aspen.com) 主机名(db03)

• 用户的管理操作(MySQL v8.0必备)

  用户管理必须由本地超级管理员完成，默认只有超级管理员拥有该权限

创建用户

create user 用户名@'白名单';

mysql> create user Aspen_Han@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

创建用户并设置密码

create user 用户名@'白名单' identified by '密码';

mysql> create user aspen@'10.0.0.0/255.255.255.0' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

查询用户

select user,host,authentication_string from mysql.user;

mysql> select user,host,authentication_string from mysql.user;
+---------------+------------------------+-------------------------------------------+
| user          | host                   | authentication_string                     |
+---------------+------------------------+-------------------------------------------+
| root          | localhost              | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost              | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost              | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| Aspen_Han     | 10.0.0.%               |                                           |
| aspen         | 10.0.0.0/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

修改用户

alter user 用户名@'白名单'identified by '密码';

mysql> alter user Aspen_Han@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+------------------------+-------------------------------------------+
| user          | host                   | authentication_string                     |
+---------------+------------------------+-------------------------------------------+
| root          | localhost              | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost              | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost              | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| Aspen_Han     | 10.0.0.%               | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| aspen         | 10.0.0.0/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

删除用户

drop user 用户名@'白名单';

mysql> drop user Aspen_Han@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+------------------------+-------------------------------------------+
| user          | host                   | authentication_string                     |
+---------------+------------------------+-------------------------------------------+
| root          | localhost              | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost              | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost              | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| aspen         | 10.0.0.0/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+------------------------+-------------------------------------------+
4 rows in set (0.00 sec)

权限基本管理

1. 授权-grant
   MySQL多次授权是叠加的关系，后一次授权无法覆盖前一次授权

   grant 权限 on 数据库.表 to 用户@'白名单' identified by 密码;

mysql> grant all on *.* to aspen@'10.0.0.0/255.255.255.0' identified by '123';
Query OK, 0 rows affected, 1 warning (0.16 sec)

权限 SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ALL 代表所有权限，一般是普通管理员拥有 with grant option 为其他用户授权权限，一般是超级管理员的权限

权限范围 *.* 代表全库范围 数据库.* 代表单个库下所有表 数据库.表 代表单个库下单个表文件

2. 回收权限

revoke权限 on 数据库.表 from 用户@'白名单';

mysql> revoke INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE on *.* from aspen@'10.0.0.0/255.255.255.0';
Query OK, 0 rows affected (0.09 sec)

3. 查看用户权限-show grants for

show grants for 用户@'白名单'

mysql> show grants for aspen@'10.0.0.0/255.255.255.0';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for aspen@10.0.0.0/255.255.255.0                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES ON *.* TO 'aspen'@'10.0.0.0/255.255.255.0' |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.36 sec)

2.MySQL连接管理

MySQL是C/S模式的软件结构，提供两种方式供客户端连接数据库，分别为socket和TCP/IP
socket(提供本地登录服务)

mysql -u用户名 -p密码 -S socket文件

socket文件登录前提：socket文件位置必须准确，登录用户必须具备localhost登录权限； MySQL客户端默认查找socket文件路径：/tmp/mysql.scok

[root@db01 ~]# mysql -uroot -p123456 -S /tmp/mysql.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 11
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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> 

TCP/IP

mysql -u用户名 -p密码 -h主机地址 -P端口号

MySQL默认端口号：3306

[root@aspen ~]# mysql -uaspen -p123 -h10.0.0.151 -P3306
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 12
Server version: 5.7.26 MySQL Community Server (GPL)

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

mysql命令的其他参数

• -e 免交互式执行MySQL语句

mysql -u用户名 -p密码 -e "SQL语句"

[root@db01 ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• < 导入数据库脚本
  

  mysql -u用户名 -p密码<数据库脚本

[root@db01 ~]# ls
anaconda-ks.cfg  world.sql
[root@db01 ~]# mysql -uroot -p123456 <world.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+

MySQL客户端内置功能

• help 查看帮助
• \G 将数据表内容按行显示
• \c或Ctrl+C 返回命令
• exit或quit或\q或Ctrl+D 退出数据库登录
• source 'SQL脚本' 导入SQL脚本
• system 'Linux命令' 在数据库中调用系统命令

mysql> help contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
......

mysql> select * from mysql.user where user='aspen'\G;
*************************** 1. row ***************************
                  Host: 10.0.0.0/255.255.255.0
                  User: aspen
           Select_priv: Y
           Insert_priv: Y
......

mysql> select * from mysql.user where user='aspen'\c
mysql> exit
Bye

mysql> drop database world;
Query OK, 3 rows affected (0.46 sec)

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

mysql> source ~/world.sql;
Query OK, 0 rows affected (0.00 sec)
.......

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

3.MySQL初始化配置

初始化方式 预编译(略，需要在编译安装时定义定制参数) 初始化配置文件(常用) 命令行(一般在维护时使用)

初始化配置文件

• MySQL初始化配置文件读取顺序

mysqld --help --verbose |grep my.cnf

文件顺序排序靠后的优先级高 /etc/my.cnf > /etc/mysql/my.cnf > /usr/local/mysql/etc/my.cnf > ~/.my.cnf

[root@db01 ~]# mysqld --help --verbose| grep 'my.cnf'
mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 2 - No such file or directory)
2019-08-15T13:15:31.240101Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

• 初始化配置文件格式
  

  [mysqld] #服务端标签 user=Linux用户 #定义MySQL服务端管理用户 basedir=数据库软件目录 #定义MySQL服务端程序位置(软件安装目录) datadir=数据库数据目录 #定义MySQL服务端数据目录 socket=socket文件 #定义MySQL服务端socket文件位置 server_id=实例ID #定义MySQL服务端系统编号(主从复制中，必要参数，范围：0 < n < =65535) port=端口号 #定义MySQL服务端端口 log_error=错误日志 #定义MySQL服务端错误日志位置 [mysql] #客户端标签 socket=socket文件 #定义MySQL客户端Socket文件(该文件要与服务端socket文件一致)

[root@db01 ~]# vim /etc/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/data/mysql.log
[mysql]
socket=/tmp/mysql.sock

服务端标签形式(服务端标签影响MySQL的启动)

[mysqld] [mysqld_safe] [server]

客户端标签形式(客户端标签影响MySQL的登录)

[mysql]] [mysqldump] #专门用作数据库备份的客户端] [client]

4.MySQL的其他启动方式(命令行初始化配置)

日常启动关闭方式

service mysqld start|stop|status|restart systemctl start|stop|status|restart mysqld

[root@aspen ~]# service mysqld start
Starting MySQL.... SUCCESS! 
[root@aspen ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
[root@aspen ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@aspen ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[root@aspen ~]# systemctl start mysqld
[root@aspen ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
[root@aspen ~]# systemctl stop mysqld
[root@aspen ~]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

临时性维护启动方式
mysqld

mysqld & #启动MySQL时并将启动日志打印到屏幕上 mysqld --defaults-file=配置文件 #读取指定配置文件启动MySQL

[root@db01 ~]# mysqld --defaults-file=/opt/my.cnf --user=root &
[1] 7586
[root@db01 ~]# ls /tmp
mysql.sock  mysql.sock.lock  vmware-root_6122-994292192  vmware-root_6578-734627870
[root@db01 ~]# mysql -uroot -p123456 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          60 |
+-------------+
[root@db01 ~]# cat /opt/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=60
port=3306
log_error=/data/mysql/3306/data/mysql.log
[mysql]
socket=/tmp/mysql.sock

mysqld_safe

mysqld_safe --维护参数 & #临时指定特定参数启动MySQL mysqladmin shutdown #关闭临时性启动的MySQL

[root@db01 ~]# mysqld_safe --defaults-file=/data/mysql/3306/my.cnf  &
[root@db01 ~]# ls /tmp/
mysql.sock  mysql.sock.lock
[root@db01 ~]# mysqladmin shutdown
2019-08-15T14:22:18.907472Z mysqld_safe mysqld from pid file /data/mysql/3306/data/db01.pid ended
[1]+  Done                    mysqld_safe --defaults-file=/data/mysql/3306/my.cnf --skip-grant-tables --skip-networking
[root@db01 ~]# ls /tmp/

命令行参数优先级高于配置文件; mysqld_safe --socket=socket文件 #指定socket文件启动MySQL

[root@db01 ~]# mysqld_safe --defaults-file=/data/mysql/3306/my.cnf --socket=/tmp/3306.sock &
[1] 18764
[root@db01 ~]# 2019-08-15T14:27:03.525476Z mysqld_safe Logging to '/data/mysql/3306/data/db01.err'.
2019-08-15T14:27:03.550667Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/3306/data

[root@db01 ~]# ls /tmp/
3306.sock  3306.sock.lock

5.多实例环境部署

step1 准备数据目录

mkdir -p /data/mysql/330{7,8,9}/data

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

step2 准备配置文件

[root@db01 /data]# vim mysql/3307/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
server_id=7
port=3307
log_error=/data/mysql/3307/data/mysql.log
log_bin=/data/mysql/3307/mysql-bin

[root@db01 /data]# vim mysql/3308/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/mysql.sock
server_id=8
port=3308
log_error=/data/mysql/3308/data/mysql.log
log_bin=/data/mysql/3308/mysql-bin

[root@db01 /data]# vim mysql/3309/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql.sock
server_id=9
port=3309
log_error=/data/mysql/3309/data/mysql.log
log_bin=/data/mysql/3309/mysql-bin

step3 初始化多套数据库

mv /etc/my.cnf /etc/my.cnf.bak

[root@db01 /data]# ls /etc/my.cnf
ls: cannot access /etc/my.cnf: No such file or directory

[root@db01 /data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql/ --datadir=/data/mysql/3307/data/
2019-08-16T02:20:31.747598Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-16T02:20:32.590501Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-16T02:20:32.694287Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-16T02:20:32.855627Z 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: 6ca5e638-bfcc-11e9-a36e-000c2938d306.
2019-08-16T02:20:32.856995Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-16T02:20:32.858556Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /data]# ls mysql/3307/data/
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  sys

[root@db01 /data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql/ --datadir=/data/mysql/3308/data/
2019-08-16T02:26:57.111620Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-16T02:26:57.668462Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-16T02:26:57.792530Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-16T02:26:57.852711Z 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: 521fc681-bfcd-11e9-acec-000c2938d306.
2019-08-16T02:26:57.855230Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-16T02:26:57.858155Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /data]# ls mysql/3308/data/
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  sys

[root@db01 /data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql/ --datadir=/data/mysql/3309/data/
2019-08-16T02:28:06.450250Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-16T02:28:07.561568Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-16T02:28:07.886044Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-16T02:28:07.931377Z 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: 7be4f306-bfcd-11e9-a13c-000c2938d306.
2019-08-16T02:28:07.932271Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-16T02:28:07.934305Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@db01 /data]# ls mysql/3309/data/
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  sys

step4 systemd管理多实例

[root@db01 /data]# cd /etc/systemd/system/

[root@db01 /etc/systemd/system]# cp mysqld.service mysqld3307.service 
[root@db01 /etc/systemd/system]# vim mysqld3307.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=/application/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
LimitNOFILE = 5000

[root@db01 /etc/systemd/system]# cp mysqld.service mysqld3308.service 
[root@db01 /etc/systemd/system]# vim mysqld3308.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=/application/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
LimitNOFILE = 5000

[root@db01 /etc/systemd/system]# cp mysqld.service mysqld3309.service
[root@db01 /etc/systemd/system]# vim mysqld3309.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=/application/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf
LimitNOFILE = 5000

step5 授权

[root@db01 /etc/systemd/system]# ll -d /application/mysql/ /data/mysql/330{7,8,9}/data
drwxr-xr-x 10 mysql mysql 141 Aug 13 16:08 /application/mysql/
drwxr-xr-x  5 mysql mysql 147 Aug 16 10:20 /data/mysql/3307/data
drwxr-xr-x  5 mysql mysql 147 Aug 16 10:26 /data/mysql/3308/data
drwxr-xr-x  5 mysql mysql 147 Aug 16 10:28 /data/mysql/3309/data

step6 多实例启动

[root@db01 /etc/systemd/system]# systemctl start mysqld3307.service 
[root@db01 /etc/systemd/system]# systemctl start mysqld3308.service 
[root@db01 /etc/systemd/system]# systemctl start mysqld3309.service 
[root@db01 /etc/systemd/system]# ps -ef | grep mysqld
mysql      8693      1  5 10:41 ?        00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
mysql      8727      1 21 10:41 ?        00:00:02 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
mysql      8761      1 36 10:41 ?        00:00:02 /application/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf
root       8790   7333  0 10:41 pts/0    00:00:00 grep --color=auto mysqld

step7 验证多实例

tcp    LISTEN     0      80       :::3307                 :::*                   users:(("mysqld",pid=8693,fd=22))
tcp    LISTEN     0      80       :::3308                 :::*                   users:(("mysqld",pid=8727,fd=22))
tcp    LISTEN     0      80       :::3309                 :::*                   users:(("mysqld",pid=8761,fd=22))
[root@db01 /etc/systemd/system]# mysql -S /data/mysql/3307/mysql.sock -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@db01 /etc/systemd/system]# mysql -S /data/mysql/3308/mysql.sock -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@db01 /etc/systemd/system]# mysql -S /data/mysql/3309/mysql.sock -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

6.重置超级管理员密码

step1 停止数据库服务

systemctl stop mysqld

[root@db01 ~]# ss -lntup|grep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=7961,fd=27))
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# ss -lntup|grep 3306

step2 临时启动数据库

mysqld_safe --skip-grant-tables --skip-networking &

[root@db01 ~]# mysqld_safe --defaults-file=/data/mysql/3306/my.cnf --skip-grant-tables --skip-networking &
[1] 9178
[root@db01 ~]# 2019-08-16T02:51:59.621734Z mysqld_safe Logging to '/data/mysql/3306/data/mysql.log'.
2019-08-16T02:51:59.648872Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/3306/data

[root@db01 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

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

--skip-grant-tables #跳过MySQL授权表 --skip-networking #跳过网络启动，不启动端口；(防止非法用户通过TCP/IP登录数据库)

step3 手动开启授权表

flush privileges;

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

step4 修改超级管理员密码

alter user root@'localhost' identified by '新密码';

mysql> alter user root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

step5 关闭临时数据库

mysqladmin shutdown -u用户 -p密码

[root@db01 ~]# mysqladmin shutdown -uroot -p123
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2019-08-16T03:08:14.716189Z mysqld_safe mysqld from pid file /data/mysql/3306/data/db01.pid ended
[1]-  Done                    mysqld_safe --defaults-file=/data/mysql/3306/my.cnf --skip-grant-tables --skip-networking

step6 启动数据库

systemctl start mysqld

[root@db01 ~]# mysql -uroot -p123
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 2
Server version: 5.7.26 MySQL Community Server (GPL)

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

7.MySQL本地升级(实际环境中一般使用迁移升级，本地升级风险较大)

mysql-8.0.17-二进制安装包

step1 备份数据库

mysqldump -u用户名 -p密码 -A>备份文件.sql

[root@db01 ~]# mysqldump -uroot -p123 -A >/tmp/backup.sql

step2 升级软件
将高版本软件解压到新的目录；

[root@db01 ~]# cd /application/
[root@db01 /application]# rz -E
rz waiting to receive.
[root@db01 /application]# ls
mysql  mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz
[root@db01 /application]# tar xf mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz [root@db01 /application]# ls
mysql
mysql-8.0.17-linux-glibc2.12-x86_64
mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz
[root@db01 /application]# mv mysql-8.0.17-linux-glibc2.12-x86_64 mysql8
[root@db01 /application]# ls
mysql  mysql8  mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

step3 修改配置文件及环境变量

[root@db01 /application]# tail -1 /etc/profile
export PATH=/application/mysql8/bin:$PATH
[root@db01 /application]# source /etc/profile
[root@db01 /application]# mv /etc/init.d/mysqld /etc/init.d/mysqld.bak
[root@db01 /application]# mv mysql8/support-files/mysql.server /etc/init.d/mysqld
[root@db01 /application]# vim /data/mysql/3306/my.cnf 
[mysqld]
basedir=/application/mysql8
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/data/mysql.log
[mysql]
socket=/tmp/mysql.sock
[root@db01 /application]# vim /etc/systemd/system/mysqld.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=/application/mysql8/bin/mysqld --defaults-file=/data/mysql/3306/my
.cnf
LimitNOFILE = 5000

step4 授权

[root@db01 /application]# ll /application/mysql8
total 452
drwxr-xr-x  2 mysql mysql   4096 Jun 27 06:35 bin
drwxr-xr-x  2 mysql mysql     86 Jun 27 06:35 docs
drwxr-xr-x  3 mysql mysql    261 Jun 27 06:35 include
drwxr-xr-x  6 mysql mysql   4096 Jun 27 06:35 lib
-rw-r--r--  1 mysql mysql 336955 Jun 25 18:23 LICENSE
-rw-r--r--  1 mysql mysql 101805 Jun 25 18:23 LICENSE.router
drwxr-xr-x  4 mysql mysql     30 Jun 27 06:35 man
-rw-r--r--  1 mysql mysql    687 Jun 25 18:23 README
-rw-r--r--  1 mysql mysql    700 Jun 25 18:23 README.router
drwxrwxr-x  2 mysql mysql      6 Jun 27 06:35 run
drwxr-xr-x 28 mysql mysql   4096 Jun 27 06:35 share
drwxr-xr-x  2 mysql mysql     77 Jun 27 06:35 support-files
drwxr-xr-x  3 mysql mysql     17 Jun 27 06:35 var

step5 启动数据库

systemctl start mysqld

[root@db01 /application]# systemctl start mysqld.service
[root@db01 /application]# mysql -V
mysql  Ver 8.0.17 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
[root@db01 /application]# mysql -uroot -p123
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 12
Server version: 8.0.17 MySQL Community Server - GPL

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

step6 升级数据(5.6->5.7)

mysql_upgrage

MySQL本地版本回滚

[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# cd /data/mysql/3306/data/
[root@db01 /data/mysql/3306/data]# rm -rf *
[root@db01 /data/mysql/3306/data]# ls
[root@db01 /data/mysql/3306/data]# vim ../my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/data/mysql.log
[mysql]
socket=/tmp/mysql.sock

[root@db01 /data/mysql/3306/data]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@db01 /data/mysql/3306/data]# source /etc/profile

[root@db01 /data/mysql/3306/data]# rm -f /etc/init.d/mysqld
[root@db01 /application/mysql/support-files]# cp mysql.server /etc/init.d/mysqld

[root@db01 /application/mysql/support-files]# vim /etc/systemd/system/mysqld.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=/application/mysql/bin/mysqld --defaults-file=/data/mysql/3306/my.cnf
LimitNOFILE = 5000

[root@db01 /application/mysql/support-files]# cp /tmp/backup_dir/* /data/mysql/3306/data/ -r
[root@db01 /application/mysql/support-files]# ls /data/mysql/3306/data/
auto.cnf  ib_buffer_pool  ib_logfile0  mysql      performance_schema  world
db01.err  ibdata1         ib_logfile1  mysql.log  sys

[root@db01 /data/mysql/3306]# chown -R mysql. data/*

[root@db01 /application/mysql/support-files]# systemctl start mysqld.service 
[root@db01 /application/mysql/support-files]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@db01 /data/mysql/3306]# mysql -uroot -p123
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 2
Server version: 5.7.26 MySQL Community Server (GPL)

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

附：思维导图