数据库
安装数据库
第一步：安装数据库（mariadb）服务
[root@mysql ~]# yum install mariadb.x86_64
已加载插件：langpacks
正在解决依赖关系
--> 正在检查事务
---> 软件包 mariadb.x86_64.1.5.5.52-1.el7 将被 安装
--> 正在处理依赖关系 mariadb-libs(x86-64) = 1:5.5.52-1.el7，它被软件包 1:mariadb-5.5.52-1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 mariadb-libs.x86_64.1.5.5.35-3.el7 将被 升级
---> 软件包 mariadb-libs.x86_64.1.5.5.52-1.el7 将被 更新
--> 解决依赖关系完成
依赖关系解决
================================================================================
Package 架构 版本 源 大小
================================================================================
正在安装:
mariadb x86_64 1:5.5.52-1.el7 rhel7 8.6 M
为依赖而更新:
mariadb-libs x86_64 1:5.5.52-1.el7 rhel7 761 k
事务概要
================================================================================
安装 1 软件包
升级 ( 1 依赖软件包)
总下载量：9.4 M
Is this ok [y/d/N]: y
Downloading packages:
No Presto metadata available for rhel7
(1/2): mariadb-libs-5.5.52-1.el7.x86_64.rpm | 761 kB 00:00
(2/2): mariadb-5.5.52-1.el7.x86_64.rpm | 8.6 MB 00:00
--------------------------------------------------------------------------------
总计 37 MB/s | 9.4 MB 00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在更新 : 1:mariadb-libs-5.5.52-1.el7.x86_64 1/3
正在安装 : 1:mariadb-5.5.52-1.el7.x86_64 2/3
清理 : 1:mariadb-libs-5.5.35-3.el7.x86_64 3/3
验证中 : 1:mariadb-5.5.52-1.el7.x86_64 1/3
验证中 : 1:mariadb-libs-5.5.52-1.el7.x86_64 2/3
验证中 : 1:mariadb-libs-5.5.35-3.el7.x86_64 3/3
已安装:
mariadb.x86_64 1:5.5.52-1.el7
作为依赖被升级:
mariadb-libs.x86_64 1:5.5.52-1.el7
完毕！
第二步：开启数据库服务，并设置开机自启
##开启数据库服务
[root@mysql ~]# systemctl start mariadb
##设置开机自启动
[root@mysql ~]# systemctl enable mariadb
ln -s ‘/usr/lib/systemd/system/mariadb.service’ ‘/etc/systemd/system/multi-user.target.wants/mariadb.service’
第三步：关闭数据库接口
##搜索mysql的接口
[root@mysql ~]# netstat -antuple | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 75183 3937/mysqld
##修改文件，使其接口对外开放关闭
[root@mysql ~]# vim /etc/my.cnf
skip-networking=1
##重启服务
[root@mysql ~]# systemctl restart mariadb
##查看接口为空
[root@mysql ~]# netstat -antuple | grep mysql
第四步：设定数据库安全初始化
##mysql密码初始化
[root@mysql ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
数据库安全设置完成后，进入数据库需要输入密码
格式：mysql -u用户 -p密码 退出— quit
u和用户，p和密码之间没有空格
数据库中的基础命令
##显示数据库——SHOW DATABASES;
##显示数据库中包含的所有数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
##进入数据库——USE mysql;
##切换到mysql数据库中
MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]>
 | |
