[Linux] Database management
Database (Database) is a warehouse built on computer storage equipment, which organizes, stores and manages data according to the data structure.
To put it simply, it can be regarded as an electronic filing cabinet-the place where electronic documents are stored, and users can add, intercept, update, delete and other operations on the data in the documents.
First, install and deploy http://www.daiqiyang.com
# the database is 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 enter directly here for the first time: set the password for the root of the database: make some initialization settings later, and generally choose Y.
# add a permanent permit policy to the firewall
[root@mail] # firewall-cmd-- permanent-- add-service=mysql
# reload firewall configuration
[root@mail] # firewall-cmd-- reload
Second, login and use
# logging in to the database system
[root@mail ~] # mysql-uroot-predhat / / Note there is no space between commands and parameters [root@mail ~] # mysql-uroot-p / / so that the login can hide the password [root@mail ~] # mysql-uroot-h localhost-p [DATABASE NAME]
-u: the user name that connects to the mysql server
-ip address or hostname of the h:mysql server
-p: password to connect to the mysql server
# check how many databases the system has
MariaDB [(none)] > show databases; / / commands in the database all end with;
# exit the database system
MariaDB [(none)] > quitMariaDB [(none)] > exit
# create a database
MariaDB [(none)] > create database luntan
# switch to a database
MariaDB [mysql] > use mysql
# View the 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)
# query the table structure of the created table
MariaDB [mysql] > desc person
# insert a few pieces of data
MariaDB [mysql] > insert into person (number,name,birthday) values (1, "", 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 the contents of the table
MariaDB [mysql] > delete from person where name= ""; MariaDB [mysql] > delete from person where number=3
# update the data in the table
MariaDB [mysql] > update person set name= "" where name= "xixi"; MariaDB [mysql] > update person set number=1 where birthday=20191226
Management of users and control of access rights
Create a database login 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 ()
Log in to the database using the Xiaoming user:
[root@localhost] # mysql-u xiaoming-p
# View databases that can be accessed
MariaDB [(none)] > show databases
# the right to log in to a table as a root user to a xiaoming user
MariaDB [(none)] > grant select,update,insert,delete on mysql.person to xiaoming@localhost
Exit the database system and log in again using the xiaoming user
[root@localhost ~] # mysql-u xiaoming-pMariaDB [(none)] > use mysql
# testing 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=1MariaDB [mysql] > delete from person where number=1
# Log in with root user and change the permissions of xiaoming user
MariaDB [(none)] > revoke delete on mysql.person from xiaoming@localhost
# use select statement to delete table data to confirm that permissions have been disabled
MariaDB [mysql] > delete from person where number=3
IV. Backup and restore
Back up all tables in the entire database
[root@mail ~] # mysqldump-u root-p mysql > / mysql_backup_20160510.dump / / make a backup file, location can be selected
# use root users to log in to the database and delete the person table
MariaDB [mysql] > drop table person
# exit the system and perform a restore operation
[root@mail] # mysql-u root-p mysql
< /mysql_backup_20160510.dump 或者使用source命令读入表信息。 #登陆数据库系统 [root@mail ~]# mysql -u root -p #查看person表 MariaDB [mysql]>Select * from person
-
Copyright notice: this article is the original article of CSDN blogger "pessimistic optimist", in accordance with the copyright agreement of CC 4.0BY-SA. Please attach the original source link and this statement for reprint.
Original link: https://blog.csdn.net/weixin_43997530/article/details/103634828
