Database mariadb for Linux

I. Introduction to Database MARIADB

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9E/3B/wKioL1mNsMqjnU3TAAhNTvMHGrE162.png "title=" Screenshot from 2017-08-11 21-26-50.png "alt=" Wkiol1mnsmqjnu3taahntvmhgre162.png "/>

Two. Install and start the service, check the port status

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9E/4C/wKiom1mNseTRG9QQAAAvppDH9mM905.png "style=" float : none; "title=" screenshot from 2017-08-10 17-18-19.png "alt=" Wkiom1mnsetrg9qqaaavppdh9mm905.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9E/4C/wKiom1mNsdDTFBcfAACpGLcN5Mw565.png "style=" float : none; "title=" screenshot from 2017-08-10 16-59-06.png "alt=" Wkiom1mnsddtfbcfaacpglcn5mw565.png "/>

Three. Preparation before use after installation

1. Turn off the database-enabled network interface

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9E/3B/wKioL1mNsdfR3zbuAABdOiyHLes932.png "style=" float : none; "title=" screenshot from 2017-08-10 17-00-49.png "alt=" Wkiol1mnsdfr3zbuaabdoiyhles932.png "/>

Vim/etc/my.cnf

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9E/3B/wKioL1mNsdbj-8dMAAC7wRlJVJ0253.png "style=" float : none; "title=" screenshot from 2017-08-10 17-00-12.png "alt=" Wkiol1mnsdbj-8dmaac7wrljvj0253.png "/>

Skip-networking=1 shutting down a database-enabled network interface

2. Database Security Initialization
Mysql_secure_installation

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9E/4C/wKiom1mNsd_AtMTyAAFtVSqYuEQ271.png "style=" float : none; "title=" screenshot from 2017-08-10 17-02-26.png "alt=" Wkiom1mnsd_atmtyaaftvsqyueq271.png "/>

Question Explanation:

Enter current password to root (enter for none): Database original password (no direct carriage return by default)
Set root Password? [y/n] Whether to set the database super user password
New Password: Enter the super user password you want to set
Re-enter new password: repeat input

Remove anonymous users? [y/n] Whether to remove anonymous user access rights

Disallow Root login remotely? [y/n] Whether to prohibit the super-user from remote login

Remove test database and access to it? [y/n] Refreshing the database

All point to enter can be

Four. Basic SQL statement operations for the database
1. Login
Mysql-uroot-pwestos-u indicates the specified login user, and-p indicates that the user password is specified

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9E/3B/wKioL1mNseOyJ5zQAAChjzhMVdA013.png "style=" float : none; "title=" screenshot from 2017-08-10 17-02-55.png "alt=" Wkiol1mnseoyj5zqaachjzhmvda013.png "/>

2. Query (query language uppercase and lowercase letters can be used, enterprises in order to standardize the use of capital)
show databases; Display Database

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9E/4D/wKiom1mNtYjTmFuKAADfoNEHkdA416.png "title=" Screenshot from 2017-08-11 09-24-30.png "alt=" Wkiom1mntyjtmfukaadfonehkda416.png "/>

Use MySQL; Go to MySQL Library

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9E/3F/wKioL1mOV8Wxr-MMAABZNfql6AA874.png "title=" Screenshot from 2017-08-12 09-17-34.png "alt=" Wkiol1mov8wxr-mmaabznfql6aa874.png "/>

Show tables; Displays the name of the table in the current library

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9E/3B/wKioL1mNtbGh_J5gAACy91jUKCw931.png "title=" Screenshot from 2017-08-11 09-24-42.png "alt=" Wkiol1mntbgh_j5gaacy91jukcw931.png "/>

DESC user; Querying the structure of the user table (displays the names of all fields)

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/9E/3B/wKioL1mNthyS56fWAACCad2JXnE722.png "title=" Screenshot from 2017-08-11 09-24-49.png "alt=" Wkiol1mnthys56fwaaccad2jxne722.png "/>

select * from user; Query all content in the user table (* can be replaced with any field in this table)

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9E/4D/wKiom1mNtjbAG2ZnAAD7y0HZulM463.png "title=" Screenshot from 2017-08-11 09-24-56.png "alt=" Wkiom1mntjbag2znaad7y0hzulm463.png "/>

Plus query criteria

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9E/4D/wKiom1mNtoGROjdfAAGTxAnQvsI603.png "title=" Screenshot from 2017-08-11 09-25-18.png "alt=" Wkiom1mntogrojdfaagtxanqvsi603.png "/>

