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

Install and use mariadb

Provided directly by centos 7

Centos 6

General binary installation process

1. Prepare the data directory

Take / mydata/data as an example

2. Configure mariadb

# groupadd-r-g 200 mysql

# useradd-r-g 200-u 200 mysql

# tar xf mariadb-VERSION-linux.tar.gz-C / usr/local

# ln-s mariadb-VERSION-linux mysql

# cd / usr/local/mysql

# chown root.mysql. / *

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

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

# chkconfig-add mysqld

3. Prepare the configuration file

Configuration format: ini-like format, and each program provides configuration information through a single configuration file:

[prog_name]

Profile lookup order:

/ etc/my.cnf-> / etc/mysql/my.cnf-- >-- default-extra-file=/PATH/TO/CONF_FILE-- > ~ / .my.cnf

# mkdir / etc/mysql

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

Add three configurations

Datadir = / mydata/data

Innodb_file_per_table = on

Skip_name_resolve = on

4. Start the service

Service mysqld start

5. Database security initialization

Mysql_secure_installation

6. The program composition of mariadb

C:

Mysql

-uUSERNAME

-hHOST

-pPASSWORD

Mysqldump,mysqladmin...

S:

Mysqld_safe

Mysqld

Mysqld_multi

7. Two kinds of socket addresses for service monitoring:

Ip socket: listens on port 3306 of tcp and supports remote communication

Unix sock: listening on the sock file (/ tmp/mysql.sock,/var/lib/mysql/mysql.sock), only local communication is supported

Server:localhost,127.0.0.1

8. Command:

Client commands: local execution

Mysql > help

Each command has an end form and an abbreviated form:

Status-- >\ s

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

Each command must have a command end symbol, which defaults to a semicolon

SQL statement:

DDL:Data Defination Language

Create,drop,alert

DML:Data Manipulation Language

Insert,delete,update,select

Grant,revoke

View the character sets that the data can support:

Mysql > show character set

View the collation:

Mysql > show collation

View supported engines:

Show engines

Create (delete) the database:

CREATE DATABASE [IF NOT EXISTS] 'DB_NAME'

DROP DATABASE [IF EXISTS] 'DB_NAME'

Mysql > CREATE DATABASE IF NOT EXISTS testdb

Create (delete) a table:

CREATE TABLE [IF NOT EXISTS] tb_name (col1 datatype modifier, col2 datatype modifier, col3 datatype modifier) ENGINE=''

DROP TABLE [IF EXISTS] tb_name

Mysql > CREATE TABLE tbl1 (id INT NOT NULL,name VARCHAR (100) NOT NULL,age TINYINT)

View the table:

Mysql > SHOW tb_name [from db_name]

View the table structure:

Mysql > DESC [db_name.] tb_name

Data type:

Character type:

Fixed length character type: CHAR (#), BINARY (#)

Variable length characters: VARCHAR (#), VARBINARY (#)

Numeric type:

Exact numerical type:

Integer: int

Tinyint:1byte

Smallint:2byte

Mediumint:3byte

Int:4byte

Bigint:8byte

Decimal: decimal

Approximate numerical type:

Single precision floating point type:

Float

Double precision floating point type:

Double

Modifier:

All types apply

NOT NULL: non-null constraint

DEFAULT VALUE: setting default values for

PRIMARY KEY

UNIQUE KEY

Numerical type is suitable for:

UNSIGNED unsigned type

AUTO_INCREMENT self-growing type

Mysql > CREATE DATABASE testdb

Mysql > USE testdb

Mysql > CREATE TABLE students1 (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR (20) NOT NULL, age tinyint UNSIGNED)

Mysql > DESC students

Mysql > CREATE TABLE students2 (id int UNSIGNED NOT NULL, name VARCHAR (20) NOT NULL, age tinyint UNSIGNED, PRIMARY KEY (id,name))

Mysql > DESC students2

Get help:

Mysql > HELP CREATE TABLE

View the commands when creating the table:

Mysql > SHOW CREATE TABLE students2

View table status:

Mysql > SHOW TABLE STATUS LIKE 'students2'\ G\ Gmure-> values are displayed vertically

ALTER TABLE tb_name

Field:

Add Field: ADD

ADD col1 datatype [FIRST | AFTER col_name]

Delete field: DROP

Modified field: ALTER,CHANGE,MODIFY

CHANGE id sid int UNSIGNED NOT NULL

Addition of fields:

Mysql > ALERT TABLE students1 ADD gender ENUM ('masking dint f') AFTER name

Mysql > ALERT TABLE students1 CHANGE id sid int UNSIGNED NOT NULL

Add an index:

Mysql > ALERT TABLE students1 ADD INDEX (age)

View the index:

Mysql > SHOW INDEXES FRON students1

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.