Mysql database binary installation and basic entry operations, mysql binary

Mysql database binary installation and basic entry operations, mysql binary

Mysql Databases have a lot of knowledge. It takes the same effort and time to learn linux. The small Editor also only has some skins to share with you ~

1. Install MySQL

(1) installation method:

1,Install the yum package.

Advantages: fast and simple installation

Disadvantage: It's too rigid to fix all files.

2,Binary Package: Expand to a specific path and use it after simple configuration (recommended ),Lab 2

3. Source Code: Compilation and installation, which is too troublesome

 

(2) mysql program composition:

Client:

Mysql: CLI Interactive Client Program

Mysqldump, mysqladmin...

Server:

Mysqld_safe mysql backup

Mysqld

Mysqld_multi: Multiple instances

Two socket addresses for server listening:

Ip socket: listening onTcp 3306Port, supporting remote communication

Unix sock: Listening to sock files (/tmp/mysql. sock,/var/lib/mysql. sock), only supports local communication

 

Experiment 1: Install MySQL from yum source and enable the Configuration Service

1. Open the official website. There are various versions of yum sources on the top. Find the desired version and set the yum source.

Https://downloads.mariadb.org/mariadb/repositories/

If you cannot access the network, you can also use the old version on your own CD.

2. If you do not need an old version of the CD, you need to configure the yum source. Here I chose Version 10.2.

Vim/etc/yum. repos. d/along. repo

[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.2/centos7-amd64gpgcheck=0

 

If there are other yum sources, add an enabled = 0 to temporarily disable them, and yum clean all to clear the cache. Note: Maria installed by the official yum source does not install the dependent package, you also need to install the dependent packages on your own.

3. Install and enable the yum Service

Here, we will install the old version on the CD.

Yum-Y installMariadb-server

Systemctl start mariadbEnable Service

Ss-nutl opened tcp port 3306

Query the process information of the port lsof-I: 3306 or netstat-tnlp | grep 3306

Note: after the service is enabled, one moreMysqlIts home directory: stores the database, which is equivalent to each table in the database.

When the mysql user is the installation package, a script is executed to create a mysql user, prompting us to create a user for Binary installation.

Rpm-q -- scripts mariadb-server can view this script

 

 

4. Run mysql

It is found to be root and can be queried and deleted, which is not safe.

You can also log on to mysql xxx anonymously.

5. Run the Security script

/Usr/bin/mysql_secure_installation

 

Experiment 2: Install mariadb in binary mode and enable the Configuration Service

Note:Before installation, make sure that you do not have the mariadb service on your system. If you have an earlier version, uninstall it and delete the mysql user.

1, go to the official website to download their desired version of http://mariadb.org

Rpm-qi mariadb can be found on the official website

Upload, decompress the package

Rz,Tar xvfMariadb-10.2.8-linux-x86_64.tar.gz-C/usr/local/

(Unlike compiling and installation, unpacking can be placed in any directory. This binary installation must be specified in this directory)

2. cd/usr/local/found that mariadb's directory name does not meet the requirements

Ln-s mariadb-10.2.8-linux-x86_64/mysql creating soft connections can also be renamed

3. Create a mysql user

Useradd -D/App/mysqldb-R-m-s/Sbin/nologinMysql

4. Create and modify the configuration file

The ls support-files/package contains a configuration file, but the location is incorrect. Put it in/etc/mysql/my. cnf.

Mkdir/etc/mysql

Cp support-files/my-huge.cnf/etc/mysql/my. cnf we demonstrate a large

Vim/etc/mysql/my. cnf modify the configuration file

[Mysqld] datadir =/app/mysqldb // specify the total directory. The required innodb_file_per_table = on // makes every table database a file, it is convenient to manage skip_name_resolve = on // ignore reverse resolution of name, speeding up

5. Execute the script to create a system database.

Cd/usr/local/mysql must execute the script in this directory because the script is dead.

./Scripts/mysql_install_db-- User =Mysql-- Datadir =/App/mysqldb execute the script

The mysql System database is generated in/app/mysqldb /.

6. Copy the service script

Cp support-files/mysql. server/etc/init. d/mysqld

Chkconfig -- add mysqld

Chkconfig -- list mysqld

Service mysqld start failed. Check the cause of failure: log files are missing and must have read and write permissions on log files.

7. Create a configuration file

Mkdir/var/log/mariadb/

Touch/var/log/mariadb. log

Chown mysql/var/log/mariadb. log

Service mysqld start is enabled successfully

Set PATH:

Vim/etc/profile. d/mysql. sh

PATH =/usr/local/mysql/bin: $ PATH

./Etc/profile. d/mysql. sh

8. Run the Security Initialization Script.

Mysql_secure_installation

 

Ii. Basic mysql operations

1. Interactive command lineCommand:Mysql

Mysql Command Options:

-UUSERNAME: User name. The default value is root.

-HHOST: the server HOST. The default value is localhost.

-PPASSWORD: the user's PASSWORD.-p is recommended. The default PASSWORD is null.

 

2. Some Terms in mysql

Database: database

Table: table

Index: index

Engine: engines

Column and field: column

\ G: vertical display

3. SQL statements: (4 categories)

DDL: Data Defination Language: Data Definition Language. Modify the table structure.

CREATE, DROP, and ALTER)

