Database Introduction-1 (architecture, basic management)

2nd MySQL architecture and basic Management 2.1 client and server-side model 2.1.1 MySQL service structure

MySQL is a typical C/s mode, the server and the client are composed of two parts

        Server-side program mysqld        client program MySQL comes with client (MySQL, mysqladmin, mysqldump, etc.)                  third  -party Client API Interface (PHP-MYSQL)

2.1.2 MySQL Connection mode

TCP/IP Connect the network connection string (connect via username and password IP port)

Mysql-uroot-p123-h 127.0.0.1-p 3306

Socket Connect network sockets (user name password socket file)

The use of MySQL command in Linux without other parameters to connect the way that

Mysql-uroot-poldboy123 is using a socket file as the login

2.1.3 MySQL during the boot process

Start the background daemon and generate worker threads

Pre-allocated memory structure for MySQL processing data usage

The instance is MySQL . the background process + thread + pre-allocated memory structure

Composition of 2.2 mysqld services

2.2.1 Connection Layer

The communication protocol is: TCP/IP or socket

Connection thread is the number of connections

User authentication for communication protocol through user name password authentication

2.2.2 SQL layer

SQL is a structured query statement (internal logical language of the database) Sql92 sql99

DDL Database Definition Language

DCL Database Control Language

DML Database Operation language

DQL Data Query Language

Functions of the SQL layer: SELECT * from Wordpress.user;

1, the judgment syntax, the sentence, the semantic    judgment statement type 2, the database cannot respond directly the SQL statement    must be clear to know the data in which disk  3, the Database object authorization condition to judge the    authorization failure not to continue 4, resolves (parser)    Parse the SQL statement into execution plan, run execution plan, generate the way to find Data 5, optimization (optimizer)    run execution Plan   5.6 based on the cost of the algorithm, from the execution plan to choose the least cost to "executor" 6, "executor"    Run execution plan     The final production how to go to disk to find Data Mode 7, the way to take data, to the lower level (storage engine layer) to process 8, the final data will be taken out of abstraction to the administrator or user can understand the way (table), show in front of the user 9, query cache: Cached before the query data.    If the table we are querying is a frequently changing table, the query cache is not set too large

SQL Layer Processing Flow

2.2.3 Storage Engine Layer

The storage method is determined by the upper layer

The storage engine is a server component that acts as a handler for different table types.

Storage Engine Layer Features:

Save data, Fetch data

Different ways to store data

Different ways of managing:

Business (Increase, delete, change)

Backup recovery

Advanced Features (high-availability architecture, read-Write separation architecture)

2.2.4 relies on the capabilities of the storage engine

The storage engine is a server component that acts as a handler for different table types.

The storage engine is used to:

Store data, retrieve data, find data        storage media by index,  transactional capabilities,  Lock, Backup and restore, optimize

Special Features:

Full-Text Search, referential integrity, spatial data processing    

Double treatment

The upper layer includes the SQL parser and optimizer, and the lower layer contains a set of storage engines

The SQL layer does not depend on the storage engine:

The engine does not affect SQL processing

2.2.5 logical composition of MySQL (Database internal structure)

The logical composition is for the user to be able to read the data, so that you can better understand the data.

A way to manage your data.

Object:            A library contains tables in a directory representation table in Linux with            column structure and Row Records, in Linux with multiple files representing                row record                column structure

Switch libraries

mysql> use MySQL;

View Table

Mysql> Show tables;

View column information (record)

mysql> desc User;

2.2.6 how MySQL is stored

The program files are stored in the server installation directory along with the data catalog. program executables and log files are created when you execute various client programs, management programs, and utilities. The primary use of disk space is the data directory.

Server log files and status files:

Contains information about the statements processed by the server. Logs can be used for troubleshooting, monitoring, replication, and recovery.

InnoDB log file: (applies to all databases) resides at the data directory level.

InnoDB system tablespace: contains data dictionaries, undo logs, and buffers.

Each database has a single directory under the data directory (regardless of what type of table is created in the database). The database catalog stores the following content:

