Ubuntu 24.04 安装与配置 MySQL 指南

1. 安装 MySQL 服务

• 使用 apt 自动安装 MySQL-8.0

sudo apt install mysql-server mysql-client

• 安装完毕后检查版本和服务状态

mysql --version
systemctl status mysql

• 检查端口是否正在监听

sudo ss -ltpn | grep 3306

• 让 MySQL 服务随操作系统一起启动

sudo systemctl enable mysql

• 安全加固

sudo mysql_secure_installation

• 重新启动 MySQL 服务

sudo systemctl restart mysql

2. 登录与设置

• 以 root 用户免密登录到 MySQL

sudo mysql -u root

• 检查数据库实例

show databases;

• 编码设置

show variables like '%colla%';
show variables like '%charac%';

• 数据文件存放处

show variables like 'datadir';

• 检查 MySQL 的时区与当前时间

SELECT @@global.time_zone, @@session.time_zone;
select now();

• 调整时区设置（设置为中国时区）

timedatectl list-timezones
timedatectl set-timezone Asia/Chongqing

3. MySQL 功能验证

• 创建数据库实例、新用户并授权

CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'testdbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'TestDbPasswd1+';
GRANT ALL PRIVILEGES ON testdb.* TO 'testdbuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

• 以新用户登录

mysql -u testdbuser -p

• 创建表，插入数据

use testdb;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE='utf8mb4_unicode_ci';
INSERT INTO t1 (`name`,`code`) VALUES('NameA','CodeA');
INSERT INTO t1 (`name`,`code`) VALUES('NameB','CodeB');
INSERT INTO t1 (`name`,`code`) VALUES('NameC','CodeC');
INSERT INTO t1 (`name`,`code`) VALUES('NameD','CodeD');
COMMIT;

• 查询数据

SELECT * FROM t1;

• 退出

EXIT;

4. MySQL 远程连接

• 修改 MySQL 配置文件，允许远程连接

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

将 bind-address 改为 0.0.0.0。

• 重启 MySQL 服务生效

sudo systemctl restart mysql

• 创建可远程访问的用户

CREATE USER 'remoteuser'@'%' IDENTIFIED WITH mysql_native_password BY 'RemotePasswd1+';
GRANT ALL PRIVILEGES ON testdb.* TO 'remoteuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

5. Sakila 样例数据库

• 创建文件夹并进入

mkdir sakila
cd sakila

• 下载压缩包

wget https://downloads.mysql.com/docs/sakila-db.tar.gz

• 解压缩

tar zxvf ./sakila-db.tar.gz
cd sakila-db

• 导入到 MySQL 数据库

sudo mysql -u root source /home/hadoop/sakila/sakila-db/sakila-schema.sql source /home/hadoop/sakila/sakila-db/sakila-data.sql show databases;

• 客户端连接权限配置

GRANT ALL PRIVILEGES ON sakila.* TO 'remoteuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

6. 练习任务

• 创建学生成绩表格并插入数据

mysql -h localhost -u testdbuser -p

show databases;
use testdb;
CREATE TABLE tb_student (
  `NAME` varchar(100) NOT NULL,
  `ENGLISH` FLOAT DEFAULT 0 NULL,
  `MATH` FLOAT DEFAULT 0 NULL,
  `COMPUTER` FLOAT DEFAULT 0 NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO tb_student (NAME, ENGLISH, MATH, COMPUTER) VALUES ('zhangsan',69, 86, 77);
INSERT INTO tb_student (NAME, ENGLISH, MATH, COMPUTER) VALUES ('lisi', 55,100, 88);
SELECT * FROM tb_student;
SELECT NAME, COMPUTER FROM tb_student WHERE NAME='zhangsan';
UPDATE tb_student SET MATH=95 WHERE NAME='lisi';