DML: Data Manipulation Language: indicates the Data operation Language used to modify Data in the table.

INSERT, DELETE, UPDATE (UPDATE data)

DQL: Data Query Language

SELECT is widely used and flexible.

DCL: Data Control Language. grant permissions.

GRANT, REVOKE (cancel authorization)

 

Iii. Database Operations

1. view the database: show databases;

View tables in a database: show tables [from database_name] if it is already in this database, you do not need to add from

The database has the information_schema database, which is read-only. Only users with special root permissions can log on to the database and cannot drop or delete the database.

The database has the processing method of # mysql50 #. mozilla Similar libraries, because there is a hidden file in the mysql home directory and cannot be deleted.

Just remove these hidden files.

2. Create a database:

CREATEDATABASE )[IF NOT EXISTS] (If it does not exist, create) 'db _ name'; if it is successfully created, a table database file is generated.

Character set 'character set name' sets the character SET, which is not recommended and not recommended.

COLLATE 'collate name' is not recommended for setting sorting rules.

Note: naming rules for database objects

Must start with a letter

It can contain numbers and three special characters (#_$)

Do not use reserved MySQL words

Same SchemaObjects under (database)Cannot have the same name

 

3. delete a database

DROPDATABASE [if exists] 'db _ name'; the table DATABASE file is deleted successfully.

View All supported CHARACTER sets: show character set; no need to change

View all the supported sorting rules: show collation; no need to change

4. Obtain Command help: mysql> help create database;

 

Example: create database testdb; create a database testdb

Drop database testdb; Delete the testdb database

 

4. Create and delete a table

1. view all tables in the database: show tables from base_name;

View table structure:DescTbl_name;

2. Create a table, Following the paradigm

①CREATE TABLE[If not exists] 'tbl _ name' (col1 type1 modifier, col2 type2 modifier ,...)

FieldInformation:

• Col type1 specifies the type

• Primary key (col1,...) () composite PRIMARY KEY

• INDEX (col1,...) INDEX

• Unique key (col1,...) UNIQUE KEY

Table options:

• ENGINE [=] engine_name (ENGINE setting, which is good by default)

Show engines; view supported ENGINESEngine type

• ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} the row format. The DEFAULT value is good.

②Create tableStudents3 select * [id, name] from students; you can also create a table to completely copy the structure of another table [or the structure selected by yourself]. The copied table has no constraints, such as the primary key.

InsertStudents3 select * from students; the students3 table exists, completely copying the content of the other table

Obtain HELP for creating a TABLE: mysql> help create table;

3. delete a TABLE: DROP TABLE[If exists] 'tbl _ name ';

4. Example:

Example 1:CREATE TABLETestdb. students (id int unsigned not null primary key, name VARCHAR (20) not null, age tinyint UNSIGNED );

Description: Create the students table named in the testdb database. The table has three columns: id, name, and age. id: the data type int is positive, not empty, and is set as the primary key. name: data Type: VARCHAR (20), not empty; age: Data Type: tinyint UNSIGNED)

