Linux Operations Phase IV (i) MYQL basic concepts and compilation and installation steps

Linux Operation Phase IV (i) MYQL Basic concepts and steps for compiling and installing

First, the relevant basic concepts:

1. file storage: Data redundancy and inconsistency, data access difficulty, data isolation, integrity problem, atomicity problem, concurrency access exception, security.

2, DBMS (Database Management System) function: Data management independence, data access, data integrity, security, data centralized management, concurrent storage, data recovery, reduce application development cycle.

3. RDBMS (relational DBMS): Creation, deletion, modification of database and table, creation and deletion of indexes, management of users and rights, deletion and modification of data, etc.

4. DML (Data manapulate language):INSERT into,UPDATE,DELETE,REPLACE , SELECT;

DDL (Definition):CREATE,ALTER,DROP;

DCL (Control):GRANT,REVOKE.

Note: The command is not case-sensitive, but it is better to develop good habits, command uppercase, convenient to view complex statements and so on.

5, Program: Instruction + data. The two-way search can be found in three times;BTREE index 4 times can be found.

6, The structure of the data: hierarchical type (with a subordinate relationship, coupling degree close); NET structure; relational structure (table).

7, Marco steamed Bread Theory: The system is provided to us is the seed; The library provides us with a face; through The SHELL we can have steamed bread.

8, The programmer or the developer through the drive ( translation software, programming interface more simple and easy to use than the API) to complete the query operation, the general user through the terminal login command line operation; thread pool: A large number of users flow into, Control the flow.

9, go to the IOE (IBM,ORACLE,EMC).

10. MYSQL(MY structurequery Language) package format: Binary package (Package Manager-specific RPM,EXE, MSI); Universal binary format (no installation, simple configuration after decompression); source package (compile and install).

11, MYSQL:c/S architecture,client-side ( software MYSQL),server-side ( software m Ysql-server).

12, after the installation is completed MySQL needs to initialize (initialize is to build MySQL itself database, the library name is MySQL, normal user login can not see this library).

13, server-side Login:

#mysql (log in directly using this method, the default is root, empty password, local login);

#mysql-U (--user) username-p (--password)-H (--host) My_Server (-u specified user,-p need to enter a password, if the empty password directly enter,- H specifies which host or IP login is specified .

14, metadata area (storage metadata such as INODE), data area (divided into disk block).

15. RDB objects: Libraries, tables, indexes, views, users, stored procedures, stored functions, triggers, event schedulers.

16, CONSTRAINT(constraint):

Domain constraints (constraints on data types);

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, a table can have only one primary key);

Uniqueness constraint: A field in each row is not allowed to appear the same value, can be empty, a table can have more than one;

Check for sex constraints

17. File: Presentation layer (to user), logical layer (file system-storage engine), physical layer (data block);

Relational data: Presentation layer (table), logical layer (storage engine), physical layer (data file).

18. Data Query and Storage: Query Manager (DML interpreter, DDL interpreter, query execution engine), Storage Manager (permissions and integrity manager, transaction manager, File Manager, Buffer Manager).

19, MYSQL Single-process multithreading (daemon thread, application thread), thread reuse (thread reuse);

20, MYSQL Advantage:speed/relibility(stability)/scalability(scalability)/ease ofuse/portability and standards Compliance/multiusersupport/internationalization/wide Applicationsupport (C/C++/Java/perl/php/python /ruby/jdbc/odbc/.net)/opensource.

21, MYSQL on the 32bit system, up to 2.7G memory , on the 64bit system, the SMP(symmetric multi-processor support poor); Individual statements run on only one CPU .

22, projection (only output specified properties, example:>select col1,col2 from Stl_name; );

Select (Output only qualifying lines, example:>select col1,col2 from stl_name WHERE col3= ' value '; );

Natural Connection: all attributes with the same name have the same value on the same line, for example:tbl1_name.col_name=tbl2_name.col_name;

Cartesian product: (a+b)* (c+d) =ac+bd+bc+bd;

And: Set operation.

23,                sql language components: ddl, dcl ddl< Span style= "font-family: ' The song Body '; ), vision definition, transaction control, embedded sql sql

