Shulou(Shulou.com)06/02 Report--

(expanding) three paradigms of MySQL:

Add:

Introduction to several Design Paradigm of Relational Database

1. The first normal form (1NF)

In any relational database, the first normal form (1NF) is the basic requirement of the relational schema, and the database that does not meet the first normal form (1NF) is not a relational database.

The so-called first normal form (1NF) means that each column of a database table is an inseparable basic data item, and there can not be multiple values in the same column, that is, an attribute in an entity cannot have multiple values or duplicate attributes. If there are duplicate attributes, you may need to define a new entity, the new entity consists of duplicate attributes, and there is an one-to-many relationship between the new entity and the original entity. In the first normal form (1NF), each row of the table contains only one instance of information. For example, for the employee information table in figure 3-2, you cannot display all employee information in one column, nor can you display two or more of them in one column; each row of the employee information table represents only one employee's information. an employee's information appears only once in the table. In short, the first paradigm is a column without repetition.

2. The second normal form (2NF)

The second normal form (2NF) is established on the basis of the first normal form (1NF), that is, to meet the second normal form (2NF) must first meet the first normal form (1NF). The second normal form (2NF) requires that each instance or row in the database table must be uniquely localized. To achieve differentiation, you usually need to add a column to the table to store the unique identity of each instance. As shown in figure 3-2, the employee number (emp_id) column is added to the employee information table, because each employee's employee number is unique, so each employee can be uniquely distinguished. This unique attribute column is called the primary keyword or primary key, primary code.

The second normal form (2NF) requires that the attributes of the entity are completely dependent on the primary keyword. The so-called complete dependence means that there can not be an attribute that depends only on part of the primary keyword. If it exists, then this part of the attribute and the primary keyword should be separated to form a new entity. There is an one-to-many relationship between the new entity and the original entity. To achieve differentiation, you usually need to add a column to the table to store the unique identity of each instance. In short, the second paradigm is that the non-primary attribute is not partially dependent on the primary keyword.

3. The third normal form (3NF)

To meet the third normal form (3NF), we must first meet the second normal form (2NF). In short, the third normal form (3NF) requires that a database table does not contain non-primary keyword information that has already been contained in other tables. For example, there is a department information table in which each department has a department number (dept_id), department name, department profile, and so on. Then after the department number is listed in the employee information table in figure 3-2, the department name, department profile and other department-related information can no longer be added to the employee information table. If there is no department information table, it should also be built according to the third normal form (3NF), otherwise there will be a lot of data redundancy. In short, the third paradigm is that attributes do not depend on other non-primary attributes.

Why there is a database: the question of file management data:

1. Data redundancy and inconsistency

two。 Big data is difficult to visit.

3. Data isolation

4. Integrity and atomicity

5. Concurrent access exception

6. Security problem

As a result, there is a database.

Overview of Database

Database: refers to a collection of data that is stored together in a certain way, can be shared by multiple users, has as little redundancy as possible, and is independent of the application.

Basic concepts of database server

Database

DBMS database management system (large software that can operate and manage databases)

RDBMS relational database (= DBMS)

1. The data appears in tabular form.

two。 Various record names for each behavior

3. The data field corresponding to the record name of each column

4. Many rows and columns form a form

5. Several forms make up database

Data sheet

Data (record)

Field (id name....)

Type (define what is in the field)

Primary key (for retrieval)

Three basic forms of database

Hierarchical model:

A model for organizing database data in the form of hierarchical structure

Disadvantages: redundant data

Mesh model:

A model for organizing database data in the form of a mesh structure

Disadvantages: difficult maintenance in later stage

Relational model:

A model for organizing database data in the form of relational structure

Sql statement:

Structured query statement

Sql type:

DML data manipulation language: used to manipulate data in a database

INSERT

DELETE

SELECT

UPDATE

DDL data description language: used to build databases and define data relationships

CREATE

DROP

ALTER

DCL data control language: used to control the permissions of database building

GRANT

REVOKE

Relational database structure

