Linux中mysql的安装、远程访问、基础操作、文件导入

cheet card

安装启动操作
1.安装mysql命令 ：$ sudo apt-get install -y mysql-server
2.查看mysql的版本命令（注意-V是大写，不然会出现如下错误）：$ mysql -V
3.启动mysql命令(关闭，重启等只需将start换成stop,restart等即可)：$sudo service mysql start
4.登录mysql命令为：$ mysql -u用户名 -p密码
5.连接远程数据库：$ mysql -h  -P  -u -p

数据库操作
1.查看数据库：> show databases; （注意分号“；”不要落下）
2.新建一个数据库命令：> create database 数据库名称;
   删除一个数据库命令：> drop database 数据库名称;
3.使用某个数据库：> use 数据库名称;

表操作
1.查看表命令：> show tables;
2.建立一个新表：> create table 表名 （字段参数）; 或 >create table if not exists 表名（字段参数）；
   删除一个旧表：> drop table 表名； 或 >drop table if exists 表名；
3.查看表结构：> desc 表名称; 或 >show columns from 表名称;
4.对表数据的操作：
   增：>insert into 表名称 (字段名1，字段名2，字段名3......) values(字段名1的值，字段名2的值，字段名3的值......);
   删：>delete from 表名称 where 表达式;
   改：>update 表名称 set 字段名=“新值” where 表达式；
   查：>select 字段名1,字段名2,字段名3..... from 表名称;
5.增加字段：>alter table 表名称 add 字段名 数据类型 其它; (其它包括默认初始值的设定等等)
6.删除字段：>alter table 表名称 drop 字段名;

用户相关操作
注：以下命令均需先以root身份登录mysql：mysql -uroot -p
1.添加新用户
（1）创建新用户：> insert into mysql.user(Host,User,Password) values("localhost","user1",password("password1"));
（2）为用户分配权限：
            设置用户可以在本地访问mysql：grant all privileges on *.* to username@localhost identified by "password" ;
            设置用户只能访问指定数据库：grant all privileges on 数据库名.* to username@localhost identified by "password" ;
（3）刷新系统权限表：>flush privileges;
2.查看MySql当前所有的用户：>SELECT DISTINCT User FROM mysql.user;
3.删除用户及其数据字典中包含的数据：>drop user 'xbb'@'localhost';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

1. 安装

1. 使用root账号安装mysql

# 下载
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
# 检查mariaDB是否被覆盖
rpm -qa |grep mariadb
rpm -e --nodeps mariadb-libs

# 解压文件及安装
tar -xvf mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
rpm -ivh mysql-community-common-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.28-1.el7.x86_64.rpm # libaio缺少,需要安装
		rpm -qa | grep libaio
		yum -y install libaio
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2. 启动mysql并创建root、管理员两个账号

## 初始化
mysqld --initialize --console

## 启动mysql
systemctl start mysqld

# 查看root临时密码
cat /var/log/mysqld.log |grep localhost
1
2
3
4
5
6
7
8

  在linux界面使用mysql -u root -p，输入root临时密码后进入mysql语句。

# 修改密码
alter user 'root'@'localhost' identified by 'Root_root1'

# 开放 root 账户远程登录
## 1.进入数据库
	use mysql

## 2.修改user表,root账户可在任意IP登录
	update user set host='%' where user='root';  
	update user set host='localhost' where user='root'; # 恢复仅本地连接	

## 3.创建新用户guanliyuan,可在任意IP登录
	create user 'guanliyuan'@'%' identified with mysql_native_password by '!guanliyuaN4';
	
## 4.给用户guanliyuan可访问所有数据库，并拥有与root一样的增删改查权力
	grant all privileges on *.* to 'guanliyuan'@'%' with grant option;

## 5.创建账户张三,仅对所有数据库有select权力,在任意IP可登录
	CREATE USER zhangsan IDENTIFIED BY '!zhangsaN2024';
	SHOW GRANTS FOR zhangsan;
	GRANT SELECT on *.* TO 'zhangsan'@'%' WITH GRANT OPTION;
	FLUSH PRIVILEGES;
	
    # 以下为完整示例
    创建一个使用密码作为认证方式的账号
    CREATE USER 'kalacloud-remote'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
    根据自己的需要，给你用于远程访问的账号赋予权限
    GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'kalacloud-remote'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3. 基础操作

3.1 数据库的查看、创建、修改、删除

# 创建数据库
create database demo;

# 查看mysql的数据库
show databases;

# 查看数据库demo信息
show create database demo;

# 创建数据库时为其指定字符集，语法如下：
	# mysql常用编码方式有 utf8 utf16 gbk unicode iso latin ascii
create database 数据库名 character set 编码方式;

# 选择使用的数据库
use 数据库名;

# 查看当前正在使用的数据库;
select database();

alter database demo character set gbk
drop database hello
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.2 mysql的数据类型

  二进制、时期时间、字符串、整数类型、小数类型;
  详细可看参考文件MySQL的字段类型，该怎么选？

3.3 数据表的基本操作

