Linux上安装MySQL

简介

  • MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。
  • MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,大大增加了速度并提高了灵活性。
  • MySQL使用标准的SQL数据语言形式。

规划

网址

  • MySQL官网Download

版本

  • OS: CentOS 7.7Ubuntu 18.04
  • MySQL: 5.7.28

部署

CentOS

  • 下载对应系统版本的rpm-bundle包:
1
tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
  • 安装依赖包:
1
yum install -y libaio numactl
  • 卸载mariadb-libs软件包:
1
rpm -e --nodeps $(rpm -qa | grep mariadb)
  • 安装MySQL服务:
1
2
3
4
5
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
  • 创建工作目录:
1
2
mkdir -p /data/mysql/{data,tmp,log}
chown -R mysql:mysql /data/mysql/{data,tmp,log}

Ubuntu

  • 下载对应系统版本的deb-bundle包:
1
tar -xf mysql-server_5.7.28-1ubuntu18.04_amd64.deb-bundle.tar
  • 安装依赖包:
1
apt install -y libaio1 libaio-dev libmecab2 libmecab-dev
  • 安装MySQL服务:
1
2
3
4
5
6
7
8
9
# 弹出窗口,设置管理员密码
dpkg -i mysql-common_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i libmysqlclient20_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i libmysqlclient-dev_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i libmysqld-dev_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i mysql-community-client_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i mysql-client_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i mysql-community-server_5.7.28-1ubuntu18.04_amd64.deb
dpkg -i mysql-server_5.7.28-1ubuntu18.04_amd64.deb
  • 停止MySQL服务:
1
systemctl stop mysql.service
  • 迁移工作目录:
1
2
3
4
mkdir -p /data/mysql/{data,tmp,log}
chown -R mysql:mysql /data/mysql/{data,tmp,log}
cp -a /var/lib/mysql/* /data/mysql/data
rm -rf /var/lib/mysql
  • 更改系统限制:
1
vim /etc/apparmor.d/usr.sbin.mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# Allow pid, socket, socket lock file access
/data/mysql/tmp/mysqld.pid rw,
/data/mysql/tmp/mysql.sock rw,
/data/mysql/tmp/mysql.sock.lock rw,
/run/mysqld/mysqld.pid rw,
/run/mysqld/mysqld.sock rw,
/run/mysqld/mysqld.sock.lock rw,

# Allow data dir access
/data/mysql/data/ r,
/data/mysql/data/** rwk,

# Allow log file access
/data/mysql/log/ r,
/data/mysql/log/** rw,

# Allow tmp file access
/data/mysql/tmp/ r,
/data/mysql/tmp/** rw,
  • 重新加载系统配置:
1
systemctl reload apparmor.service

配置

  • CentOS的配置文件:/etc/my.cnf
  • Ubuntu的配置文件:/etc/mysql/my.cnf
  • 配置文件内容:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[client]
port = 3306
socket = /data/mysql/tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]
port = 3306
datadir = /data/mysql/data
pid-file = /data/mysql/tmp/mysqld.pid
socket = /data/mysql/tmp/mysql.sock
log-error = /data/mysql/log/error.log
character_set_server = utf8mb4
user = mysql
bind-address = *
server-id = 1
symbolic-links=1
connect_timeout = 3600
wait_timeout = 3600
interactive_timeout = 3600
explicit_defaults_for_timestamp = true

测试

维护

CentOS

  • 停止MySQL服务:
1
systemctl stop mysqld.service
  • 重启MySQL服务:
1
systemctl restart mysqld.service
  • 获取MySQL服务状态:
1
systemctl status mysqld.service
  • 启用开机自启MySQL服务:
1
systemctl enable mysqld.service
  • 禁用开机自启MySQL服务:
1
systemctl disable mysqld.service
  • 查看MySQL服务状态:
1
systemctl status mysqld.service

Ubuntu

  • 停止MySQL服务:
1
systemctl stop mysql.service
  • 重启MySQL服务:
1
systemctl restart mysql.service
  • 获取MySQL服务状态:
1
systemctl status mysql.service
  • 启用开机自启MySQL服务:
1
systemctl enable mysql.service
  • 禁用开机自启MySQL服务:
1
systemctl disable mysql.service

测试

  • 获取版本信息:
1
mysql -V
  • 启动MySQL服务:
1
2
3
4
5
# CentOS
systemctl start mysqld.service

# Ubuntu
systemctl start mysql.service
  • 创建软链接:
1
ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock
  • 查看服务端口:
1
2
3
4
5
# CentOS
ss -nlput | grep "mysqld"

# Ubuntu
ss -nlput | grep "mysql"
  • 获取密码:
1
2
3
4
# CentOS:获取随机生成的密码
MySQL_PASSWORD=$(cat /data/mysql/log/error.log | grep "A temporary password" | awk '{print $NF}')

# 在安装服务时,设置了密码
  • 登录数据库:
1
mysql -u root -p"${MySQL_PASSWORD}"
  • 修改密码:
1
2
-- 密码必须满足一定的复杂度, 包含大小写字母、数字、特殊字符,长度满足8位
SET PASSWORD = 'Admin&88';
  • 创建数据库:
1
CREATE DATABASE IF NOT EXISTS account DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
  • 获取所有的数据库:
1
SHOW DATABASES;
  • 切换数据库:
1
USE account;
  • 创建数据表:
1
2
3
4
5
6
CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
nickname varchar(100) NOT NULL,
gender CHAR(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 获取所有的数据表:
1
SHOW TABLES;
  • 插入数据项:
1
2
3
4
INSERT INTO user (nickname, gender) VALUES
('王宇霄', 'M'),
('沈建', 'M'),
('李杰', 'M');
  • 查询数据:
1
SELECT id as 'ID', nickname as '姓名', gender as '性别' FROM user ORDER BY id ASC;

请作者喝瓶肥宅快乐水