MySQL基础操作

MySQL数据库

MySQL基础操作

创建登录用户

$ mysqladmin -uroot password '123456'   #创建root用户及密码 
$ mysql>create user zhangsan@'%' identified by '123456';  
	%:指任意的远程终端（只能远程，不包含本地）
	localhost :指的是本地登录
	IP地址: 指的是指定某IP登录

测试用户登录

$ yum -y install mysql
$ mysql -h 192.168.88.10 -usans -p123456

用户为自己更改密码

# set password=password（'新密码'）;
$ set password=password（'123456'）；#能登录MySQL的前提下，覆盖数据库内的旧密码)

root用户为其他用户找回密码

# set password for 用户名@'登录方式'=password('新密码')；
set password for sans@'%'=password('123123')；

root找回自己的密码并修改

1）关闭数据库
$ service mysqld stop
2）修改主配置文件（/etc/my.cnf）<------ skip-grant-tables
$ vim /etc/my.cnf
	skip-grant-tables      (添加到文件中)
3）启动数据库
$ service mysqld start
4）空密码登录并修改密码
$ mysql -uroot
$ update mysql.user set password=password('新密码') where user='root'and host='localhost';
5）删除skip-grant-tables,重启数据库验证新密码
$ service mysqld stop
$ vim /etc/my.cnf
	skip-grant-tables       (删除添加的内容）
$ service mysqld start
$ mysql -uroot            （不能登录了需要加-p加新密码）
$ mysql -uroot -p新密码

数据库的增删改查

创建数据库

$ Mysql>create database web;   创建web数据库
$ Mysql>show databases;         查看数据库

创建数据表

$ Mysql>use web;   #选择要使用的数据库
$ Mysql>create table a1 (id int ,name char(30)); #创建a1表，并添加id和name字段以及类型
$ mysql> show tables; #查看生成的数据表
$ Mysql>describe a1; #查看表结构（字段）
例：
Mysql>create table a2 (        #创建a2的表
->id int unsigned not null auto_increment, #字段要求为正数、且自增长、主键
->name char(30) not null default ' ', #字符型长度30字节，默认值为空格
->age int not null default 0, #字段默认值为0
->primary key (id)); #设置id为主键.不能为空，不能重复

插入数据

Mysql>use test;  #选择使用的库也可用test.a1 #表示test库下的a1表。
Mysql>insert into a2 (id,name,age) values (1,'zhangsan',21)； #指明插入字段和数据
Mysql>select * from a2; #显示a2表的数据
Mysql>insert into a2 values (2,'lisi',20)； #按顺序插入指定字段
Mysql>insert into a2 values (3,'wangwu')； #未声明年龄
Mysql>insert into a2 values (4,'zhao',19),(5,'sun',25)； #插入多条数据

将表a2的数据复制到表a1

Mysql>select * from a1;
Mysql>insert into a1 (id,name) select id,name from a2; #查询a2值，并写入到a1
Mysql>select * from a1;

将不同年龄段的（28-38）的内容，插入到别的表

Mysql> insert into a4 select * from a1 where age between 28 and 38;

删除数据库

Mysql>drop database abc； Mysql>show databases;

删除数据表

Mysql>drop table a1； Mysql>show tables;

删除表里的数据记录

Mysql>delete from a2 where id=5; #删除id=5的记录 
Mysql>delete from a2 where age between 23 and 25; #删除年龄在23-25之间的

修改表中的数据

Mysql>update a2 set age=21 where id=3; 
update 表名 set 结果 where 条件； #将id为3的表内的age改为21.

修改数据表的名称

Mysql>alter table a2 rename a1;

修改数据表的字段类型

Mysql>describe a1; 
Mysql>alter table a1 modify name char(50); 
Mysql>describe a1;

修改数据表的字段类型详情

Mysql>describe a1; 
Mysql>alter table a1 change name username char(50) not null default ' ';                         #字段名 要修改的字段名 字段类型 不能为空  默认空格 
Mysql>describe a1;

rename ： 只修改表名
modify : 只修改字段属性
change：修改字段名+字段属性 

添加字段

Mysql>describe a1; 
Mysql>alter table a1 add time datetime;                        
						字段名 字段类型 
Mysql>describe a1; #添加位置默认在末尾  

Mysql>alter table a1 add birthday year first; #添加字段到第一列 
Mysql>alter table a1 add sex nchar(1) after id; #添加到指定字段后

删除字段

Mysql>alter table a1 drop birthday;#字段名

Mysql用户授权

授予用户全部权限

查询已经存在的用户

$ Mysql>select user from mysql.user; #查询已存在的用户

给已经存在的用户授权

# grant 权限 on 库.表 to 用户@'登录方式' 
$ Mysql>grant all on aa.a1 to sans@'%'; #给已存在用户授权

创建用户并授权

# grant 权限 on 库.表 to 用户@'登录方式' identified by '123456'; #创建用户并授权] 
$ Mysql>grant all on aa.a1 to abc@'%' identified by '123456'; #创建用户并授权 
列：Mysql>grant all on aa.* to abc@'%' identified by '123456';

取消abc用户的删除库、表、表中数据的权限

$ Mysql>revoke drop,delete on aa.a1 from abc@'%'； #取消删除权限（登录abc测试） 
$ Mysql>show grants for abc@'%'； #查看指定用户的授权 
$ Mysql>show grants for sans@'%'；

取消wyy用户的drop，delete权限，并查看是否还具有此权限。

Mysql备份和还原

把数据库aa 备份到/root目录下

mysqldum -u用户名 -p密码 数据库名 > 目录/文件名 
$ mysqldump –uroot –p aa > ~/aa.sql

模拟数据库aa丢失（删除数据库aa）

Mysql>drop database aa;

通过aa.sql文件还原（指定导入到哪个库中）

mysql –uroot –p test < aa.sql

将aa.sql导入到其他虚拟机的库中

$ scp aa.sql root@192.168.190.112:/tmp 
$ cd /tmp 
$ mysql text < aa.sql

备份多个数据库（--databases）

mysqldump –uroot –p --databases aa test > abc.sql

（先模拟丢失）

Mysql>drop database aa; 
Mysql>drop database test;

还原

mysql –uroot –p < abc.sql

将abc.sql导入到其他虚拟机的库中

$ scp abc.sql root@192.168.190.112:/tmp 
$ cd /tmp 
$ mysql  < abc.sql

备份有规则的数据库/var/lib/mysql/

$ Mysql>create database a1; #连续创建三个a开头的数据库 
$ mysqlhotcopy --flushlog –u='root' –p='456' --regexp=^a  
^a 表示以a开头的数据库

还原（先模拟丢失）

$ Mysql>drop database a1; #顺序删除a开头的数据库 
$ cp –a /mnt/* /var/lib/mysql/ #复制产生的文件到数据库目录下 
#登录数据库查看即可

关于二进制日志还原

开启二进制日志

先关闭mysqld服务。

$ service mysqld stop

修改配置文件

$ vim /etc/my.cnf # 添加 
	log-bin=mysql-bin # 开启二进制日志

配置好后启动服务

$ service mysqld start

查看二进制日志文件

查看开启二进制日志后生成的文件

$ cd /var/lib/mysql #二进制日志存放位置

配置实验所需环境，创建库，在库中创建表，并写入数据,然后删除

$ create database bbs; 
$ use bbs; 
$ create table user (id int,name char(30),pass char(50)); 
$ insert into bbs.user values (0,'zhangsan','linux2020') 
$ insert into bbs.user values (1,'lisi','linux2020') 
# 在/var/lib/mysql中将bbs库mv bbs /opt下 

按时间还原：

如果数据库中的bb库被删，需要还原

查看二进制日志内容

$ mysqlbinlog mysql-bin.000001 #查看二进制日志内容

还原并查看

$ mysqlbinlog --start-datetime='2018-09-11 14:24:00' --stop-datetime='2018-09-11 14:28:00' mysql-bin.000006 | mysql –uroot –p123123 
# mysqlbinlog --start-datetime='起始时间' --stop-datetime='结束时间' 二进制日志文件| mysql –uroot –p123123 

注：所选时间段一定要完整包含所有动作（可以在原来基础上稍微增加点时间

按文件大小还原：还原到bb库被删除的数据状态

查看bb库被删除前后的文件大小

$ mysqlbinlog mysql-bin.000001

还原并查看

mysqlbinlog --start-position 264 --stop-position 341 mysql-bin.000001 | mysql –uroot –p123123

查看是否恢复

$ show databases; use bbs; show tables; select * from user;

# 将本机的二进制日志传到其他机器上进行恢复

$ scp mysql-bin.000001 root@192.168.190.112:/var/lib/mysql/ mysqlbinlog --start-position 264 --stop-position 341 mysql-bin.000001 | mysql
``