MySQL Database operations

I. Introduction what is MySQL?

• MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications.
• MySQL saves data in different tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.
• The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software has adopted a dual licensing policy, divided into community and commercial version, due to its small size, fast, low total cost of ownership, especially the open source, the development of the general small and medium-sized web site to choose MySQL as the site database.
• Thanks to its Community edition performance, PHP and Apache make a good development environment

Introduction to the database:

is a combination of data structure. Electronic file cabinets, a database is a collection of data stored together in a certain way.

The composition of the database:

The database is made up of tables, and the database is mapped to a directory in the system
Tables are made up of related data, and tables are mapped to files in the system
Records are made up of fields of a certain number of different attributes

The database management system is a computer software designed to manage the database, and it has the functions of storage, interception, maintenance and backup in general.

Classification of databases:

relational database

A database based on a relational model

A common relational database:
MySQL: Open Source code
Alternatives to Mariadb:mysql
Oracle: Closed Source
SQL Server: Microsoft's
Db2:ibm
Access: Microsoft Corporation

Non-relational database:
The most obvious difference from a relational database: SQL is no longer used as the query language
Mongodb
Hadoop

Structure of MySQL:

c/S structure: client/server architecture client and server-side installed software

CentOS7:
Server:mariadb-server
Client:mariadb

REDHAT6:
Server:mysql-server
Client:mysql

Second, login, help

MySQL supports anonymous login by default

Anonymous Login

# MySQL

Setting a password for the root user

# mysqladmin-u Root passwordnew password:confirm New password:

Local login:

# mysql-u root-p123456 Note: There must be no space after-p

Remote login:

# mysql-u Root-h 192.168.1.2-p123456

Help:

Official website: http://www.mysql.com
Http://dev.mysql.com/doc

MySQL Get client Help

MariaDB [(None)]>help; Or? or \h MariaDB [(None)]> Create databases-> \c clears the current input SQL statement, be sure to use MariaDB when not knocking the semicolon [(none)]> exit quit quit \q Maria DB [(none)]> use farm; Switch Database mariadb [(none)]> \u testmariadb [(Farm)]> source Qqfarm.sql; Execute SQL script mariadb [test]> \. QQFARM.SQLMARIADB [(none)]> system ls; Call System command MARIADB [(none)]> \! ls

　　

Forgot MySQL password modify configuration file

#vim/etc/my.cnf
[mysqld]skip-grant-table//Add skip permission Table Datadir=/var/lib/mysql

Restart Service

# systemctl Restart Mariadb.service

Log in to MySQL

# MySQL
MariaDB [(None)]> select User,password,host from Mysql.user; View user, password, host three fields +------+-------------------------------------------+-----------------+| for the user table in the MySQL library user | password | Host |+------+-------------------------------------------+-----------------+| Root | *23ae809ddacaf96a | localhost | | Root | | ula.example.com | | Root | | 127.0.0.1 | | Root | | :: 1 | | | | localhost | | | | ula.example.com |+------+-------------------------------------------+--------------------+mariadb [(None)]> Update Mysql.user set Password=password ("123") where user= "root" and host= "localhost"; Update Password mariadb [(none)]> Exitbye

Remove or comment the option to skip the permission table in the configuration file

#vim/etc/my.cnf
[Mysqld] #skip-grant-table datadir=/var/lib/mysql

Restart Service

# systemctl Restart Mariadb.service

　　

Three, the basic SQL statement classification:

DML Data Manipulation Language Database manipulation language
Insert Inserts Data
Delete Deletes data
Update updates updated data

DDL data Definition Language Database definition language
databases, tables, indexes, stored procedures, functions
Create creates created
Drop Delete Drop
Alter modify ALTER

DCL Data Control Language database controlling language
Example: Controlling a user's access rights
Grant Authorized Grant
REVOKE undo REVOKE

DQL data Query Language database querying language
Select query Select

Basic SQL statements:

Capitalization issues in SQL statements:
1) keywords, function names, column names, and index names are case insensitive
2) database names, table names, aliases are case-sensitive (because Linux is case-sensitive)

Operation of the Library

#查看所有库show databases; #创建库create database name; #创建库并指定字符集create database name DEFAULT CHARACTER SET UTF8; #库的字符集alter database DatabaseName DEFAULT CHARACTER SET UTF8; #查看库的属性 show create database name; #切换库use Apple; #删除库drop database Apple; #查看现在使用 Library Select Database ();

Operation of the table

