一.数据库的安装及初始化
Mariadb 数据库管理系统是Mysql的一个分支,主要由开源社区在维护。
数据库
1.数据库的安装
(1)yum源是否配置正常
[root@localhost ~]# yum repolist
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
repo id repo name status
!westos rhel7.3 4,751
repolist: 4,751
(2)查找数据库
[root@localhost ~]# yum search mysql
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
: manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
============================== N/S matched: mysql ==============================
MySQL-python.x86_64 : An interface to MySQL
akonadi-mysql.x86_64 : Akonadi MySQL backend support
dovecot-mysql.x86_64 : MySQL back end for dovecot
libdbi-dbd-mysql.x86_64 : MySQL plugin for libdbi
mysql-connector-java.noarch : Official JDBC driver for MySQL
mysql-connector-odbc.x86_64 : ODBC driver for MySQL
pcp-pmda-mysql.x86_64 : Performance Co-Pilot (PCP) metrics for MySQL
perl-DBD-MySQL.x86_64 : A MySQL interface for Perl
php-mysql.x86_64 : A module for PHP applications that use MySQL databases
qt-mysql.i686 : MySQL driver for Qt's SQL classes
qt-mysql.x86_64 : MySQL driver for Qt's SQL classes
qt3-MySQL.i686 : MySQL drivers for Qt 3's SQL classes
qt3-MySQL.x86_64 : MySQL drivers for Qt 3's SQL classes
qt5-qtbase-mysql.i686 : MySQL driver for Qt5's SQL classes
qt5-qtbase-mysql.x86_64 : MySQL driver for Qt5's SQL classes
rsyslog-mysql.x86_64 : MySQL support for rsyslog
mariadb.x86_64 : A community developed branch of MySQL ##需要安装的数据库
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
Name and summary matches only, use "search all" for everything.
(3)安装数据库
[root@localhost ~]# yum install mariadb-server.x86_64 -y
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
: manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
westos | 4.1 kB 00:00
Resolving Dependencies
--> Running transaction check
---> Package mariadb-server.x86_64 1:5.5.52-1.el7 will be installed
--> Processing Dependency: mariadb(x86-64) = 1:5.5.52-1.el7 for package: 1:mariadb-server-5.5.52-1.el7.x86_64
--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.52-1.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.52-1.el7 will be installed
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mariadb-server x86_64 1:5.5.52-1.el7 westos 11 M
Installing for dependencies:
mariadb x86_64 1:5.5.52-1.el7 westos 8.6 M
perl-DBD-MySQL x86_64 4.023-5.el7 westos 140 k
Transaction Summary
================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 19 M
Installed size: 104 M
Downloading packages:
--------------------------------------------------------------------------------
Total 33 MB/s | 19 MB 00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : 1:mariadb-5.5.52-1.el7.x86_64 1/3
Installing : perl-DBD-MySQL-4.023-5.el7.x86_64 2/3
Installing : 1:mariadb-server-5.5.52-1.el7.x86_64 3/3
Verifying : 1:mariadb-server-5.5.52-1.el7.x86_64 1/3
Verifying : perl-DBD-MySQL-4.023-5.el7.x86_64 2/3
Verifying : 1:mariadb-5.5.52-1.el7.x86_64 3/3
Installed:
mariadb-server.x86_64 1:5.5.52-1.el7
Dependency Installed:
mariadb.x86_64 1:5.5.52-1.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7
Complete!
(4)设置数据库的状态为开启
[root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since Fri 2019-04-26 05:57:36 CST; 18s ago ##状态开启
Process: 3525 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 3446 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 3524 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─3524 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─3682 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: The lates...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: You can f...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: http://de...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: Support M...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: Corporati...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: Alternati...
Apr 26 05:57:33 localhost.localdomain mariadb-prepare-db-dir[3446]: http://ma...
Apr 26 05:57:33 localhost.localdomain mysqld_safe[3524]: 190426 05:57:33 mysq...
Apr 26 05:57:33 localhost.localdomain mysqld_safe[3524]: 190426 05:57:33 mysq...
Apr 26 05:57:36 localhost.localdomain systemd[1]: Started MariaDB database se...
Hint: Some lines were ellipsized, use -l to show in full.
(5)检验数据库是否可用
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> ##可用
2.安全初始化
(1)设置登录密码
[root@localhost ~]# mysql_secure_installation ##进入密码设置模式
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): ##输入之前的密码,无密码不用输
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] Y ##是否设置超级用户密码
New password: ##输入新密码为redhat
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success! ##设置成功
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y ##禁止匿名用户登录
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y ##禁止超级用户远程登录
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y ##删除默认数据库的文件
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y ##立即重新加载权限列表
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
(2)超级用户输入密码登录数据库
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
(3)拒绝外部通过网络访问数据库
查看对外开放接口
[root@localhost ~]# netstat -antlupe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 37075 3682/mysqld ##对外开放接口为3306
修改配置文件跳过所有关于网络的设置
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mariadb.service
[root@localhost ~]# netstat -antlupe | grep mysql ##接口关闭
二.数据库的管理
1.数据库的查询
[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.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(1)SHOW DATABASES ##查询库信息
MariaDB [(none)]> SHOW DATABASES; ##查询库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
(2)USE DATABASENAME ##进入某一库
MariaDB [(none)]> USE mysql ##进入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]>
(3)SHOW TABLES; ##查询库中的所有表
MariaDB [mysql]> SHOW TABLES; ##查询库中的所有表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
(4)SELECT * FROM TABLES ##查看某一表的所有信息
MariaDB [mysql]> SELECT * FROM user; ##查看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 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | 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 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | 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 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | 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)
**(5)SELECT Host FROM user WHERE User=‘root’; ## 按条件查询表中信息
**
MariaDB [mysql]> SELECT Host FROM user WHERE User='root'; ##查询用户名为root的表中的信息
+-----------+
| Host |
+-----------+
| 127.0.0.1 |
| ::1 |
| localhost |
+-----------+
3 rows in set (0.00 sec)
2.数据库的建立
CREATE DATABASE westos; ## 新建一个库名称为westos
USE westos; # 进入新建的库
CREATE TABLE linux ( #在库中创建一个新的表
-> username varchar(10) not null, #表中包含的字段以及字段的要求
-> password varchar(50) not null
-> );
DESC linux; # 查看表结构
INSERT INTO linux VALUES(‘lee’,‘123’); #给表中插入内容
INSERT INTO toto VALUES(‘tom’,‘123’);
SELECT * FROM linux;
SELECT username FROM linux; 查看表中某一字段的内容
[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.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE westos; ##创建一个库westos
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES ##westos在库中表示创建成功
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> USE westos ##调用新建库westos
Database changed
MariaDB [westos]> CREATE TABLE linux (
-> username varchar(10) not null,
-> password varchar(50) not null
-> );
Query OK, 0 rows affected (0.11 sec)
MariaDB [westos]> DESC linux ##查看调用的表结构,是否与建立的一致
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(50) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> INSERT INTO linux VALUES ('lee','123'); ##给表中插入内容lee 123
Query OK, 1 row affected (0.18 sec)
MariaDB [westos]> INSERT INTO linux VALUES ('tom','123'); ##给表中插入内容 tom 123
Query OK, 1 row affected (0.09 sec)
MariaDB [westos]> SELECT * FROM linux; ##查看linux表中的内容与建立的是否一致
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
| tom | 123 |
+----------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> SELECT username,password from linux; ##查看linux表中关于username和password的内容
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
| tom | 123 |
+----------+----------+
2 rows in set (0.00 sec)
3.数据库的更改
(1) ALTER TABLE linux RENAME redhat; ##更改数据表格的名字
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 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]> SHOW TABLES
-> ;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> ALTER TABLE linux RENAME redhat; ##更改数据表格的名字
Query OK, 0 rows affected (0.14 sec)
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| redhat |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> ALTER TABLE redhat RENAME linux;
Query OK, 0 rows affected (0.09 sec)
MariaDB [westos]> SHOW TABLES;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
| tom | 123 |
+----------+----------+
2 rows in set (0.00 sec)
(2)ALTER TABLE linux ADD class varchar(20) not null;##给表中添加一个字段,默认在最后添加
MariaDB [westos]> ALTER TABLE linux ADD class varchar(20) not null;
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+-------+
| username | password | class |
+----------+----------+-------+
| lee | 123 | |
| tom | 123 | |
+----------+----------+-------+
2 rows in set (0.00 sec)
(3)ALTER TABLE linux DROP class; ##移除表格中某一字段
MariaDB [westos]> ALTER TABLE linux DROP class;
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
| tom | 123 |
+----------+----------+
2 rows in set (0.00 sec)
(4)ALTER TABLE linux ADD class varchar(20) not null AFTER ##在username后面添加一字段
MariaDB [westos]> ALTER TABLE linux ADD class varchar(20) not null AFTER username;
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee | | 123 |
| tom | | 123 |
+----------+-------+----------+
2 rows in set (0.00 sec)
(5) UPDATE linux set password=‘456’ ##更改某一字段
MariaDB [westos]> UPDATE linux set password='456';
Query OK, 2 rows affected (0.37 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee | | 456 |
| tom | | 456 |
+----------+-------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> UPDATE linux set password='123';
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee | | 123 |
| tom | | 123 |
+----------+-------+----------+
2 rows in set (0.00 sec)
(6)UPDATE linux set password=‘456’ WHERE username=‘lee’ ##更改某一具体字段的某一内容
MariaDB [westos]> UPDATE linux set password='456' WHERE username='lee';
Query OK, 1 row affected (0.43 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| lee | | 456 |
| tom | | 123 |
+----------+-------+----------+
2 rows in set (0.00 sec)
4.数据库的删除
(1)DELETE FROM linux WHERE username=‘lee’; ##删除某一字段
MariaDB [westos]> DELETE FROM linux WHERE username='lee';
Query OK, 1 row affected (0.36 sec)
MariaDB [westos]> SELECT * FROM linux;
+----------+-------+----------+
| username | class | password |
+----------+-------+----------+
| tom | | 123 |
+----------+-------+----------+
1 row in set (0.00 sec)
(2) DROP TABLE linux; ##删除表格
MariaDB [westos]> DROP TABLE linux;
Query OK, 0 rows affected (0.13 sec)
MariaDB [westos]> SHOW TABLES;
Empty set (0.00 sec)
(3)DROP DATABASE westos; ##删除数据库
MariaDB [westos]> DROP DATABASE westos;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> SHOW TABLES;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
三.用户的授权与权力回收
1.用户的授权
[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.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 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]>CREATE USER lee@'localhost' indetified by 'westos'; ##给用户lee授权
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SELECT User FROM user; ##查看是否有新用户lee产生
+------+
| User |
+------+
| root |
| root |
| lee |
| root |
+------+
4 rows in set (0.00 sec)
MariaDB [mysql]>GRANT SELECT ON westos.* TO lee@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS FOR lee@localhost; ##查看lee现在拥有的权力
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT ON `westos`.* TO 'lee'@'localhost' 仅选泽权 |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysql -ulee -p ##用户lee可以登录数据库
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES; ##可以查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| 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]> INSERT INTO linux VALUES('westos','123'); ##在表格中插入字符不成功时没有授予其可写权力
ERROR 1142 (42000): INSERT command denied to user 'lee'@'localhost' for table 'linux'
MariaDB [mysql]> GRANT INSERT ON westos.* TO lee@localhost; ##授予其可写权
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS FOR lee@localhost; ##查看权力
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT, INSERT ON `westos`.* TO 'lee'@'localhost' ##选择,可写权力均有 |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> 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]> INSERT INTO linux VALUES('westos','123') ##插入字符
-> ;
Query OK, 1 row affected (0.45 sec)
MariaDB [westos]> SELECT * FROM westos;
ERROR 1146 (42S02): Table 'westos.westos' doesn't exist
MariaDB [westos]> SELECT * FROM linux; ##插入字符成功
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
| tom | 123 |
| westos | 123 |
+----------+----------+
3 rows in set (0.00 sec)
2.权力的回收
MariaDB [mysql]> REVOKE INSERT ON westos.* FROM lee@localhost; ##移除插入权力
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS FOR lee@localhost;
+-------------------------------------------------+
| Grants for lee@localhost |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' |
| GRANT SELECT ON `westos`.* TO 'lee'@'localhost' | ##插入权力被回收
+-------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> DROP USER lee@localhost; ##删除用户
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SELECT User FROM mysql.user; ##lee用户被删除
+------+
| User |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)
MariaDB [mysql]> FLUSH PRIVILEGES; ##刷新
Query OK, 0 rows affected (0.00 sec)‘
四.用户数据库的备份
1.数据库进行备份
[root@localhost ~]# mysqldump -uroot -p westos >/mnt/westos.sql ##将库westos备份至/mnt/westos.sql
Enter password:
[root@localhost ~]# mysqldump -uroot -p --all-database --no-data ##对所有库进行备份只备份结构不备份数据
Enter password:
-- MySQL dump 10.14 Distrib 5.5.52-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.52-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
2.对库进行删除
MariaDB [(none)]> DROP DATABASE westos; ##删除库westos
Query OK, 1 row affected (0.13 sec)
MariaDB [(none)]> SHOW DATABASES; ##此时库中无westos数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> exit
Bye
3.数据库的恢复
恢复方式1
[root@localhost ~]# mysql -uroot -p -e "CREATE DATABASE westos;" ##在数据库中新建一个库westos
Enter password:
[root@localhost ~]# mysql -uroot -p westos < /mnt/westos.sql ##将备份的数据放入新建的库中
Enter password:
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES; ##westos出现则备份成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
Database changed
MariaDB [westos]> SHOW TABLES; ##linux表格出现,备份成功
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec
恢复方式2
[root@localhost ~]# vim /mnt/westos.sql
[root@localhost ~]# mysql -uroot -p westos < /mnt/westos.sql
Enter password:
四.修改超级用户密码
1.当超级用户知道密码需要更改
[root@localhost ~]# mysqladmin -uroot -predhat password lee ##更改超级用户密码为lee
[root@localhost ~]# mysql -uroot -p ##登录数据库
Enter password: 输入密码lee登录成功
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
2.超级用户忘记密码需要更改
[root@shareserver ~]# systemctl stop mariadb.service ##关闭数据库服务
[root@localhost ~]# mysqld_safe --skip-grant-tables & ##跳过验证列表并且进入后台运行登录
[1] 7304
[root@localhost ~]# 190429 20:55:55 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
190429 20:55:55 mysqld_safe A mysqld process already exists
^C
[1]+ Exit 1 mysqld_safe --skip-grant-tables
[root@localhost ~]# mysql ##进入数据库
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> UPDATE mysql.user set Password=password('redhat') WHERE User='MariaDB [(none)]> UPDATE mysql.user set Password=password('redhat') WHERE User='root'; ##更改超级用户密码为redhat
Query OK, 1 row affected (0.00 sec)
Rows matched: 3 Changed: 1 Warnings: 0
MariaDB [(none)]> exit
Bye
[root@localhost ~]# ps aux | grep mysql ##查看mysql的所有进程
root 7043 0.0 0.1 113252 1576 pts/0 S+ 20:52 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 7198 0.0 8.4 906916 85624 pts/0 Sl+ 20:52 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 7558 0.0 0.0 112648 960 pts/2 R+ 21:02 0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 7043 ##关闭没有停止的进程7043
[root@localhost ~]# kill -9 7198 ##关闭没有停止的进程 7198
[root@localhost ~]# kill -9 7558
-bash: kill: (7558) - No such process
[root@localhost ~]# systemctl restart mariadb.service ##重启服务
[root@localhost ~]# mysql -uroot -p ##以超级用户身份登录数据库
Enter password: ##输入密码redhat,验证成功
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>