The 18th chapter uses the MARIADB database management system

Chapter Overview:The MySQL database management system was changed from open source to closed by Oracle, resulting in the selection of MARIADB for many Linux distributions, including Red Hat. This section will teach you to use MARIADB database management tools to manage your database and learn about common operations such as creating, searching, updating, inserting, and deleting data forms. and proficient in database user creation and authorization, database backup and recovery methods, not only to meet the requirements of RHCE exam questions, but also to help your operation and maintenance work.

Chapter directory Structure

• 18.1 Database Management System
• 18.2 Initializing the MARIADB service program
• 18.3 managing databases and form data
  • 18.3.1 create a user and authorize
  • 18.3.2 Creating a database and forms
  • 18.3.3 managing form data

18.1 Database Management system

Our lives are constantly exposed to data, and databases are warehouses that store this data through a specified organizational structure, and with the development of the Internet and information technology, the database has evolved from the initial storage of simple tables to large distributed patterns that store massive amounts of data.

In the information society, it is a precondition for scientific research and decision management to fully and effectively manage and utilize all kinds of information resources. Database technology is the core of information system, such as management information System, office automation system, decision support system, and is an important technical means for scientific research and decision-making management. Database management system (i.e.Database Management System) is a software program that can establish, use and maintain the database, and the database management system makes it easy for users to maintain the security and usability of the database by transforming the specific physical data in the computer into abstract logical data that is suitable for the user to understand.

MYSQLis a well-known database management system, mature technology, simple configuration, open source free and good extensibility, but MySQL after the acquisition of Oracle company from the open source into a closed, slow update to many Linux distributions (such as Red HatRHEL7、Fedora、Centos、OpenSUSE、SlackwareAnd so on, and many have decided to abandon the former most popular database management system, and turned to mariadb. MariaDBis a MySQL database management system maintained by the open Source Community Branch products, fully compatible with MySQL, frankly, although Google and Wikipedia, the industry giants have adopted the MARIADB, but does not mean that there is a significant performance improvement than MySQL, It is a decision made from a technology monopoly perspective.

Have a problem? Dare to ask!

Because the reader hardware is different or the operation error may lead to the experimental configuration error, please be patient and carefully look at the operation steps, don't be discouraged ~

Linux Technical Exchange Please add a group: 560843 ( full ), Group B: 340829 ( recommended ), Group C: 463590 ( recommended ), click here to view the national group.

* This group features: through password verification to ensure that every group is the "Linux on this Learning" reader, answer more targeted, free of charge to receive customized gifts on a regular basis.

18.2 Initializing the MARIADB service program

MARIADB does have many features that are functionally extensible relative to MySQL, such as microsecond support , thread pooling , subquery optimization , Group submission , progress reporting and so on.

Install the MARIADB service program:

[[email protected] ~]# yum install mariadb mariadb-serverloaded plugins:langpacks, Product-id, Subscription-manager ..... ......... Omit part of the installation process ...... .....                  INSTALLING:MARIADB x86_64 1:5.5.35-3.el7 rhel7 8.9 M mariadb-server         x86_64 1:5.5.35-3.el7 rhel7 minstalling for Dependencie perl-compress-raw-bzip2           x86_64 2.061-3.el7 rhel7 k perl-compress-raw-zlib x86_64 1:2.061-4.el7 Rhel7 k perl-dbd-mysql x86_64 4.023-5.el7 rhel7 k perl-          DBI x86_64 1.627-4.el7 rhel7 802 k Perl-data-dumper X86_64             2.145-3.el7 rhel7 k perl-io-compress Noarch 2.061-2.el7          RHEL7 260 k Perl-net-daemon Noarch 0.48-5.el7 rhel7 Wuyi k Perl-plrpc            Noarch 0.2020-14.el7 rhel7 k ......... ..... Omit part of the installation process ...... ..... complete!

Start the MARIADB service program and add it to the boot entry:

[Root@Linuxprobe ~]# systemctl Start mariadb
[Root@Linuxprobe ~]# Systemctl Enable MARIADB
LN - S '/usr/lib/systemd/system/mariadb.service ' '/etc/systemd/system/multi-user.target.wants/mariadb.service '

In order to ensure the security of the database, it is necessary to initialize the work:

1th Step: Set the root user password.
2nd Step: Delete the anonymous account.
3rd step: Prohibit the root user from remote login.
4th step: Delete the test database and remove access to it.
5th step: Refresh the authorization table, so that the initialization of the settings immediately effective.

Initialize the database service program:

[Email protected] ~]#mysql_secure_installation/usr/bin/mysql_secure_installation:line 379:find_mysql_client:command not foundnote:running all PARTS of this SCRIPT I  S 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 CurrentPassword for the root user. If you ' ve just installed MariaDB, Andyou Haven ' t set the root password yet, the password would be a blank,so you should just Press ENTER here. Enter current password to root (enter for none):The current database password is empty, hit enter directly. OK, successfully used password, moving on ... Setting The root password ensures that nobody can log into the Mariadbroot user without the proper authorisation. Set root Password? [y/n]yNew Password:Enter the database password that you want to set for the root user. Re-enter new password:Repeat and enter the password again. Password Updated successfully! Reloading privilege tables. ... success!  By default, a MariaDB installation had an anonymous user, allowing Anyoneto log into MariaDB without had to had a user  Account created Forthem.  This was intended only for testing, and the Installationgo a bit smoother. You should remove them before moving into aproduction environment. Remove anonymous users? [y/n]y (delete anonymous account)... success!  Normally, Root should only is allowed to connect from ' localhost '. Thisensures that someone cannot guess at the root of password from the network. Disallow Root login remotely? [y/n]y (disable root user from remote login)... success!  By default, the MariaDB comes with a database named ' Test ' anyone canaccess. This was also intended only for testing, and should was removedbefore moving into a production environment. Remove test database and access to it? [y/n]y (delete the test database and remove access to it)-Dropping test database ... success! -Removing privileges on test database ... success! Reloading the privilege tables would ensure that all changes made so farwill take effect immediately. Reload privilege tables now? [y/n]y (Refresh the authorization table so that the initialization setting takes effect immediately)... success! Cleaning up ...  All done! If you've completed all of the above steps, your mariadbinstallation should now is secure. Thanks for using mariadb!

