数据库

1:配置yum源,下载mariadb

[root@localhost Desktop]# vim /etc/yum.repos.d/rhel_dvd.repo 
[root@localhost Desktop]# yum search mariadb
Loaded plugins: langpacks
============================= N/S matched: mariadb =============================
mariadb-bench.x86_64 : MariaDB benchmark scripts and data
mariadb-devel.i686 : Files for development of MariaDB/MySQL applications
mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-server.x86_64 : The MariaDB server and related files
mariadb.x86_64 : A community developed branch of MySQL
mariadb-test.x86_64 : The test suite distributed with MariaD

  Name and summary matches only, use "search all" for everything.
[root@localhost Desktop]# yum install mariadb-server.x86_64
Loaded plugins: langpacks
rhel_dvd                                                  | 4.1 kB  00:00:00     
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.35-3.el7 will be installed
--> Processing Dependency: mariadb(x86-64) = 1:5.5.35-3.el7 for package: 1:mariadb-server-5.5.35-3.el7.x86_64
--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.35-3.el7.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: 1:mariadb-server-5.5.35-3.el7.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.35-3.el7.x86_64
--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.35-3.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.35-3.el7 will be installed
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
--> Running transaction check
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Running transaction check
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================
 Package                     Arch       Version               Repository    Size
=================================================================================
Installing:
 mariadb-server              x86_64     1:5.5.35-3.el7        rhel_dvd      11 M
Installing for dependencies:
 mariadb                     x86_64     1:5.5.35-3.el7        rhel_dvd     8.9 M
 perl-Compress-Raw-Bzip2     x86_64     2.061-3.el7           rhel_dvd      32 k
 perl-Compress-Raw-Zlib      x86_64     1:2.061-4.el7         rhel_dvd      57 k
 perl-DBD-MySQL              x86_64     4.023-5.el7           rhel_dvd     140 k
 perl-DBI                    x86_64     1.627-4.el7           rhel_dvd     802 k
 perl-Data-Dumper            x86_64     2.145-3.el7           rhel_dvd      47 k
 perl-IO-Compress            noarch     2.061-2.el7           rhel_dvd     260 k
 perl-Net-Daemon             noarch     0.48-5.el7            rhel_dvd      51 k
 perl-PlRPC                  noarch     0.2020-14.el7         rhel_dvd      36 k

Transaction Summary
=================================================================================
Install  1 Package (+9 Dependent packages)

Total download size: 21 M
Installed size: 107 M
Is this ok [y/d/N]: y
Downloading packages:
(1/10): mariadb-server-5.5.35-3.el7.x86_64.rpm            |  11 MB  00:00:00     
(2/10): mariadb-5.5.35-3.el7.x86_64.rpm                   | 8.9 MB  00:00:00     
(3/10): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm    |  32 kB  00:00:00     
(4/10): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm     |  57 kB  00:00:00     
(5/10): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm             | 140 kB  00:00:00     
(6/10): perl-DBI-1.627-4.el7.x86_64.rpm                   | 802 kB  00:00:00     
(7/10): perl-IO-Compress-2.061-2.el7.noarch.rpm           | 260 kB  00:00:00     
(8/10): perl-Net-Daemon-0.48-5.el7.noarch.rpm             |  51 kB  00:00:00     
(9/10): perl-PlRPC-0.2020-14.el7.noarch.rpm               |  36 kB  00:00:00     
(10/10): perl-Data-Dumper-2.145-3.el7.x86_64.rpm          |  47 kB  00:00:00     
---------------------------------------------------------------------------------
Total                                                16 MB/s |  21 MB  00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Data-Dumper-2.145-3.el7.x86_64                          1/10 
  Installing : 1:mariadb-5.5.35-3.el7.x86_64                                2/10 
  Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                  3/10 
  Installing : perl-Net-Daemon-0.48-5.el7.noarch                            4/10 
  Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                   5/10 
  Installing : perl-IO-Compress-2.061-2.el7.noarch                          6/10 
  Installing : perl-PlRPC-0.2020-14.el7.noarch                              7/10 
  Installing : perl-DBI-1.627-4.el7.x86_64                                  8/10 
  Installing : perl-DBD-MySQL-4.023-5.el7.x86_64                            9/10 
  Installing : 1:mariadb-server-5.5.35-3.el7.x86_64                        10/10 
  Verifying  : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                   1/10 
  Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                            2/10 
  Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                          3/10 
  Verifying  : perl-PlRPC-0.2020-14.el7.noarch                              4/10 
  Verifying  : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                  5/10 
  Verifying  : 1:mariadb-server-5.5.35-3.el7.x86_64                         6/10 
  Verifying  : perl-DBI-1.627-4.el7.x86_64                                  7/10 
  Verifying  : 1:mariadb-5.5.35-3.el7.x86_64                                8/10 
  Verifying  : perl-DBD-MySQL-4.023-5.el7.x86_64                            9/10 
  Verifying  : perl-IO-Compress-2.061-2.el7.noarch                         10/10 