File logical relationship:

Upper layer: fil

Bottom layer: stored in the data block of the hard disk in a binary way

Middle tier: file system

Database logical relationship:

Upper layer: data tabl

Underlying: fil

Middle tier: storage engine (provides methods for storing, creating, updating, and querying data)

Storage engines supported by mysql:

MYISAM

Default engine, insert and query are faster

Features such as transactions, row-level locks, and foreign key constraints are not supported

Note:

Transaction: batch processing of a sql statement, in order to ensure data atomicity

Locks: row-level locks (less conflicts, slow speed); table-level locks (more conflicts, high speed); page-level locks (compromise)

Constraint

Domain constraints: data type constraint

Foreign key constraints: referential integrity constraint

Primary key constraint: a field uniquely identifies the entity to which this field belongs and is not allowed to be empty

There can be only one primary key in a table

Uniqueness constraint: a field in each row is not allowed to have the same value and can be empty

There can be more than one in a table

Checking constraints: age: int

INNODB

Supports functions such as transactions, row-level locks, and foreign key constraints

MEMORY

Working in memory, saving data through hashing, fast and unable to save data permanently

Data storage and query

Storage manager (to achieve the function of storage, create the structure of the data table through DDL, and then save the data through DML)

Transaction manager: providing transaction functionality

File manager: saving the correspondence between database data and files

Permissions and Integrity Manager: setting storage permissions

Buffer manager: managing buffer space

Query manager (to realize the query function, receive the user's query request, understand the user's query request, submit the query request to the storage manager, realize the final storage)

DDL, DML interpreter

Query execution engine

Database working mode:

Working mode of single-process and multi-thread

Daemon thread

Application thread (user thread)

Add: the working mode of apache:

A process processes a request

A thread processes a request

One thread processes multiple requests

Mysql optimization

1. Vertical expansion

two。 Thread reuse

3. Caching mechanism (nosql)

Disadvantages:

SMP symmetric multiprocessor architecture

3. Emurr model

Entity relation modeling

Entity: data object, that is, visible and tangible

Contact: indicates an association between one or more entities

Attribute: a property of an entity

Database-basic overview

The version of the database

1. Community version

two。 Enterprise edition

3. Cluster version

Installation of database

Dedicated package Manager (binary)

Deb, rpm, etc.

Mysql MySQL client programs and shared libraries

Related programs required by mysql-server MySQL server

Source code package (compilation and installation)

Configure 、 cmake

Common configuration options for databases

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-specify the stump installable path (default is / usr/local/mysql)

-DMYSQL_DATADIR=/data/mysql-the path to the data file of mysql

-DSYSCONFDIR=/etc-configuration file path

-DWITH_INNOBASE_STORAGE_ENGINE=1-using the INNOBASE storage engine

-DWITH_ARCHIVE_STORAGE_ENGINE=1-often used for logging and aggregation analysis, but does not support indexing

-DWITH_BLACKHOLE_STORAGE_ENGINE=1-Black Hole Storage engine

-introduction to canceling some storage engine instructions during DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 compilation

-DWITHOUT_FEDERATED_STORAGE_ENGINE=1

-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_READLINE=1-support batch import of mysql data

-DWITH_SSL=system-mysql supports ssl sessions and implements data recovery based on ssl

-DWITH_ZLIB=system-Compression Library

-DWITH_LIBWRAP=0-whether access control can be implemented based on WRAP

-DMYSQL_TCP_PORT=3306-default port

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock-default socket file path

-DENABLED_LOCAL_INFILE=1-whether to enable the LOCAL_INFILE feature

-DEXTRA_CHARSETS=all-whether additional character sets are supported

-DDEFAULT_CHARSET=utf8-default coding mechanism

-DDEFAULT_COLLATION=utf8_general_ci-sets the collation of the default language

-DWITH_DEBUG=0-DEBUG function setting

-DENABLE_PROFILING=1-whether the performance analysis feature is enabled

Service: mysqld

Port: 3306

