MySQL基本管理

数据库基本管理

一 MySQL启动关闭流程

mysql是一个典型的C/S服务结构

1.mysql自带的客户端程序（/service/mysql/bin）
	mysql
	mysqladmin
	mysqldump

2.mysqld一个二进制程序，后台的守护进程
	单进程
	多线程

1.1 启动数据库

1、/etc/init.d/mysqld start ---> mysql.server ---> mysqld_safe ---> mysqld
2、systemctl start mysql ---> mysqld_safe ---> mysqld
3、mysqld_safe --defaults-file=/etc/my.cnf ---> mysqld_safe ---> mysqld

1.2 停止数据库

/etc/init.d/mysqld stop
systemctl stop mysqld

# 如果是自己用命令如mysqld_safe启动的mysql，可以用mysqladmin来关闭
mysqladmin -uroot -p123 shutdown  

# 无论如何都不建议使用
kill -9 pid
killall mysqld
pkill mysqld

# 直接kill数据库可能会出现问题：
1.如果在业务繁忙的情况下，数据库不会释放pid和sock文件
2.号称可以达到和Oracle一样的安全性，但是并不能100%达到
3.在业务繁忙的情况下，丢数据（补救措施，高可用）

二 MySQL设置密码

初始状态下，管理员root，密码为空，默认只允许从本机登录localhost

#  设置初始密码 由于原密码为空，因此-p可以不用
[root@jason ~]# mysqladmin -uroot password "123"    

# 修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码
[root@jason ~]# mysqladmin -uroot -p"123" password "456"        

三 mysql连接工具与方式

3.1 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

使用密码登录

#1.正确登录命令
[root@db01 scripts]# mysql -uroot -p123
[root@db01 scripts]# mysql -u root -p123
[root@jason ~]# mysql  # 以root用户登录本机，密码为空

#2.错误登录命令
[root@db01 scripts]# 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

3.2 第三方的连接工具

1.sqlyog
2.navicat
#注意：数据库是没有外网的，想连接可以使用通道
3.应用程序连接MySQL
#注意：需要加载对应语言程序的API

四 配置文件

4.1 mysql配置文件的作用

1.影响服务端的启动（mysqld）
2.影响客户端的连接

4.2 配置文件介绍

强调：配置文件中的注释可以有中文，但是配置项中不能出现中文

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=jason
password=4573


#！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准

4.3 统一字符编码

xshell ---> linux ---> mysql数据库

系统编码

#1.命令行临时修改 : 
LANG=us_EN.UTF-8
LANG=zh_CN.UTF-8

临时修改报错命令为应文 : LANG=us_EN.UTF-8
#2.修改系统字符集
Centos6 永久修改：[root@db03 ~]# vim /etc/sysconfig/i18n
Centos7 永久修改：[root@db03 ~]# vim /etc/locale.conf

修改mysql数据库默认字符集

• 方法1：cmake时候指定

  cmake .
  -DDEFAULT_CHARSET=UTF8 \
  -DDEFAULT_COLLATION=UTF8_GENERAL_CI
  

• 方法2：配置文件指定

  #1.修改配置文件
  [mysqld]
  character-set-server=utf8
  collation-server=utf8_general_ci
  
  [client]
  default-character-set=utf8
  [mysql]
  default-character-set=utf8
  
  #2. 重启服务
  #3. 查看修改结果：
  \s
  show variables like '%char%';
  

  注解：

  校验规则: utf8_general_ci
  	1）ci：大小写不敏感
  	2）cs或bin：大小写敏感
  
  #一个表里面不可能出现同名不同大小写的字段
  
  #查看校验规则
  mysql> show collation；
  

  注意：最好在[mysqld]下也加上下述配置，规范一些

  # 反向解析
  skip-name-resolve
  
  # 安装目录和数据目录
  basedir= 安装目录
  datadir= 数据目录
  

  mysql数据库怎么存入emoji表情

  # 第一步：设置数据库字符编码为utf8mb4_general_ci  设置相应表字段字符编码为utf8mb4_general_ci
  
  # 第二步：设置my.cnf增加以下配置信息
  [client]
  default-character-set = utf8mb4
  
  [mysql]
  default-character-set = utf8mb4
   
  [mysqld]
  character-set-client-handshake = FALSE
  character-set-server = utf8mb4
  collation-server = utf8mb4_unicode_ci
  init_connect='SET NAMES utf8mb4'
  
  # 第三步：重启MySQL 保存表情数据查看数据库就可以看到数据库中有表情字符的存在。
  

如果没有配置默认编码，也可以在建库指定，

#1、创建数据库指定字符集和校验规则
create database if not exists db1 charset utf8 collate utf8_general_ci;

#2、修改
alter database db1 charset utf8mb4 collate utf8mb4_general_ci;

创建的新表默认继承其所在库的字符编码，除非你自己指定

# 建表时指定编码
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT comment 'id',
`name` char(20) NOT NULL comment '名字',
PRIMARY KEY (`id`) comment '主键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


# 改表的字符集:
alter table test charset utf8;

批量修改库和表的字符编码

#1、批量修改字符集，30个库，300个表
1.我们先创建10个库
for n in `seq 10`;do mysql -uroot -p123 -e "create database db$n charset gbk;";done

#2、库里创建表
for n in `seq 10`;do mysql -uroot -p123 -e "use db$n;create table user$n(id int,name varchar(10)) charset gbk";done


for n in `seq 10`;do mysql -S /data/3309/mysql.sock -uroot -p3309 -e "use db$n;create table user$n(id int,name varchar(10)) charset gbk";done

