Mariadb 数据库

安装

安装 mariadb

1
[root@yzbtdiy ~]# yum -y install mariadb mariadb-server

启动 mariadb 服务

1
[root@yzbtdiy ~]# systemctl restart mariadb

初始化

设置 root 密码,移除匿名用户,关闭远程访问等一系列安全操作

1
[root@yzbtdiy ~]# mysql_secure_installation

登陆

用数据库的root用户登录,密码为redhat

1
[root@yzbtdiy ~]# mysql -uroot -predhat

基本操作

查看数据库

1
2
3
4
5
6
7
8
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |+--------------------+
3 rows in set (0.00 sec)

创建数据库

1
2
MariaDB [(none)]> create database testsql;
Query OK, 1 row affected (0.00 sec)

进入数据库

1
2
MariaDB [(none)]> use testsql;
Database changed

查看数据表

1
2
MariaDB [testsql]> show tables;
Empty set (0.00 sec)

创建表

1
2
MariaDB [testsql]> create table user(id varchar(10),name varchar(30));
Query OK, 0 rows affected (0.01 sec)

插入数据

1
2
3
4
5
MariaDB [testsql]> insert into user values('1','zhangsan');
Query OK, 1 row affected (0.00 sec)
MariaDB [testsql]> insert into user values('2','lisi');
Query OK, 1 row affected (0.00 sec)MariaDB [testsql]> insert into user values('3','wangwu');
Query OK, 1 row affected (0.00 sec)

查看表中数据

1
2
3
4
5
6
7
8
9
MariaDB [testsql]> select * from user;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)

删除id为1的数据

1
2
3
4
5
6
7
8
9
MariaDB [testsql]> delete from user where id=1;
Query OK, 1 rows affected (0.00 sec)
MariaDB [testsql]> select * from user;
+------+--------+
| id | name |
+------+--------+
| 2 | lisi |
| 3 | wangwu |
+------+--------+2 rows in set (0.00 sec)

更新id为3的数据name字段为zhaoliu

1
2
3
4
5
6
7
8
9
10
11
MariaDB [testsql]> update user set name="zhaoliu" where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testsql]> select * from user;
+------+---------+
| id | name |
+------+---------+
| 2 | lisi |
| 3 | zhaoliu |
+------+---------+
2 rows in set (0.00 sec)

删除数据表

1
2
MariaDB [testsql]> drop table user;
Query OK, 0 rows affected (0.00 sec)

删除testsql数据库

1
2
MariaDB [testsql]> drop database testsql;
Query OK, 0 rows affected (0.00 sec)

用户管理

创建数据库用户

1
2
MariaDB [(none)]> create user testuser@localhost identified by '1234';
Query OK, 0 rows affected (0.00 sec)

赋予用户权限

1
2
MariaDB [(none)]> grant select,insert,update,delete on testsql.* to testuser@localhost;
Query OK, 0 rows affected (0.00 sec)

撤销用户权限

1
2
MariaDB [(none)]> revoke select,insert,update,delete on testsql.* from testuser@
Query OK, 0 rows affected (0.00 sec)

删除用户

1
2
MariaDB [(none)]> delete from mysql.user where user='testuser';
Query OK, 1 row affected (0.00 sec)

刷新权限

1
2
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

重置 root 密码

跳过授权表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@yzbtdiy ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
# 在[mysqld]中添加skip-grant-tables,修改完密码后删除此行!!!
skip-grant-tables
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启服务,重载配置文件

1
[root@yzbtdiy ~]# systemctl restart mariadb

直接无密码登录

1
2
3
4
5
[root@yzbtdiy ~]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

修改root密码

1
2
3
4
MariaDB [(none)]> update mysql.user set password=password('root') where user='
root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

刷新权限

1
2
3
4
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye

用新密码登录

1
2
3
4
5
6
[root@yzbtdiy ~]# mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>