MySQL binary installation and basic start-up operations

Preface: MySQL database, the knowledge is very much, want to learn to learn this piece of knowledge, estimated also to spend and learn Linux the same energy and time. Small series is also only some fur, for everyone to share ~

One, MySQL installation

(1) Installation method:

1, the package Yum installation, under the experimental one

Advantages: Quick and easy installation

Disadvantage: The location of the death of the various documents, too rigid

2, binary format package : Expand to a specific path, and after simple configuration can be used (recommended), under experiment two

3. Source code: Compile and install, too troublesome

(2) MySQL program consists of:

Client:

MYSQL:CLI Interactive client program

Mysqldump, Mysqladmin ...

Server:

Mysqld_safe MySQL Backup

Mysqld

Mysqld_multi: Multi-instance

The server listens to two socket addresses:

IP socket: Listens on TCP's 3306 port, supports remote communication

Unix sock: Listen on the sock file (/tmp/mysql.sock,/var/lib/mysql/mysql.sock), only native communication is supported

Experiment one: Yum source installs MySQL and opens the settings service

1, open the official website, there are various versions of the Yum source, find the version you want to set up Yum source

https://downloads.mariadb.org/mariadb/repositories/

If you can't get on the net, you can use the old version of your CD.

2, if you do not use their own old version of the CD, to configure the Yum source

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

[MARIADB]

Name = MariaDB

BaseURL = Http://yum.mariadb.org/10.2/centos7-amd64

Gpgcheck=0

If there are other Yum sources, add a enabled=0 to temporarily shut down, yum clean to clear the cache, note: The official Yum source installed by Maria does not install dependent packages, but also need to install their own dependent packages

3. Yum installation and start-up service

Here, we'll install the old version of the CD directly.

Yum -y install mariadb-server

systemctl start mariadb Open Service

Ss-nutl 3306 of TCP ports are open

Process information for the query Port lsof-i: 3306 or NETSTAT-TNLP | grep 3306

Note: After opening the service, there will be one more MySQL user, its home directory: storage database, equivalent to each table in the database

When the MySQL user is installing the package, a script is executed that creates the MySQL user and prompts us to create a user if the binary installation is required

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

4. mysql Run

Discovery is root, can be deleted, it's not safe.

can also mysql xxx anonymous login

5. Running Security scripts

/usr/bin/mysql_secure_installation

Experiment 2:2 install MARIADB and turn on settings service

Note: before installing, make sure you do not have mariadb service on your system, old version can be removed first, delete mysql user

1, go to the official website to download the version you want http://mariadb.org

Rpm-qi MARIADB can check the official website

Upload, unpack and unzip

RZ,tar xvf mariadb-10.2.8-linux-x86_64.tar.gz -c/usr/local/

(unlike a compiled installation, unpacking can be placed in any directory, this binary installation must be specified in this directory)

2, cd/usr/local/found MARIADB directory name does not meet the requirements

Ln-s Mariadb-10.2.8-linux-x86_64/mysql Creating a soft connection can also be renamed

3. Create a MySQL user

Useradd - D /app/mysqldb -r-m-S /sbin/nologin MySQL

4. Create a modification configuration file

LS support-files/package has a configuration file, but the place is wrong, to be placed in the/ETC/MYSQL/MY.CNF

Mkdir/etc/mysql

CP SUPPORT-FILES/MY-HUGE.CNF/ETC/MYSQL/MY.CNF we demonstrate a big

VIM/ETC/MYSQL/MY.CNF Modifying a configuration file

[Mysqld]

DataDir =/app/mysqldb //Specify the total directory, required

Innodb_file_per_table = ON//make each table database a file for easy administration

Skip_name_resolve = ON//Ignore reverse parsing of names, speed up

5, execute the script, create the system database

Cd/usr/local/mysql Be sure to execute the script in this directory, because the script is dead.

./scripts/mysql_install_db --user=mysql --datadir=/app/mysqldb Execution Script

The MySQL system database will be generated in/app/mysqldb/after completion

6. Copy the service script to the past

