myql配置文件mysql.properties中存放mysql连接参数:
#mysql ip
mysql_ip=192.168.1.1
#mysql端口
mysql_port=3306
#mysql登陆用户名
mysql_user=root
#mysql登陆密码
mysql_password=123456
注意:配置参数不要用.连接,否则eval读取不到参数值。
#!/bin/bash
while read line;do
eval "$line"
done < /home/hadoop/mysql.properties
echo "mysql ip: $mysql_ip"
echo "mysql port: $mysql_port"
echo "mysql login username: $mysql_user"
echo "mysql login password: $mysql_password"
#mysql init SQL:
#
#create database sync;
#drop table if exists sync.sync_status;
#create table if not exists sync.sync_status(table_name varchar(30) not null,flag tinyint comment 'whether initialized,0-not initailzed,1-has initialized' default 0,primary key(table_name)) charset=utf8mb4 comment 'table init status';
#READ FROM MYSQL
result=`mysql -h${mysql_ip} -P${mysql_port} -u${mysql_user} -p${mysql_password} << EOF | tail -n+2
select * from sync.sync_status;
EOF`
echo "$result" | while read line;do
table_name=`echo $line|cut -d ' ' -f 1`
flag=`echo $line|cut -d ' ' -f 2`
echo "table name:$table_name,init flag:$flag"
done
src_table_name="mdrp.action_log1";
result2=`mysql -h${mysql_ip} -P${mysql_port} -u${mysql_user} -p${mysql_password} << EOF | tail -n+2
select flag from sync.sync_status where table_name='$src_table_name';
EOF`
if [[ $result2 > 0 ]];then
echo "table $src_table_name has inited."
else
echo "table $src_table_name has not inited."
fi
echo "single row:$result2"
#table_name=`echo $result|cut -d ' ' -f 1`
#flag=`echo $result|cut -d ' ' -f 2`
#echo "table name:$table_name,init flag:$flag"
#WRITE TO MYSQL
mysql -h${mysql_ip} -P${mysql_port} -u${mysql_user} -p${mysql_password} -e "insert into sync.sync_status values('mdrp.action_log',1)";
if [ $? -ne 0 ];then
echo "insert table init status failed."
else
echo "update table init status success."
fi