shell脚本连接、读写、操作mysql数据库实例

本文介绍了如何在shell中读写mysql数据库。主要介绍了如何在shell 中连接mysql数据库,如何在shell中创建数据库,创建表,插入csv文件,读取mysql数据库,导出mysql数据库为xml或html文件, 并分析了核心语句。本文介绍的方法适用于PostgreSQL ,相对mysql而言,shell 中读写PostgreSQL会更简单些。

1. 连接mysql 数据库

shell中连接数据库的方法很简单,只需要指定用户名,密码,连接的数据库名称,然后通过重定向,输入mysql的语句,如下所示:

代码如下:

mysql -u USERNAME -p PASSWORD DATABASENAME <<EOF 2>/dev/null
    show databases;
EOF

但这并不是一个好办法,任何使用该脚本的用户都能看到该数据库用户的账号和密码,要解决这个问题,可以用mysql 数据库的一个特殊配置文件。mysql 数据库使用$HOME/.my.cnf 文件来读取特殊的启动命令和设置。其中一项设置是由该用户账户发起的mysql 会话的默认密码。要在这个文件中设置默认密码,可以加入下面的内容:

代码如下:

[client]
password = 123456
然后,别忘了修改权限:
chmod 400  .my.cnf
这样就可以通过脚本访问mysql数据库了,如下所示:
#!/bin/bash
MYSQL=`which mysql`
$MYSQL test -u root << EOF
show databases;
show tables;
select * from employees where salary > 4000;
EOF

2. 创建数据库

通过上面的方法连接数据库,再通过重定向输入mysql语句,shell中读写mysql基本就介绍完了。只要把sql语句写对了,通过重定向执行即可,下面来看一个实例:

代码如下:

#!/bin/bash
##############################
# @file create_db_mysql.sh
# @brief create database and tables in mysql
# @author Mingxing LAI
# @version 0.1
# @date 2013-01-20
##############################
USER="root"
DATABASE="students"
TABLE="students"
######################
#crate database
mysql -u $USER << EOF 2>/dev/null
CREATE DATABASE $DATABASE
EOF
[ $? -eq 0 ] && echo "created DB" || echo DB already exists
######################
#create table
mysql -u $USER $DATABASE << EOF 2>/dev/null
CREATE TABLE $TABLE(
id int,
name varchar(100),
mark int,
dept varchar(4)
);
EOF
[ $? -eq 0 ] && echo "Created table students" || echo "Table students already exist"
######################
#delete data
mysql -u $USER $DATABASE << EOF 2>/dev/null
DELETE FROM $TABLE;
EOF

这个脚本比较简单,就是几条SQL语句,没什么好解释的,下面来看一下,如何读入csv 文件,然后插入到mysql数据库中。

3. 插入csv 文件

上面创建了一个学生表,表中有学生的学号,姓名,成绩,系别,假设有一个csv文件,内容如下:

代码如下:

$cat data
1,Navin M,98,CS
2,Kavya N,70,CS
3,Nawaz O,80,CS
4,Hari S,80,EC
5,Alex M,50,EC
6,Neenu J,70,EC
7,Bob A,30,EC
8,Anu M,90,AE
9,Sruthi,89,AE
10,Andrew,89,AE

为了将csv 文件插入到数据库,我们需要逐行读入,然后给字符串加上双引号,最后生成语句如下:

代码如下:

insert into students VALUES(1, "Navin M", 98, "CS");

要解析csv 文件,最好的工具莫过于awk了,将域的分隔符指定为逗号-F,,awk就自动将各个域拆分出来了,然后在需要双引号的地方打印输出一个双引号,就能够轻松得到下面这样的数据:

代码如下:

1, "Navin M", 98, "CS" awk 代码如下:
query=`echo $line | awk -F, '{ printf("%s,\"%s\",%s,\"%s\"", $1, $2, $3, $4)}'`
statement=`echo "INSERT INTO $TABLE VALUES($query);"`
echo $statement