CP Support-files/mysql.server/etc/init.d/mysqld

Chkconfig--add mysqld Set the service at which RunLevel, at which runlevel the service is opened

Chkconfig--list mysqld

Service mysqld start failed, see failure reason: Missing log file, log file must have read and write permission

7. Create a configuration file

mkdir/var/log/mariadb/

Touch/var/log/mariadb/mariadb.log

Chown Mysql/var/log/mariadb/mariadb.log

Service mysqld start open 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, the same experiment

Mysql_secure_installation

Second, MySQL basic entry operation

1. Command line Interactive command:mysql

Options for MySQL command:

- u USERNAME: User name; default is root

- H Host: Server host; Default to LocalHost

- P PASSWORD: User's password; We recommend that you use-p, the default is a blank password

2. Some nouns in MySQL

Databases: Database

Tables: Table

Indexes: Index

Engine: Engines

Columns, Fields: Column

\G: Vertical Display

3.SQL Statement: (4 major categories)

DDL: Data defination Language The database definition language, modifying the table structure

Create, Drop (delete), Alter (modify table structure)

DML: Data manipulation Language , modifying data in table

INSERT, DELETE, update (updated data)

DQL : Query Language for data query Language

SELECT uses many, very flexible

DCL : Data Control Language, authorization limit

Grant, REVOKE (cancel authorization)

Third, the operation of the database

1, view the database: show databases;

To view a table in a database: show tables [from database_name] if it is already under this library, do not add from

Database has INFORMATION_SCHEMA library, this library is read-only library, only the root of the special privileges of the user login to see, cannot drop delete

The database has #mysql50#.mozilla similar library processing method, because the MySQL home directory has a hidden file caused, cannot delete

Just remove the hidden files.

2. Create a database:

CREATE database [If notEXISTS] (if it does not exist, create) ' db_name '; Create a successful one, generate a table database file

CHARACTER sets ' CHARACTER set ' name ' Setting the character set, not recommended, not recommended

COLLATE ' COLLATE name ' setting collation, not recommended

Note: Naming rules for database objects

Must start with a letter

Can include a number and three special characters (# _ $)

Do not use the reserved word for MySQL

Objects under the same schema(database) cannot have the same name

3. Delete Database

DROP DATABASE [IF EXISTS] ' db_name '; Deletion succeeds, the corresponding table database file is deleted

View support for all character sets: Show CHARACTER set; No need to change

View all collation support: show COLLATION; No need to change

4, get command to use Help: mysql>help CREATE database;

Example: Create database TestDB; Create a TestDB database

Drop database TestDB; Delete TestDB Database

Iv. Creating a Delete table

1. View all tables in the database: Show tables from Base_name;

View table structure :desc tbl_name;

2, create the table , according to the paradigm

① CREATE TABLE [IF not EXISTS] ' tbl_name ' (col1 type1 modifier, col2 type2 modifier, ...)

Field Information:

col type1 specified type

primary KEY (col1,...) () Composite PRIMARY key

index (col1, ...) index

Unique key (col1, ...) unique key

Table options:

engine [=] Engine_name (engine settings, default is good)

SHOW ENGINES; View supported engine engines types

Row_format [=] {default| dynamic| fixed| compressed| Redundant | COMPACT} row format, the default is good

② CREATE TABLE students3 SELECT * [Id,name] from students; You can also create a table that completely replicates the structure of another table [or the structure of your choice]; The replicated table has no constraints, such as a primary key

insert into STUDENTS3 SELECT * from students; Have students3 this table to completely replicate the contents of another table

Get help creating Table:mysql> helper CREATE table;

3. Delete tables: Drop table [IF EXISTS] ' Tbl_name ';

4. Example:

Example 1:CREATE TABLE testdb.students (id int UNSIGNED not NULL PRIMARY key,name VARCHAR) not null,age tinyint unsi gned);

Explanation: Create a TestDB library named students table with 3 columns: ID, name, age;id: data type int is positive, not empty, set as 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 (a) not null,age tinyint unsigned,primary key (Id,name));

