Mysql - MySQL基础指令 - 《Linux运维》

1.Mysql管理指令
2.SQL语句
3.mysql备份
- 1. 使用mysqldump指令备份
- 2. mysql-binlog日志备份

1.Mysql管理指令

1.创建登录用户

MariaDB [mysql]> create user user1@'192.168.%.%' identified by 'user1';
Query OK, 0 rows affected (0.00 sec)
#创建user账户，允许192.168.0.0网段登录，密码为user1；‘%’为通配符;

2.给自己的用户改密码

MariaDB [mysql]> set password=password('123456');

3.给其他用户修改密码

MariaDB [mysql]> set password for user1@'192.168.%.%'=password('111');

4. 找回root密码

[root@localhost ~]# vim /etc/my.cnf            #修改mysql配置文件
skip-grant-tables                            #跳过密码检查
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
MariaDB [(none)]> update mysql.user set password=password('123456')  where user='root';

5. 给用户授权

 1.普通用户user1登录时只能查看information表和test表
     [root@localhost ~]# mysql -u user1 -h 192.168.10.11 -p 111     #user1登录mysql
    MariaDB [(none)]> show databases;        #user1只能查看这两个表
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    +--------------------+
    2 rows in set (0.00 sec)
2.root下修改user1权限，使它能够查看user表
    [root@localhost ~]# mysql -u root -p123456
    MariaDB [(none)]> grant select on mysql.user to user1@'192.168.%.%';     #允许user1查询mysql下的user表
    MariaDB [mysql]> flush privileges;        #刷新权限
3.使用user1登录，查看结果
    MariaDB [(none)]> show databases;    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | test               |
    +--------------------+
可以授予的权限类型：
    all，select，delet，drop，update

6. 取消用户权限

1.创建user1,并授权对mysql数据库的user表有select和insert权限
MariaDB [(none)]> grant select,insert on mysql.user  to user1@'192.168.%.%' identified by 'user1';
MariaDB [(none)]> flush privileges;
2.使用user1登录，查看数据库
[root@localhost mysql]# mysql -uuser1 -puser1 -h 192.168.10.11
MariaDB [(none)]> show grants for user1@'192.168.%.%';        #查看用户权限
+-------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.%.%                                                                          |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'192.168.%.%' IDENTIFIED BY PASSWORD '*34D356465421354asoasdasd468C4'   |
| GRANT ALL PRIVILEGES ON `mysql`.`user` TO 'user1'@'192.168.%.%'                                       |
+-------------------------------------------------------------------------------------------------------+
MariaDB [(none)]> show databases;        #可以查看mysql库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3.取消查看权限
[root@localhost ~]# mysql -uroot -p123456
MariaDB [(none)]> revoke select on mysql.user from user1@'192.168.%.%';
4.登录user1查看
MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
MariaDB [mysql]> show grants for user1@'192.168.%.%';
+-------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.%.%                                                                          |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'192.168.%.%' IDENTIFIED BY PASSWORD '*34D37F0D1D71C3DBF28E291705468C4' |
--------------------------------------------------------------------------------------------------------+
#登录地址不同，权限不一样，user1@192.168.%.%和user1@localhost的权限是不一样的

2.SQL语句

1.创建数据库

MariaDB [mysql]> create databse web

2. 切换数据库

MariaDB [mysql]> use web

3. 创建数据表

MariaDB [web]> create table stu1( id int primary key auto_increment, 
                name char(20) not null,
                age int not null,
                sex char(1) not null default 'M' );
约束:
    primary key：主键约束，唯一非空;
    auto_increment：自增长;
    not null：非空;
    default：默认值
    foreign key：外键;

4.查看表字段

MariaDB [web]> desc stu1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(11)  | NO   |     | NULL    |                |
| sex   | char(1)  | NO   |     | M       |                |
+-------+----------+------+-----+---------+----------------+

5.插入数据

1.按字段顺序插入
MariaDB [web]> insert into stu1 values (1,'zhangsan','18','M');
MariaDB [web]> select * from stu1;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
+----+----------+-----+-----+
2.选择字段插入
MariaDB [web]> insert into stu1 (name,age) values ('lisi','20');
MariaDB [web]> select * from stu1;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
|  2 | lisi     |  20 | M   |
+----+----------+-----+-----+
#这里id是自增长的，sex是有默认值；
注意：非空和主键的字段不插入值，会报错；
3.将一个表的数据复制到另一个表
MariaDB [web]> insert into stu2 (id,name) select id,name from stu1;
MariaDB [web]> select id,name from stu2;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+

6.删除数据

MariaDB [web]> select * from stu1；
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
|  2 | lisi     |  20 | M   |
|  3 | wangwu   |  19 | M   |
|  4 | zhaoliu  |  50 | M   |
+----+----------+-----+-----+
MariaDB [web]> delete from stu1 where age>20;    #删除年龄大于20的记录
MariaDB [web]> select * from stu1;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
|  2 | lisi     |  20 | M   |
|  3 | wangwu   |  19 | M   |
+----+----------+-----+-----+

7. 修改表中的数据

MariaDB [web]> update stu1 set sex='W' where id=2;        #将id=2的sex修改为w
MariaDB [web]> select * from stu1;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
|  2 | lisi     |  20 | W   |
|  3 | wangwu   |  19 | M   |
+----+----------+-----+-----+

8. 数据表字段修改删除增加

1.增加表的字段
    MariaDB [web]> alter table stu1  add address char(50);    #添加address字段，类型char(50)，默认插入在表的最后
    MariaDB [web]> alter table stu1 add phone int after name; #在name字段之后添加phone字段
2.修改字段属性
    MariaDB [web]> alter table stu1 modify name char(10);    #modify只能修改字段的数据类型
    MariaDB [web]> alter table stu1 change name username char(8) null;    #change可以修改整个字段，包括字段名

3.mysql备份

1. 使用mysqldump指令备份

[root@localhost ~]# mysqldump -u root -p123123 web stu1 > /tmp/stu1.sql
[root@localhost ~]# mysql -uroot -p123123
MariaDB [(none)]> use web
MariaDB [web]> drop tables stu1;
MariaDB [web]> show tables;
Empty set (0.00 sec)
[root@localhost ~]# mysql -uroot -p123123 web < /tmp/stu1.sql
[root@localhost ~]# mysql -uroot -p123123
MariaDB [(none)]> select * from web.stu1;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
|  2 | lisi     |  20 | M   |
+----+----------+-----+-----+

2. mysql-binlog日志备份

1.修改my.cnf配置文件，开启binlog日志记录功能
[root@localhost ~]# vim /etc/my.cnf
log-bin=mysq-bin
[root@localhost ~]# systemctl restart mariadb
2.删除创建的表
[root@localhost mysql]# mysql -uroot -p123123
MariaDB [(none)]> use web
MariaDB [web]> drop tables stu1;
3.使用mysqlbinlog恢复
[root@localhost mysql]# mysqlbinlog --start-datetime '2020-04-13 11:05:45' --stop-datetime '2020-04-13 11:05:46' /var/lib/mysql/mysq-bin.000001 | mysql -uroot -p123123
4.验证
MariaDB [(none)]> select * from web.stu1;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  18 | M   |
|  2 | lisi     |  20 | M   |
+----+----------+-----+-----+