Data files:     data files that are specific to the storage engine. These files may also contain metadata or index information, depending on the storage engine being used. Format file (. frm): Contains a description of each table and/or view structure, located in the appropriate database directory. Trigger:     A named database object that is associated with a table and activated when a specific event occurs on that table.

The location of the data directory depends on the configuration, operating system, installation package, and distribution. The typical location is/var/lib/mysql.

MySQL stores the system database (MySQL) on disk.

MySQL contains information such as users, privileges, plug-ins, help lists, events, time zone implementations, and storage routines.

2.2.7 MySQL Architecture summary

SQL optimize the relevant concepts

Parser: A way to execute SQL from the execution plan database

Optimizer: Know the basic rules that directly affect which execution plan to choose in the future

Query caching: In a production environment, Redis memcached are generally used instead

Logical Structure

a library is a directory, in order to store multiple tables

table in the corresponding library, with multiple files to represent

MyISAM Table 3 files: (. myd data file. myi index file FRM table structure definition file)

Innodb:2 or one, shared tablespace (ibdata1 base table Metadata), stand-alone tablespace (default table storage after 5.6)

How to use Disk

1, more than one library directory, the directory of the storage of multiple tables stored files

2, auto.cnf db02.err db02.pid ibdata1 ib_logfile0 ib_logfile1 such as

2.3 MySQL Management 2.3.1 connection management: MySQL