#创建表create table T1 (id int,name char), #查看表的结构desc t1; #查看当前库中所以表show tables;  #查看表的属性show CREATE table t1\g; #添加字段alter table name operation field name data type [constraint]; #追加alter table T1 add TEL int (one); #在name字段后面插入sex字段alte R table T1 add sex char (ten) after name; #添加字段在第一列alter table T1 add UID Int (ten) first; #修改字段名及属性alter table T1 change ID ID int (5); #仅修改字段属性alter table T1 modify ID int (ten); #删除字段alter table T1 drop UID;  #修改表名alter table T1 Rename to T2;  #删除表drop table nametable;  

Logged Operations

#添加记录insert into T1 set id=2,name= ' Jim '; INSERT into T1 (id,name) VALUES (3, ' Tom '); #添加多条记录insert into T1 (id,name) VALUES (8, ' Harry '), (9, ' Kitty '), insert into T1 values (5, ' Kiki '), (6, ' Jack '), (7, ' banana '), and #所有字段都要有数据insert into T1 values (4, ' Apple '); #更新记录update T2 set age=18 where name is NULL; #满足where后面的条件做什么事update info_name set tel= "456789123" where num=2; #多 Conditional positioning, &&=and Two conditions are satisfied with update info_name set sex= "M" where num=2 && age=18; #|| =or  arbitrarily satisfies its condition update info_name set sex= "M" Where Num=3 | | age=18; #删除记录delete from Info_name where Name= ' Tom ' and sex= ' m ';    Delete from info_name where SEX is null, #清空表记录delete from Info_name;

　　

Basic functions of functions

#database ()     to view the current library Select Database (), #user ()     to view the currently logged on users select User ();

Aggregation functions

#sum ()    summation function Select SUM (age) from T1; #avg ()    mean function Select AVG (age) from T1; #max ()    maximum function Select Max (age) from T1; #min ()    minimum function select min from T1; #count ()    statistics how many records per type of sex field select Sex,count (Sex) from T1 GROUP by sex;# Count the number of MySQL scores above 80 min. Select Mysql,count (*) from score where mysql>80; #统计表中有多少条非空的记录select count (*) from pass;

Time function

#查看当前时间select curtime (); #查看当前日期select curdate (); #查看系统时间select now (); select Sysdate ();

Other functions

#concat ()    connection function Select Concat (username,uid) from Pass;select concat (username, ", uid) from Pass;select concat ( Username, ': ', uid) from Pass;select concat (username, ': ', uid) as ' username-uid ' from Pass; #lower () Convert to    lowercase update pass Set username= ' root ' where username= ' root '; select lower (username) from pass; #upper ()    Convert to uppercase Select upper (username) from Pass; #length () for    length select Username,length (username) from pass;

　　

V. Copying of records and tables

　　Requirement: There are two tables, but the number of fields is different

Prepare table

MariaDB [test]> CREATE TABLE t1 (ID int primary key,name varchar (20)); MariaDB [test]> INSERT INTO T1 values (1, ' Jim '), (2, ' Tom '), (+, ' Jack ');

Replication of records

#复制全部记录MariaDB [test]> insert INTO User1 (id,name) select * from user; #复制部分记录insert to User2 (id,name) SELECT * from us ER where id<1000;

Copy table copy Partial table (cannot copy primary key, foreign key, and index)

MariaDB [test]> CREATE TABLE User3 select * from user;

Fully replicate tables (you can copy the structure of a table)

MariaDB [test]> CREATE table User4 select name from user;

Copy only the structure of a table

Method One: Do not copy primary key, foreign key, index

MariaDB [test]> CREATE TABLE USER5 SELECT * from user where 1<0;

Method Two: Full replication

MariaDB [test]> CREATE table user6 like user; MariaDB [test]> SELECT * from User6;

　　

Vi. data import and export import data

Syntax: Load data infile ' file name ' into table name; ( The delimiter is a space for the latter tab )

Load data infile "/opt/test.txt" into table test;

Note:1) Do not put the file in the/tmp and/root directory, MySQL cannot read.

2) MySQL user to import the file as long as the need to read permissions

3) The file is enclosed in quotation marks.

Syntax: Load data infile ' file name ' into table table name fields terminated by ' delimiter ';( any delimiter )

Load data infile '/opt/pass.txt ' into table pass fields terminated by ': ';

Exporting data

Select *from passwd into outfile '/test/pass ' fields terminated by ': ';

　Note:1) The file name must not exist before export.

2) MySQL user must have read and write access to the directory

3) The export file defaults to |t as the field delimiter, \ n is the line delimiter

VII. User Rights Management view users

