MySQL数据库基本管理
目录
MySQL数据库基本管理
Mysql是一个典型的C/S服务结构，它自带客户端，例如：mysql、mysqladmin和mysqldump等。
1、设置Mysql密码
初始状态下，管理员root的密码为空或者是随机生成的，而且只允许本机登录。一般情况下，我们安装好Mysql之后的第一件事就是修改默认的密码。
# 设置初始密码 由于原密码为空，因此-p可以不用
[root@localhost ~]# mysqladmin -uroot password "123"
# 修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码
[root@localhost ~]# mysqladmin -uroot -p"123" password "456"
2、MySQL连接工具与方式
Mysql的连接方式和工具有很多种，这里我们详细讲解一下。
- MySQL自带的连接命令工具
Mysql自带的连接工具叫mysql,下面我们来详细介绍一下mysql连接工具。
mysql
#常见的特定于客户机的连接选项：
-u: 指定用户 mysql -uroot
-p: 指定密码 mysql -uroot -p567
-h: 指定主机域 mysql -uroot -p567 -h127.0.0.1
-P: 指定端口 mysql -uroot -p567 -h127.0.0.1 -P3307
-S: 指定socket文件 mysql -uroot -p567 -S /tmp/mysql.sock
-e: 指定SQL语句(库外执行SQL语句) mysql -uroot -p567 -e "show databases;"
--protocol: 指定连接方式 mysql --protocol=TCP --protocol=socket
登录Mysql服务器
#1.正确登录命令
[root@localhost ~]# mysql -uroot -p123
[root@localhost ~]# mysql -u root -p123
[root@localhost ~]# mysql # 以root用户登录本机，密码为空
#2.错误登录命令
[root@localhost ~]# mysql -u root -p 123 # -p选项与密码之间不要有空格
连接方式
1.TCP/IP的连接方式
2.套接字连接方式，socket连接
#查看连接方式
mysql> status;
--------------
Connection: Localhost via UNIX socket
3.举例：
3.1.TCP/IP连接，通常带有-h选项的都是TCP/IP链接
mysql -uroot -p -h127.0.0.1 -P 端口号
mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock
3.2.socket连接
mysql -uroot -p123(默认连接方式,socket)
4.注意:
4.1.因为使用TCP/IP连接,需要建立三次握手
4.2.不一定-h都是tcp，-hlocalhost是socket连接
mysql -uroot -p -hlocalhost
- 第三方工具连接
我们常常再工作中，是不使用Mysql自带的数据库连接工具来连接数据库的，一般情况下我们使用的是第三方数据库连接工具来连接Mysql的。其中我们最常用的数据库连接工具是navicat来连接数据库，这个数据库连接工具的功能非常强大，非常适合用来操作数据库。
# 在链接之前，必须创建远程连接用户
# 设置权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'Oldboy@666' WITH GRANT OPTION;
ALL PRIVILEGES : 所有的权限
*.* ： 正对于所有的库所有的表
root ： 用户名
localhost ： 可以链接的IP（%代表所有的IP）
Oldboy@666 ： 远程链接的密码
# 刷新权限
FLUSH PRIVILEGES;
下面我们介绍一下naticat数据库连接工具的使用。
3、MySQL配置文件
MySQL配置文件的作用是配置MySQL，使MySQL按照我们指定的方式健康运行。
注：配置文件中的注释可以有中文，但是配置项中不能出现中文。
1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效
[mysqld]
;skip-grant-tables
port=3306
character_set_server=utf8
default-storage-engine=innodb
innodb_file_per_table=1
#解压的目录
basedir=E:\mysql-5.7.19-winx64
#data目录
datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据
#2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123
#3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
[mysql]
;port=3306
;default-character-set=utf8
user=root
password=4573
#！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准
4、统一字符集编码
使用过数据库的小伙伴应该清楚，统一字符集编码的必要性非常大，当我们使用统一的字符集编码时数据库查出来的数据才不会错。
- 修改系统字符集编码
#1.命令行临时修改 :
LANG=us_EN.UTF-8
LANG=zh_CN.UTF-8
临时修改报错命令为应文 : LANG=us_EN.UTF-8
#2.修改系统字符集
Centos6 永久修改：[root@localhost ~]# vim /etc/sysconfig/i18n
Centos7 永久修改：[root@localhost ~]# vim /etc/locale.conf
- 编译之前指定
cmake .
-DDEFAULT_CHARSET=UTF8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI
- 配置文件指定
#1.修改配置文件
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#2. 重启服务
#3. 查看修改结果：
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.07 sec)
校验规则: utf8_general_ci
1）ci：大小写不敏感
2）cs或bin：大小写敏感
#一个表里面不可能出现同名不同大小写的字段
#查看校验规则
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
.........
utf8和utf8mb4之间的区别？
utf8不支持emoji表情而utf8mb4支持。
- 通过SQL语句指定字符集编码
#1、创建数据库指定字符集和校验规则
create database db1 charset utf8mb4 collate utf8mb4_general_ci;
5、修改MySQL root密码
无论是初始化之后第一次修改密码，还是日常维护中的修改root密码，修改root密码都是非常有必要的。再企业实际生产环境中，要求隔一段时间都会修改密码。那么具体修改密码的方式有哪些，下面我们来演示一下。
# 第一种修改密码的方式
[root@localhost mysql-5.7.34]# mysql -uroot -p"&LiK&vObh0ew"
[root@localhost mysql-5.7.34]# 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 3
Server version: 5.7.34 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 > grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意：两种重置密码方式有区别。
不知晓root密码的情况下去修改密码
# 1）跳过授权表的方式去修改密码
1、修改配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
# 添加 skip_grant_tables # 跳过授权表
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> UPDATE mysql.user set authentication_string = PASSWORD ("123456") WHERE User = "root" and Host="localhost";
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# vim /etc/my.cnf
# 删除skip_grant_tables # 跳过授权表
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uroot -p"123456"
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 3
Server version: 5.7.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
6、mysqladmin命令
1.修改密码,设置密码:password
[root@db01 ~]# mysqladmin -uroot -p旧密码 password '新密码'
2.关闭MySQL服务:shutdown
[root@db01 ~]# mysqladmin -uroot -p密码 -S socket文件 shutdown
3.库外建库:create
[root@db01 ~]# mysqladmin -uroot -p密码 create egon
[root@db01 ~]# mysql -uroot -p123456 -e 'create database egon'
4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123456 drop egon
Do you really want to drop the 'egon' database [y/N] y
Database "egon" dropped
5.查看配置文件所有的默认参数:variables
[root@db01 ~]# mysqladmin -uroot -p123456 variables
[root@db01 ~]# mysqladmin -uroot -p123456 variables | grep server_id
6.检测MySQL进程是否存活:ping
[root@db01 ~]# mysqladmin -uroot -p123456 ping
7.查看数据库 慢查询,负载信息:status
[root@db01 ~]# mysqladmin -uroot -p123456 status
Uptime MySQL服务器已经运行的秒数
Threads 活跃线程（客户）的数量
Questions 从mysqld启动起来自客户问题的数量 已经发送给服务器的查询的个数
Slow queries 已经超过long_query_time秒的查询数量
Opens mysqld已经打开了多少表
Flush tables flush ..., refresh和reload命令数量
Open tables 现在被打开的表数量
Queries per second avg: 0.046 负载
8.重载授权表,刷新缓存主机:reload，相当于flush privileges
[root@db01 ~]# mysqladmin -uroot -p123456 reload
9.刷新binlog日志
[root@db01 ~]# mysqladmin -uroot -p123456 flush-log
7、系统数据库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
1、TABLES表：提供了关于数据库中的表的信息
mysql> use information_schema
mysql> select * from tables limit 1\G
mysql> select * from tables where TABLE_NAME='city'\G
columns表：提供了关于数据库中表的列的信息
mysql> select * from columns\G
STATISTICS表：表索引的信息
COLLATIONS表：提供校验规则和字符集对应关系
Table_catalog 数据表登记目录
Table_schema 数据表所属的数据库名
Table_name 表名称
Table_type 表类型[system view|base table] 系统表 ，数据表
Engine 使用的数据库引擎[MyISAM|CSV|InnoDB]
Version 版本，默认值10
Row_format 行格式[Compact|Dynamic|Fixed]
Table_rows 表里所存多少行数据
Avg_row_length 平均行长度
Data_length 数据长度
Max_data_length 最大数据长度
Index_length 索引长度
Data_free 空间碎片 刚刚用过的暂时不再使用
Auto_increment 做自增主键的自动增量当前值 可以通过导出再导入数据进行释放
Create_time 表的创建时间
Update_time 表的更新时间
Check_time 表的检查时间
Table_collation 表的字符校验编码集
Checksum 校验和
Create_options 创建选项
Table_comment 表的注释、备注
CREATE TABLE student(gid int primary key COMMENT '列注释') COMMENT='表注释';
information_schema库下的表
