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

Introduction to the use of MySQL's own tools:

1) mysql commands: mysql commands are the most frequently used command tools, providing users with a command line interface to operate the server that manages MySQL.

Command format:

Usage:mysql [OPTIONS] [database]

Example: mysql-e "select user,host from user" mysql

Mysql-help can get the corresponding basic usage help information.

-e:-- execut=name: we are going to execute the commands after-e, but we do not enter the mysql interface through the mysql connection. This parameter is very useful in writing some basic mysql checking and monitoring scripts.

-E:-- vertical: all query results after login will be displayed in a column

-H,-- html,-X,--xml,: when these two parameters are enabled, all the results from select will be output in Html and Xml format

-- prompt=name: it is a very important parameter for operators, and its main function is to customize the display of their own mysql prompt. Personal strong recommendation:\\ u @\\ h:\\ d\\ r:\\ m:\\ s > br/ > personal strong recommendation:\\ u @\\ h:\\ d\\ r:\\ m:\\ s >

\ h: indicates the hostname

\ d: represents the current database

\ r: hours (12 hours)

\ m: minutes

\ s: seconds

-- tee=name: used to tell mysql to record all inputs and outputs to a file. When we make major maintenance changes, in order to facilitate the check, it is best to record all the inputs and outputs so that they can be easily checked.

You can also directly add to [client] in my.cnf.

Or execute tee / path at the mysql prompt

2) mysqladmin: all the functions provided are management functions related to MySQL

Usage: mysqladmin[OPTIONS] command command

Ping: the ping command can easily detect whether MySQL server can still provide services normally.

Example: mysqladmin-utest-h292.168.1.1-p ping

Note: 1. The address 192.168.1.1 is the ip of MySQL server (refers to the ip on this or other physical machine).

2. MySQL server's firewall should allow 3306/tcp communication.

3. Test must be an authorized user on MySQL sever

Status: you can get several basic status values of the current MySQL server

The results of the mysqladmin status command are:

Uptime: is the number of seconds the MySQL server is running

Threads: the number of active threads is the number of sessions opened

Questions: customer's problem (number of queries) since the server was started (as long as you interact with mysql, no matter querying the table, or querying the server status)

Slow queries: the number of slow queries

Opens: the number of database tables opened

The number of flush tables,refresh and reload commands that Flush tables:mysql has executed

Note: fflush table: refresh the table (clear cache)

Reload: overload authorization table

Refresh: wash out all tables and close and open log files

Open: the number of tables that open the database, starting with server startup

Average query time for Querish per second avg:select statements

Menory in use: assigned content (available only when MySQL is compiled with-- with-debug)

MAX memory used: maximum memory allocated (available only when MySQL is compiled with-- with-debug)

Processlist: gets the connection thread information of the current database

Monitor the running status of mysql processes

3) mysqldump: the function of this tool is to dump the data in MySQL server into text files from the database in the form of SQL statements (a backup tool, a large amount of data is not recommended, because the recovery is too slow) 4) mysqlbinlog: mainly to analyze the binary files generated by MySQL server. Additional knowledge points: 1) INFORMATION_SCHEMA data dictionary The secondary database stores information about all other databases (metadata) metadata is data about data, such as database name or table name, the data type of the column or access rights, and so on. Main system tables in the INFORMATION_SCHEMA library TABLES table: provides information about the tables and views in the database (the table_schame field represents the database name to which the data table belongs) example: select * from information--schema.tables wehere table_schema=' database 'COLUMNS table: provides information about the columns in the table, detailing all the columns of a table and the information for each column. Select * from information_schema.columns where table_schema=' database name 'and table_name=' table name' TABLE_ connections table: stores primary key constraints, foreign key constraints, unique constraints, check constraints, and description information for each field. Select * from information_schema.table_constraints where table_schema=' database name 'and table_name=' indicates' STAISTICS table: provides information about table indexes select * from information_schema.staistics wehere table_schema=' database name 'and table_name=' table name' 2) performance_schema performance dictionary This database provides important reference information for database performance optimization. 3) MySQL database: this database is also a core database, which stores user permission information and help information. 4) MySQL5.7 provides the sys system database, which contains a series of stored procedures, custom functions and views to help us quickly understand the metadata information of the system. The database of sys system combines the relevant data of information_schema and performance_schema, which makes it easier for us to retrieve metadata.

Mysqlslap performance Test MySQL Storage engine

Mysqlslap is a benchmark tool that comes with MySQL.

Advantages: query data, simple syntax, flexible and easy to use, this tool can simulate multiple clients to simultaneously send query updates to the server, give performance test data, and provide a variety of engine performance comparison, mysqlslap provides intuitive verification evidence for mysql performance before and after optimization.

Explanation of common options:-- concurrency (- c) represents the number of concurrency, multiple of which can be separated by commas. -- engins represents the engine to be tested, separated by a semicolon-- iterations (- I) represents how many times to run these tests, that is, how many times to run the results-- auto-generata-sql represents the SQL script test generated by the system itself-- auto-generate-sql-load-type: represents whether to test read, write or mixed mode (read,write,updata) Mixed)-- number-of-queries represents the total number of queries to run. -- debug-info: represents the additional output of CPU and memory related information (Note: only when MySQL is compiled with-- with-debug)-- number-int-cols: there are several properties representing the integer type in the test table-- number-char-cols: represents the number of char type fields in the test table-- create-schema represents the schema that you define (in MySQL That is, the library is the database to create the test)-query represents its own SQL script-- only-print if you just want to print to see what the SQL statement is You can use this option-- csv=name to produce CSV format data files

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.