MariaDB [(None)]> select User,host,password from Mysql.user;

Delete User

Syntax: Drop mysql.user user name @ ' host ';

MariaDB [(None)]> drop user [email protected] ' ula.example.com '; MariaDB [(none)]> Delete from mysql.user where user!= ' root ';

Create user

Create unlimited no password no IP restricted users

MariaDB [(None)]> create user ' sonfer ';

Create a local user and set a password

Create user [email protected] identified by ' sonfer123 ';

Create a remote login user

Syntax: Create user username @ ' IP ' identified by ' sonfer123 ';

Create user [email protected] ' 192.168.1.71 ' identified by ' sonfer123 ';

　　Note: Allow all IP logins not '% ', should write '%.%.%.% ', may be a bug of mariadb.

Set password using the Mysqladmin command

[Email protected] mysql]# mysqladmin-u root-p123 password ' 456 '

Using the Set password command

MariaDB [(none)]> set password for ' root ' @ ' localhost ' =password (' 123 ');

Update table Records

MariaDB [(none)]> update mysql.user set Password=password (' 2 ') where user= ' root ' and host= ' localhost '; MariaDB [(None)]> flush privileges;

Client Login Server

Syntax: mysql-u user name-p password-H server ip

Mysql-usonfer-h 192.168.1.1-psonfer123

　　

Permissions

• Global hierarchy

Global permissions apply to all databases in a given server. These permissions are stored in the Mysql.user table.

• Database hierarchy

Database permissions apply to all targets in a given database. These permissions are stored in the mysql.db and Mysql.host tables.

• Surface level

Table permissions apply to all columns in a given table. These permissions are stored in the Mysql.tables_priv table.

• Column hierarchy

Column permissions apply to a single column in a given table. These permissions are stored in the Mysql.colummns_priv table.

Authorized

Syntax: Grant permissions on the Library name. Table name to User name @ ' hostname ' identified by ' password '; (Modify password to create a user)

　　Permissions List　　　

1. Alter
2. Create
3. Delete
4. Drop
5. Index
6. Insert
7. Select
8. Update
9. File reads or writes files on the server
10. All permissions
11. Show databases

Grant Full permissions

MariaDB [(None)]> grant all on * * to [e-mail protected] identified by ' Test '; MariaDB [(None)]> flush privileges;  Refresh Permissions

Granting partial permissions

MariaDB [(None)]> Grant Select,insert,update on test.* to [e-mail protected] ' 192.168.1.% ' identified by ' 123 ';

View Current User Permissions

MariaDB [(none)]> show grants;

View additional user Rights

MariaDB [(none)]> show grants for Sonfer;

　

The principle of authorized experience ( permissions control is primarily for security reasons, so a few empirical principles need to be followed）

1. Grant only the minimum permissions that are needed to prevent users from doing bad things. For example, users just need to query, then only give select permission on it. Do not give users update,insert or delete.
2. Restrict the user's login host when creating a user
3. Delete the user without the password when initializing the database.
4. Set a password that satisfies the complexity of the password for each user.
5. Regular cleanup of unwanted users.

Reclaim Permissions

Syntax: Revoke permissions on the Library name. Table name from user @ ' host ';

MariaDB [(none)]> revoke all on * * from  [email protected] '% ';      Reclaim All Permissions mariadb [(none)]> revoke insert on * * from  [email protected] '% ';   Reclaim Partial permissions

　　

VIII. classification type of backup backup

　　　Continue to be divided into hot, warm and cold backups based on whether the service is online
Hot backup: Read and write are unaffected
Warm backup: Can only perform read operations
Cold backup: Also known as offline Backup, read and write operations are terminated.

　　　Depending on whether the data files are copied between backup and logical backup
Physical Backup: Copy data files directly, fast.
Logical backup: Typically, you export data to a text file. Slow speed, loss of floating point precision, easy to use text based on direct processing, portable ability is strong.

　　　Divided into full, incremental, and differential backups based on backup data content
Full backup: Backs up all data.
Incremental backup: Backs up only data that has changed since the last full or incremental backup.
Differential backup: Backs up only data that has changed since the last full backup

Backup Time selection: Do a backup when database traffic is small

Backing up a single database using mysqldump backup

Syntax: mysqldump-u ROOT-P2 Library name >/tmp/name.sql

[Email protected] mysql]# mysqldump-u root-p2 data1 >/tmp/data1.sql

Backing up a single table
Syntax: # mysqldump-u ROOT-P2 Library Name Table name >/tmp/name.sql

