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

The following content mainly brings you the compilation and installation of Mysql database and basic tutorials, the knowledge mentioned is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, and have a certain value of experience sharing, hoping to bring help to the majority of readers.

First, why is there a database?

Friends who have just come into contact with the database will ask what the database is used for. The literal meaning is that the warehouse used to store data is referred to as "database". In our daily life, we have a lot of transactions related to the database, such as: 1. When we go to make a train, we need real name authentication. Why can security personnel query our personal information through our × × ×? because they are all people with hukou, and our information is entered in the public security resident information database, and their information is also stored in the national citizen information database. This is also what we call the database, the early most traditional database is the text file. It is not necessary to query the whole file from beginning to end when querying data, and the efficiency of query and retrieval is extremely inefficient. With the outbreak of big data on the Internet, this way is also faced with elimination. Later, a variety of data management programs were developed, which is the beginning and rebirth of the database.

Defects in managing data in a text database:

Data redundancy, information redundancy and data integrity: make it possible for the same data to be repeated many times.

Data access is slow and troublesome.

Data isolation: it is difficult for a data split to establish a relationship again.

Atomicity problem: effectively ensure the interaction between databases and synchronize data in time.

Concurrent access: multiple concurrent access cannot be achieved in file mode.

Security issues: file authorization is too general to achieve fine authorization (such as library authorization, table authorization, user authorization)

Database management system

In order to manipulate data access more finely, an intermediate layer is established between application and data, which is specially used to load data access and management, resulting in a database management system (DBMS).

(DBMS) database management system, including front-end API interface to interact with front-end applications, as well as shell command-line interface to interact directly with programmers. At the same time responsible for the completion of data access. At this time, the front-end program will no longer face the data directly, but the database management system agent will complete the more detailed management work. This includes permissions and how to allocate data in a more efficient manner.

Database model:

1) hierarchical model

2) mesh model

3) Relational model: in 1975, E.F.Coded of IBM Research Institute put forward and published the first model paper on relational database, including Larry. Crisson and his programmer friends implemented it, and later took on a US military project called oracle to create oracle.

4) NoSQL:No only SQL, many products, each has its own focus. Because large amounts of data are stored in a distributed way, CAP testing is required, and NoSQL products usually can only meet two.

CAP testing: Consistent Avilable Partition tolerence consistency, availability, fault tolerance

Open source databases currently on the market:

1) sqliteMySQL

2) PostgreSQL (EnterpriseDB)

3) MariaDB

4) MySQL

Relational database management model:

Stored files:

1) data: saved data

2) Index: an index file that saves data

3) thing file: operating thing file

two。 Disk space manager: used to manage access to data on disk, indexing, etc.

3. Buffer manager: in order to solve the inefficiency of frequent Iamp O, some of the retrieval results are stored in the buffer memory, and the buffer manager is used to manage the storage in memory.

4. Access method interface: managing the access method of data, acting on sql statements

5. Transaction manager: when an access operation is completed, it is not written directly to the data file, and is written to the transaction log. If the database crashes at this time, the database access data will be stored in the transaction log. The database file is written to random Imax O, and the log file is written to sequential Imax O, so the latter is faster.

Transactions: meeting the ACID test

A: atomicity

C: consistency

I: isolation

D: persistence

6. Lock manager: when a data is accessed by a user, it can no longer be accessed by other users and will be locked by the lock manager.

7. Recovery manager: when the access operation writes to the transaction log but not to the data, the database crashes. On the second restart, the contents of the transaction log will be written to the data file through the recovery manager.

8.SQL query engine:

1)。 SQL Struct Query Language: a structured query language that contains the following three types of commands

(1) DCL: Data Control Language: data control language, mainly to grant or revoke certain access rights

GRANT/REVORK

(2) DDL: Data Definition Language: data definition language, creating tables and indexes

CTREAT/ALTER/DROP

(3) DML: Data Manipulation Language: data manipulation language

SELECT/INSERT/DELETE/UPDATE

2)。 The SQL query engine consists of four parts

