Linux Beginner-Database Management MARIADB Chapter

Linux Beginner-Database management MARIADB Chapter

MARIADB is a database management system, a branch of MySQL, but better than MySQL, can be said to be a substitute for MySQL. MARIADB is using an SQL statement. The following will provide a brief overview of MARIADB installation, Setup, and some basic SQL statements.

1. Installation

In the case of a Yum source, enter "Yum install mariadb-server.x86_64-y" To install the MARIADB database, different systems may have different versions.

2. Security settings

Because MARIADB is a database management system, security is a must, the most basic security settings have the following two steps.

First step, enter "Netstat-antple | grep MySQL "To see if the network port is open, as shown, is open. Edit the Profile "/etc/ym.cnf", enter "Skip-networking=1" in the location to close the port, restart the service, and then view the network port off.

The second step, enter "Mysql_secure_installation" to set the root user login password, as shown, enter two password, the system will ask some questions, for security reasons, it is recommended to select "Y".

When the above two steps are complete, the system's network port is turned off and the root user needs to enter a password to log on to the database.

3, the use of the database

Before the narrative, MARIADB is the database management system, and the use of SQL statements, the following will be described in the database used in the use of SQL statements, the General specification of SQL statements need to be uppercase, but lowercase can also be used. Here for convenience, do not make a distinction.

3.1, the database query

"Show datebases;" To display the database.

"Use MySQL;", enter a data.

"Show tables;" Displays the tables in the database.

"desc user;", to view a table named "User" information.

"SELECT * from User;" To view all the contents of this table named "User".

"SELECT * from user where host= ' 127.0.0.1 ';" To view the "Host" in the table named "User" is part of "127.0.0.1".

Select Host,process_priv from user, view the "Host" and "Process_priv" two fields from the "User" table.

3.2, the creation of the database

"CREATE database Hello;", creating a db named Hello.

"CREATE TABLE Userinf (*);", after entering the Hello database, a table named "Userinf" is created, as shown in the format and contents of the table can be written by itself.

"INSERT into Userinf values (' Tom ', ' 111 ', ' 20 ');", insert a value in this "userinf" table, as shown, you can insert a value in an empty table without writing a value, but must have a format, for example.

3.3. Changes in the database

The name of the current database can not be changed, only the data of the database can be backed up, and then re-establish a new database, import data into.

"ALTER TABLE Userinf rename information;", change the name of the table "Userinf" to "information".

"ALTER TABLE Userinf Add class varchar (50);", Add a column "class" At the end of the table "Userinf".

"ALTER TABLE Userinf drop class;", delete the "Class" column of Table "Userinf".

"ALTER TABLE Userinf Add Class varchar () after username;", adding The "Class" column after the "username" column in table "Userinf".

"Update Userinf set class= ' 3 ';" To change the data in the "Class" column in Table "Userinf" to "3".

"Pdate Userinf set class= ' 1 ' where username= ' Tom ';" Change the row of "Userinf" in table "username" to "Tom" to "1".

3.4, delete in the database

"Delete from Userinf where username= ' Tom ' and class= ' 1 ';", deleting "username" in the "Userinf" file is "Tom" and "class" is the line of "1".

"DROP table Userinf;", delete the table "Userinf".

"Drop database Hello;", delete the Databases "Hello".

4. Backup and Recovery of database

In the process of database management, database backup is often required.

"Mysqldump-uroot-predhat We >/mnt/we.sql", you can back up the "we" database, the backup file exists in the/mnt/we.sql file.

As shown, if the database "WE" is accidentally deleted, then it needs to be recovered by this backup.

To restore the backup, first enter "MYSQL-UROOT-PREDHAT-E" CREATE database we; ", to create a new databases" we ", or other names can also, you can also use the command" CREATE database we; " Established within the database.

By entering "Mysql-uroot-predhat we </mnt/we.sql", we can import the data from the original we database into a new database.

5. Forget the root user password

In the management of the database, if you forget the root user's password to reset it, the setup steps are as follows:

A, "Systemctl stop mariadb". Stop MARIADB.

B, "Mysqld_safe--skip-grant-tables &". Start Safe mode and skip the certification mark.

C, "Mysqld". No password is required to enter the database directly, enter "use MySQL" to enter the MySQL database.

D, "Update user set Password=password (' Redhat ') where user= ' root ';". Modify the "root" user's password information in the "User" table, "Password=password (' redhat ')" means the password is set to "Redhat", the preceding "Password" means to encrypt "redhat".

E, "PS Ax | grep MySQL "," kill-9 PID ". Forcibly end all processes of "MySQL".

After the above steps are completed, re-open the mariadb, then you can enter the password to log in to the database.

6. Set up users and access rights

In the use of mariadb, it is sometimes necessary to add some other users so that these users can log in and have certain permissions in the database. The specific practice is shown below.

"Create user [email protected] identified by ' Redhat ';". means to create a user, the user can only log in from the local, login password for "Redhat". If "[email protected]" is changed to "[email protected] '% '", then the representative can log in from anywhere.

After a successful creation, enter "SELECT * from Mysql.user where user= ' we '" to see the created user information.

This user is not authorized after the creation, although it can be logged in, but cannot see or modify any data in the database.

"Grant Select on hello.* to [email protected];", which means that the view permission in the "Hello" database is granted to the "we" user.

"Show grants for [email protected];", you can view the authorization list for the specified user, as shown below, "we" user has view power in the "Hello" database. If authorized but not visible, enter "flush privileges;" You can reload the authorization table.

After the authorized "we" user re-login to the database can see the "Hello" database data. You can also grant power such as "Insert", "Update", "delete", and "drop", and only the granted power can be used.

"Revoke select on hello.* from [email protected];", which means to revoke the viewing power of the user "we".

"Drop user [email protected];", means delete "we" user, delete and re-enter "SELECT * from Mysql.user where user= ' we '" will not see this user's information.

Linux Beginner-Database Management MARIADB Chapter