3. Establishment of databases and tables
Create Database Westos; Build a Westos library, create a Linux table

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9E/4D/wKiom1mNt1CCP9OLAACjb0sLg58265.png "title=" Screenshot from 2017-08-11 09-44-02.png "alt=" Wkiom1mnt1ccp9olaacjb0slg58265.png "/>

INSERT into Linux values (' User1 ', ' passwd1 '); Adding information from a Linux table

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/9E/3B/wKioL1mNt5WAyHL3AAAq4xgduxg738.png "title=" Screenshot from 2017-08-11 09-44-18.png "alt=" Wkiol1mnt5wayhl3aaaq4xgduxg738.png "/>650" this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9E/4D/wKiom1mNt6_BzuApAAA6wRG4fzs161.png "title=" screenshot from 2017-08-11 09-44-27.png "alt=" Wkiom1mnt6_bzuapaaa6wrg4fzs161.png "/>

4. Modification of the database

ALTER TABLE Linux rename message; Modify database table name

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9E/3B/wKioL1mNuCuhE8SeAACLrU4BMJM237.png "style=" float : none; "title=" screenshot from 2017-08-11 10-01-56.png "alt=" Wkiol1mnucuhe8seaaclru4bmjm237.png "/>

ALTER TABLE Linux Add class; Add a class column to a table

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9E/4D/wKiom1mNuC2jV6rHAADZzYg2X3w812.png "style=" float : none; "title=" screenshot from 2017-08-11 10-09-05.png "alt=" Wkiom1mnuc2jv6rhaadzzyg2x3w812.png "/>

Add Class column after password in table

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9E/3B/wKioL1mNuDbTgTMmAACBHZo02kI963.png "style=" float : none; "title=" screenshot from 2017-08-11 10-12-18.png "alt=" Wkiol1mnudbtgtmmaacbhzo02ki963.png "/>

Update Linux set class= ' Linux '; Update data in a table (all columns)

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9E/3B/wKioL1mNuDqRlxGQAADLOP1E4ZI408.png "style=" float : none; "title=" screenshot from 2017-08-11 10-12-27.png "alt=" Wkiol1mnudqrlxgqaadlop1e4zi408.png "/>

Update table with the name Lee column of Class Jave (conditional update, not the whole column)

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/9E/4D/wKiom1mNuD3RrtjuAACDN60byMU279.png "style=" float : none; "title=" screenshot from 2017-08-11 10-12-28.png "alt=" Wkiom1mnud3rrtjuaacdn60bymu279.png "/>

5. Deletion of databases

ALTER TABLE Linux drop class; Delete a class column from a Linux table

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9E/4D/wKiom1mNuDLyE1bjAABwFlFyjJ8529.png "style=" float : none; "title=" screenshot from 2017-08-11 10-09-17.png "alt=" Wkiom1mnudlye1bjaabwflfyjj8529.png "/>

Delete from Linux where username= ' user1 '; Delete user1 data from a Linux table

drop table Linux; Delete a Linux table
Drop database Westos; Delete Westos Library

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/9E/4D/wKiom1mNu8fTfgHbAACHqLzApN8168.png "title=" Screenshot from 2017-08-11 10-17-04.png "alt=" Wkiom1mnu8ftfghbaachqlzapn8168.png "/>

Five. Backup of the database

Mysqldump-u Root-pwestos--all-database back up left and right data in all tables
Mysqldump-u Root-pwestos--all-database--no-data back up all tables without backing up the data
Mysqldump-u root-pwestos Westos Backup Westos Library
Mysqldump-u Root-pwestos westos >/mnt/westos.sql back up the Westos library and save the data to Westos.sql
Mysqldump-uroot-pwestos Westos linux >/mnt/linux.sql backup westos Linux tables in the library
Mysqldump-uroot-pwestos Westos Test >/mnt/test.sql Backup Test table in Westos Library

1. Back up the Westos library and save the data to the Westos.sql

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/9E/4D/wKiom1mNvduwkkMxAAAhIxRBZtk402.png "title=" Screenshot from 2017-08-11 10-35-36.png "alt=" Wkiom1mnvduwkkmxaaahixrbztk402.png "/>

2. Delete Westos Library

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9E/3C/wKioL1mNvUzDYmCSAACyLndljoc660.png "style=" float : none; "title=" screenshot from 2017-08-11 10-35-56.png "alt=" Wkiol1mnvuzdymcsaacylndljoc660.png "/>