#3、怎么批量修改数据库的字符集？？？？
对于新创建的表会继承库的字符编码，但对于已经存在的老表，修改其所在库的字符编码，表的字符编码是不会修改的
alter database db1 charset utf8;
show create database db1;
show create table user1;

修改方式
for n in `seq 10`;do mysql -uroot -p123 -e "alter database db$n charset utf8;use db$n;alter table user$n charset utf8";done

4.4 设置不区分大小写

注意：开发时，数据库的库名和表名都要小写，因为很多研发使用第三方工具连接数据库，而windows不区分大小写，linux区分大小写，很容易读写错数据库；

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

可以修改配置文件来让数据库不区分大小写：

[root@jason ~]# vim /etc/my.cnf
[mysqld]
lower_case_table_names=1
[root@jason ~]# systemctl restart mysql

五 MySQL实例初始化配置（了解）

5.1 初始化配置文件的作用

1.预编译：cmake去指定，硬编码到程序当中去

2.在命令行设定启动初始化配置
--skip-grant-tables 
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my,cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err

3.初始化配置文件（/etc/my.cnf）

5.2 配置文件读取顺序

5.2.1 读取顺序

/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
defaults-extra-file （类似include）
~/.my.cnf

5.2.2 生效顺序

~/.my.cnf
defaults-extra-file （类似include）
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
/etc/mysql/my.cnf
/etc/my.cnf

5.2.3 生效顺序验证

#配置/etc/my.cnf
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1

#配置/etc/mysql/my.cnf
[root@db01 ~]# mkdir /etc/mysql
[root@db01 ~]# vim /etc/mysql/my.cnf
[mysqld]
server_id=2

#配置$basedir/my.cnf
[root@db01 ~]# vim /service/mysql/my.cnf 
[mysqld]
server_id=3

#配置~/my.cnf
[root@db01 ~]# vim ~/.my.cnf
[mysqld]
server_id=4

#重启数据库（注意这里不能使用systemctl restart mysql重启）
[root@db01 ~]# systemctl stop mysql
[root@db01 ~]# /etc/init.d/mysqld start

#查看server_id
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

5.3 思考参数执行优先级

#cmake：
socket=/service/mysql/tmp/mysql.sock

#命令行：
--socket=/tmp/mysql.sock

#配置文件：
vim /etc/my.cnf
[mysqld]
socket=/opt/mysql.sock

#default参数：
--defaults-file=/tmp/a.txt
vim /tmp/a.txt
[mysqld]
socket=/tmp/test.sock

#启动测试
mysql_safe --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock

优先级测试结论：
1、命令行
2、defaults-file
3、配置文件
4、预编译

命令行 >> defaults-file >> ~/.my.cnf >> defaults-extra-file >> $basedir/my.cnf >> /etc/mysql/my.cnf >> /etc/my.cnf >> cmake

六 mysql相关命令

6.1 mysql连接后命令行快捷命令

\c：中断当前输入语句
	\r：重新连接到服务器
	\d：设置语句sql结束符    mysql> \d ]
	\e：编辑命令    输出输入的内容 echo
	\p：打印当前命令并执行
\G：垂直显示结果
\q：退出mysql    等于 quit exit
	\g：表示结束    等于 ; 的作用
\h：显示此帮助    等于 help    #help还可以查看命令语法 help create database;
	\t：不写入outfile
\T：将所有内容附加到给定的输出文件中（只支持本次会话）        \T /tmp/a.log
	#\n：禁用寻呼机，打印到标准输出。
	#\P：设置寻呼机[到寻呼机]。通过寻呼机打印查询结果。
\R：更改mysql提示符    \R mysql>>> 
\.：执行一个sql文件    等于 source
\s：从服务器获取状态信息    等于 status
\u：切换数据库    等于 use  #查看当前所在数据库select database();
	\C：切换到另一个字符集    一般不使用
	\W：在每个语句后显示警告
	\w：不在每个语句后显示警告

6.2 help命令

mysql> help
mysql> help contents    help 用法
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show

6.3 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 jason
[root@db01 ~]# mysql -uroot -p123456 -e 'create database jason'

4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123456 drop jason
Do you really want to drop the 'jason' database [y/N] y
Database "jason" 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

七 初始sql语句

有了mysql这个数据库软件，就可以将程序员从对数据的管理中解脱出来，专注于对程序逻辑的编写

mysql服务端软件即mysqld帮我们管理好文件夹以及文件，前提是作为使用者的我们，需要下载mysql的客户端，或者其他模块来连接到mysqld，然后使用mysql软件规定的语法格式去提交自己命令，实现对文件夹或文件的管理。该语法即sql（Structured Query Language 即结构化查询语言）

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。
SQL语言分为：
DDL：Data definition Language 数据定义语言（CREATE、DROP、ALTER）
DCL：Data control Language 数据控制语言（GRANT，REVOKE，ROLLBACK，COMMIT）
DML：Data Manipulation Language 数据操作语言（INSERT，UPDATE，DELETE）
DQL：Data Query Language 数据查询语言（SELECT）

库、表、记录的增删改查

#1. 操作文件夹
增：create database db1 charset utf8;
    数据库已存在会报错，使用IF NOT EXISTS
    create database if not exists db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;


#2. 操作文件
先切换到文件夹下：use db1
增：create table t1(id int,name char);
查：show tables
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;
    

#3. 操作文件中的内容/记录
增：insert into t1 values(1,'jason1'),(2,'jason2'),(3,'jason3');
查：select * from t1;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;

清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找