Shell script operation MySQL database, using the MySQL-e parameter can perform various types of SQL (create, delete, add, delete, change, check) and other operations

MYSQL-HHOSTNAME-PPORT-UUSERNAME-PPASSWORD-E mysql-related SQL statements without running MySQL at the prompt of MySQL, which means you can manipulate MySQL in the shell.

#!/bin/bash

Hostname= "192.168.111.84" #数据库信息
Port= "3306"
Username= "Root"
Password= ""

Dbname= "Test_db_test" #数据库名称
Tablename= "Test_table_test" #数据库中表的名称

#创建数据库
Create_db_sql= "CREATE database IF not EXISTS ${dbname}"
Mysql-h${hostname}-p${port}-u${username}-p${password}-E "${create_db_sql}"

#创建表
Create_table_sql= "CREATE table IF not EXISTS ${tablename} (name varchar (), id int (one) default 0)"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${create_table_sql}"

#插入数据
Insert_sql= "INSERT INTO ${tablename} values (' Billchen ', 2)"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${insert_sql}"

#查询
Select_sql= "SELECT * FROM ${tablename}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${select_sql}"

#更新数据
update_sql= "Update ${tablename} set id=3"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${update_sql}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${select_sql}"

#删除数据
Delete_sql= "Delete from ${tablename}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${delete_sql}"
Mysql-h${hostname}-p${port}-u${username}-p${password} ${dbname}-E "${select_sql}"

Show Processlist How to filter the problem I finally know how to fix it.

MYSQL-UROOT-E-p password ' show processlist\g ';

Mysql-uroot-e ' show processlist\g ' |grep ' Info ' |grep-v "NULL" |awk-f ":" ' {print $ {} ' |sort|uniq-c|sort-rn; (see what the statement is executing Some, and do a good merge sort:)

Shell script operation MySQL database, using the MySQL-e parameter can perform various types of SQL (create, delete, add, delete, change, check) and other operations