SoFunction
Updated on 2025-04-12

MySQL docker container data update statistics shell script code method

MySQL docker container data update statistics shell script code

Create a script file

#!/bin/bash

# MySQL container nameMYSQL_CONTAINER="mysql"

# Output fileOUTPUT_FILE="./"

# Current timeCURRENT_DATE=$(date "+%Y-%m-%d %H:%M:%S")

# Execute SQL query and append the results to the fileecho "[$CURRENT_DATE]" >> $OUTPUT_FILE
echo "Executing SQL query inside the MySQL container..." >> $OUTPUT_FILE

# SQL query commandQUERY_RESULT=$(docker exec $MYSQL_CONTAINER mysql -uroot -p123456 -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_update', 'Com_insert', 'Com_delete', 'Com_replace');" -sN)

# Write the result to the fileecho "$QUERY_RESULT" >> $OUTPUT_FILE
echo "----------------------------------------" >> $OUTPUT_FILE

# Output prompt informationecho "Statistics recorded at $CURRENT_DATE"

Set permissions

In order for the script to be executed, you need to add execution permissions to the script:

chmod +x mysql_update_stats.sh

Configure timing tasks

usecronTo schedule script execution every hour.

Edit the crontab file:

crontab -e

Add the following line to the open editor:

0 * * * * /path/to/your/directory/mysql_update_stats.sh

Here0 * * * *Indicates the execution of the script at the full hour of each day.

If you put the script/home/user/scripts/In the directory, the path should be/home/user/scripts/mysql_update_stats.sh。

Things to note

MySQL container name：

  • Make sure the script is inMYSQL_CONTAINERThe variable value is correct, that is, the actual name of the MySQL container.
  • If you are not sure about the container name, you can usedocker psThe command views the list of running containers.

User permissions inside MySQL container：

  • Ensure the default user inside the MySQL container (usuallyroot）
  • Have sufficient permissions to executeSHOW GLOBAL STATUSOrder

Script paths and permissions：

  • Make sure that the directory where the script file is located and the directory where the output file is output has sufficient permissions
  • Make scripts read and write files

explain

  • Execute SQL query:usedocker execThe command executes SQL query commands directly inside the MySQL container.
  • Output result: Append the query result to the specified fileand add a split line between each record to distinguish the results of each execution.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.