Example 2: create table teachers (id int unsigned not null, name varchar (20) not null, age tinyint unsigned, primary key (id, name ));

Explanation: Create the table named teachers in the testdb database. The composite primary key of the table is the id and name columns. Others are the same as those in Example 1.

Example 3: create table students3 select * from students; create students3 to copy students content

 

5. DDL statements: Modify the table structure. Do not change the table structure as much as possible.

1. view the table structure: DESC[Db_name.] tb_name;

2. alter TABLE Structure

Alter table 'tbl _ name'

Field:

Add field: add

ADD col1 data_type [add first to the back of the FIRST segment | AFTER col_name]

Delete field: drop

Modify Field: change (field name), modify (field attribute)

Index:

Add Index

Delete index: drop

Table options:

Modify: change

View Help: Help ALTER TABLE

3. Example:

Help alter table view Help

ALTER TABLEStudents3RENAMES3; change table name

Alter table s3ADD phoneVarchar (11)AFTER nameAdd a phone after the name field of the s3 table

Alter table s1MODIFYPhone int; change the data type of phone to int.

Alter table s1CHANGE COLUMNPhone mobile char (11); change the phone field to mobile, and the data type is char (11)

Alter table s1DROP COLUMNMobile; Delete field mobile

Alter table studentsADDGender ENUM ('M', 'F') adds the gender field, which is of the enumeration type and can only be m or f

Aletr table studentsCHANGEId sid int unsigned not null primary key; change the id field to sid. The data type is int, non-empty, and primary key.

Alter table studentsADDUnique key (name); add a unique key to the name field

Alter table studentsADD INDEX(Age); add an index to the age Field

DESC students; view this table

Show indexes from students; View index information

Alter table studentsDROPAge;

6. Modify Table content using DML statements

1. View

Select * from tab_name [WHEREClause [LIMIT [m,] n];] view all content in the Table. Limit m, n skips m rows and n rows.

Select id, name,... from tab_name;

Select count (*) from tab_name; check the number of records in the Table. count () is a built-in function.

2. Modify Table content:

• INSERT add and INSERT, For example

INSERT[INTO]Tbl_name[(Col_name,...)]VALUES(Val1,...), (...),... Example 2 and 3

Analysis: ① tbl_name is not followed by (). By default, columns are based on the table structure. If () is added, the content before and after () must correspond. The sequence can be not based on the table structure, or a null value can be set, but it is best not,

② If the option is not a number, add ''. For example, name = 'along'

Insert into tab_name1 select * from tab_name2; batch import data, Example 4

• UPDATE: UPDATE and modify table content, For example