3. Restore the Westos Library

MYSQL-UROOT-PWESTOS-E "CREATE database Westos;" Build Westos Library
Mysql-uroot-pwestos Westos </mnt/westos.sql importing data into Westos Library

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9E/4D/wKiom1mNvU2RKTtmAAA9l5rc0Tg363.png "style=" float : none; "title=" screenshot from 2017-08-11 10-37-50.png "alt=" Wkiom1mnvu2rkttmaaa9l5rc0tg363.png "/>

4. Enter the database to view the Westos library as normal

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9E/4D/wKiom1mNvU7gbMhOAAEb5Gi1c-8310.png "style=" float : none; "title=" screenshot from 2017-08-11 10-38-45.png "alt=" Wkiom1mnvu7gbmhoaaeb5gi1c-8310.png "/>

Six. Password modification

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9E/3C/wKioL1mNv4WxNRwcAANCVQws2oM700.png "title=" Screenshot from 2017-08-11 22-29-40.png "alt=" Wkiol1mnv4wxnrwcaancvqws2om700.png "/>

1. Turn off the database service, turn on the MySQL login interface and ignore the authorization form Mysqld_safe--skip-grant-tables &

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9E/4D/wKiom1mNv_zgKICPAACKsgutUK4617.png "style=" float : none; "title=" screenshot from 2017-08-11 11-18-29.png "alt=" Wkiom1mnv_zgkicpaacksgutuk4617.png "/>

2.mysql directly without password can login
Update Mysql.user set Password=password (' Westos ') where user= ' root '; Update Super User password information (encrypted)

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/9E/3C/wKioL1mNv_3DKZa7AAEmIDQuSU4935.png "style=" float : none; "title=" screenshot from 2017-08-11 11-20-03.png "alt=" Wkiol1mnv_3dkza7aaemidqusu4935.png "/>

View the password after the update is encrypted

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9E/4D/wKiom1mNv_6B1lueAAGXQiILqhw231.png "style=" float : none; "title=" screenshot from 2017-08-11 11-20-15.png "alt=" Wkiom1mnv_6b1lueaagxqiilqhw231.png "/>

3.ps aux | grep MySQL filters all the processes of MySQL
Kill-9 Mysqlpid End These processes

Systemctl start mariadb re-open MySQL
Mysql-uroot-pwestos Landing Test

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9E/3C/wKioL1mNv__DjXWSAAE7AKMzGL0479.png "style=" float : none; "title=" screenshot from 2017-08-11 11-20-34.png "alt=" Wkiol1mnv__djxwsaae7akmzgl0479.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9E/4D/wKiom1mNwADhj_3CAACz1b1EfsA158.png "style=" float : none; "title=" screenshot from 2017-08-11 11-20-56.png "alt=" Wkiom1mnwadhj_3caacz1b1efsa158.png "/>

Seven. User authorization

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/9E/4E/wKiom1mNwaOTHEC4AAao7D2Tur4848.png "title=" Screenshot from 2017-08-11 22-38-58.png "alt=" Wkiom1mnwaothec4aaao7d2tur4848.png "/>

1.create user [email protected] identified by ' Westos '; Set up user Lee, password westos, this user can only log in via this machine

Create user [email protected] '% ' identified by ' Westos '; Build user lee, password westos, this user can log on through the network

2.grant Insert,update,delete,select on westos.test to [email protected]; User authorization

Grant Select on westos.* to [email protected] '% '

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/9E/3C/wKioL1mNwijTQJbmAABsXijhDmo985.png "style=" float : none; "title=" screenshot from 2017-08-11 12-34-21.png "alt=" Wkiol1mnwijtqjbmaabsxijhdmo985.png "/>

3.show grants for [email protected] '% ' View user authorization
Show grants for [email protected]

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9E/4E/wKiom1mNwiyDQmHFAACK8oYlMMo184.png "style=" float : none; "title=" screenshot from 2017-08-11 12-34-59.png "alt=" Wkiom1mnwiydqmhfaack8oylmmo184.png "/>

You can view and delete them after logging in with the Lee user.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9E/3C/wKioL1mNwi_iA1ZYAAEtpH3nsLw682.png "style=" float : none; "title=" screenshot from 2017-08-11 12-35-58.png "alt=" Wkiol1mnwi_ia1zyaaetph3nslw682.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9E/4E/wKiom1mNwjHgbJuZAAB8IamJTTU800.png "style=" float : none; "title=" screenshot from 2017-08-11 12-36-14.png "alt=" Wkiom1mnwjhgbjuzaab8iamjttu800.png "/>

