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';