(1) parser for syntax analysis of SQL language

(2) query plan, optimize the path of the query object, and it is possible to unify multiple indexes of the object.

(3) Optimizer to optimize the query mode of SQL language

(4) Solver, manage the returned results, etc.

(5) Thread pool. When the mysql CVM receives multiple concurrent requests, the allocation thread responds to multiple users.

9.SQL command line interface: SQL client program that uses SQL commands to query SQL servers

10. Application command API: different programming languages have different SQL command output interfaces. SQL statements can be delivered to the SQL server through the API driver, which is essentially a SQL client.

11.ODBC: query Standard of underlying Relational Database

Standard ANSI of 12.SQL cable language: SQL-86, SQL-89, SQL-92, SQL-99, SQL-2003

13. Transaction isolation:

Isolation level:

Read not submitted: read uncommitted

Read submit: read committed

Rereadable: repeatable read

Serialization: serializable

14.Mysql storage engine

MyISAM: no transaction

Non-aggregation

InnoDB: transactional

Clustered index

15. Constraints for relational databases:

Primary key

Foreign key

Unique key

Conditional constraint

Non-empty constraint

2. Installation of MySQL database management software:

Source of mysql program: mysql.com

1) rpm package that comes with vendor system

2) MySQL official rpm package: not recommended, it may break dependencies

3) Universal binary format: recommended

4) Source code compilation: recommended

Let's enter the actual MySQL production configuration (compilation and installation) of the relational database through a case:

Operating system: CentOS release 6.5 (x64)

Mysql version: mysql-5.6.30

(1)。 Install the development environment and development package group:

# yum-y groupinstall "Development Tools" # yum-y groupinstall "Development Libraries" # yum-y install pcre-devel# yum-y install ncurses-devel# yum-y install openssl-devel

(2)。 Install the boast platform compiler cmake

# tar xf cmake-2.8.8.tar.gz # cd cmake-2.8.8#. / configure # make & & make install

(3)。 Data storage directory planning preparation:

Plan a logical volume with a size of 20G for later data expansion:

# fdisk / dev/sda# kpartx-l / dev/sda# kpartx-af / dev/sda# partx-a / dev/sda

Create logical partition management:

# pvcreate / dev/sda3 # vgcreate myvg / dev/sda3 # lvcreate-L 10G-n mydata myvg

Format the mount logical volume directory:

# mke2fs-t ext4 / dev/myvg/mydata # mkdir / mydata # create a mount directory # vim / etc/fstab # add this line / dev/myvg/mydata / mydata ext4 defaults at the end Acl 0 'mount-a # mkdir / mydata/data# groupadd-r-g 3306 mysql # create mysql service user group # useradd-r-g 3306-d / mydata/data/-s / sbin/nologin mysql # create mysql service user # chown mysql.mysql / mydata/data/

For data security in the production environment, you can use ACL permission control:

# setfacl-m u:mysql:rwx / mydata/data/# getfacl / mydata/data/getfacl: Removing leading'/ 'from absolute path names# file: mydata/data/# owner: mysql# group: mysqluser::rwxuser:mysql:rwxgroup::r-xmask::rwxother::r-x

(4)。 Compile and install mysql-5.5.33

Before compiling and installing, we need to set up host name and host name resolution:

# hostname mysql.samlee.com# vim/etc/ sysconfig/network HOSTNAME=mysql.samlee.com# vim/etc/hosts 172.16.100.7 mysql.samlee.com

Compile and install:

# tar xf mysql-5.5.33.tar.gz# cd mysql-5.5.33# cmake. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql\ >-DMYSQL_DATADIR=/mydata/data\ >-DSYSCONFDIR=/etc\ >-DWITH_INNOBASE_STORAGE_ENGINE=1\ >-DWITH_ARCHIVE_STORAGE_ENGINE=1\ >-DWITH_BLACKHOLE_STORAGE_ENGINE=1\ >-DWITH_READLINE=1\ >-DWITH_SSL=system\ >-DWITH_ZLIB=system\ >-DWITH_LIBWRAP=0\ >-DMYSQL_UNIX_ADDR=/tmp/mysql.sock\ >-DDEFAULT_CHARSET=utf8 >-DDEFAULT_COLLATION=utf8_general_ci# make & & make install