Main configuration file: / etc/my.cnf

Script: mysql_install_db

Mysqld_safe

Data directory: / var/lib/mysql

Socket file: / var/lib/mysql/mysql.sock

When you accidentally close the database, if you can't open it again, find this, delete it and start it again.

Process file: / var/run/mysqld/mysqld.pid

Log in and out of the mysql environment

A) set the password mysqladmin-uroot password '123'

B) Log in to mysql-u user name-p

-p user password

-h login location (hostname or ip address)

-P port number (not if 3306 is changed)

-S socket file (/ var/lib/mysql/mysql.sock)

C) exit exit

D) create login user create user username @'% 'identified by' password'

E) change password set password=password ('new password')

Set password for user @ login location = password ('new password')-root user retrieves passwords for other users

When the administrator forgets his password, how to get it back?

1) close the database

2) modify the main configuration file (/ etc/my.cnf) / backup path / backup file name (backup a single database)

Mysqldump-u user name-p database name table name > / backup path / backup file name (backup data table)

-- databases Library 1, Library 2 (restore at this time-> mysql

< 备份文件) --all-databases -备份服务器中的所有数据库内容 还原：mysql 数据库 < 备份文件 mysqlhotcopy 备份：mysqlhotcopy --flushlog -u='用户' -p='密码' --regexp=正则 备份目录 还原：cp -rpf 备份目录 数据目录（/var/lib/mysql） 补充的备份机制 1.日志备份 >

Mysql show global variables like'% log%'

List the log-related variables in mysql

Error log

Information when the server starts and shuts down

Error message during server operation

Information generated when starting a process from the server from the server

The path to the log-error error log

General logging (not enabled)

Record the user's query operations to the database

General-log=ON launches the general query log

Log=ON Global Log switch

Record type of log-output log

Slow query log

Recording takes a long time to query

Log-slow-queries= saves the path to start the slow query log and sets a path

Binary log

All actions to change the state of the database (create, drop, update, etc.)

Log-bin= location startup binary log

Mysql show binary logs to view the currently used binary log

> mysql show binlog events in 'binary log (mysql-bin.000001)' view the contents of the binary log

Restore: (mysqlbinlog)

Restore by time:

Mysqlbinlog-- start-datetime 'YY-MM-DD HH:MM:SS'-- stop-datetime' YY-MM-DD HH:MM:SS' binary log | mysql (- urot-p)

Restore by file size:

-- start-position

-- stop-position

Transaction logs: recording transaction-related log information

Relay log: recording backup information from the server

two。 Multi-machine backup

Master-slave configuration: real-time backup

Master and master configuration: (master and slave configured twice) real-time backup, load balancing

Multi-slave master: real-time backup (more backup nodes)

Multi-master and one-slave: real-time backup, cost saving

Experiment

1. The login user yzh who created the mysql can log in to the mysql server

Create a login user

Test on another virtual machine

The created user changes the password for himself

Root users retrieve passwords for other users

Root retrieves his password and modifies it

The experiment of adding, deleting, changing and querying the database

Create a database

Create a datasheet

A little more complicated.

Insert data

Copy the data from table a2 to table A1

Delete database

Delete data tabl

Delete the data record in the table

Delete those between the ages of 23 and 25

Modify the data in the table

Modify the name of the data table

Modify the field type of the data table

Modify the fields of the data table

Add a field

Delete a field

Authorize the user

1. Give the user full permissions

Revoke the permission of yzh users to delete data from libraries, tables, and tables

Backup and restore database files

1. Backup the database aa to the / root directory

two。 Simulation database aa lost (delete database aa)

3. Reduction

Back up multiple databases (--databases)

Reduction

Back up a database with rules

Simulated database deletion

Reduction

About binary log restore

Open binary log

View binary log files

Restore by time:

If the bb library in the database is deleted, it needs to be restored

View the contents of the binary log

Restore and view

Restore by file size:

Restore to the deleted data state of the bb library

Check the file size before and after the deletion of the bb library

Restore and view

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.