ubuntu创建cronjob执行mysql语句

1.在机器上安装mysql客户端

apt-get install mysql-client

安装完成后 输入 mysql命令

2.配置 环境变量

切换root用户， 在 /etc/profile 最后 一行 添加 export MYSQL_PWD='数据库密码'

使用 source /etc/profile 使配置生效

当配置 好环境变量后，

使用 mysql -h 172.18.0.2 -P 32000 -u root 即可 直接连接mysql

但如果 只在 /etc/profile中 配置 环境变量，当下次 重新 登录root后， 使用 echo $MYSQL_PWD没有输出， 即配置的环境变量已经失效。

若需环境变量永久生效：

vim ~/.bashrc

将 /etc/profile 文件中配置的环境变量 copy 到这个文件中

source ~/.bashrc

3. 在 root用户下 使用 crontab -e编辑 任务， 编辑完成后， ctrl+x 退出保存，配置。 无需重新 cron服务。

配置任务是需注意：

a.定时执行的sh脚本 需用绝对路径

b. 使用环境变量执行脚本， 否则可能会出现手动执行脚本正常， 但使用cronjob触发任务却不能正确连接数据库， 0 3,7 * * * . /etc/profile; /bin/sh /xlt/tmp/xueting/TriggerBDDownloadConfig.sh

service cron status

service cron restart

4.sh 脚本如下

mysql -h 172.18.0.2 -P 32000 -u root -e " update dfm_platform.dfm_download_config set run_now = true where download_cfg_id in ( select distinct a.download_cfg_id as download_cfg_id from ( SELECT *, timestampdiff(MINUTE, CONCAT('2023-01-01 ', SUBSTRING_INDEX(trim(substring(cron_expression, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 2))+ 2, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 3))-LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 2)))), ',', 1), ':', trim(substring(cron_expression, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 1))+ 2, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 2))-LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 1)))), ':', trim(SUBSTRING_INDEX(cron_expression , ' ', 1))), DATE_FORMAT(date_add(now() , INTERVAL 8 hour), '2023-01-01 %H:%i:%s')) as over_time FROM dfm_platform.dfm_download_config WHERE is_enabled = true and is_deleted = false and trim(replace(SUBSTRING_INDEX(cron_expression , ' ', 4), SUBSTRING_INDEX(cron_expression, ' ', 3), '')) IN ('?', '*') AND cron_expression NOT LIKE '%MON%' AND cron_expression NOT LIKE '%TUE%' AND cron_expression NOT LIKE '%WED%' AND cron_expression NOT LIKE '%THU%' AND cron_expression NOT LIKE '%FRI%' AND cron_expression NOT LIKE '%SAT%' AND cron_expression NOT LIKE '%SUN%' union all SELECT *, timestampdiff(MINUTE, CONCAT('2023-01-01 ', SUBSTRING_INDEX(trim(substring(cron_expression, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 2))+ 2, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 3))-LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 2)))), ',', 1), ':', trim(substring(cron_expression, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 1))+ 2, LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 2))-LENGTH (SUBSTRING_INDEX(cron_expression , ' ', 1)))), ':', trim(SUBSTRING_INDEX(cron_expression , ' ', 1))), DATE_FORMAT(date_add(now() , INTERVAL 8 hour), '2023-01-01 %H:%i:%s')) as over_time FROM dfm_platform.dfm_download_config WHERE is_enabled = true and is_deleted = false and cron_expression LIKE CONCAT('%',(select CASE dayofweek(now()) WHEN 1 THEN 'SUN' WHEN 2 THEN 'MON' WHEN 3 THEN 'TUE' WHEN 4 THEN 'WED' WHEN 5 THEN 'THU' WHEN 6 THEN 'FRI' ELSE 'SAT' END), '%'))a left join ( select * from dfm_platform.v_dfm_instance where create_time >=DATE(CONVERT_TZ(NOW(),'SYSTEM','Asia/Shanghai')))b on a.download_cfg_id = b.download_cfg_id where b.download_cfg_id is null and a.over_time > 30);"

测试脚本：

mysql -h 172.18.0.2 -P 32000 -u root -e "update dfm_platform.dfm_download_config set data_lag = 32 where download_cfg_id = 341"