sh mysql login_shell中执行mysql的sql

0.管道函数和echo

echo "show variables where Variable_name='max_connections';" | mysql -pmysql -N

1.左位移两位

mysql -uroot -poracle -e "select * from test.t1;"

vi dml.sh

#!/bin/bash

mysql -uroot -poracle <

insert into test.t1 select 5,'v5';

exit

EOF

2.管道函数&bash函数

#my3306为快速登陆路径的凭证

mysql_config_editor set --login-path=my3306 --user=root --host=127.0.0.1 --port=3306 --password

vi tbs_sel.sh

# 表空间使用率检查函数

function f_check_tbs(){

echo "SELECT a.schema_name db_name,

CONCAT(IFNULL(ROUND((SUM(b.data_length) + SUM(b.index_length)) / 1024 / 1024,

0),

0),

'M') total,

CONCAT(IFNULL(ROUND(((SUM(b.data_length) + SUM(b.index_length)) -

SUM(b.data_free)) / 1024 / 1024,

0),

0),

'M') used,

CONCAT(IFNULL(ROUND(SUM(data_free) / 1024 / 1024, 0), 0), 'M') free,

CONCAT(IFNULL(ROUND((((SUM(b.data_length) + SUM(b.index_length)) -

SUM(b.data_free)) /

((SUM(b.data_length) + SUM(b.index_length))) * 100),

0),

0),

'%') ratio,

COUNT(table_name) TABLES

FROM information_schema.schemata a

LEFT JOIN information_schema. TABLES b

ON a.schema_name = b.table_schema

WHERE a.schema_name NOT IN

('information_schema', 'mysql', 'performance_schema')

GROUP BY a.schema_name

ORDER BY 1;"

}

echo "08、数据库表空间使用率"

f_check_tbs|mysql --login-path=my3306

3.mysql -e详解

可以用shell脚本操作mysql数据库，使用mysql的-e参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作 。

用法

mysql -hhostname -Pport -uusername -ppassword -e 相关mysql的sql语句，不用在mysql的提示符下运行mysql，即可以在shell中操作mysql的方法。

vi dml.sh

#!/bin/bash

HOSTNAME="127.0.0.1"

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(20), id int(11) 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}"