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}"