Back up multiple tables
Syntax: mysqldump-u ROOT-P2 library Name table Name Table name >/tmp/name.sql

Multi-Library backup- b,--databases
Syntax: mysqldump-u root-p2-b Library name >/tmp/name.sql

Full Library Backup -A,--all-databases
Syntax: Mysqldump-u root-p2-a >/tmp/name.sql

Recovering a single-Library restore

MariaDB [(None)]> drop database data1;   Delete Library mariadb [(none)]> CREATE Database data1; Re-build the library # The first method of recovery: [[email protected] mysql]# mysql-u root-p2 data1 </tmp/data1.sql #第二种恢复方式: [[email protected] mysql]# m Ysql-u root-p2mariadb [(none)]> use schoolmariadb [school]> source/tmp/data1.sql

Table Recovery

[Email protected] mysql]# mysql-u ROOT-P2 School </tmp/table.sql

Docu recovery (no need to manually create a database before recovery)

[Email protected] mysql]# Mysql-u ROOT-P2 </tmp/ddata.sql

Full library Recovery

[Email protected] mysql]# Mysql-u ROOT-P2 </tmp/all.sql

Nine, log management four kinds of logs

Location of log storage: Data directory
/var/lib/mysql/rpm

Error log: Logs the start, run, and shutdown processes of the MySQL server.

/VAR/LOG/MARIADB/MARIADB.LOGMARIADB [(None)]> show variables like ' log_error '; +---------------+----------------- -------------+| variable_name | Value | +---------------+------------------------------+| Log_error | /var/log/mariadb/mariadb.log |+---------------+------------------------------+1 row in Set (0.03 sec)

Binary log

Documenting all DDL and DML operations that a user makes to a database, without logging query statements, plays a critical role in disaster recovery of the data.
Ddl:create, DROP, alter
Dml:insert, UPDATE, delete
Binary log function: Do ab copy, restore

General Query log: General_log

Log all request information (including login, query) to the database, not open by default
Large log volume, disk space consumption, generally do not open

Slow query log:

The record is query time more than a specified time query statement, generally used to optimize the query, default does not open

Open a variety of logs, modify the configuration file to open the General query log temporarily open, restart service failure

MariaDB [(None)]> show variables like ' general_log '; +---------------+-------+| variable_name | Value |+---------------+-------+| General_log | OFF |+---------------+-------+s1 row in Set (0.00 sec) MariaDB [(None)]> set global general_log=on; MariaDB [(None)]> show variables like ' general_log '; +---------------+-------+| variable_name | Value |+---------------+-------+| General_log | On |+---------------+-------+1 row in Set (0.00 sec)

General query log/var/lib/mysql/short hostname. log

Permanent settings

Turn on slow query log

Open Binary Log

[[email protected] mysql]# Vim/etc/my.cnflog-bin=log_bin.log//= to the right of the log file name can be customized Log-bin-index=log_bin.index[[email Protected] mysql]# systemctl restart MARIADB

Binary Log View Mysqlbinlog

[[email protected] mysql]# Mysqlbinlog log_bin.000001 # at 4//event occurrence location #170606 14:51:00///Event occurrence time information

Use binary logs for data recovery using location points for recovery

[Email protected] mysql]# mysqlbinlog--start-position=245--stop-position=534 log_bin.000001 |mysql-u root- P2--start-position start position point--stop-position end position Point

When the usage event occurred

# mysqlbinlog--start-datetime= ' 17-06-06 15:25:04 '--stop-datetime= ' 17-06-06 15:26:15 ' log_bin.000002 | Mysql-u root-p2--start-datetime start time--stop-datetime end time

Viewing log information

MariaDB [(None)]> Show Master logs;+----------------+-----------+| Log_name | File_size |+----------------+-----------+| log_bin.000001 | 1079 | | log_bin.000002 | 1523 |+----------------+-----------+2 rows in Set (0.00 sec)

View operation record in binary log

MariaDB [(None)]> show Binlog events;

View the currently used binary log files and where they are located

MariaDB [(None)]> show Master status\g;

　　

Managing the log deletes the log (do not delete it easily)Delete all logs before a log file

MariaDB [(none)]> purge master logs to ' log_bin.000002 '; Query OK, 0 rows affected (0.05 sec)

How many days ago log files are deleted

MariaDB [(none)]> purge master logs before date_sub (Current_date,interval 1 day);

Resetting the binary log

MariaDB [(none)]> reset Master;

To manually generate a new binary log file

MariaDB [(none)]> flush logs;

　　

MySQL Database operations