[[email protected] ~]# mysql--help-u <user_name> or--host=<user_name>-p<password>-h 
Common Ways to connect:
Socket:  mysql-uroot-p123  -s/application/mysql/tmp/mysql.socktcp/ip:mysql-uroot-p123-h 10.0.0.52-p 3306
2.3.2 Database Start-up process
Mysqld file Properties
[Email protected] ~]# cd/application/mysql/bin/[[email protected] bin]# file mysqldmysqld:elf 64-bit LSB executable, x8 6-64, version 1 (SYSV), dynamically linked (uses shared libs), for Gnu/linux 2.6.18, not stripped
Mysql.server file Properties
[[Email protected] bin]# file: /support-files/mysql.server. /support-files/mysql.server:posix Shell Script Text executable
Mysqld_safe file Properties
[[email protected] data]# file/application/mysql/bin/mysqld_safe/application/mysql/bin/mysqld_safe:posix shell Script Text Executable
You can see that both the Mysql.server file and the Mysqld_safe file are script files, and finally the mysqld binaries are called to start.
2.3.3 How MySQL is closed
Recommended method:
1, mysqladmin-uroot-p123 shutdown2, servive mysqld stop
Try to avoid using the KILL command
Mysqladmin command to turn off MySQL
[[email protected] bin]# mysqladmin shutdown-uroot-p123warning:using a password on the command line interface can is in Secure.
　　perror Command
[Email protected] bin]# perror 13OS error code  :  Permission denied
The perror command is a MySQL self-command that can query the meaning of the MySQL error code.
2.3.4 MY.CNF Configuration file description
function :
1, affect the start of the server process
2, affect the client program
Configure MY.CNF
Use different "tags" to explicitly specify which part of the feature is affected
Server-side
[Server]    [MYSQLD]-----> General Settings    [mysqld_safe][client]     ----> For ease of setting this item    [MySQL] [     mysqladmin]    [ Mysqldump
MY.CNF file Configuration Instance
[Email protected] data]# cat/etc/my.cnf[mysqld]basedir=/application/mysqldatadir=/application/mysql/datasocket=/ Tmp/mysql.socklog-error=/var/log/mysql.logport=3307[mysql]socket=/tmp/mysql.sockuser=rootpassword=123
Check the process information, you can see the same as the MY.CNF configuration
[Email protected] data]# Ps-ef |grep [my]sql root       3411 1918  0 15:52 pts/1    00:00:00/bin/sh/application /mysql/bin/mysqld_safemysql      3548   3411  0 15:52 pts/1    00:00:00/application/mysql/bin/mysqld-- Basedir=/application/mysql--datadir=/application/mysql/data--plugin-dir=/application/mysql/lib/plugin--user= MySQL--log-error=/var/log/mysql.log--pid-file=/application/mysql/data/db02.pid--socket=/tmp/mysql.sock--port= 3307
Configuration of the My.cnf file
2.3.5 the read process of the configuration file
/ETC/MY.CNF---/etc/mysql/my.cnf $MYSQL _home/my.cnf---defaults-extra-file=/tmp/clsn.txt--and ~/. My.cnf
Note: Assuming that 4 configuration files are present while using--defaults-extra-file to specify the parameter file, if there is a "parameter variable" in the 5 configuration file, then the parameter value in the subsequent configuration file overrides the parameter variable value in the previous configuration file, This means that the value set in ~/.MY.CNF is used
If you use the./bin/mysqld_safe daemon to start the MySQL database, the absolute path > parameters of the--defaults-file=< configuration file are used, and only the configuration file specified by this parameter is used.
2.4 MySQL Multi-instance configuration 2.4.1 What is a MySQL multi-instance?
To put it simply, MySQL multi-instance is to open several different service ports at the same time on one server (for example: 3306/3307) running multiple MySQL service processes simultaneously, these service processes through different socket listens different service port to provide the service.
These MySQL multiple instances share a set of MySQL installers that use different my.cnf (or the same) configuration files, startup programs (or the same), and data files. In providing services, multi-instance MySQL logically appears to be independent, they are based on the configuration file corresponding to the set value of the server response to the number of resources.
The function and problem of 2.4.2 MySQL multi-instance
Efficient use of server resources
When a single server resource is left, you can take advantage of the remaining resources to provide more services and enable logical isolation of resources
Conserve server resources
When the company funds tight, but the database needs to separate as far as possible to provide services, and the need for master-slave replication and other technologies, multi-instance is no better
MySQL multi-instance has its advantages, but there are drawbacks, for example, there will be resources to preempt each other problem. When a database instance is in high concurrency or has a SQL slow query, the entire instance consumes a lot of resources such as system CPU, disk I/O, and so on, causing the quality of other DB instance services on the server to fall together.
2.4.3 multi-instance configuration ideas
More than 1     sets of profiles 2 multiple     sets of data 3 multiple     socket4     multiple ports 5     multiple log files 6     multiple boot programs
2.4.4 Multi-instance configuration process
Pre-MySQL installation is consistent with 1.4MySQL installation and is no longer duplicated here (configuration complete does not start)
First milestone: Multi-instance configuration file preparation
[Email protected]/]# tree/data//data/├──3306│   ├──my.cnf│   └──mysql└──3307    ├──my.cnf    └──mysql
3306-Port my.cnf configuration file
[email protected]/]# cat data/3306/my.cnf [Client]port            = 3306socket          =/data/3306/mysql.sock[mysqld]user    = Mysqlport    = 3306socket =  /data/3306/mysql.sockbasedir =/application/mysqldatadir =/data/3306/ Datalog-bin =/data/3306/mysql-binserver-id = 6[mysqld_safe]log-error=/data/3306/mysql_3306.errpid-file=/data/3306 /mysqld.pid
3307-Port my.cnf configuration file
[email protected]/]# cat  /data/3307/my.cnf [Client]port            = 3307socket          =/data/3307/mysql.sock[mysqld] User    = Mysqlport    = 3307socket  =/data/3307/mysql.sockbasedir =/application/mysqldatadir =/data/3307/ Datalog-bin =/data/3307/mysql-binserver-id = 7[mysqld_safe]log-error=/data/3307/mysql_3307.errpid-file=/data/3307 /mysqld.pid
Writing Administrative Scripts 3306
View Code MySQL Multi-instance startup script
Writing Administrative Scripts 3307
 View Code MySQL Multi-instance startup script
