MYSQL数据库命令整合(一)

systemctl start mysqld //启动数据库服务 (status查看状态)
systemctl enable mysqld //设置开机自启

/etc/my.cnf //mysql主配置文件
/var/lib/mysql //数据库目录
3306 //默认端口(ss -utnlp | grep :3306)
mysqld //mysql主进程名称
tcp //传输协议
mysql:mysql //进程所以者与所属组(chown -R)
/var /log/mysqld.log //错误文件

grep password /var/log/mysqld.log //第一次启动时,我们需要进日志查看配置随机密码

0或者LOW
validate_password_policy 1 或者 MEDIUM(默认)
2 或者 STRONG
set global validate_password_policy=0; //只验证长度
set global validate_password_length=6; //修改密码长度,默认值是8个字符
alter user user() identified by “123456”; //修改登陆密码

vim /etc/my.cnf //修改配置添加如下内容
[mysqld]
validate_password_policy=0
validate_password_length=6 //永久设置

mysql -h服务器IP或域名 -u用户名 -p密码 数据库名称 //进入数据库某库格式

常用的SQL命令分类:
DDL数据定义语言(create,alter,drop)
DML数据操作语言(insert,update,delete)
DCL数据控制语言(grant,revoke)
DTL 数据事务语言(commit,rollback,savepoint)

show databases; //查看数据库
use mysql; //切换数据库
select database(); //查看当前数据库
create database tts character set utf8mb4; //创建数据库tts并支持中文
drop database tts; //删除数据库tts

create table 数据库名称.数据表名称(
字段名1 数据类型(宽度) 约束条件,
字段名2 数据类型(宽度) 约束条件,); //创建数据表

show character set; //查看所以可用编码

insert into 数据库名称.数据表名称 values (值列表); //添加数据表内容

select * from 库名.表名; //查看数据表所有内容

update 数据库名称.数据表名称 set 字段=值 [where条件] // 更新数据表 不加where就是表中所以更新
delete from 库名.表名 where 字段=‘值’; // 删除满足条件的数据
delete from 库名.表名; //删除表中所有数据
drop table 库名表名; //删除整个数据表

字符类型

char(字符数) //固定长度
varchar(字符数) //可变长度
text/blob //字符最大
数值类型
tinyint //微小整数
smallint //小整数
mediumint //中整数
int //大整数
bigint //极大整数
float //单精度浮点数(M.D)
double //双精度浮点数(M.D) (M大于D)
nsigned //空格后添加表示无负数

时间格式:YYYYmmddhhmmss,或者"YYYY-mm-dd hh:mm:ss"
year() //获取年
month() //获取月
day() //获取日
date() //获取日期
time() //获取时间
now() //获取当前系统日期和时间

默认使用4位数字表示,当只用2位数字负值时:
01_{69自动识别为2001}2069
70_{99自动识别为1970}1999
year年份类型,范围1901-2155
time时间类型,范围HH:MM:SS