(5)。 Database server initialization

# cd / usr/local/mysql/# chown-R: mysql/ usr/local/mysql/# assigns directory user groups to run for mysql#. / script/mysql_install_db to create system libraries # scripts/mysql_install_db-- user=mysql-- datadir=/mydata/data/# cp support-files/my-large.cnf / etc/my.cnf # copy configuration files # vim/etc/my.cnf # Edit configuration file add this item under [mysqld]: datadir = / mydata/data# cp support-files/mysql.server / etc/init.d/mysqld # replication startup script # chkconfig-- add mysqld # add service list # service mysqld Start # start the mysql service

Query the files created after initialization and startup:

# ll / mydata/data/total 28700 RWMI / RWMI. 1 mysql mysql 18874368 May 6 14:27 ibdata1 # inodb storage engine tablespace file-rw-rw----. 1 mysql mysql 5242880 May 6 14:27 ib_logfile0 # inodb things Log-rw-rw----. 1 mysql mysql 5242880 May 6 14:27 ib_logfile1drwx-. 2 mysql root 4096 May 6 14:25 mysql-rw-rw----. 1 mysql mysql 107 May 6 14:27 mysql-bin.000001 # binary Log-rw-rw----. 1 mysql mysql 19 May 6 14:27 mysql-bin.index-rw-r-. 1 mysql root 1777 May 6 14:27 mysql.samlee.com.err # error message record-rw-rw----. 1 mysql mysql 6 May 6 14:27 mysql.samlee.com.pid # pid File drwx-. 2 mysql mysql 4096 May 6 14:25 performance_schemadrwx-. 2 mysql root 4096 May 6 14:25 test

Export header files, library files, and man documents:

# ln-sv / usr/local/mysql/include/ / usr/include/mysql# vim / etc/ld.so.conf.d/mysql.conf add this line: / usr/local/mysql/lib/# ldconfig# ldconfig-p | grep mysql# vim / etc/man.config add this line: MANPATH / usr/local/mysql/man# man mysqld

(6) configure PATH environment variables

# vim / etc/profile.d/mysql.shexport PATH=$PATH:/usr/local/mysql/bin# chmod + x / etc/profile.d/mysql.sh#. / etc/profile.d/mysql.sh

The above mysql compiles the installation process.

Third, the application of MySQL configuration file my.cnf and MySQL command:

1)。 Server profiles and client configurations are centralized configurations that are shared by multiple applications and can be divided into the following three parts:

(1) [mysql]: the mysql client configuration section is only used to configure mysql's own client.

(2) [mysqld]: mysql client server configuration.

[client]: all clients of mysql, including other clients.

How to use the configuration file:

1. It looks for each file in turn, and the result is that all files are merged.

2. If a parameter appears multiple times in multiple files, the read will eventually take effect.

2)。 You can query mysql service variables as follows:

(1) display the options available when the mysqld program starts, usually long options

(2) display the service variables available in the configuration file of mysqld

# / usr/local/mysql/bin/mysqld-help-verbose

Enter mysql mode to query:

Mysql > SHOW GLOBAL VARIABLESmysql > SHOW SESSION VARIABLES

3)。 Modify the mysql administrator password to delete anonymous users:

The user account consists of two parts: username@host

Host can also use wildcards:

%: any character of any length

_: match any single character

(1) Delete all anonymous users

Mysql > DROP USER'@ 'localhost'; mysql > DROP USER' @ 'mysql.samlee.com'; mysql > DROP USER' root'@'::1'

(2) change the administrator password

The first way:

Mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' redhat'); mysql > SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD (' redhat'); mysql > SET PASSWORD FOR 'root'@'mysql.samlee.com' = PASSWORD (' redhat'); mysql > FLUSH PRIVILEGES

The second way:

