First, MySQL Database Foundation

1.1. Database model

A database consists of an ordered set of data that is in a structured data table. The data tables are interrelated and reflect the essential relations between the objective things. The database system provides security control and integrity control of the data. The development of database is divided into several stages: artificial management stage, file system stage, database system stage, advanced database stage. There are about 3 kinds of them: hierarchical database, network database and relational database.

Text database defects: Data redundancy and inconsistency, data access difficulties, data isolation, integrity issues, atomicity problems, concurrency access issues, security issues.

Database system features: Data management independence, data access, data integrity and security, data centralized management, concurrent storage and recovery, reduce application development time.

The database system is composed of hardware and software, and the hardware is mainly used to store the data in the database, including computer, storage device and so on. The software section mainly includes DBMS, operating system supporting DBMS, and access technology to support multi-language application development.

Generic relational database schema:

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/9B/97/wKiom1lkubmCWwBNAACCI5cY8TY388.png-wh_500x0-wm_ 3-wmp_4-s_3193517306.png "title=" image 1.png "alt=" Wkiom1lkubmcwwbnaacci5cy8ty388.png-wh_50 "/>

1.2. Data types and Constraints

The data type determines the format in which the data is stored on the computer, representing different types of information. The commonly used data types are:

Character: CHAR (n) VARCHAR (n) BINARY (n) VARBINARY (n) TEXT (n) BLOB (n) Numerical: Integral type: TINYINT SMALLINT mediumint INT BIGINT Modifier: UNSIGNED not NULL Float type: float DOUBLE DateTime: Date Time DATETIME STAMP Boolean Null Built in: ENUM, SET Field Modifiers: Not NULL non-null NULL NULL UNSIGNED unsigned DEFAULT does not apply to text type PRIMARY Key Primary Key Unique key Auto_increment: Fields with autogrow type must be primary or unique

Domain constraints: Data type constraints

FOREIGN KEY constraints: referential integrity constraints

PRIMARY KEY constraint: A field uniquely identifies the entity to which this field belongs, and is not allowed to be empty, only one primary key in a table.

Uniqueness constraint: A field in each row does not allow the same value, and you can have more than one table in a blank

CHECK constraint: User defined valid range of values.

1.3. mysql Introduction

MySQL is a small relational database management system, compared to other large database management systems (Oracle, DB2), MySQL is small, limited in functionality, but it is small, fast, low cost, and it provides the functionality for a slightly more complex applications already enough.

The main operating flow of MySQL is as follows:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/9B/97/wKiom1lkvSTAtzJVAADumAkQwFc034.png-wh_500x0-wm_ 3-wmp_4-s_1218971200.png "title=" image 2.png "alt=" Wkiom1lkvstatzjvaadumakqwfc034.png-wh_50 "/>

The MySQL database management system provides a number of command-line tools that can be used to manage MySQL servers, access control of databases, manage MySQL users, and database backup and recovery tools.

Server-side Tool program:

Mysqld:sql Daemon. After the program runs, the client can connect to the server to access the database. Mysqld_safe: Server startup script. Mysqld_multi: Server startup script to start or stop multiple servers installed on the system. Myisamchk: A utility used to describe, inspect, optimize, and maintain MyISAM tables. Mysqlbug:mysql Bug report script. It can be used to send bug reports to the MySQL messaging system. MYSQL_INSTALL_DB: The script creates a MySQL authorization table with default permissions. This is usually done only once when the system first installs MySQL.

Client tool Program:

