[Linux] Database Management

Keywords: Linux MySQL MariaDB Database firewall

The database organizes, stores and manages data according to the data structure. It is a warehouse built on the computer storage device.

Simply speaking, it can be regarded as an electronic file cabinet itself - the place where electronic files are stored. Users can add, intercept, update and delete data in files.
1. Install and deploy http://www.daiqiyang.com

#The database is already installed by default, if not, use the following command to install it

[root@mail ~]# yum install -y mariadb

#Start the database service

[root@mail ~]# systemctl restart mariadb

#Initialize the database

[root@mail ~]# mysql_secure_installation                                         

First direct return here:

Set the password for the root of the database: 

 Later, do some initialization settings, generally Y is OK.

#Add Permanent Allow Policy to Firewall

[root@mail ~]# firewall-cmd --permanent --add-service=mysql

#Reload Firewall Configuration

[root@mail ~]# firewall-cmd --reload

2. Landing Use

#Database System Logon

[root@mail ~]# Mysql-uroot-predhat //Note that there is no space between command and parameter here

[root@mail ~]# Mysql-uroot-p //so login can hide password  

[root@mail ~]# mysql -u root -h localhost -p [DATABASE NAME]

-u: The user name to connect to the mysql server;

-h: ip address or hostname of mysql server;

-p: Password to connect to mysql server;

#See how many databases your system has

MariaDB [(none)]> show databases;                                          //Commands in the database end with;

#Exit the database system

MariaDB [(none)]> quit

MariaDB [(none)]> exit

#Create a database

MariaDB [(none)]> create database luntan;

#Switch to a database

MariaDB [mysql]> use mysql;

#View tables in the database

MariaDB [mysql]> show tables;

#View the table structure of the data table

MariaDB [mysql]> desc user;

#Query some data in the user table

MariaDB [mysql]> select host,user,password from user;

#Create a table

MariaDB [mysql]> create table person (

    -> number int(11),

    -> name varchar(255),

    -> birthday date);

#Table structure of table created by query

MariaDB [mysql]> desc person;

#Insert several pieces of data

MariaDB [mysql]> insert into person (number,name,birthday) values (1,"haha",20191225);

MariaDB [mysql]> insert into person (number,name,birthday) values (2,"xixi",20191226);

MariaDB [mysql]> insert into person (number,name,birthday) values (3,"hehe",20191227);

#Query table contents

MariaDB [mysql]> select * from person;

#Delete table contents

MariaDB [mysql]> delete from person where name="haha";

MariaDB [mysql]> delete from person where number=3;

#Update data in tables

MariaDB [mysql]> update person set name="haha"  where name="xixi";

MariaDB [mysql]> update person set number=1 where birthday=20191226;

3. User management and control of access rights

Create Database Logon User

MariaDB [mysql]> create user xiaoming@localhost identified by 'redhat';

MariaDB [mysql]> create user xiaohong@localhost identified by "redhat";

MariaDB [mysql]> select host,user,password from user;

View current users:

MariaDB [(none)]> select user();

View the current user's database:

MariaDB [(none)]> select database();

Use Xiaoming user to log in to the database:

[root@localhost ~]# mysql -u xiaoming -p

#View accessible databases

MariaDB [(none)]> show databases;

#Permissions to log in as root to a table for the xiaoming user

MariaDB [(none)]> grant select,update,insert,delete on mysql.person to xiaoming@localhost; 

Exit the database system and log back in using the xiaoming user

[root@localhost ~]# mysql -u xiaoming -p

MariaDB [(none)]> use mysql;

#Test various permissions

MariaDB [mysql]> select * from person;

MariaDB [mysql]> insert person (number,name,birthday) value (3,"xiaoming",20181228);

MariaDB [mysql]> update person set name="xixi" where number=1

MariaDB [mysql]> delete from person where number=1;

#Change the permissions of the xiaoming user by logging in with the root user

MariaDB [(none)]> revoke delete on mysql.person from xiaoming@localhost;

#Use the select statement to delete table data and confirm that permissions are disabled

MariaDB [mysql]> delete from person where number=3 ;

IV. Backup and Restore

Back up all tables of the entire database

[root@mail ~]# MySQL dump-u root-p MySQL >/mysql_backup_20160510.dump // Make a backup file in a location of your choice

#Log in to the database using the root user and delete the person table

MariaDB [mysql]> drop table person;

#Exit the system for restore

[root@mail ~]# mysql -u root -p mysql < /mysql_backup_20160510.dump

Or use the source command to read table information.

#Log on to the database system

[root@mail ~]# mysql -u root -p

#View person table

MariaDB [mysql]> select * from person;

--------
Copyright Statement: This is an original article by CSDN blogger "Pessimistic Optimist", which follows the CC 4.0 BY-SA copyright agreement. Please attach a link to the original source and this statement.
Original Link: https://blog.csdn.net/weixin_43997530/article/details/103634828

Posted by matrixd on Wed, 01 Jan 2020 22:34:22 -0800