UPDATETbl_nameSETCol1 = val1, col2 = val2 ,...[WHEREClause] [order by 'col _ name' [DESC] [LIMIT [m,] n]; example 5

Analysis: If the where clause is not added, all columns are directly modified. Limit m, n skips m rows and n rows are required.

• DELETE: DELETE table content, For example

Delete from tbl_name [WHERE clause] [ORDERBY 'col _ name' [DESC]; optionalSort firstSpecify the number of rows to be deleted. Example 6:

Analysis: If the where clause is not added, all columns are directly deleted.

•TRUNCATETABLE tbl_name; clear the TABLE and quickly clear the TABLE. When deleting the TABLE, use it with caution.

3. Example

① Select * from students limit 3, 2;

② Insert into students (id, name, age) values (1, 'along ', 18); add 1 row of information

③ Insert into students values (2, 'xiaoming', 19), (3, 'xiaohong ', 20); add 2 or 3 rows of information

④ Insert s2 select * from students; copy the information of the students table to the s2 table

⑤ Update s2 set name = 'xiaohei', age = 30 where id = 2; modify the content of line id = 2

⑥ Delete from s2 where id = 3; delete row id = 3 of table s2

VII. DQL statement, select

DQL: SELECT, many usage

SELECT col1, col2,... FROM tbl_name [WHEREClause] [ORDERBY 'col _ name' [DESC] [LIMIT[M,] n]; query table content information, Example 1 above

1. Field representation:

*: All Fields

As: Field alias,If you have prepared a table in advance and want to change the English of the column to Chinese, you do not need to modify it. You can use the alias directly. Example 1

2. Sorting:OrderCol_name [desc]

Explanation: After 'by', specify the column, desc reverse sorting, and-col_name can also be used in reverse sorting.

Note: if there is a null value in it, the null value in the forward sorting is in the first row, and the null value in the reverse sorting is in the last row, you can order by-col_name desc, that is, to put the null value in the last row, example 2

3. WHERE clause: option after where

Operator: Example 3

>,<, >=, <=, == ,! =

BETWEEN... AND...

LIKE: Fuzzy match,

%: Any character of any length

_: Any single character;

RLIKE: Regular Expression Pattern Matching

Is null, is not null searches for null values, cannot use =, can only use is, so it IS best NOT to have NULL, it IS difficult to manage

IN (val1, val2 ,...) Discrete value display

Conditional logical operations:

And, or, not

Example:

① Select id as student id, name, age from students; Set alias

② Select * from students order by-age desc; sort by age Column forward, null at the end

③ Select * from students where age> = 20; display age> = 20

Select * from students where age between 18 and 20; display 18-20

Select * from students where name like 'xiao % '; display name starting with xiao

Select * from students where name rlike 'ng $ '; display name ending with ng

Select * from s2 where age is null; displays rows with null values

Select * from students where age in (, 30); displays the rows of age =, 30

 

 

8. DCL Data Control Language, granting permissions

(1) User Account

1. user Account: 'user' @ 'host'

User: user Name

Host: hosts that allow users to remotely connect to the mysqld service

IP address, network address, host name, wildcard (% and _)

2,CreateUser:

CREATE USER'Username' @ 'host '[IDENTIFIED'Password'];

For example, create user 'along' @ '192. 192.% 'identified by 'centos'; Add the along account in the network segment 192.168.30, And you can enter the centos password to connect.

Note: The account here is not the same as the linux User

 

3. view the current Login User:

SELECT user ();

4. view the added users:

SELECTUser, Host, PasswordFROM mysql. user;

 

5,DeleteUser:DROPUSER 'username' @ 'host ';

Example: drop user 'along '@ '192. 168.30.107 ';

 

6. Change Password: Use the first method

①SET PASSWORD'User' @ 'host' = PASSWORD ('Password ');

Analysis: password (); is a built-in function called.

For example, set password for 'along '@ '192. 192. %' = password ('along ');

② UPDATE user SET password = PASSWORD ('magedu') WHERE User = 'root ';

Note: The table that has changed the user is not recommended. The command to modify the table does not take effect immediately and must be executed.FLUSH PRIVILEGESRefresh to take effect

③/Usr/local/mysql/bin/mysqladmin-u root-poldpassword password 'newpassword'

Note: Only created users have very low permissions, so we need to authorize them.

 

(2)DCL, authorization, revoke permissions

1. Authorization

GRANT Priv_type,...ON[Object_type]Db_name.tb_name TO'User' @ 'host' [identified by 'Password'] [withgrant option]; Authorize and create an account

① Priv_type: ALL [PRIVILEGES] Authorization type:

Insert, delete, update, select, all Permissions

② Db_name.tb_name: Which table of the database is authorized:

*. *: All tables in the database

Db_name. *: all tables in the specified database

Db_name.tb_name: specified table of the specified database

Db_name.routine_name: Specifies the stored procedure and function of the database.

For example, grant all on test. * to 'along2 '@' % 'identified by 'cento'; create an along2 user, and allow it to log on to all hosts using the centos password. It has all permissions on all tables in the test database.

2. Revoke authorization:

REVOKE priv_type,...ON Db_name.tb_nameFROM 'user' @ 'host

For example, revoke delete on test. * from 'along2 '@' % '; revoke the permission of along2 @' % 'to delete all tables in the test database.

Note:

① When the MariaDB service process starts, all the authorization tables in the mysql database will be read to the memory.

② GRANT or REVOKE operations will be stored in the system table, and MariaDB's service process will automatically re-read the authorization table to take effect.

③ For commands that cannot or cannot re-read the authorization table in time, you can manually re-read the authorization table by the MariaDB service process:

Mysql>Flush privileges;

Well, for mysql, I just got in touch with it. I feel like I am still out of the door. I hope you can make progress together !!!