Note that the script will be able to work properly with the Continue permission
Second milestone: Create a Data Catalog and authorize
[Email protected]/]# mkdir/data/{3306,3307}/data-p[[email protected]/]# chown-r mysql.mysql/data/
Third milestone: Initializing data
Cd/application/mysql/scripts  &&/mysql_install_db  --defaults-file=/data/3306/my.cnf--basedir=/ Application/mysql--datadir=/data/3306/data--user=mysql./mysql_install_db  --defaults-file=/data/3307/my.cnf --basedir=/application/mysql--datadir=/data/3307/data--user=mysql
Fourth milestone: Create a log file
#5.6.36 particularity: You need to create an error log file
Touch/data/3306/mysql_3306.errtouch/data/3307/mysql_3307.err
Fifth milestone: Start a multi-instance
[Email protected] scripts]#/data/3306/mysql startstarting MySQL ... [Email protected] scripts]#/data/3307/mysql startstarting MySQL ...
Check MySQL status
[[email protected] scripts]# ps-ef |grep [my]sql root 4341 1 0 16:46 pts/1 00:00:00/bin/sh/applicat Ion/mysql/bin/mysqld_safe--defaults-file=/data/3306/my.cnf--pid-file=/data/3306/3306.pidmysql 4526 4341 13 16:46 PTS/1 00:00:03/application/mysql/bin/mysqld--defaults-file=/data/3306/my.cnf--basedir=/application/mysql-- Datadir=/data/3306/data--plugin-dir=/application/mysql/lib/plugin--user=mysql--log-error=/data/3306/mysql_    3306.err--pid-file=/data/3306/3306.pid--socket=/data/3306/mysql.sock--port=3306root 4549 1 0 16:46 PTS/1 00:00:00/bin/sh/application/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf--pid-file=/data/3307/3307. Pidmysql 4734 4549 Wuyi 16:46 pts/1 00:00:08/application/mysql/bin/mysqld--defaults-file=/data/3307/my.cnf--bas Edir=/application/mysql--datadir=/data/3307/data--plugin-dir=/application/mysql/lib/plugin--user=mysql-- Log-error=/data/3307/mysql_3307.err--pid-file=/data/3307/3307.pid--socket=/data/3307/mysql.sock--port=3307 
Check port information
[[email protected] scripts]# netstat  -lntup |grep mysql tcp        0      0::: 3306                     :::*                        LISTEN      4526/ mysqld         TCP        0      0::: 3307                     :::*                        LISTEN      4734/mysqld
This completes the configuration of the MySQL multi-instance
2.4. Use of 5 multi-instance MySQL
Local connection mode
Mysql-s/data/3306/mysql.sockmysql-s/data/3307/mysql.sock
2.4.6 about MySQL multi-instance selection
1, the financial tension of the company's choice of funds tight, the company's business visits are not small, but also hope that the different services of the database service as independent as possible 2, concurrent access is not particularly large business when the company's business visits are not too large, the server's resources are basically wasteful, which is suitable for multi-instance use 3, Web portal Application MySQL multi-instance scene configuration hardware good server, can save IDC cabinet space, run multiple instances will not reduce the hardware resources is not slow waste is generally from the library multi-instance, for example: A department using IBM server for 48 core CPU, memory 96GB, a server running four or three instances: in addition, Sina Network is also used for multi-instance, memory 48GB or so. Multiple instances are typically applied in a test environment in an enterprise environment.
2.5 MySQL forgot password what to do?
First step: Stop the Service
/etc/init.d/mysqld stop
Step two: Add parameters to start the service
Cd/application/mysql/bin/mysqld_safe--skip-grant-table--user=mysql--skip-networking &
This mode
No password login
Network users cannot log in
Local login only
None of the commands related to authorization can be executed.
Step three: Change the password
[[email protected] 3306]# mysqlmysql> update mysql.user set Password=password (' 123 ') where user= ' root ' and host= ' local Host ';mysql> flush privileges;
MYSQL5.7 Modify password Modify field authentication_string
Fourth step: Exit the Restart Service
/etc/init.d/mysqld restart
Fifth Step: Login Verification
Mysql-uroot-p123
The password has been modified successfully
2.6 References
Https://zh.wikipedia.org/wiki/MySQL#%E6%AD%B7%E5%8F%B2  MySQL History http://blog.51cto.com/oldboy/1728380   MySQL common error code and code description HTTPS://WWW.ABCDOCKER.COM/ABCDOCKER/28   MySQL Introduction https://www.abcdocker.com/ ABCDOCKER/90   MySQL Multi-instance http://oldboy.blog.51cto.com    Script source
 Introduction to Databases-1 (architecture, basic management)