Installed:
  mariadb-server.x86_64 1:5.5.35-3.el7                                           

Dependency Installed:
  mariadb.x86_64 1:5.5.35-3.el7                                                  
  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7                                   
  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7                                    
  perl-DBD-MySQL.x86_64 0:4.023-5.el7                                            
  perl-DBI.x86_64 0:1.627-4.el7                                                  
  perl-Data-Dumper.x86_64 0:2.145-3.el7                                          
  perl-IO-Compress.noarch 0:2.061-2.el7                                          
  perl-Net-Daemon.noarch 0:0.48-5.el7                                            
  perl-PlRPC.noarch 0:0.2020-14.el7                                              

Complete!

2:启动程序

设置开机启动

关闭火墙

[root@localhost Desktop]# systemctl start mariadb
[root@localhost Desktop]# systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'
[root@localhost Desktop]# netstat -antlpe | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         803733     4517/mysqld         
[root@localhost Desktop]# systemctl status firewalld.service 
firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled)
   Active: active (running) since Thu 2017-08-10 02:41:27 EDT; 2h 18min ago
 Main PID: 484 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─484 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid

Aug 10 02:41:27 localhost systemd[1]: Started firewalld - dynamic firewall d...n.
Aug 10 04:03:04 localhost firewalld[484]: 2017-08-10 04:03:04 ERROR: UNKNOWN_...0
Aug 10 04:05:23 localhost firewalld[484]: 2017-08-10 04:05:23 ERROR: UNKNOWN_...0
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost Desktop]# systemctl stop firewalld.service 

3:进入数据库

[root@localhost Desktop]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye
[root@localhost Desktop]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/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
skip-networking=1     ##编辑  忽略网络上连接
[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

4:开始使用mariadb

[root@localhost Desktop]# systemctl restart mariadb
[root@localhost Desktop]# netstat -antlpe | grep mysql
[root@localhost Desktop]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye

5:设置权限和密码

[root@localhost Desktop]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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] 
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] 
 ... 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] 
 ... 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] 
 - 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]  ... 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!

6:使用部分,此时需要用密码登陆

[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# 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.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看数据库

1:查询

show databases;
use mysql;
show tables;
desc user;
select * from user;
select * from user where Host=’127.0.0.1’;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

进入某个库

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]> 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 * from 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 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]> select * from user where Host='127.0.0.1';
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| 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 |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 row in set (0.00 sec)

2:创造数据库

create database westos;
use westos;
create table linux (
username varchar(50) not null,
passwd varchar(50) not null,
age varchar(4) );

insert into linux values (‘lee’,’123’,’20’);

MariaDB [mysql]> create database westos;
Query OK, 1 row affected (0.00 sec)

3:使用数据库

MariaDB [mysql]> use westos;
Database changed
Database changed

创造表格

MariaDB [westos]> create table linux (
    -> username varchar(50) not null,
    -> password varchar(50) not null,
    -> age varchar(4) );
Query OK, 0 rows affected (0.08 sec)

查看表格配置

MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(50) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
| age      | varchar(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


MariaDB [westos]> insert into linux values ('lee','123','20');
Query OK, 1 row affected (0.41 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)
#查看表格内容
MariaDB [westos]> select * from linux;
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| lee      | 123      | 20   |
+----------+----------+------+
1 row in set (0.00 sec)

添加信息

1:
alter table linux rename message;
alter table linux add class varchar(50);
alter table linux drop class;
alter table linux add class varchar(50) after passwd;
update linux set class=’linux’;
update linux set class=’java’ where username=’lee’;

MariaDB [westos]> insert into linux values ('wang','123','18');
Query OK, 1 row affected (0.40 sec)

MariaDB [westos]> select * from linux;
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| lee      | 123      | 20   |
| wang     | 123      | 18   |
+----------+----------+------+
2 rows in set (0.00 sec)

修改表格名称

MariaDB [westos]> alter table linux rename message;
Query OK, 0 rows affected (0.12 sec)

MariaDB [westos]> select * from linux;
ERROR 1146 (42S02): Table 'westos.linux' doesn't exist
MariaDB [westos]> select * from message;
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| lee      | 123      | 20   |
| wang     | 123      | 18   |
| wang     |          | 18   |
| wang     | 123      |      |
|          |          |      |
|          |          | 33   |
+----------+----------+------+
6 rows in set (0.00 sec)

MariaDB [westos]> alter table message rename linux;
Query OK, 0 rows affected (0.05 sec)

MariaDB [westos]> select * from linux;
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| lee      | 123      | 20   |
| wang     | 123      | 18   |
| wang     |          | 18   |
| wang     | 123      |      |
|          |          |      |
|          |          | 33   |
+----------+----------+------+
6 rows in set (0.00 sec)
###添加字符
MariaDB [westos]> alter table linux add class varchar(50);
Query OK, 6 rows affected (0.16 sec)               
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+------+-------+
| username | password | age  | class |
+----------+----------+------+-------+
| lee      | 123      | 20   | NULL  |
| wang     | 123      | 18   | NULL  |
| wang     |          | 18   | NULL  |
| wang     | 123      |      | NULL  |
|          |          |      | NULL  |
|          |          | 33   | NULL  |
+----------+----------+------+-------+
6 rows in set (0.00 sec)
MariaDB [westos]> alter table linux drop class;
Query OK, 6 rows affected (0.13 sec)               
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| lee      | 123      | 20   |
| wang     | 123      | 18   |
| wang     |          | 18   |
| wang     | 123      |      |
|          |          |      |
|          |          | 33   |
+----------+----------+------+
6 rows in set (0.00 sec)

在特定位置添加字符

MariaDB [westos]> alter table linux add class varchar(50) after username;
Query OK, 6 rows affected (0.12 sec)               
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+-------+----------+------+
| username | class | password | age  |
+----------+-------+----------+------+
| lee      | NULL  | 123      | 20   |
| wang     | NULL  | 123      | 18   |
| wang     | NULL  |          | 18   |
| wang     | NULL  | 123      |      |
|          | NULL  |          |      |
|          | NULL  |          | 33   |
+----------+-------+----------+------+
6 rows in set (0.01 sec)

更改字符

MariaDB [westos]> update linux set class='linux';
Query OK, 6 rows affected (0.05 sec)
Rows matched: 6  Changed: 6  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+-------+----------+------+
| username | class | password | age  |
+----------+-------+----------+------+
| lee      | linux | 123      | 20   |
| wang     | linux | 123      | 18   |
| wang     | linux |          | 18   |
| wang     | linux | 123      |      |
|          | linux |          |      |
|          | linux |          | 33   |
+----------+-------+----------+------+
6 rows in set (0.00 sec)

按条件更改字符

MariaDB [westos]> update linux set class='westos' where username='wang';
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+--------+----------+------+
| username | class  | password | age  |
+----------+--------+----------+------+
| lee      | linux  | 123      | 20   |
| wang     | westos | 123      | 18   |
| wang     | westos |          | 18   |
| wang     | westos | 123      |      |
|          | linux  |          |      |
|          | linux  |          | 33   |
+----------+--------+----------+------+
6 rows in set (0.00 sec)

MariaDB [westos]> alter table linux add jieduan varchar(50);
Query OK, 6 rows affected (0.47 sec)               
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [westos]> update linux set jieduan='weichengnian' where age='18';
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | NULL         |
| wang     | westos | 123      | 18   | weichengnian |
| wang     | westos |          | 18   | weichengnian |
| wang     | westos | 123      |      | NULL         |
|          | linux  |          |      | NULL         |
|          | linux  |          | 33   | NULL         |
+----------+--------+----------+------+--------------+
6 rows in set (0.00 sec)

MariaDB [westos]> update linux set jieduan='weichengnian' where age<'18';
Query OK, 2 rows affected (0.39 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | NULL         |
| wang     | westos | 123      | 18   | weichengnian |
| wang     | westos |          | 18   | weichengnian |
| wang     | westos | 123      |      | weichengnian |
|          | linux  |          |      | weichengnian |
|          | linux  |          | 33   | NULL         |
+----------+--------+----------+------+--------------+
6 rows in set (0.00 sec)

MariaDB [westos]> update linux set jieduan='chengnian' where age>'20';
Query OK, 1 row affected (0.41 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | NULL         |
| wang     | westos | 123      | 18   | weichengnian |
| wang     | westos |          | 18   | weichengnian |
| wang     | westos | 123      |      | weichengnian |
|          | linux  |          |      | weichengnian |
|          | linux  |          | 33   | chengnian    |
+----------+--------+----------+------+--------------+
6 rows in set (0.00 sec)

MariaDB [westos]> update linux set jieduan='chengnian' where age>='20';
Query OK, 1 row affected (0.06 sec)
Rows matched: 2  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | chengnian    |
| wang     | westos | 123      | 18   | weichengnian |
| wang     | westos |          | 18   | weichengnian |
| wang     | westos | 123      |      | weichengnian |
|          | linux  |          |      | weichengnian |
|          | linux  |          | 33   | chengnian    |
+----------+--------+----------+------+--------------+
6 rows in set (0.00 sec)

MariaDB [westos]> update linux set username='li' where age='' and  jieduan='weichengnian';
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [westos]> select * from linux;+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | chengnian    |
| wang     | westos | 123      | 18   | weichengnian |
| wang     | westos |          | 18   | weichengnian |
| li       | westos | 123      |      | weichengnian |
| li       | linux  |          |      | weichengnian |
| liu      | linux  |          | 33   | chengnian    |
+----------+--------+----------+------+--------------+
6 rows in set (0.00 sec)

删除字符

delete from linux where username=’lee’ and class=’java’;
drop table linux;
drop database westos;

按条件删除

MariaDB [westos]> delete from linux where username='liu';
Query OK, 1 row affected (0.37 sec)

MariaDB [westos]> select * from linux;+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | chengnian    |
| wang     | westos | 123      | 18   | weichengnian |
| wang     | westos |          | 18   | weichengnian |
| li       | westos | 123      |      | weichengnian |
| li       | linux  |          |      | weichengnian |
+----------+--------+----------+------+--------------+
5 rows in set (0.00 sec)

双条件删除

MariaDB [westos]> delete from linux where username='wang' and password='';
Query OK, 1 row affected (0.06 sec)

MariaDB [westos]> select * from linux;
+----------+--------+----------+------+--------------+
| username | class  | password | age  | jieduan      |
+----------+--------+----------+------+--------------+
| lee      | linux  | 123      | 20   | chengnian    |
| wang     | westos | 123      | 18   | weichengnian |
| li       | westos | 123      |      | weichengnian |
| li       | linux  |          |      | weichengnian |
+----------+--------+----------+------+--------------+
4 rows in set (0.00 sec)

删除表格

MariaDB [westos]> drop table linux;
Query OK, 0 rows affected (0.05 sec)

MariaDB [westos]> select * from linux;
ERROR 1146 (42S02): Table 'westos.linux' doesn't exist

删除库

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)

备份

mysqldump -uroot -pwestos westos > /mnt/westos.sql
drop westos;
mysql -uroot -pwestos -e “create database westos;”
mysql -uroot -pwestos

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(50) not null,
    -> passwd   varchar(50) not null,
    -> age  varchar(4) );
Query OK, 0 rows affected (0.42 sec)

MariaDB [westos]> insert into linux values ('lee','123','20');
Query OK, 1 row affected (0.06 sec)

MariaDB [westos]> insert into linux values ('liu','123','20');
Query OK, 1 row affected (0.06 sec)

MariaDB [westos]> select * from linux;
+----------+--------+------+
| username | passwd | age  |
+----------+--------+------+
| lee      | 123    | 20   |
| liu      | 123    | 20   |
+----------+--------+------+
2 rows in set (0.00 sec)

MariaDB [westos]> quit;
Bye
[root@localhost ~]# mysqldump -uroot -pwestos westos > /mnt/westos.sql
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [westos]> drop database westos;
Query OK, 1 row affected (0.44 sec)

MariaDB [(none)]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot -pwestos -e "create database westos;"
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program 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)]> quit
Bye
[root@localhost mnt]# mysql -uroot -pwestos westos< /mnt/westos.sql
[root@localhost mnt]# cd
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program 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)]> select * from westos.linux;
+----------+--------+------+
| username | passwd | age  |
+----------+--------+------+
| lee      | 123    | 20   |
| liu      | 123    | 20   |