Myisampack: A tool that compresses myisam tables to produce smaller read-only tables MySQL: command-line tools that interactively input SQL statements or execute them in batch mode from a file Mysqlaccess: A script that examines permissions to access host names, user names, and database combinations Mysqladmin: A client program that performs administrative operations, such as creating or deleting databases, overloading authorization tables, reopening log files, and so on, and can also be used to check version, process, and server status information Mysqlbinlog: tool that reads statements from binary logs, binary log files contain executed statements that can be used to help the system recover from a crash Mysqlcheck: checking, repairing, analyzing, and optimizing tables mysqldump: Dumps MySQL database to a file Mysqlhotcopy: A tool to quickly back up the MyISAM table when the server is running Mysqlimport: Use load DATA infile to import a text file into the relevant table's library program Mysqlshow: Client program that displays information about databases, tables, columns, and indexes Perror: A tool that displays the meaning of the system or MySQL error code mysqladmin [Options] command [ARG] [command [ARG]] ... [Options]: All access options are available Command Create db_name database drop db_name Delete database Debug: Open debug Log and log in error log; Status: Displays brief status information (--sleep #: Interval duration--count #: Batches displayed) Extended-status: Output mysqld state variables and their values, equivalent to performing mysql> SHOW GLOBAL status Variables: Output mysqld for each server variable Flush-hosts: Empty host-related cache: DNS resolution cache, previously denied access to mysqld host list due to excessive number of connection errors Flush-logs: Log scrolling, binary logs, and trunk logs Refresh: Equivalent to simultaneous use of flush-logs and flush-hosts Flush-privileges, Reload: Refresh Authorization Form Flush-status: Resetting the value of a state variable Flush-tables: Closes the currently open table file handle Flush-threads: Emptying the thread cache Password: Modify the current user's password; Ping: Test whether the server is online Processlist: Displays the list of MySQL threads Shutdown: Closing the mysqld process; Start-slave/stop-slave: Start/close from server thread Kill: Kills the specified thread, can kill multiple at a time, separated by commas, cannot have extra spaces

1.4, Source installation MySQL5.5

Install CMake software with dependent packages:

[email protected] home]# Yum install-y cmake ncurses-devel

Create users and Groups

[[email protected] home]# groupadd mysql[[email protected] home]# useradd mysql-s/sbin/nologin-m-G MySQL

Unzip compile MySQL

[[Email protected] home] #wget  [[email protected] home] #tar  xf  Mysql-5.5.56.tar.gz[[email protected] home] #cd  mysql-5.5.56[[email protected] home]# cmake . -dcmake_install_prefix=/application/mysql-5.5.56 -dmysql_datadir=/application/ Mysql-5.5.56/data -dmysql_unix_addr=/application/mysql-5.5.56/tmp/mysql.sock -ddefault_charset=utf8  -ddefault_collation=utf8_general_ci -dextra_charsets=gbk,gb2312,utf8,ascii -denabled_local_ Infile=on -dwith_innobase_storage_engine=1 -dwith_federated_storage_engine=1 -dwith_blackhole_ Storage_engine=1 -dwithout_example_storage_engine=1 -dwithout_partition_storage_engine=1 -dwith _fast_mutexes=1 -dwith_zlib=bundled -denabled_local_infile=1 -dwith_readline=1 -dwith_ Embedded_server=1 -dwith_debug=0[[email protected] home] #make  [[email protected]  home] #make  install[[email proteCted] application]# cd /application/[[email protected] application]# ln -s  /application/mysql-5.5.56/ /application/mysql[[email protected] application]# mkdir  /application/mysql/data -p[[email protected] application]# cd mysql[[email  protected] mysql] #chown   -R root:mysql  /application/mysql[[email  Protected] mysql] #mdkir  -p /application/mysql-5.5.56/tmp   [[email  Protected] mysql] #chmod  -r 777 /application/mysql-5.5.56/tmp

Select profile and Environment variables

[[email protected] MySQL] #cp support-files/my-large.cnf/etc/my.cnf [[email protected] MySQL] #vim/etc/profil e.d/mysql.sh Export Path=/application/mysql/bin: $PATH

Initializing the database