|---|---|
##显示数据库中的表——SHOW TABLES;
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
##.查询表结构
MariaDB [mysql]> DESC user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
##查询表中的信息
MariaDB [mysql]> SELECT Host,User,Password FROM user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
##新建数据库
MariaDB [mysql]> CREATE DATABASE westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
##新建数据库中的表
MariaDB [mysql]> USE westos
Database changed
MariaDB [westos]> SHOW TABLE;
MariaDB [westos]> SHOW TABLES;
Empty set (0.00 sec)
MariaDB [westos]> CREATE TABLE linux (
-> username varchar(20) not null,
-> password varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
-> username varchar(20) not null, ##字符类型为char，不能为空
-> password varchar(10) not null ##字符类型为char，不能为空
##在linux表格中插入信息
MariaDB [westos]> INSERT INTO linux values('cai','123');
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| cai | 123 |
+----------+----------+
1 row in set (0.00 sec)
select * from linux; ##查看linux表格中的所有信息
##修改增加表属性
在linux表格中添加age字段
MariaDB [westos]> ALTER TABLE linux ADD age varchar(8) after username;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+------+----------+
| username | age | password |
+----------+------+----------+
| cai | NULL | 123 |
+----------+------+----------+
1 row in set (0.00 sec)
##修改删除表属性
在linux表格中移除age字段
MariaDB [westos]> ALTER TABLE linux DROP age;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| cai | 123 |
+----------+----------+
1 row in set (0.00 sec)
##修改linux表格中的信息（例：password）
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| cai | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> UPDATE linux SET password='110' WHERE username='cai';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| cai | 110 |
+----------+----------+
1 row in set (0.00 sec)
##修改表格名字
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> ALTER TABLE linux RENAME westos;
Query OK, 0 rows affected (0.00 sec)
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| westos |
+------------------+
1 row in set (0.00 sec)
##删除表格中的某一行
MariaDB [westos]> SELECT * FROM westos;
+----------+----------+
| username | password |
+----------+----------+
| cai | 110 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> INSERT INTO westos values('tian','456');
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> SELECT * FROM westos;
+----------+----------+
| username | password |
+----------+----------+
| cai | 110 |
| tian | 456 |
+----------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> DELETE FROM westos WHERE username='cai';
Query OK, 1 row affected (0.00 sec)
MariaDB [westos]> SELECT * FROM westos;
+----------+----------+
| username | password |
+----------+----------+
| tian | 456 |
+----------+----------+
1 row in set (0.00 sec)
##删除表格
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| westos |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> DROP TABLE westos;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> SHOW TABLES;
Empty set (0.00 sec)
##删除数据库
MariaDB [westos]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [westos]> DROP DATABASE westos;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
数据库用户管理（创建用户、授权、取消授权、修改密码）
##创建用户
MariaDB [(none)]> CREATE DATABASE westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE westos;
Database changed
MariaDB [westos]> CREATE TABLE linux(
-> username varchar(20) not null,
-> password varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> INSERT INTO linux values('hello','123');
Query OK, 1 row affected (0.00 sec)
MariaDB [westos]> INSERT INTO linux values('cai','123');
Query OK, 1 row affected (0.00 sec)
##创建用户(root用户),并用新建的用户登陆
##hate@localhost 本地用户 ##hate@‘%’ 远程用户
##以数据库root用户进行登陆
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##创建用户cai，密码为123
MariaDB [(none)]> CREATE USER cai@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
##以数据库cai用户进行登陆
[root@mysql ~]# mysql -ucai -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##权限不足
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
##给用户授权
##使用数据库root用户登陆
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##授予cai用户查询、更新、删除、插入权限。
mysql.*代表授予ajie的权限对数据库mysql下的所有表单有效
MariaDB [(none)]> GRANT select,update,delete,insert ON mysql.* TO cai@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
##使用数据库cai用户登陆
[root@mysql ~]# mysql -ucai -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
###此时查看所有数据库的时候，可以看到有mysql了
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
#查看数据库mysql中的所有表单
MariaDB [(none)]> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
MariaDB [mysql]>
数据库用户密码的修改
##修改root用户密码(记得密码)
##使用数据库root用户登陆
[root@mysql ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##修改密码为123 此方式属于加密修改，数据库中看不到root用户密码
MariaDB [(none)]> SET password = PASSWORD('123');
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> quit
Bye
##修改root用户密码(忘记密码)
##停止mariadb服务
[root@mysql ~]# systemctl stop mariadb
##跳过密码验证
[root@mysql ~]# mysqld_safe --skip-grant-tables &
[1] 3399
[root@mysql ~]# 190813 09:51:58 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
190813 09:51:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^C
##直接免密登陆
[root@mysql ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##修改密码为789，这种修改方式在数据库中可以看到密码
MariaDB [(none)]> UPDATE mysql.user SET password='789' WHERE User='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
##看到密码为789
MariaDB [(none)]> SELECT *FROM mysql.user;
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | 789 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | 789 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | 789 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| localhost | cai | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | |
| localhost | westos | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
5 rows in set (0.00 sec)
##修改密码为123，这种修改方式在数据库中不能看到密码
MariaDB [(none)]> UPDATE mysql.user SET password=password('123') WHERE User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
##密码为加密字符
MariaDB [(none)]> SELECT *FROM mysql.user;+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| localhost | cai | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | |
| localhost | westos | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | | |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
5 rows in set (0.00 sec)
##刷新MySQL的系统权限相关表
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
##退出数据库
MariaDB [(none)]> quit
Bye
##查询跳过服务的进程，必须要关闭该进程，否则一直免密登陆
[root@mysql ~]# ps -aux | grep mysql
root 3399 0.0 0.1 113248 1568 pts/1 S 09:51 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 3554 0.0 8.7 843956 84648 pts/1 Sl 09:51 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 3612 0.0 0.0 112656 944 pts/1 R+ 09:53 0:00 grep --color=auto mysql
##关闭进程
[root@mysql ~]# kill -9 3399
[root@mysql ~]# kill -9 3554
[1]+ 已杀死 mysqld_safe --skip-grant-tables
##开启mariadb服务
[root@mysql ~]# systemctl start mariadb
##免密登陆不行
[root@mysql ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
##用修改后的密码登陆
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
[root@mysql ~]#
数据库的备份
第一步：备份userdata库，且导入/xxx/userdata.sql文件—— mysqldump -uroot -predhat userdata > /xxx/userdata.sql
##使用root去备份，-p代表它有密码，westos为要备份的数据库，备份到/mnt/下名称为backup_westos.dump
[root@mysql ~]# mysqldump -u root -p westos > /mnt/back_westos.dump
Enter password:
##查看文件是否生成
[root@mysql ~]# ls /mnt/
back_westos.dump
第二步：删除数据库wechat中的表单myid的数据
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> DROP DATABASE westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
第三步：恢复数据库
[root@mysql ~]# mysql -uroot -p westos < /mnt/back_westos.dump#输入重定向来导入数据westos进行还原
Enter password:
[root@mysql ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
160
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