Set the firewall's allow policy for the database service:

[Email protected] ~]# firewall-cmd--permanent--add-service=mysqlsuccess[[email protected] ~]# firewall-cmd-- Reloadsuccess

Log in to the database using the root user:

[Email protected] ~]# mysql-u root-penter Password: Enter the password for the root user in the database. Welcome to the MariaDB Monitor. Commands End With; or \g.your MariaDB connection ID is 5Server version:5.5.35-mariadb MariaDB servercopyright (c) $, Oracle, Monty Program Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. MariaDB [(None)]>

To view the currently existing database:

MariaDB [(None)]> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema |+--------------------+ 3 rows in Set (0.01 sec)

Modify the password for the current user in the database (the password in the example is Redhat):

MariaDB [(none)]> Set password = password (' Redhat '); Query OK, 0 rows Affected (0.00 sec) MariaDB [(none)]> Exitbye

Using the old password will no longer log in to the database:

[Email protected] ~]# mysql-u root-penter password:error 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)

18.3 managing databases and form data

A relational database ismade up of one or more data forms (tables)that typically hold multiple data records (record).

18.3.1 Create a user and authorize

To create a new database user:

command to create a database user: "Create user username@ hostname identified by ' password ';

MariaDB [(None)]> create user [email protected] identified by ' linuxprobe '; Query OK, 0 rows Affected (0.00 sec)

Go to the MySQL database:

MariaDB [(none)]> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-a

To view the newly created user , host , name , and password information :

MariaDB [mysql]> Select Host,user,password from user where user= "Luke"; +-----------+------+----------------------- --------------------+| Host      | user | password                                  |+-----------+------+-------------------------------------------+| localhost | luke | * 55d9962586be75f4b7d421e6655973db07d6869f |+-----------+------+-------------------------------------------+1 row In Set (0.00 sec)

Log in with a new user after exiting the database:

the database password for the Luke user is entered here Welcome to the MariaDB Monitor.  Commands End With; or \g.your MariaDB connection ID is 6Server version:5.5.35-mariadb MariaDB servercopyright (c) $, Oracle, Monty Program Ab and others. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Only one database can be viewed at this time:

MariaDB [(None)]> show databases;+--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in Set (0.03 sec)

Common scenarios for authorizing operations of the database Grant command:

Edit

Command	Role
GRANT permissions on the database. Form name to User name @ host name	Grant authorization for a specific form in a particular database.
GRANT permissions on the database. * To User name @ host name	Grant authorization for all forms in a particular database.
GRANT Permissions on * * To User name @ host name	Grant Authorization for all databases and all forms.
GRANT permissions 1, permissions 2 on the database. * to username @ host Name	Give multiple authorizations to all forms in a database.
GRANT all privileges on * * To User name @ host name	Give full authorization to all databases and all forms (with care).

Switch back to the root user login database and go to the MySQL database:

[[email protected] ~]# mysql-u root-pmariadb [(none)]> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changed

Give Luke users the ability to query , update , delete , and insert user forms:

MariaDB [mysql]> GRANT Select,update,delete,insert on mysql.user to [email protected]; Query OK, 0 rows Affected (0.00 sec)

To view the current authorization of the Luke User:

MariaDB[(None)]&Gt Show grants forLuke@localhost
+-------------------------------------------------------------------------------------------------------------+
|Grants forLuke@localhost|
+-------------------------------------------------------------------------------------------------------------+
|GRANT USAGE on*.*To' Luke '@' localhost 'Identified by PASSWORD' *55d9962586be75f4b7d421e6655973db07d6869f ' |
|GRANT SELECT, INSERT, UPDATE, DELETE on`Mysql`.`User`To' Luke '@' localhost ' |
+-------------------------------------------------------------------------------------------------------------+
2Rowsinch Set (0.00Sec)

Switch to the Luke user again to view the available databases:

[[email protected] ~]# mysql-u luke-pmariadb [(None)]> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              |+--------------------+2 rows in Set (0.01 sec)

Go to the MySQL database and see the user form:

MariaDB [(None)]> use mysqlreading table information for completion of table and column namesyou can turn off this feat Ure to get a quicker startup With-adatabase changedmariadb [mysql]> show tables;+-----------------+| Tables_in_mysql |+-----------------+| User            |+-----------------+1 row in Set (0.01 sec)

After switching back to the root user, cancel just all of the authorizations:

MariaDB [(none)]> revoke Select,update,delete,insert on mysql.user from [email protected]; Query OK, 0 rows Affected (0.00 sec)

Check again for the permission of the Luke User:

MariaDB[(None)]&Gt Show grants forLuke@localhost
+-------------------------------------------------------------------------------------------------------------+
|Grants forLuke@localhost|
+-------------------------------------------------------------------------------------------------------------+
|GRANT USAGE on*.*To' Luke '@' localhost 'Identified by PASSWORD' *55d9962586be75f4b7d421e6655973db07d6869f ' |
+-------------------------------------------------------------------------------------------------------------+
1Rowinch Set (0.00Sec)

The 18th chapter uses the MARIADB database management system