Explanation: Create the TestDB library named Teachers table; The composite primary key for the table is the ID and Name column; others as in Example 1

Example 3:create table STUDENTS3 SELECT * from students; Create Students3 Copy the contents of students

V. DDL statements: Modify the table structure, try not to change the table structure, rarely used

1. View table structure:DESC [Db_name.] Tb_name;

2. Modify the table structure alter

ALTER TABLE ' Tbl_name '

Field:

Add Field: Add

Add Col1 data_type [first plus to the back of paragraph one | After col_name of a certain paragraph]

Delete field: Drop

Modify field: Change (field name), Modify (field property)

Index:

Adding an index: add

Delete index: Drop

Table options:

Modify: Change

View Help: Helping ALTER TABLE

3. Example:

Help ALTER TABLE View assistance

ALTER TABLE students3 RENAME S3; Change table name

ALTER TABLE S3 ADD phone varchar (one) after name; Add a phone after the name field of the S3 table

ALTER TABLE S1 MODIFY phone int; Change the phone's data type to int

ALTER TABLE S1 change COLUMN phone Mobile char (11); Rename the field phone to field mobile with a data type of char (11)

ALTER TABLE S1 DROP COLUMN Mobile; Delete Field Mobile

ALTER TABLE Students Add gender enum (' m ', ' F ') increases the gender field, which is an enumeration type, only M or F

ALETR TABLE students change ID sid int UNSIGNED not NULL PRIMARY KEY; The Change ID field is SID, the data type is positive int, non-null, primary key

ALTER TABLE students ADD UNIQUE KEY (name); Add unique keys in the Name field

ALTER TABLE students ADD INDEX(age); Index in the Age field

DESC students; View this table

SHOW INDEXES from students; View index Information

ALTER TABLE Students DROP age;

Vi. DML statements, modifying table contents

1. View

SELECT * from Tab_name [WHERE clause [limit [m,]n];] View all contents of the table, LIMIT m,n skip m rows, n rows

Select Id,name,... from Tab_name; Find out what's in the specified table

Select COUNT (*) from Tab_name; View the number of records in a table, count () is a function of its own

2, modify the contents of the table:

• Insert Add, inserts , below examples

INSERT [Into] Tbl_name [(Col_name,...)] VALUES (Val1,...), (...),... The following example 2,3

Analysis: ①tbl_name (), default by table structure of the column, if Add (), before and after () content to correspond, order can not be in the table structure, can also set a null value, but it is best not,

② option is not a number, add ' ', Example: Name= ' along '

INSERT INTO TAB_NAME1 select * from Tab_name2; Bulk Guide data, example 4

• Update: Updates, modify table contents , examples below

UPDATE tbl_name SET col1=val1, Col2=val2, ... [WHERE clause] [ORDER by ' col_name ' [DESC]] [LIMIT [M,]n]; The following example 5

Analysis: If you do not add where, directly all the columns are modified, Limit m,n skip M row, to n rows

• Delete: Remove the contents of the table with an example

Delete from tbl_name [WHERE clause] [ORDER BY ' col_name ' [DESC]]; You can sort and then specify the number of deleted rows, example 6

Analysis: If you do not add where, all the columns are deleted directly

• TRUNCATE TABLE tbl_name; Clear the table, quickly empty, delete the time, regardless of the log, cautious use

3. Example

①select * FROM students limit 3, 2;

②insert into students (Id,name,age) VALUES (1, ' along ', 18); Add 1 lines of information

③insert into students values (2, ' xiaoming ', +), (3, ' Xiaohong ', 20); Add 2, 3 lines of information

④insert S2 SELECT * from students; Copy the information from the students table to the S2 table

⑤update S2 set name= ' Xiaohei ', age=30 where id=2; Modify the contents of a id=2 line

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

Vii. DQL Statement, select

Dql:select, a lot of usage.

SELECT col1,col2,... From Tbl_name [WHERE clause] [ORDER by ' col_name ' [DESC]] [LIMIT [m,]n]; Query table content information, in Example 1,