[[email protected] mysql]# ./scripts/mysql_install_db  --user=mysql --data=/ Application/mysql/data/installing mysql system tables ... 170711 22:09:40 [warning]  ' thread_concurrency '  is deprecated and will  be removed in a future release.170711 22:09:40 [note] ignoring -- secure-file-priv value as server is running with --bootstrap.170711  22:09:40 [note] ./bin/mysqld  (Mysqld 5.5.56-log)  starting as process  28738&NBSP, ..... Okfilling help tables ... 170711 22:09:41 [warning]  ' thread_concurrency '  is deprecated and will  be removed in a future release.170711 22:09:41 [note] ignoring -- secure-file-priv value as server is running with --bootstrap.170711  22:09:41&nBsp [note] ./bin/mysqld  (Mysqld 5.5.56-log)  starting as process 28745  okto start mysqld at boot time you have to copysupport-files/ Mysql.server to the right place for your systemplease remember to  set a password for the mysql root user ! to do so, start the server, then issue the following  commands:./bin/mysqladmin -u root password  ' New-password ' ./bin/mysqladmin -u  root -h mylinux password  ' New-password ' alternatively you can run:./bin/ mysql_secure_installationwhich will also give you the option of  Removing the testdatabases and anonymous user created by default.   this isstrongly recommended for&nBsp;production servers. See the manual for more instructions. You can start the mysql daemon with:cd . ; ./bin/mysqld_safe  &you can test the mysql daemon with mysql-test-run.plcd ./ Mysql-test ; perl mysql-test-run.plplease report any problems at http ://bugs.mysql.com/

Adding startup scripts and setting boot up

[[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld[[email protected] mysql]# chmod +x/etc/init . d/mysqld [[email protected] mysql]# chkconfig--add mysqld[[email protected] mysql]# chkconfig--level 2345 mysqld on[[em AIL protected] mysql]# service mysqld start starting MySQL. success!

MySQL configuration file:

/ETC/MY.CNF or/etc/mysql/my.cnf--> $MYSQL _base/my.cnf-->--defaults-extra-file=my.cnf

-->~/. my.cnf

How to use the configuration file

1, it finds each need to find the file, the result is all files and set;

2, if a parameter in multiple files appear multiple times, after reading the final effect;

1.5. mysql Variables and scopes

Server variable: @@ 变量 name

Display: SELECT @@ 变量 Name

Setting: Set global| SESSION Variable name = ' value '

Set the value of the server variable: (only for dynamic variables)

Server variables supported for modification:

Dynamic variable: Can be modified by MySQL runtime, can be modified instantly

Static variable: Modify its value in the configuration file and restart it to take effect;

Dynamic adjustment of the parameters of the effective way:

Global variables: server level, only valid for newly established sessions after modification;

Session variables: Session level, valid only for the current session, and when the session is established, inherit variables from the global;

To view server variables:

Mysql> SHOW [{global| SESSION}] VARIABLES [like ' '];mysql> SELECT @@{global| SESSION}. Varilable_name;mysql> SELECT * from INFORMATION_SCHEMA. Global_variables WHERE variable_name= ' NAME ';mysql> SELECT * from INFORMATION_SCHEMA. Session_variables WHERE variable_name= ' NAME ';

Modify variables: Default only Administrators have permission to modify global variables

Mysql> SET {global| SESSION} variable_name= ' VALUE ';

Note: Both global and session-level dynamic variable modifications are invalidated after restarting Mysqld, and to be permanently valid, defined in the corresponding segment in the configuration file [Mysqld].

1.6, MySQL Chinese data garbled solution

1), set the client's character set to match the table before inserting data into the table

SELECT CREATE Table Tb_name \g View the character set of the table set names character set;

2), specify the character set in the SQL file and log in to MySQL import data

SOURCE Tb_namq.sql;

3), specify character set in SQL file, import data via MySQL command

Mysql-uroot-p ' Password ' tb_name<tb_name.sql

4), specify the MySQL command to set the CharSet

Mysql-uroot-p ' password '--default-character-set= character set Tb_name<tb_name.sql

5), configure the client and server-side character set in the profile my.cnf

6), enter MySQL, you can see whether the configuration character set is consistent

SHOW VARIABLES like ' character_set% ';

This article is from the "Wind and Drift" blog, please be sure to keep this source http://yinsuifeng.blog.51cto.com/10173491/1946762

First, MySQL Database Foundation