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

Detailed explanation of mysql Database Foundation and client commands under linux

1.mysql database storage engine:

SHOW ENGINES; # View the storage engines supported by mysql

There are two common storage engines:

MyISAM: three files per table:

.frm: table structure

.MYD: table data

.MYI: table index

InnoDB: all tables share one tablespace file by default

Recommendation: a separate tablespace file for each table; this feature is not turned on by default

.frm: table structure

.ibd: tablespace, containing table data and table indexes

.opt: character set and character collation

Open InnoDB to create independent tablespace files per table:

Vim / etc/my.cnf # adds the following line

Innodb_file_per_table = 1

The method of viewing InnoDB server variables by SHOW VARIABLES LIKE 'innodb%';

2.mysql client connection server-side tools

Options:

-- user,-u # means to specify a login user

-- host,-h # means to specify a mysql server

-- password,-p # means user password

-- port # means to specify the mysql server connection port

-- protocol # connects using the specified protocol (e.g. Tcp,Socket,pipe,memory)

-- database DATABASE,-D # specifies the default database connected to the msyql server

For example: SELECT DATABASE (); # View the current (default) database

Two working modes of 3.mysql: interactive mode and batch mode (script mode)

For example: mysql

There are client side commands and server side commands.

Client command: server statement has statement Terminator, default semicolon

Mysql >

\?: client commands to get help

\ c: terminate statement execution in advance

\ d: define statement Terminator

\ g: no matter what the statement Terminator is, send this statement directly to the server for execution

\ G: the function is similar to\ g, but the results are displayed vertically

\! Commands: executing shell command

\ W: a warning message is displayed after the execution of the statement

\ #: name completion is supported for newly created objects

\ Q: exit the mysql database

\. Execute mysql script file

\ s: display mysql database status information

The 5.mysql > command or statement is not fully typed. There are the following situations:

-> wait for the next statement

Wait for the next single quotation mark

Wait for the next double quotation mark

`> wait for the next backquote

/ * > wait for the next * /

6. Server-side commands for help: help keyword

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

Command subcommand:

Create DATABASE creates a database

Drop DATABASE deletes the database

Ping to check the connection status of the database

Processlist views the list of current processes in the database

Status to view database current status statistics

-- sleep N: display frequency

-- count N: displays multiple statu

Extended-status: display status variabl

Variables: display server variabl

Flush-privileges: let mysqld reread the authorization table, which is equivalent to reload

Flush-status: reset most server state variables

Flush-logs: binary and relay log scrolling

Flush-hosts: clear dns cache and connection error messages

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

Shutdown: shuts down the mysql server process

Password: change the password for the specified user to connect to the database

Version: server version and current status information

Start-slave: start replication, start the replication thread from the server

Stop-slave: turning off replication

For example: mysqladmin-uroot-p password 'NEW_PASS' # set password

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.