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

MySQL AB-- > MySQL

Solaris: binary version

Www.mysql.com

MariaDB: www.mariadb.org

Features of MariaDB:

Plug-in storage engine: there are many implementation versions of the storage manager, and their functions and features may be slightly different. Users can choose flexibly according to their needs.

Storage engine is also known as "table type"

(1) more storage engines

MyISAM: transactions are not supported; (CentOS 6-- > 5.1.73 default storage engine is MylSAM Red Hat adjusted to inndDB)

MyISAM-- > Aria

InnoDB-- > XtraDB supports things

Support things after 5.5

Support transactions

(2) many extensions and new features

(3) more test components are provided.

(4) truly open source; is really open source

The distribution mechanism of MySQL:

Enterprise: enterprise Edition

Commutiny: community Edition

Install and use MariaDB:

(1) rpm package

(a) provided by the issuer of OS

(B) official provision of procedures

(2) Source code packet

(3) packages in universal binary format; (just like green software on win)

Note for installation of universal binary format:

1. Pay attention to the version. Some versions require glibc version.

two。 Deployment location must be / usr/local (tar xf MARIADB_NAME-C / usr/local)

3. Requires that the file name must be mysql (soft connection is generally chosen instead of naming the folder ln-sv MARIADB_NAME mysql directly)

4. When moving files, be sure to belong to the main array, which defaults to the mysql of the system user, (chown-R root:mysql. / *)

5. Create a data storage directory (the database may be very large in the future. Independent partitioning is recommended for non-testing. Pay attention to the permissions to create a directory)

6, the / etc/my.cnf file (main configuration file) is read by default, even if it is binary installed

7. When you need to set the configuration file independently, there is a profile template under the support-files under the installation package (all the files at the end of cnf are applicable to different hardware configurations)

8. Copy the template file to / etc/ or self-built directory / etc/mysql/my.cnf and rename it

9.mysql can be multithreaded, while for linux, a process uses at most 2.7 gigabytes of memory, which is useless

10. Specify the mysql database data location in the file to be configured (add datadir = / SOME/TO)

11.skip_name_resolve = ON

12 innodb_file_per_table = ON

13. Copy the mysql.server from the installation package support-files file to / etc/rc.d/init.d/mysqld (confirm the execution permission to execute chkconfig-- add mysqld)

14. Initialize the database. Execute scripts/mysql_install_db-- user=mysql-datadir=/mydata/data in the path where the database is located (/ usr/local/mysql)

Install MariaDB in universal binary format:

(1) prepare the data directory

Take the / mydata/data directory as an example

(2) install and configure mariadb

# useradd-r mysql

# tar xf mariadb-VERSION.tar.xz-C / usr/local

# cd / usr/local

# ln-sv mariadb-VERSION mysql

# cd / usr/local/mysql

# chown-R root:mysql. / *

# scripts/mysql_install_db-- user=mysql-datadir=/mydata/data

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

# chkconfig-add mysqld

(3) provide configuration file

A configuration file in ini format through which all programs can obtain configuration information

[program_name]

Query the effective order of configuration files: ~] # mysql-- help

# cp support-files/my-large.cnf / etc/my.cnf

Add three options:

Datadir = / mydata/data

Innodb_file_per_table = ON

Skip_name_resolve = ON

(4) start the service

# service mysqld start

The composition of MariaDB program:

C:Client

Mysql:CLI interactive client program

Mysqldump: backup tool

Mysqladmin: management tool

Mysqlbinlog: view the MySQL binary log

...

S:Server

Mysqld

Mysqld_safe: it is recommended to run server programs

Mysqld_multi: multiple instances

Three types of socket addresses:

IPv4, 3306/tcp

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

C S: localhost, 127.0.0.1

Command line interactive client program: mysql

Mysql

Mysql [OPTIONS] [database]

Common options:

-uUSERNAME: user name. Default is root.

-hHOST: the address of the remote host (i.e. mysql server). Default is localhost.

-p [password]: the password of the user represented by USERNAME; default is empty

Note: the user account of mysql consists of two parts: 'USERNAME'@'HOST';, through which HOST is used to restrict which remote hosts this user can connect to the current mysql service

The representation of HOST, which supports the use of wildcards

%: matches any character of any length

172.16%.%,-- > 172.16.0.0 Universe 16

_: match any single character

-Ddb_name: after connecting to the server, set the database specified there as the default database

-e 'SQL COMMAND;': connects to the server and asks it to execute this command and return directly

Command:

Client commands: local execution

Mysql > help

\ u db_name: set which library is the default database # backslash

\ Q: exit

\ d CHAR: set a new statement Terminator

\ g: statement end tag

\ G: statement closing tag, the result is explicit in vertical mode

\ s: view database status

Server command: send it to the server through mysql connection and retrieve the result

DDL, DML, DCL

Note: each statement must have a statement Terminator, which defaults to the semicolon (;)

* * *

Data type:

Tables: rows and columns A table can have no rows but can not be called a table without columns.

Creating tables: defining fields in a table

When defining a field, the key step is to determine its data type

Used to determine: data storage format, types of operations that can participate in operations, and valid data ranges that can be represented

Character types: character set

Code table: establishing a mapping relationship between characters and binary numbers

Species:

Character type:

Fixed length character type:

CHAR (#): character case insensitive

BINARY (#): case sensitive

Variable length character type:

VARCHAR (#)

VARBINARY (#)

Object Storage:

TEXT text 2 ^ 32 square text 4G indistinguishable

BLOB is case sensitive

Built-in type:

SET collection (only the combination of abc can be entered for a given abc)

ENUM enumeration (inferior to men and women. The week. Kind or something)

Numerical type:

Exact numerical type:

INT (TINYINT (one byte 0255), SMALLINT (small integer 2 bytes), MEDIUMINT (3 bytes), INT (4 bytes), BIGINT (8))

Approximate numerical type:

FLOAT single precision floating point

DOBULE double precision floating point

Date and time type:

Date type: DATE

Time type: TIME

Date and time type: DATETIME

Timestamp: TIMESTAMP

Year: YEAR (2), YEAR (4)

Data types have modifiers:

UNSIGNED: unsigned

NOT NULL: non-empty

DEFAULT value: default

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.