1, the field representation method:

*: All fields

as: field alias, if the table in advance, want to change the English column to Chinese, without modification, you can directly use aliases, the following example 1

2, Sort:order by col_name [DESC]

Explanation: By after the specified column, desc reverse sort, reverse can also be-col_name

Note: If there is a null value, the forward sort null value in the first row, the reverse sort null in the last line, can order By-col_name Desc is a positive sort, there is a null value on the last row, the following example 2

3, WHERE clause:where after the option

Operator: The following example 3

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

Between ... And ...

like: fuzzy Matching,

% : Any character of any length

_: any single character;

rlike : Regular expression pattern matching

Is null, was not NULL to look for null values, cannot be used =, can only be used is, so it is better not to have null, bad management

In (Val1,val2,... ) Discrete value Display

Conditional logical Operation:

And, or, not

Example:

①select ID as student number, name name, age ages from students; Setting aliases

②select * FROM students order By-age DESC; Sort by age column forward, NULL in the last

③select * from students where age>=20; Shows the age>=20

SELECT * from students where age between and 20; Showing 18-20 of

SELECT * from students where name is like ' xiao% '; Display name is the beginning of Xiao

SELECT * from students where name Rlike ' ng$ '; Display name is the end of NG

SELECT * FROM S2 where-is null; Rows that display null values

SELECT * from students where age in (18,20,30); Show age=18,20,30 Lines

Eight, DCL Data Control language, authorization limit

(1) User account

1. User account: ' username ' @ ' host '

User: Username

Host: Allow users to remotely connect to the MYSQLD service through which hosts

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

2. Create User:

CREATE USER ' username ' @ ' host ' [identified by ' password '];

Example: Create user ' along ' @ ' 192.168.30.% ' identified by ' CentOS '; Add along account in 192.168.30 this network segment, you can enter the CentOS password connection

Note: The account here and the Linux user is not a thing

3, check the current user login:

SELECT user ();

4. View the users who have been added:

SELECT User,host,password from Mysql.user;

5, Delete users:drop user ' username ' @ ' host ';

Example: Drop user ' along ' @ ' 192.168.30.107 ';

6, Change Password: The first type of multi-use

① SET PASSWORD for ' user ' @ ' host ' = PASSWORD (' PASSWORD ');

Analysis: password (); Is the function that called the own

Example: Set password for ' along ' @ ' 192.168.30.% ' =password (' along ');

②update user SET Password=password (' magedu ') WHERE user= ' root ';

Note: the equivalent of changing the user's table, not recommended, the command to modify the table will not take effect immediately, need to execute flush privileges Refresh to take effect

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

Note: Only users who are created have very little permissions, so we want to authorize

(2) DCL, authorization , reclaim 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 Add, delete Delete, update change, select Check, all permissions

②db_name.tb_name: Which table on which database is authorized:

*. *: Table of all libraries

db_name.*: Specify all tables for the library

Db_name.tb_name: Specify a table for the specified library

Db_name.routine_name: Specifying stored procedures and functions for a library

Example: Grant all on test.* to ' along2 ' @ '% ' of ' identified by ' CentOS '; Create a along2 user, allow it to log in with a CentOS password on all hosts, and have all permissions on all tables in the test library

2. Recycling Authorization:

REVOKE priv_type, ... on Db_name.tb_name From ' user ' @ ' host

Example: Revoke delete on test.* from ' along2 '; Recycle [email protected] '% ' user delete permissions on all tables in test library

Attention:

When the ①MARIADB service process starts, it reads all the authorization tables in the MySQL library to memory

Execute permission operations such as ②grant or revoke are saved in the system table, and the MariaDB service process will usually automatically reread the authorization table to make it effective

③ for commands that cannot or cannot reread the authorization table in a timely manner, you can manually reread the authorization table for the MARIADB service process:

Mysql> FLUSH privileges;

Ok, about MySQL, small series is just contact, feel oneself still in the door, hope everybody progress together!!!

MySQL binary installation and basic start-up operations