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 permissions apply to all databases in a given server. These permissions are stored in the Mysql.user table.
Database permissions apply to all targets in a given database. These permissions are stored in the mysql.db and Mysql.host tables.
Table permissions apply to all columns in a given table. These permissions are stored in the Mysql.tables_priv table.
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
- Alter
- Create
- Delete
- Drop
- Index
- Insert
- Select
- Update
- File reads or writes files on the server
- All permissions
- 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)
- 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.
- Restrict the user's login host when creating a user
- Delete the user without the password when initializing the database.
- Set a password that satisfies the complexity of the password for each user.
- 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