MySQL服务
2019-08-15 本文已影响99人
Aspen_Han
一、概述
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安装
- 二进制安装(常用)
- 源码包编译安装
MySQL程序结构.jpg
二、安装部署
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.软件上传安装
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.修改系统环境变量
vim /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
vim /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
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=/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
用户管理
- 设置/修改超级管理员密码(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
- 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 '密码';
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)
权限基本管理
- 授权-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 为其他用户授权权限，一般是超级管理员的权限
权限范围
*.* 代表全库范围
数据库.* 代表单个库下所有表
数据库.表 代表单个库下单个表文件
- 回收权限
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)
- 查看用户权限-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的其他启动方式(命令行初始化配置)
MySQL多种启动方式.jpg
日常启动关闭方式
service mysqld start|stop|status|restartsystemctl 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>
附：思维导图
MySQL.jpg
