1. Install the main program and server of MariaDB database

[root@fudanwuxi ~]# yum install mariadb mariadb-server.x86_64
[root@fudanwuxi ~]# systemctl restart mariadb.service 
[root@fudanwuxi ~]# systemctl enable mariadb.service        
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

2. Initialization

[root@fudanwuxi ~]# mysql_secure_installation   #Installation, i.e. initialization

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):   #Enter the password of the root user of the current database. If there is no password at the beginning, press enter directly
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   #Set password for database root

New password:   #First pass code
Re-enter new password:   #Second pass 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  #Remove anonymous users
 ... 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  #Forbid remote login of database root user
 ... 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  #Remove test database and related data
 - 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  #Reload for initialization to take effect immediately
 ... 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!

3. Login database (change password, create user, authorize, cancel authorization)

• Change root password

[root@fudanwuxi ~]# mysql -u root -p  #-u is used to specify logging in as database user root, - p is used to verify the password of user database
Enter password:   #Enter the database password for user root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;  #See which databases are available
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> set password = PASSWORD('fudanwuxi');  #Change password to fudanwuxi
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit  #Exit database
Bye
[root@fudanwuxi ~]# mysql -u root -p  #Perform login test again
Enter password:   #Refuse to log in when entering the initialization password
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@fudanwuxi ~]# mysql -u root -p  #Retest login
Enter password:   #Enter the password you just modified fudanwuxi and log in successfully
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

• Create a normal user

[root@fudanwuxi ~]# mysql -u root -p  #Log in as database root user
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> create user ajie@localhost identified by 'ajie123';  #Create a database user Ajie with the password of ajie123
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit  #Exit root login
Bye
[root@fudanwuxi ~]# mysql -u ajie -p  #Login with ajie
Enter password:   #Enter the password ajie123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;  #You can only view one database because of insufficient permissions
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

• General users' rights of database

[root@fudanwuxi ~]# mysql -u root -p  #Log in with the database root user
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> grant select,update,delete,insert ON mysql.* to ajie@localhost;  #Give ajie users query, update, delete and insert permissions. Mysql. * indicates that the permission granted to ajie is valid for all forms under mysql
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@fudanwuxi ~]# mysql -u ajie -p  #Exit root and use ajie to log in
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;  #When viewing all databases, you can see that mysql is available
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use mysql  #Enter to use mysql database
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;  #View all forms in mysql database
+---------------------------+
| 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.01 sec)

MariaDB [mysql]> 

• Cancel normal user authorization

[root@fudanwuxi ~]# mysql -u root -p  #Log in to root
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> revoke select,update,delete,insert on mysql.* from ajie@localhost;   #Remove the permissions you just granted to ajie
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for ajie@localhost;  #View the permission of user ajie. There is no permission granted previously
+-------------------------------------------------------------------------------------------------------------+
| Grants for ajie@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ajie'@'localhost' IDENTIFIED BY PASSWORD '*A65FBC245EF7CC4346000B7CFA058E0D5A234219' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit  #Sign out
Bye
[root@fudanwuxi ~]# mysql -u ajie -p  #Login with ajie
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;  #Check the database, and you can't see the mysql database that you could see when you granted permission
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

4. Create databases and forms (find, modify, delete)

• Create a database wechat

[root@fudanwuxi ~]# mysql -u root -p  #Log in as root
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database wechat;  #Add a new database named wechat. Delete use drop.
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;  #Check the database again. There is another wechat
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wechat             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

• Create the form myid in the database wechat

MariaDB [(none)] > use wechat; 񖓿 enter the use database wechat
Database changed
MariaDB [wechat]> create table myid(name char(15),address char(15),price int);  
#Create a form named myid.
Define the structure of storing data content, three fields,
The 15 character field name stores the wechat name;
address storage area of 15 character field;
The integer field price stores the sales price.
Query OK, 0 rows affected (0.01 sec)