# 创建表格
create table 表名(
		字段名 数据类型 [(长度)] [约束条件], 
		字段名 数据类型 [(长度)] [约束条件], 
		字段名 数据类型 [(长度)] [约束条件], 
		.....
);

create table student(
	-> id  int unsigned,
	-> name varchar(10),
	-> age tinyint unsigned,
	-> sex enum('男', '女'),
	-> birthday date,
	-> hobby set('唱歌', '表演', '编程')
	);

# 展示表格式化后结果,更好看
show create table student\G
# 查看表格字段信息
desc/describe student
# 删除表格;
drop table OmicsCNGene_melt;

# 修改数据表名
alter table 旧表名 rename to 新表名

# 修改字段名,注意：新字段的数据类型不能省略！
alter table 表名 change 旧字段名 新字段名 新字段的数据类型;

# 修改字段类型
alter table 表名 modify 字段名 新的数据类型

# 添加字段
alter table 表名 add 新字段名 新字段数据类型 [first|after 已经存在的字段名]

# 修改字段位置
alter table 表名 modify 字段名1 数据类型 [first|after 字段名2]

# 删除字段
alter table 表名 drop 字段名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

3.4 数据表结构的修改

  极少用，表一旦建立，最好不要再改变，否则现有程序会被严重影响，尤其是表中已有数据的时候
  一定要在设立之初把表格一次性设置好。
  比如，修改字段名、修改字段类型、添加字段、修改字段位置，具体可见3.3、3.5内容

3.5 表中数据的增、删、改

# 插入数据
insert into 表名 (字段名 字段名, 字段名) values (值, 值, 值, 值, 值)
insert into student values (1, 'zhangsan', 18, '男', '1990-10-08', '唱歌,编程')

insert into 表名 set 字段名=值(字段名=值, ...)
insert into student set id=3, name='lisi', age=19, sex='男'

# 修改记录
update 表名 set 字段名=值[字段名=值, ...] [where 条件表达式]
update student set age=23,sex='女' where name = 'zhangsan'

# 删除记录
delete from 表名 [where 条件表达式]
dalete from student where name = 'zhangsan'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.6 表中字段值的约束

primary key 主键约束
not null 非空约束
unique  唯一约束
check(条件语句) 检查约束
foreign key(字段名) references 表名(字段名)
default <默认值>  默认值约束
1
2
3
4
5
6

4. 远程访问设置

4.1 阿里云、腾讯云开放安全组

4.2 集群mysql防火墙打开

# 查看防火墙
systemctl status firewalld
# 查看3306端口状态
firewall-cmd --zone=public --query-port=3306/tcp
# 如果是no，表示关闭，打开3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
# 防火墙重载
firewall-cmd --reload
# 再次查看3306状态(yes)
firewall-cmd --zone=public --query-port=3306/tcp
1
2
3
4
5
6
7
8
9
10

5. windows访问

5.1 登录

  如何登录网上有很多教程，如果遇到报错，可针对性搜索。
  某一类报错，可参考DBeaver连接时报错：Public Key Retrieval is not allowed Public Key Retrieval is not allowed

5.2 创建临时数据库TEMP

  利用3.3介绍是知识，创建一个数据库TEMP，刷新后能在windows下看到。

5.3 待导入文件前10行导入TEMP数据库

（1）正常导入，从DDL部分获得正确的表格创建语句；
（2）报错，修改”列“中报错字段的数据类型，右下角保存改变。重新导入测试数据，从DDL部分获得正确的表格创建语句；

6. 文件导入

6.1 更改指定的导入文件存放目录

show variables like 'secure_file_priv';
 
secure-file-priv的值有3种情况：
secure_file_priv=null      -- 默认值为null，限制mysql，禁止导入导出文件
secure_file_priv=""        -- 表示不限制路径，允许mysql 导入导出文件
secure_file_priv="D:/path" -- 指定路径，表示只允许在该路径目录下导入导出文件(这里的路径值可以自己设置)
1
2
3
4
5
6

  使用root账号在/etc/my.conf文件中修改后，重启数据库生效

6.2 更改mysql数据库datadir存放目录

  使用show variables like '%datadir%';，查看目前的存放路径。
  使用root账号在/etc/my.conf文件中修改后，把原路径下的Data文件夹整个复制到目标路径下。重启数据库生效。

6.3 导入文件

(1) sql语句切入到目标database下;
(2) 使用5.3中得到的create语句创建table target；
(3) 使用load data infile导入文件
load data infile '/user_mount/abs_path/Media.csv' into table Media character set utf8 fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\n' IGNORE 1 LINES;

参考文件

(1) CentOS / Linux 安装MySQL（超简单详细）
(2) MySQL数据库SQL语言入门，精讲精练
(3) MySQL基础知识 - 账号和权限管理
(4) MySQL的字段类型，该怎么选？
(5) 最全服务器安装MySQL和远程连接教程（含常见问题解决）
(6) DBeaver连接时报错：Public Key Retrieval is not allowed Public Key Retrieval is not allowed
(7) ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot exec
(8) MySQL数据存储路径如何修改
(9) Linux下mysql导入csv文件
(10) 菜鸟mysql四分钟导入千万级别的数据