4.revoke Delete on westos.* from [email protected]; Remove User authorization rights

The data in the table cannot be deleted after removing the delete power of the Lee user

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9E/3C/wKioL1mNwjTiCNm2AADRBds2sAk952.png "style=" float : none; "title=" screenshot from 2017-08-11 12-36-30.png "alt=" Wkiol1mnwjticnm2aadrbds2sak952.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9E/3C/wKioL1mNwjjy-WeLAAFuZuBerpE821.png "style=" float : none; "title=" screenshot from 2017-08-11 12-36-51.png "alt=" Wkiol1mnwjjy-welaafuzuberpe821.png "/>

5.drop user [email protected] Remove Lee Users

drop user [email protected] '% ' Delete users

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9E/4E/wKiom1mNwjnQoDBlAAC2C9lYJPs136.png "style=" float : none; "title=" screenshot from 2017-08-11 12-37-05.png "alt=" Wkiom1mnwjnqodblaac2c9lyjps136.png "/>

Eight. Web page management tools for databases
1. Install the service and prepare the phpMyAdmin package
Systemctl Start httpd
Systemctl Enable httpd
Systemctl Stop Firewalld
Systemctl Disable FIREWALLD

phpmyadmin-3.4.0-all-languages.tar.bz2

2. Configure the Database Web page management tool

Unzip the phpMyAdmin package and rename the extracted directory to Mysqladmin

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9E/3C/wKioL1mNxMWg7IerAADL9M6F_uI896.png "style=" float : none; "title=" screenshot from 2017-08-11 14-16-35.png "alt=" Wkiol1mnxmwg7ieraadl9m6f_ui896.png "/>

Check the phpMyAdmin profile documentation to find the software code

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/9E/3C/wKioL1mNxMSjSuCmAAAfRhgiqis506.png "style=" float : none; "title=" screenshot from 2017-08-11 14-08-44.png "alt=" Wkiol1mnxmsjsucmaaafrhgiqis506.png "/>

Copy the configuration template file in phpMyAdmin, edit the template file, add the software code to the configuration file

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9E/4E/wKiom1mNxMbxDXBZAABmwS9-Lnk351.png "style=" float : none; "title=" screenshot from 2017-08-11 14-17-01.png "alt=" Wkiom1mnxmbxdxbzaabmws9-lnk351.png "/>

Vim config.inc.php

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9E/4E/wKiom1mNxMXzwJrBAAApphZGWkw426.png "style=" float : none; "title=" screenshot from 2017-08-11 14-12-39.png "alt=" Wkiom1mnxmxzwjrbaaapphzgwkw426.png "/>

Install yum install httpd PHP php-mysql-y

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9E/3C/wKioL1mNxMaSTU6qAAA-H7J3k0k679.png "style=" float : none; "title=" screenshot from 2017-08-11 14-17-50.png "alt=" Wkiol1mnxmastu6qaaa-h7j3k0k679.png "/>

3. Test: Access Http://172.25.254.128/mysqladmin

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9E/4E/wKiom1mNxMejEg7PAADUDwUTmL0067.png "style=" float : none; "title=" screenshot from 2017-08-11 14-18-20.png "alt=" Wkiom1mnxmejeg7paadudwutml0067.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9E/3C/wKioL1mNxMjR8WcQAAHnrDymLAE752.png "style=" float : none; "title=" screenshot from 2017-08-11 14-28-08.png "alt=" Wkiol1mnxmjr8wcqaahnrdymlae752.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9E/4E/wKiom1mNxMmA3Y0sAAE3MjACqMo667.png "style=" float : none; "title=" screenshot from 2017-08-11 14-33-51.png "alt=" Wkiom1mnxmma3y0saae3mjacqmo667.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/9E/3C/wKioL1mNxMrjOkY-AAEPfgTiZpQ529.png "style=" float : none; "title=" screenshot from 2017-08-11 14-33-59.png "alt=" Wkiol1mnxmrjoky-aaepfgtizpq529.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9E/3C/wKioL1mNxMvCuePhAAFMqr6mWnc558.png "style=" float : none; "title=" screenshot from 2017-08-11 14-34-06.png "alt=" Wkiol1mnxmvcuephaafmqr6mwnc558.png "/>

Login success, you can edit the library, tag table, edit can see the SQL statement, for learning statements and improve the efficiency of editing is very helpful.

Database mariadb for Linux