24,         &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP, How to use the programming language with Rdbms (with dynamic sql similar, However, its statement must be fully determined at the time of Program compilation); dynamic Sql (programming language usage function ( mysql_connect () SQL the server sends a query statement and saves the result to a variable and then processes it. ）。

25, MYSQL plug-in storage engine:MyISAM(does not support transaction, table lock, but the performance is good for the data warehouse, modify less, query more scenes);InnoDB(supports transactions, row locks, works like oracle, applicable to online transaction processing).

26, The document organization: The heap file organization (a record can be placed anywhere in the file); Sequential file organization (sorted by search code (indexed field); Hash file organization (hash).

27. Data Dictionary(metadata of the relationship): Table name, field name, field type and length, view, constraint, user name, permission password.

28. Alpha(Beta version)--relasecandidate(Release Candidate)--"GA" (General Availabili Ty).

29. MySQL Startup Lookup Center Profile MY.CNF priority order:/etc/my.cnf-->/etc/mysql/my.cnf--> $MYSQL _home /MY.CNF-->--DEFAULT-EXTRA-FILE=/PATH/TO/FILE-->~/MY.CNF.

30, Table Manager (responsible for creating, reading, modifying the table structure definition file, maintaining table descriptor adjustment cache, management table lock);

Table Definition module: Table creation, deletion, renaming, removing, updating, inserting;

Table Maintenance Module: Inspection, modification, backup, recovery, optimization (defragmentation) and parsing.

Character Set: The correspondence between Chinese characters and coding, example:gbk/gb2312/utf-8/gb18030

/mydata/data/mysql,#ls ( view The files in the MySQL Data directory )

If the storage engine is MyISAM, the created table has three files (a. frm table Structure. MYD table data,. MYI table index);

If the storage engine is InnoDB, the created table has two files (. frm table structure,. ibd table space (including table structure and table index), and another db.opt(holds the default character set and collation)

InnoDB storage engine, all tables share a tablespace file, it is recommended to change to a separate tablespace file per table, method: Change configuration file my.cnf in [mysqld] Zone add a row innodb_file_per_table = 1, restart theservice after logging in to MySQL, view >show GLOBAL VARIABLES like '%innodb% ' ;

second, compile and install mysql5.5.23(redhat6.4) steps:

#hwclock -S (set the system timefrom the hardware clock, another -W(Set the hardware clock to the Curr ENT system time)

#mkdir-pv/mydata/data (preferably mount this directory to a separate LVM partition disk)

#groupadd-R MySQL

#useradd-R-G mysql-d/mydata/data MySQL

#id MySQL

#chown-R Mysql:mysql/mydata/data

#tar ZXVF cmake-3.3.0.tar.gz (cross-platform compiler, where cmake. (equivalent to./configure), CMake. –LH ( equivalent to./configure–help))

#cd cmake-3.3.0

#./bootstrap

#make && make Install

#which cmake (view cmake binary file path)

#tar ZXVF mysql-5.5.23.tar.gz

#cd/mysql-5.5.23

#cmkae. –LH | Less (view compilation options)

#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 (Ensure that all options are installed in the software and components, e.g.SSL corresponds to installing OpenSSL)

#make && make Install

#chown-R: Mysql/usr/local/mysql (change this directory name to MySQL)

#cd/usr/local/mysql

#scripts/mysql_install_db--user=mysql--datadir=/mydata/data (Initialize)

#cp support-files/my-large.cnf/etc/my.cnf (copy template configuration file)

#cp Support-files/mysql.server/etc/rc.d/init.d/mysqld

#chkconfig--add mysqld

#chkconfig--list mysqld

#service mysqld Start

#vim/etc/profile.d/mysql.sh

Export path= $PATH:/usr/local/mysql/bin

#source/etc/profile.d/mysql.sh

#echo $PATH

#vim/etc/my.cnf

[Mysqld] Add a row

DataDir =/mydata/data

#mysql (Use this method to log in directly)

>use MySQL;

>select User,host,password from User;

>update mysql.user SET password=password (' Redhat ') WHERE user= ' root '; ( change all local root accounts to Redhat, note:root (127.0.0.1) and root (localhost) are not the same account, Although the result of password change is local root. )

>drop USER ' @localhost;

>drop USER ' @localhost. Localdomain; (delete two anonymous accounts)

>show ENGINES; (view supported storage engines)

#mysql-uroot-p-h localhost (to make it easy to use #mysql in the future , you can create a new ~/.my.cnf in your home directoryand add the following statement:

[Client]

user = root

Password = Redhat

host = localhost

）

This article is mainly from the study of "Ma Brother Network Video" made notes.

This article is from the "Linux Operational Difficulty Learning notes" blog, please be sure to keep this source http://jowin.blog.51cto.com/10090021/1682167

Linux Operations Phase IV (i) MYQL basic concepts and compilation and installation steps