# mysqladmin-uroot-h372.16.100.7 password 'redhat'-p# mysqladmin-uroot-hlocalhost password' redhat'-p# mysqladmin-uroot-hmysql.samlee.com password 'redhat'-p

The third way:

# mysql-uroot-hlocalhost-pmysql > use mysql;mysql > UPDATE user SET password = PASSWORD ('redhat') WHERE user =' root';mysql > FLUSH PRIVILEGES

4)。 Connect to the MySQL server

The connection process is as follows:

Mysql client mysqld

Mysqld receives connection requests:

Local communication: the client and server are on the same host and communicate based on the 127.0.0.1 (localhost) address or lo interface

Linux OR Unix: Unix Sock, / tmp/mysql.sock, / var/lib/mysql/mysql.sock

Windows: memory, pipe

Remote communication: the client and the server are located on different hosts, or use non-loopback addresses to communicate on the same host

TCP socket

Client tools: mysql, mysqladmin, mysqldump, mysqlcheck

[client]

General options:

-u,-- user=

-h,-- host=

-p,-- password=

-- protocol= {tcp | socket | memory | pipe}

-- port=

-- socket= for example: / tmp/mysql.sock

Port on which mysql listens by default: 3306/tcp

The demonstration is as follows:

# mysql-uroot-hlocalhost-pEnter password: # mysql-uroot-h327.0.0.1-p-- port=3306-- socket=/tmp/mysql.sockEnter password:

5) .MySQL working mode:

Script mode:

# mysql-uUsername-hhostname-p

< /path/to/mysql_script.sql 脚本演示如下: mysql交互式模式： 客户端命令 mysql>

Help

Mysql >\?

\ C: end the current input execution

\ g: display by column

\ G: display in rows

\ Q: exit mysql

\!

\ s

\. / path/to/mysql_script.sql

Server-side command: command Terminator is required, default is semicolon (;)

Mysql > help contents

Mysql > help Keryword

Mysql command line options:

-- compress

-database=,-D

-H,-- html: a document that outputs results in html format

-X,-- xml: output format is xml

-- sate-updates: rejects update or delete commands without where clauses

Mysql command prompt:

Mysql > waiting for commands to be entered

->

'>

">

`>

/ * > comment information

Keyboard shortcuts for mysql:

Ctrl + w: delete the word before the cursor

Ctrl + u: delete everything from the cursor to the beginning of the command line

Ctrl+ y: paste content deleted using Ctrl+w or Ctrl+u

Ctrl + a: move the cursor to the beginning of the line

Ctrl + e: move the cursor to the end of the line

Mysqldmin tool:

Mysqladmin [options] command [arg] [command [arg]]...

Command:

Create DB_NAME

Drop DB_NAME

Debug: open the debug log and record it in error log

Status: displaying brief status information

-- sleep #: interval length

-- count #: batch displayed

Extended-status: outputs the state variables of mysqld and their values, which is equivalent to executing "mysql > SHOW GLOBAL STATUS"

Variables: output each server variable of mysqld

Flush-hosts: clear the host-related cache: DNS parses the cache. The list of hosts that were previously denied access to mysqld due to too many connection errors

Flush-logs: log scrolling, binary log and relay log

Refresh: equivalent to using flush-logs and flush-hosts at the same time

Flush-privileges:

Reload: function is the same as flush-privileges

Flush-status: resets the value of the state variable

Flush-tables: closes the currently open table file handle

Flush-threads: clear thread cache

Kill: kill specified threads. You can kill multiple threads at a time, separated by commas, but with no extra spaces.

Password: change the password of the current user

Ping:

Processlist: displays a list of mysql threads

Shutdown: shut down the mysqld process

Start-slave

Stop-slave: starting / shutting down slave server threads

Example demonstration:

# mysqladmin-uroot-hmysql.samlee.com status-- sleep 1-p # mysqladmin-uroot-hmysql.samlee.com variables-p

GUI client tools:

Navicat for mysql

Toad for mysql

Mysql front

Sqlyog

PhpMyAdmin

For the above compilation, installation and basic tutorials of Mysql database, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.