当然了,你也可以用其他办法,不过,几乎没有比awk更简单的了,第2种方法如下:

代码如下:

oldIFS=$IFS
IFS=,
values=($line)

values[1]="\"`echo ${values[1]} | tr ' ' '#' `\""
values[3]="\"`echo ${values[3]}`\""

query=`echo ${values[@]} | tr ' #' ', '`
IFS=$oldIFS

statement=`echo "INSERT INTO $TABLE VALUES($query);"`
echo "$statement"

首先通过指定域分隔符,将csv文件解析成一个数组,然后将空格替换成一个特殊的符号"#"(因为后面的替换中,会一次性输出数组,而数组是用空格分隔各字段,我们要将分隔数组的空格替换成逗号,所以这里将数据中的空格替换成"#") ,给字符串加上双引号,最后再把空格替换成逗号,把"#"替换为空格。这种方法真是让人抓狂,我第一次就没有看明白,尤其是为什么要将空格替换成"#"。

完整的插入数据的程序如下:

代码如下:

#!/bin/bash
#
# @file write_to_db_mysql.sh
# @brief wirte data to database in mysql
# @author Mingxing LAI
# @version 0.1
# @date 2013-01-20
#

USER="root"
DATABASE="students"
TABLE="students"
if [ $# -ne 1 ]; then
    echo $0 DATAFILE
    echo
    exit 2
fi
data=$1
while  read line;
do
#   query=`echo $line | awk -F, '{ printf("%s,\"%s\",%s,\"%s\"", $1, $2, $3, $4)}'`
    oldIFS=$IFS
    IFS=,
    values=($line)
    values[1]="\"`echo ${values[1]} | tr ' ' '#' `\""
    values[3]="\"`echo ${values[3]}`\""
    query=`echo ${values[@]} | tr ' #' ', '`
    IFS=$oldIFS
    statement=`echo "INSERT INTO $TABLE VALUES($query);"`
#   echo $statement

mysql -u $USER $DATABASE << EOF
    INSERT INTO $TABLE VALUES($query);
EOF
done < $data
if [[ $? -eq 0 ]]; then
    echo "Wrote data into DB"
fi

4. 读取数据

知道怎么在shell 中连接mysql ,也知道了怎么在shell中批量执行sql 语句,读取数据,就没有任何难度了。

代码如下:

#!/bin/bash
#
# @file read_db_mysql.sh
# @brief read data from mysql
# @author Mingxing LAI
# @version 0.1
# @date 2013-01-20
#

USER="root"
DATABASE="students"
TABLE="students"

#用tail 去掉表头
depts=`mysql -u $USER $DATABASE <<EOF | tail -n +2
SELECT DISTINCT dept FROM $TABLE;
EOF`

for d in $depts; do
    echo Department: $d
    result="`mysql -u $USER $DATABASE << EOF
    set @i:=0;
    SELECT @i:=@i+1 as rank, name, mark FROM students WHERE dept="$d" ORDER BY mark DESC;
EOF`"

echo "$result"
echo
done

我们还可以在mysql语句中,使用选项来控制数据的输出格式

-H 输出为html
    -X 输出为xml

如下所示:

代码如下:

#!/bin/bash
USER="root"
DATABASE="students"
TABLE="students"

mysql -u $USER $DATABASE -H << EOF
select * from $TABLE
EOF

html 格式的可读性比较差,输出效果如下:

代码如下:

<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH><TH>mark</TH><TH>dept</TH></TR><TR><TD>1</TD><TD>Navin M</TD><TD>98</TD><TD>CS</TD></TR><TR><TD>2</TD><TD> Kavya N</TD><TD>70</TD><TD>CS</TD></TR><TR><TD>3</TD><TD> Nawaz O</TD><TD>80</TD><TD>CS</TD></TR><TR><TD>4</TD><TD>Hari S</TD><TD>80</TD><TD>EC</TD></TR><TR><TD>5</TD><TD>Alex M</TD><TD>50</TD><TD>EC</TD></TR><TR><TD>6</TD><TD>Neenu J</TD><TD>70</TD><TD>EC</TD></TR><TR><TD>7</TD><TD>Bob A</TD><TD>30</TD><TD>EC</TD></TR><TR><TD>8</TD><TD>Anu M</TD><TD>90</TD><TD>AE</TD></TR><TR><TD>9</TD><TD>Sruthi</TD><TD>89</TD><TD>AE</TD></TR><TR><TD>10</TD><TD>Andrew</TD><TD>89</TD><TD>AE</TD></TR></TABLE>

可读性差也可以理解,因为人家觉得,你没必要修改么,直接以html形式展示数据就可以了。

代码如下:

id    name    mark    dept
1    Navin M    98    CS
2    Kavya N    70    CS
3    Nawaz O    80    CS
4    Hari S    80    EC
5    Alex M    50    EC
6    Neenu J    70    EC
7    Bob A    30    EC
8    Anu M    90    AE
9    Sruthi    89    AE
10    Andrew    89    AE

xml形式的数据显示就比较正常了,直接将上面的-H 换成-X,输出如下:

代码如下:

<?xml version="1.0"?>
<resultset statement="select * from students" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="id">1</field>
    <field name="name">Navin M</field>
    <field name="mark">98</field>
    <field name="dept">CS</field>
  </row>
  <row>
    <field name="id">2</field>
    <field name="name"> Kavya N</field>
    <field name="mark">70</field>
    <field name="dept">CS</field>
  </row>
</resultset>

完。

时间: 2014-08-17

MySQL5.7安装过程并重置root密码的方法(shell 脚本)

由于 MySQL 5.7 版本的 root 密码是首次启动时随机生成的,并且还要求必须修改后才能使用,所以有了本文:使用 shell 脚本完成安装和设置新的 root 密码. 以官方的 rpm 包安装为例,先下载,使用 yum 命令安装,如果有需要的依赖包会自动安装 wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar tar xf mysql-5.7.17-1.el6

Mysql 忘记root密码和修改root密码的解决方法(小结)

一 修改root密码的三种办法 方法1: 用SET PASSWORD命令 首先登录MySQL. 格式:mysql> set password for 用户名@localhost = password('新密码'); 例子:mysql> set password for root@localhost = password('123'); 方法2:用mysqladmin 格式:mysqladmin -u用户名 -p旧密码 password 新密码 例子:mysqladmin -uroot -p12

Shell脚本实现监控MySQL主从同步

代码如下: 复制代码 代码如下: #!/bin/bash #check MySQL_Slave Status #crontab time 00:10 MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'` MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "

Shell脚本中执行sql语句操作mysql的5种方法

对于自动化运维,诸如备份恢复之类的,DBA经常需要将SQL语句封装到shell脚本.本文描述了在Linux环境下mysql数据库中,shell脚本下调用sql语句的几种方法,供大家参考.对于脚本输出的结果美化,需要进一步完善和调整.以下为具体的示例及其方法. 1.将SQL语句直接嵌入到shell脚本文件中 复制代码 代码如下: --演示环境  [root@SZDB ~]# more /etc/issue  CentOS release 5.9 (Final)  Kernel \r on an \

CentOS下mysql定时备份Shell脚本分享

1.备份语句 复制代码 代码如下: # /usr/local/mysql/bin/mysqldump -utest -ptest test --socket=/tmp/mysql.3306.sock > /usr/local/mysql_backup/3306/test-`date +%Y%m%d`.sql.gz 语句说明:   /usr/local/mysql/bin/mysqldump:MySql安装目录下 -utest:分为两块,一块为-u一块为test:其中-u表示其为用户名,test表

Mysql 忘记root密码的完美解决方法

一.更改my.cnf配置文件 1.用命令编辑/etc/my.cnf配置文件,即:vim /etc/my.cnf 或者 vi /etc/my.cnf 2.在[mysqld]下添加skip-grant-tables,然后保存并退出 3.重启mysql服务:service mysqld restart 二.更改root用户名 1.重启以后,执行mysql命令进入mysql命令行 2.修改root用户密码 MySQL> UPDATE mysql.user SET Password=PASSWORD('新

MySQL密码忘了怎么办?MySQL重置root密码方法

MySQL有时候忘记了root密码是一件伤感的事.这里提供Windows 和 Linux 下的密码重置方法. Windows: 1.以系统管理员身份登陆系统. 2.打开cmd-----net start 查看mysql是否启动.启动的话就停止net stop mysql. 3.我的mysql安装在d:\usr\local\mysql4\bin下. 4.跳过权限检查启动mysql. d:\usr\local\mysql\bin\mysqld-nt --skip-grant-tables 5.重新打

把mysql查询结果保存到文件的shell脚本

该脚本是先删除已经存在的文件,然后后台执行SQL语句将其执行结果以一定的格式写入文件 复制代码 代码如下: #!/bin/bashif [ -f "/var/lib/mysql/hell.txt" ]; thenrm -f /var/lib/mysql/hell.txtecho "delete /var/lib/mysql/hell.txt" >> hell.txtfimysql -uroot -plab <<EOFuse home;sele

在Linux环境下mysql的root密码忘记解决方法(三种)

MySQL密码的恢复方法之一 1.首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库. 因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的 状态下,其他的用户也可以任意地登录和修改MySQL的信息.可以采用将MySQL对外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态.最安全的状态是到服务器的Console上面操作,并且拔掉网线. 2.修改MySQL的登录设置: # vi /etc/my.cnf 在[mysq

Mysql 5.7 忘记root密码或重置密码的详细方法

在Centos中安装完MySQL数据库以后,不知道密码,这可怎么办,下面给大家说一下怎么重置密码 1.修改配置文件my.cnf 按i编辑 [root@iZ2ze14tbj23jllo85kuh1Z ~]# vim /etc/my.cnf 在[mysqld]中添加 skip-grant-tables 例如: [mysqld] **skip-grant-tables** datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock 键盘 Esc 保存

Mysql5.7忘记root密码及mysql5.7修改root密码的方法

关闭正在运行的 MySQL : [root@www.woai.it ~]# service mysql stop 运行 [root@www.woai.it ~]# mysqld_safe --skip-grant-tables & 为了安全可以这样禁止远程连接: [root@www.woai.it ~]# mysqld_safe --skip-grant-tables --skip-networking & 使用mysql连接server: [root@www.woai.it ~]# my

MySQL5.7.20解压版安装和修改root密码的教程

1. 下载MySQL Archive(解压版) 网址:https://downloads.mysql.com/archives/community/ 2. 解压文件到自定义安装目录: 解压后没有 "data" 文件夹和 "my.ini" 配置文件 3. 解压完后配置环境变量 系统变量: 新建"MYSQL_HOME"值:xxx\mysql-5.7.20-winx64 (自定义安装目录的根路径) 追加Path::%MYSQL_HOME%\bin 4.

Linux fedora 24 忘记密码图形化界面修改root密码的方法

方法及其简单,只需要两步即可: 1.第一步: 打开终端,输入sudo su命令. –此处的密码为普通用户的密码,也就是开机时输入的密码. 2.第二步: 直接sudo passwd root就重置了roor密码了. 此时输入新的密码即可,个人建议密码最好字母加数字相结合. PS:下面看下fedora23忘记root密码怎么修改? 第一步:在开机进入grub菜单的时候选择正确的启动项,按e 第二步:找到开头是linux16这一行,在末尾加上rd.break,注意空格,按ctrl+x 第三步:此时进入

mysql5.7.18安装并修改初始密码的方法

Centos 安装MySQL可以参考之前写的一篇文章 Centos7.3 安装Mysql5.7并修改初始密码 windows安装mysql5.7有两种方式 1.下载.msi安装文件直接根据界面提示进行安装 2.下载.biz压缩包 这里接介绍的是第二种压缩版的安装方式 官方的参考文档 https://dev.mysql.com/doc/refman/5.7/en/windows-install-archive.html 1.下载mysql 其他版本下载地址 https://dev.mysql.co

MySQL修改root账号密码的方法

MySQL数据库中如何修改root用户的密码呢?下面总结了修改root用户密码的一些方法 1: 使用set password语句修改 mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.08 sec) mysql> set password=password('123456'); Query OK,

mysql 8.0.16 winx64及Linux修改root用户密码 的方法

连接数据库等基础操作请自行解决哈,本篇是重点记录如何改密码. 一.查询用户密码: 查询用户密码命令: select host, user, authentication_string from mysql.user ; host:允许用户登录的ip'位置'%表示可以远程: user:当前数据库的用户名: authentication_string:用户密码(后面有提到此字段): 二. 设置(或修改)用户密码: 默认root密码为空的话 ,下面使用navicat就无法连接(之前我装的5.7好像还可

Mac下MySQL5.7忘记root密码的解决方法

mysql5.7忘记root密码的操作步骤: 1. 在系统偏好设置中停止MySQL服务. 2.执行命令以安全模式启动MySQL: cd /usr/local/mysql/bin sudo ./mysqld_safe --skip-grant-tables 3.新打开一个命令行窗口,在MySQL中执行 update mysql.user set authentication_string=PASSWORD('你的密码') where User='root'; FLUSH PRIVILEGES; 注

Mysql5.6忘记root密码修改root密码的方法

mysql5.6忘记数据库的root密码: [root@oraserver139 ~]# mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 查看当前安装的mysql版本: root@oraserver139 ~]# rpm -qa | grep MySQL MySQL-server-5.6.15-1.el6.x86_6

MySQL5.1忘记root密码的解决办法(亲测)

网上搜了很多方法,杂,重复度高.但随便选一个却不奏效.这里简单贴一个亲测的. 相关阅读:忘记mysql数据库root用户密码重置方法[图文] 参考后,我用的方法: #win cmd下net stop MySQL停止MySQL服务(可能服务名称不一样) >net stop MySQL #MySQL提供跳过访问控制的命令行参数,通过在命令行以此命令启动MySQL服务器: #safe_mysqld –skip-grant-tables& >mysqld –skip-grant-tables

Mysql5.7忘记root密码怎么办(简单且有效方法)

在上篇文章给大家介绍了Mysql5.7忘记root密码及mysql5.7修改root密码的方法 Mysql5.7忘记密码快速且简单的解决方法,具体方法详情如下所示: # 最简单最粗暴的方法 找到mysql的配置文件直接编辑 vim /etc/my.cnf # 在 [mysqld] 中加上一行跳过权限限制 skip-grant-tables # 保存退出 重启mysql服务 service mysqld restart # 用户登录 mysql -uroot -p (直接点击回车,密码为空) #

Windows 8.1下MySQL5.7 忘记root 密码的解决方法

[背景] 最近学习碰到了一件挺令人尴尬的事情,我把MySQL的密码给忘记了,所以MySQL登录不进去. 网上的解决方案很多,尝试了很多,依旧是被窗口的各种报错虐.感谢大神的助力,MySQL忘记密码解决方案才得以清晰,希望本篇博客的解决过程可以帮到读者. [问题解决过程] 一.跳过MySQL的权限审查进入MySQL 首先,找到MySQL的my.ini配置文件. 然后,打开文件,在mysqld下,添加一句代码 skip-grant-tables ,以跳过权限的审核. 很重要的一步:win+R 输入s