MariaDB [wechat] > show tables; (to view the form again, there is a new myid form just created
+------------------+
| Tables_in_wechat |
+------------------+
| myid             |
+------------------+
1 row in set (0.00 sec)

MariaDB [wechat] > describe myid; initialize the new form myid
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(15) | YES  |     | NULL    |       |
| address | char(15) | YES  |     | NULL    |       |
| price   | int(11)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [wechat]> 

• Insert data and query data into form myid

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('jschinamobile','jiangsu','999');  #Insert a piece of information, name, address and price into the form myid, and their values correspond to jschinamobile,jiangsu,999 respectively
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> select * from myid;  #Query all data in the myid form
+---------------+---------+-------+
| name          | address | price |
+---------------+---------+-------+
| jschinamobile | jiangsu |   999 |
+---------------+---------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select name,price from myid;   #Query only name and price data in myid form
+---------------+-------+
| name          | price |
+---------------+-------+
| jschinamobile |   999 |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> update myid set price='666666' where name='jschinamobile';  #Change the price value with the name value of jschinamobile to 6666
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [wechat]> select * from myid;  #To view the form, the price value changes to 6666
+---------------+---------+--------+
| name          | address | price  |
+---------------+---------+--------+
| jschinamobile | jiangsu | 666666 |
+---------------+---------+--------+
1 row in set (0.00 sec)

MariaDB [wechat]> delete from myid;  #Delete everything in the myid form
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> select * from myid;  #Query the myid form, and the prompt is empty
Empty set (0.00 sec)

MariaDB [wechat]> 

• Specified search

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('jschinamobile','jiangsu','999'); 
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('www.wuxier.cn','jiangsu','555');  
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('fudan','shanghai','1000');        
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('ajie','dongtai','666');           
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> select * from myid;
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| www.wuxier.cn | jiangsu  |   555 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
4 rows in set (0.00 sec)

MariaDB [wechat]> select * from myid where name='ajie';  #Find the one whose name value is ajie in the form myid
+------+---------+-------+
| name | address | price |
+------+---------+-------+
| ajie | dongtai |   666 |
+------+---------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select name,price from myid where name='ajie';   #The search value is ajie, only the information of name and price
+------+-------+
| name | price |
+------+-------+
| ajie |   666 |
+------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid where price='555';   #Find the value 555 in the form myid      
+---------------+---------+-------+
| name          | address | price |
+---------------+---------+-------+
| www.wuxier.cn | jiangsu |   555 |
+---------------+---------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid where price>'600';  #Find the one whose price value is greater than 600 in myid
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
3 rows in set (0.00 sec)

5. Backup of database

• Backup database wechat

[root@fudanwuxi ~]# mysqldump -u root -p wechat > /root/backup_wechat.dump  
#Use root to backup, - p means that it has a password, wechat is the database to be backed up, and the name of the backup under / root / is backup [wechat. Dump
Enter password:   #Enter the password of the database root user
[root@fudanwuxi ~]# ll /root/
total 12
-rw-------. 1 root root 1783 May 14 12:46 anaconda-ks.cfg
-rw-r--r--. 1 root root 1972 Aug  6 14:35 backup_wechat.dump  #Check the / root / directory and generate the backup file [wechat. Dump]
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Desktop
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Documents
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Downloads
-rw-r--r--. 1 root root 1831 May 14 12:53 initial-setup-ks.cfg
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Music
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Pictures
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Public
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Templates
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Videos

• Delete data of form myid in database wechat

[root@fudanwuxi ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 |
| mysql              |
| performance_schema |
| wechat             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use wechat
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 [wechat]> show tables;
+------------------+
| Tables_in_wechat |
+------------------+
| myid             |
+------------------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid;
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| www.wuxier.cn | jiangsu  |   555 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
4 rows in set (0.00 sec)

MariaDB [wechat]> delete from myid;     #delete 
Query OK, 4 rows affected (0.01 sec)

MariaDB [wechat]> select * from myid;   #The data in the query form myid is empty
Empty set (0.00 sec)

• Recover database

[root@fudanwuxi ~]# mysql -u root -p wechat < /root/backup_wechat.dump   #Input reset import data wechat for restore
Enter password: 
[root@fudanwuxi ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 |
| mysql              |
| performance_schema |
| wechat             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use wechat;
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 [wechat]> show tables;
+------------------+
| Tables_in_wechat |
+------------------+
| myid             |
+------------------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid;   #View the information in the form myid. The restore is successful
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| www.wuxier.cn | jiangsu  |   555 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
4 rows in set (0.02 sec)