枚举类型(选择类型
enum(值1,值2,值3…) //单选项
set(值 1,值 2,值 3…) //多选项

二

常用约束条件:
null //允许为空,默认设置
not null //不允许为空
key //索引类型
default //设置默认值,缺省为NULL

表结构基本用法

add //添加表字段
alter table 库名.表名 add 字段名称 类型(宽度) 约束条件; //添加表字段

modify //修改字段类型
alter table 库名.表名 modify 字段名称 类型(宽度) 约束条件; //first最后添加放到最前面(默认)
//after 字段,放到字段后面

change //修改自定义名称
alter table 库名.表名 change 原字段名称 新字段名称 类型(宽度) 约束条件;

drop //删除表中某字段
alter table 库名.表名 drop 字段名称

rename //修改字段名称
alter table 库名.表名 rename 新的数据表名称

                           键值

INDEX(index) //普通索引 (标志是MUL)
UNIQUE(unique) //唯一索引
FULLTEXT(fulltext) //全文索引
PRIMARYKEY(primary key) //主键 (标志是PRI)
FOREIGNKEY(foreign key) //外键

create index 索引名称 on 数据表(字段名称) //添加索引
drop index 索引名称 on 数据表 //删除索引

alter table 库名.表名 add primary key(字段名称) //已存在的表添加主键
alter table 库名.表名 drop primary key; //删除主键
auto_increment //自动添加属性一般和主键一起使用

foreign key(表A的字段名称) references 表B(字段名称) //创建添加外键(需要两个表)
on update casecade on delete casecade //表B后添加,(同步更新,同步删除)

show create table press.book\G; //查看外键的名称

alter table press.book drop foreign key 外键名称 //删除外键

           定义工作目录

mkdir /myload //创建一个目录
chown mysql /myload //设置为所以者
vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload” //自定义导出导出路径

load data infile “目录名/文件名” into table 库名.表名
fields terminated by “分隔符” lines terminated by “\n” //数据导入

select命令 into outfile “目录名/文件名” fields terminated by “分隔符”
lines terminated by “\n” //数据导出
select 字段1…字段N from 库名.表名; //查询表记录
基本匹配的条件
字段select * from 库名.表名 where之后添加
= //等于(id=3)

           //大于(uid>3)

= //大于或等于(uid>=3)
< //小于(uid<3)
<= //小于或等于(uid<=3)
!= //不等于(uid!=3)

字符比较select * from 库名.表名 where之后添加
= //相等(name=“root”)
!= //不相等(name!=“root”)
is null //空(shell is null)
is not null //非空(shell is not null)

逻辑匹配select * from 库名.表名 where之后添加
or //逻辑或(条件1 and 条件2 and 条件3)
and //逻辑与(条件1 or 条件2 or 条件3)
!或not //逻辑非

范围匹配select * from 库名.表名 where之后添加
in (值列表) //在…里…
not in (值列表) //不在…里…
between数字 and 数字 //在…之间…
distinct 字段名 //去重显示

模糊查询 //select * from 库名.表名 where之后添加
where 字段名 like “通配符” //查询用法
_ //表示一个字符
% //表示0~n个字符

正则表达式 //select * from 库名.表名 where之后添加字段 regexp 后添加以下表达式
^ //以开头
$ //以结尾
[ ] //范围

• //一次以上
  | //或者

四则运算(字段必须是数值类型)

• //加法(uid+gid)

• //减法(uid-gid)

• //乘法(uid*gid)
  / //除法(uid/gid)
  % //取余数(求模)(uid%gid)
  () // 提高优先级(uid+gid)/2

聚集函数select 命令(*) from 库名.表名 //添加以下命令
avg(字段名) //统计字段平均值
sum(字段名) //统计字段之和
min(字段名) //统计字段最小值
max(字段名) //统计字段最大值
count(字段名) //统计字段值个数

查询结果排序select * from 库名.表名
SQL查询 order by 字段名 [asc|desc] //asc升序,desc降序

查询结果分组
SQL查询 group by 字段名 //用法

查询结果过滤
SQL查询 having 条件表达式 //用法(表达式如name=‘mysql’)

限制查询结果显示的行
SQL查询 limit 数字 //用法 显示查询结果前多少条记录
SQL查询 limit 数字1,数字2 //指定范围(0表示第1行,1起始行,2总行数)

                         (四)权限列表:

all //所以权限
suage //无权限
select //查询权限
inset //插入权限
update //更新权限 [update(name,uid) 指定字段]
delete //删除权限
create table //创建表权限
库名:
. //所以库所以表
库名.* //一个库
库名.表名 //一张表

客户端地址
% //所以主机
192.168.4.% //网段内所以主机
192.168.4.1 //1台主机
localhost //数据库本机

grant 权限列表 on 数据库名 to 用户名@“客户端地址” identified by “密码” [ with grant option //用户授权用法 [ with grant option] 可选项,授权后可以添加新用户

登录用户使用
select user(); //显示登录用户名及客户端地址
show grants; //用户显示自身访问权限
show grants for 用户名@“客户端地址” //管理员查看以有授权用户权限
set password=password(“密码”) //授权用户链接后修改链接密码
setpassword for 用户名@“客户端地址” =password(“密码”) //管理员重置授权用户链接密码
drop user 用户名@“客户端地址”; //删除授权用户(必须有管理员权限)
select user,host from mysql.user; //查看所有授权用户

user表 //记录已有的授权用户及权限
db表 //记录已有授权用户对数据库对数据库的访问权限
tables_priv表 //记录已有授权用户对表的访问权限
columns_priv表 //记录已有授权用户对字段的访问权限

revoke 权限列表 on 库名.表名 from 用户名@“客户端地址”; //撤销权限命令格式

mysqladmin -hlocalhost -uroot -p password “新密码”
Enter password:输入旧密码 //更改密码

                                 找回密码

(1)vim /etc/my.cnf //主配置文件添加skip-grant-tables
(2)systemctl restart mysqld //刷新服务
(3)update mysql.user set authentication_string=password(“密码”)
where user=“root” and host=“localhost” ;
(4)flush privileges //刷新

备份
物理备份
cp,tar //冷备
备份操作
cp -r /varlib/mysql 备份目录/mysql.bak
tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*

逻辑备份
mysqldump //备份命令
mysql //恢复命令
恢复操作
cp -r 备份目录/mysql.bak /var/lib/mysql
tar -zxvf /root/mysql.tar.gz -C /var/lib/mysql/ //解包放到mysql下
chown -R mysql:mysql /var/lib/mysql //改为所有者所属组

逻辑备份 完全备份 增量备份 差异备份
完全备份表示方式
-A //所有库
数据库名 //单个库
库名.表名 //单张表
-B 库名1 库名2 //多个库
完成
mysqldump -u -root -p密码 库名 > 目录/xxx.sql
完成恢复
mysql -uroot -p密码 库名 < 目录/xxx.sql

增量备份(binlog日志)

server_id=数字 //指定id值(1-255)
log_bin=目录名/文件名 //启用binlog日志(目录/文件自定义)
max_binlog_size=数值m //指定日志文件容量,默认1G

purge master logs to “binlog文件名” //删除指定日志
reset master; //删除所有日志

数据恢复
statement //报表模式
row //行模式
mixed //混合模式
修改日志格式
vim /etc/my.cnf //配置文件里添加binlog_format=“模式名称”
show variables like “binlog_format” //查看当前日记记录格式

分析日志:
–start-position=数字 //起始偏移量
–stop-position=数字 //结束偏移量
–start-datetime=“yyyy-mm-dd hh:mm:ss” //起始时间
–stop-datetime=“yyyy-mm-dd hh:mm:ss” /结束时间
mysqlbinlog [选项] binglog日志文件名 //查看日志内容
mysqlbinlog 起始选项 结束选项 路径/目录 | mysql -uroot -p密码 // 恢复数据

                     五 percona软件 innobackupex备份与恢复

(1)rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm //需要安装的软件包

常用选项
–host //主机名
–user //用户名
–port //端口号
–password //密码
–databases //数据库名
–databases=“库名” //一个库
–databases=“库名1 库名2” //多个库
–databases=“库名.表名” //1张表
–no-timestamp //不用日期命名备份文件存储的子目录名

–redo-only //日志合并
–apply-log //准备恢复数据
–copy-back //完全数据
–incremental 目录名 //增量备份
–incremental-basedir=目录名 //增量备份时,指定上一次备份数据存储的目录名
–incremental-dir=目录名 //准备恢复数据时,指定增量备份数据存储的目录名

                     (二)innobackupex 完全备份与恢复

备份命令格式
完全备份与恢复
恢复单张表
增量备份与恢复
完全备份
innobackupex --user root --password abc123…Q /allbak --no-timestamp //完全备份 (/allbak是目录)

数据完全恢复步骤
停止数据库服务
清空数据库目录
准备恢复数据
拷贝备份数据到数据库目录下
修改目录的所有者/组用户为mysql
启动服务

(1)systemctl stop mysqld //停止服务
(2)rm -rf /var/lib/mysql/* //清空数据目录
(3)innobackupex --apply-log /opt/allbak/ //准备恢复数据
(4)innobackupex --copy-back /opt/allbak/ //恢复数据
(6)chown -R mysql:mysql /var/lib/mysql //更改所有者所属组
(7)systemctl start mysqld //启动法务

             使用完全备份文件，恢复单张表的所有数据

innobackupex --user root --password 密码 --databases=“db5” /db5bak --no-timestamp //db5是一个库,db5bak是一个目录可以自定义

恢复单张表的所有数据步骤：
删除表空间文件
在备份文件里，导出表信息
把导出表信息文件拷贝到数据库目录下
导入表信息
删除表信息文件
查看表记录
(1)alter table 库名.表名 discard tablespace; //删除表空间文件(库里操作)
(2)innobackupex --apply-log --export /数据完全备份目录 // 导出表信息
(3)cp 数据完全备份目录/数据库名目录/表名.{ibd,cfg,exp} 数据库目录/库名目录 //拷贝表信息文件如 cp /db5bak/db5/b.{cfg,exp,ibd} /var/lib/mysql/db5/
(4)chown -R mysql:mysql /数据库目录/库名 //修改所有者/所属组
(5)alter table 库名.表名 import tablespace; //到处表空间(库里操作)
(6)rm -rf /数据库目录/库名/表名.{cfg,exp} //删除信息文件

                   (三)innobackupex增量备份与恢复

增量备份
(1)innobackupex --user root --password 密码 /allbak --no-timestamp //完全备份 (/allbak是目录)
(2)innobackupex --user root --password 密码 /allbak incremental /new1dir --incremental-basedir=/allbak --no-timestamp //增量备份 (new1dir自定义,)

增量恢复
停止服务
清空数据库目录
准备恢复数据
合并数据
拷贝数据到数据库目录下
修改数据库目录的所有者和组用户为mysql
启动服务
查看数据
(1)systemctl stop mysqld //停止服务
(2)rm -rf /var/lib/mysql/* //清空数据库目录
(3)innobackupex --apply-log --redo-only /opt/fullbak //准备恢复数据
(4)innobackupex --apply-log --redo-only /opt/fullbak --incremental-dir=/opt/new1dir //合并数据
(5)innobackupex --copy-back /opt/fullbak //恢复数据到数据库目录下
(6)chown -R mysql:mysql /var/lib/mysql //改属主和属组
(7)systemctl start mysqld 启动服务