MySQL comes with tool use introduction

MySQL comes with tool use introduction:
1) MySQL command: MySQL command is the most used command tool, providing users with a command line interface to operate the server to manage 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 command after-e, but do not enter MySQL interface via MySQL connection. This parameter is useful when we write some basic MySQL inspection and monitoring scripts.
-E:--vertical: All query results after login will be displayed in a vertical column
-H,--html,-x,--xml,: After enabling these two parameters, all the results of select will be output in HTML, XML format
--prompt=name: It is a very important parameter for OPS people, whose main function is to customize the display of their own MySQL prompt. Br/> personally strongly recommends: \ \[email protected]\\h:\\d\\r:\\m:\\s>
\h: Indicates host name
\d: Represents the current database
\ r: Hours (12-hour system)
\m: Minutes
\s: Sec
--tee=name: Used to tell MySQL to log all input and output contents to a file. When we make a large maintenance change, in order to facilitate the investigation, it is best to record all the input and output content, convenient to be checked.
can also be added directly to the MY.CNF [client]
Or execute tee/path below the MySQL prompt
2) Mysqladmin: The functions provided are mysql-related management functions
Usage:mysqladmin[options]command command
The ping:ping command makes it easy to detect if MySQL server is still serving properly
Example: Mysqladmin-utest-h192.168.1.1-p ping
Note: 1, address 192.168.1.1 is the IP of MySQL server (refers to the IP on native or other physical machine)
2. MySQL server firewall to allow 3306/TCP communication
3. Test must be an authorized user on MySQL Sever
Status: You can get several basic status values for 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 Problems since server startup (number of queries) (as long as you interact with MySQL, regardless of the query table, or the query server status is recorded once)
Slow queries: Is the number of slow queries
Opens: Number of database tables that have been opened
Flush Tables:mysql The number of flush Tables,refresh and reload commands that have been executed
Note: Fflush table: Refresh tables (Clear Cache)
Reload: Overloaded Authorization table
Refresh: Erase all tables and close and open log files
Open: Opens the number of tables for the database, starting with server startup
Querish per second Avg:select statement average time to query
Menory in use: Allocated content (only available when MySQL is compiled with--with-debug)
Max memory used: Allocated maximum RAM (only available when MySQL is compiled with--with-debug)
Processlist: Gets the connection thread information for the current database
Monitor MySQL process running status

3) Mysqldump: The function of this tool is to dump the data in MySQL server into a text file 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 analysis of MySQL Binary files generated by server additional knowledge Points: 1) information_schema data dictionary, the secondary database stores information about all other databases (metadata) metadata is data about the data, such as database name or table name, column        Data type or access rights, and so on. Main system tables in the INFORMATION_SCHEMA Library Tables table: Provides information about tables and views in the database (the Table_schame field represents the database name that the data table belongs to) Example: SELECT * FR                Om information--schema.tables wehere table_schema= ' database ' columns table: Provides the column information 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_constraints table: Store Master                Key constraints, FOREIGN KEY constraints, UNIQUE constraints, check constraints, and descriptive 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 ' per    Formance_schema Performance Dictionary, this database provides important reference information for database performance optimization 3) MySQL database: This database is also a core database, storing user's 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 system's meta-data information. The SYS system database combines INFORMATION_SCHEMA and performance_schema data to make it easier to retrieve metadata.

Mysqlslap performance test MySQL's storage engine
Mysqlslap is a benchmark tool that comes with MySQL,
Advantages: Query data, simple syntax, flexible and easy to use, the tool can simulate multiple clients simultaneously to the server to issue query updates, give performance test data, and provide a variety of engine performance comparison, Mysqlslap for MySQL performance optimization before and after providing visual proof of validation.

    常用选项的解释：        --concurrency （-c）  代表并发数量，多个可以用逗号隔开。        --engins  代表要测试的引擎，可以有多个，用分号隔开        --iterations （-i） 代表要运行这些测试多少次，即运行多少次后，得到结果        --auto-generata-sql  代表系统自己生成的SQL脚本来测试        --auto-generate-sql-load-type：代表要测试的是读是写还是混合模式（read，write，updata，mixed）        --number-of-queries  代表总共要运行多少次查询。        --debug-info：代表要额外输出CPU以及内存的相关信息（注：只有在MySQL用--with-debug编译时即可）        --number-int-cols  ：代表测试表中的integer类型的属性有几个        --number-char-cols  ：代表测试表的char类型字段数量        --create-schema  代表自己定义的模式（在MySQL中也就是库即创建测试的数据库）        -query  代表自己SQL脚本        --only-print  如果只想打印看看SQL语句是什么，可以用这个选项        --csv=name 生产CSV格式数据文件

Example: Test with a SQL script or statement of our own definition
Prepare the database tables you want to test first

#！ /bin/bash
Hostname= "localhost"
Port= "3306"
Username= "Root"
Password= "123456"
Dbname= "Test1"
Tablename= "TB1"
#create Database
Mysql-h ${hostname}-P ${port}-u ${username}-p${password}-E "drop database if exists ${dbname}"
Create_db_sql= "CREATE database if not exists ${dbname}"
Mysql-h ${hostname}-P ${port}-p${password}-u ${username}-E "${create_db_sql}"
#create table
Create_table_sql= "CREATE table if not exists ${tablename} (Stuid int. NOT NULL primary key,stuname varchar () not null,stus Ex char (1) Not Null,cardid Carchar (a) not null,birthday datetime,entertime datetime,address varchar (+) default null) "
Mysql-h ${hostname}-p{port}-p${password}-u ${username} ${dbname}-E "${create_table_sql}"
#insert Data to Table
I=1
While [$i-le 20000]
Do
Insert_sql= "INSERT INTO ${tablename} values ($i, ' Zhangsan ', ' 1 ', ' 1234567890 ', ' 1995-09-09 ', ' 2018-09-09 ', ' Zhongguo ', ' Beijing ', ' Didu ') "
Mysql-h ${hostname}-u ${username}-p {PORT}-p${password} ${dbname}-E "${insert_sql}"
Let i++
Done
#select data
Select_sql= "SELECT COUNT (*) from ${tablename}"
Mysql-h ${hostname}-p {PORT}-p${password}-u ${username} ${dbname}-E "${select_sql}"

The test table required to execute the script generation Mysqlslap tool

Execute the Mysqlslap tool for testing

Mysqlslap--defaults-file=/etc/my.cnf--concurrency=10,20--iterations=1--create-schema= ' test '--query= ' SELECT * From Test.tb1 '--engine=myisam,innodb--number-of-queries=2000-uroot-p123456-verbose

MySQL comes with tool use introduction