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)]>