3-unit8 Mariadb database

# unit8 database #

The topics covered in this module are:

* configure and manage the database

* configure database users and access permissions

* back up and restore the database

# basic sql statement operation of database #

1. Download and install

Yum install mariadb-server-ysystemctl start mariadb

two。 Database query operation:

Mysql # # Login

Show databases; # # display database

Use mysql; # # enter the mysql library show tables; # # to display the names of the tables in the current library

Desc linux; # # displays the fields of table linux in the mysql library

Select * from user; # # query everything in the user table (* any field in the table can be replaced) select User,Host,password from user; # # query the User,Host,password field in the user table quit # # exit

Netstat-antlpe | grep mysql # # View mysql status

Vim / etc/my.cnf

10 skip-networking=1systemctl restart mariadb

Mysql_secure_installation # # set up mysql service

User login

Mysl-uroot-pwestos # #-u indicates the designated login user, and-p indicates the user password mysql-uroot-p # # enter the password after login

3. Establishment of database and table

Create database westos; # # create westos database

Create table linux (# # create linux table with two fields username and password-> username varchar (20) not null, # # username field has a maximum length of 20 characters and cannot be empty-> password varchar (25) not null); # # password field has a maximum length of 25 characters and cannot be empty

Insert into linux values ('user1','westos123'); # # insert data into the linux table, the data in the username field is westos123 unencrypted insert into linux values (' user1',password ('123')); # # the data inserted into the password field is encrypted with password

4. Update database information

Update linux set password=password ('redhat') where username='user1'; # # update the password of user1 and encrypt

Update linux set password='redhat' where username='user1'; # # update the password of user1 without encrypting update linux set password='redhat' where (username='user1' or username='user2'); # # update the password of user1 and user2

Delete from linux where username='user1'; # # Delete user1 information

Alter table linux add class varchar (20) not null; # # add the class field to the last column in the linux table alter table linux add date varchar (20) not null after password; # # add the date field after the password field

Alter table linux drop class; # # Delete the class field from the linux table

5. Delete database

Delete from linux where username='user1'; # # Delete user1 information from the linux table drop table linux; # # Delete the linux table

Drop database westos; # # Delete westos library

6. Backup of database

Mysqldump-uroot-pwestos-- all-database # # back up all data in all tables

Mysqldump-uroot-pwestos-- all-database-- no-data # # back up all tables But do not back up data mysqldump-uroot-pwestos westos # # backup westos library mysqldump-uroot-pwestos westos > / mnt/westos.sql # # backup westos library and save all data to / mnt/westos.sqlmysqldump-uroot-pwestos westos linux > / mnt/linux.sql # # backup linux table in westos library and save all data to / mnt/linux.sqlmysql-uroot-pwestos-e "drop database westos "# # Delete the westos library mysql-uroot-pwestos-e" create database westos; "# # establish the westos library mysql-uroot-pwestos westos < / mnt/westos.sql # # transfer the data to the westos library mysql-uroot-pwestos-e" select * from westos.linux; "# query all the contents of the linux table in the westos library

Mysql-uroot-pwestos westos < / mnt/linux.sql # # Import data from linux table to westos library

7. User authorization

Create user cui@localhost identified by '123customers; # # create a user cui, this user can only log in to create user cui@'%' identified by' 123clients locally; # # create a user cui, which can log in through the network

Grant insert,update,delete,select on westos.linux to cui@localhost; # # user authorization grant select on westos.linux to cui@'%'

Show grants from cui@'%'; # # View user authorization show grants for cui@localhost

Revoke update on westos.linux from cui@localhost; # # remove the user's right to update revoke delete on westos.linux from cui@localhost; # # remove the user's right to delete

Drop user cui@'%'; # # deleting a user

Test:

Mysql-ucui-p123 # # login via local machine

Mysql-ucui-P123-h localhost

Vim / etc/my.cnf

10 skip-networking=0

Systemctl restart mariadb

Mysql-ucui-P123-h 172.25.254.162 # # Log in through the network

8. Password modification

Mysqladmin-uroot-pwestos password 123 # # change the superuser password

# when the super user forgets the password #

Ps aux | grep mysql # # filter all processes in mysql and end them

Kill-9 mysqlid # # forcibly stop the process

Systemctl restart mariadb

Mysqld_safe-skip-grant-table & # # enable the mysql login interface and ignore the authorization table

Mysql # # login directly without a password

Update mysql.user set Password=password ('123') where User='root'; # # update superuser password information

Mysql-uroot-p123 # # login test

# Database web page management #

1. Installation

Yum install httpd php php-mysql-y

Systemctl start httpd # # enable httpd

Systemctl enable httpd

Systemctl stop firewalld # # close the fire wall

Systemctl disable firewalld

Need to download

PhpMyAdmin_4.7.0_beta1_all_languages.zip # # download the database web page software package

Tar jxf phpMyAdmin_4.7.0_beta1_all_languages.tar.gz2-C / var/www/html # # extract the package and store it in / var/www/html

Mv mv phpMyAdmin-4.7.0-beta1-all-languages/ / var/www/html/mysqladmin # # modify the file name

Cd mysqladmin

Cp-p config.sample.inc.php config.inc.php # # modify configuration file

Vim config.inc.php

17$ cfg ['blowfish_secret'] =' mysql'; / * YOU MUST FILL IN THIS FOR COOKIE AUTH! * /

Systemctl restart httpd

Test:

Visit http://172.25.254.162/mysqladmin