7:密码忘记了修改数据库密码

先关闭服务
[root@localhost ~]# systemctl stop mariadb.service 
跳过初始读取密码界面
[root@localhost ~]# mysqld_safe --skip-grant-tables &
[1] 3748
[root@localhost ~]# 170810 23:06:28 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170810 23:06:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

直接进入mysql

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program 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)
##进入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]> select * from 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 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | 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 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.01 sec)

更改密码

MariaDB [mysql]> update user set Password=password('redhat') where User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [mysql]> quit
Bye

关闭相关进程

[root@localhost ~]# ps aux | grep mysql
root      3748  0.0  0.0 113248  1556 pts/0    S    23:06   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     3903  0.0  4.7 924612 90308 pts/0    Sl   23:06   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      3973  0.0  0.0 112640   936 pts/0    R+   23:11   0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 3748
[root@localhost ~]# kill -9 3903
[1]+  Killed                  mysqld_safe --skip-grant-tables

重启服务

[root@localhost ~]# systemctl start mariadb 

尝试密码登陆

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program 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)

8:用户授权

[root@localhost ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

创建用户

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

查看用户授权

MariaDB [(none)]> show grants for lee@localhost
    -> ;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

用户授权

MariaDB [(none)]> grant select on westos.* to lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye

测试

[root@localhost ~]# mysql -ulee -plee
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| westos             |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use westos;
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 [westos]> select * from linux;
+----------+--------+------+
| username | passwd | age  |
+----------+--------+------+
| lee      | 123    | 20   |
| liu      | 123    | 20   |
+----------+--------+------+
2 rows in set (0.00 sec)

未授权

MariaDB [westos]> drop table linux;
ERROR 1142 (42000): DROP command denied to user 'lee'@'localhost' for table 'linux'
MariaDB [westos]> quit
Bye

授权

[root@localhost ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant delete on westos.* to lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for lee@localhost
    -> ;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, DELETE ON `westos`.* TO 'lee'@'localhost'                                                    |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye

测试

[root@localhost ~]# mysql -ulee -plee
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use westos;
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 [westos]> select * from linux;
+----------+--------+------+
| username | passwd | age  |
+----------+--------+------+
| lee      | 123    | 20   |
| liu      | 123    | 20   |
+----------+--------+------+
2 rows in set (0.00 sec)

MariaDB [westos]> delete from linux where username='lee';
Query OK, 1 row affected (0.10 sec)

MariaDB [westos]> select * from linux;
+----------+--------+------+
| username | passwd | age  |
+----------+--------+------+
| liu      | 123    | 20   |
+----------+--------+------+
1 row in set (0.00 sec)

MariaDB [westos]> drop table linux;
ERROR 1142 (42000): DROP command denied to user 'lee'@'localhost' for table 'linux'
MariaDB [westos]> quit
Bye
[root@localhost ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants for lee@localhost
    -> ;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, DELETE ON `westos`.* TO 'lee'@'localhost'                                                    |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> grant drop on westos.* to lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -ulee -plee
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> drop table linux;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use westos;
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 [westos]> drop table linux;
Query OK, 0 rows affected (0.39 sec)

MariaDB [westos]> select * from linux;
ERROR 1146 (42S02): Table 'westos.linux' doesn't exist
MariaDB [westos]> 

撤销授权

[root@localhost ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> revoke drop on westos.* from lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> revoke drop on westos.* from lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> revoke delete on westos.* from lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> revoke select on westos.* from lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> show grants for lee@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除用户

MariaDB [(none)]> drop user lee@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值