MySQL database base and client commands in Linux

MySQL database base and client commands in Linux

1.mysql Database Storage Engine:

SHOW ENGINES; #查看mysql支持的存储引擎

The following two storage engines are common:

MyISAM: Three files per table:

. frm: Table structure

. MYD: Table Data

. MYI: Table Index

InnoDB: Default All Tables share a tablespace file;

Recommendation: A separate tablespace file per table; This feature is not turned on by default

. frm: Table structure

. IBD: Table space, containing table data and table indexes

. OPT: Character set and character sorting rules

Open InnoDB Each table creates a separate Tablespace file feature approach:

vim/etc/my.cnf #新增如下一行

innodb_file_per_table = 1

SHOW VARIABLES like ' innodb% '; How to view InnoDB server variables

2.mysql Client Connection server-side tools

Options:

--user,-u #意思是指定登入用户

--host,-H #意思是指定mysql服务器

--password,-P #意思是用户密码

--port #意思是指定mysql服务器连接端口

--protocol #使用指定协议连接 (e.g. tcp,socket,pipe,memory)

--database database,-D #指定连入msyql服务器的默认数据库

For example: SELECT DATABASE (); #查看当前 (default) database

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

Example: MySQL < init.sql #此条命令为批处理模式

4.mysql> has client commands and server-side command points

Client command: The server statement has a statement terminator, the default semicolon;

Mysql>

\?: Client commands Get help

\c: Early termination of statement execution

\d: Defining a statement Terminator

\g: Regardless of the statement Terminator, the statement is sent directly to the server-side execution;

\g: Similar to the \g function, but the results are displayed in vertical form;

\! Command: Execute shell command

\w: A warning message is displayed after the statement execution is completed;

\#: For new objects, support the name completion function;

\q: Exiting the MySQL database

\.: Execute MySQL script file

\s: Displaying MySQL database status information

5.mysql> command or statement is not complete, there are several situations:

Wait for the next statement

' > Wait for the next single quotation mark

> Wait for the next double quote

' > Wait for the next anti-quote

/*> wait for the next */

6. Server-side command get help: keyword

mysqladmin [Options] command [ARG] [command [ARG]] ...

Command subcommand:

Create Database

Drop DATABASE Delete Databases

Ping to view the connection status of a database

Processlist View the list of current processes in the database

Status View database current status statistics

--sleep N: Display frequency

--count N: Show multiple states

Extended-status: Show state variables

Variables: Display server variables

Flush-privileges: Let mysqld reread authorization form, equivalent to reload;

Flush-status: Resetting most server state variables

Flush-logs: binary and Trunk log scrolling

Flush-hosts: Clear DNS cache and connection error messages

Refresh: Equivalent to simultaneous execution of flush-hosts and Flush-logs

Shutdown: Shutting down the MySQL server process

Password: Modify the password for the specified user connection database

Version: Server versions and current status information;

Start-slave: Initiates replication and initiates a copy of the thread from the server;

Stop-slave: Turn off replication;

For example: mysqladmin-uroot-p password ' new_pass ' #设定密码

This article is from the "Xavier Willow" blog, please be sure to keep this source http://willow.blog.51cto.com/6574604/1792514

MySQL database base and client commands in Linux