Avinash Pawar's questions

我正在尝试使用选项从 bash 脚本捕获 mysql 表中删除的行数--execute，但似乎不起作用。我回答了一些问题，其中提到使用选项，它可以工作，但没有达到预期的效果。我使用了 mysql 命令和awk 和 sed-vv的各种组合，但似乎没有任何效果。-vvv --silent--skip-column-names

MySQL 客户端是mysql Ver 8.0.39 for Linux on aarch64 (MySQL Community Server - GPL)

https://stackoverflow.com/questions/2628310/how-to-get-number-of-rows-deleted-from-mysql-in-shell-script

https://stackoverflow.com/questions/1083866/how-to-get-number-of-rows-affected-while-executing-mysql-query-from-bash

如何在使用 MySQL -e,--execute 时输出 UPDATE/INSERT 查询结果？

以下是代码

deleted=$(mysql --login-path=lp1 --init-command="SET SQL_LOG_BIN = 0" -vv --silent --skip-column-names db -e "delete from table1 where column1 date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 23:59:59') order by column1 limit 20;")

echo ${deleted}

该语句删除了某些行，但打印了如下输出

-------------- delete from table1 where column1 between date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 23:59:59') order by column1 limit 20 -------------- Query OK, 20 rows affected --------------  1 row in set Bye

如果我再添加一行来获取 row_count，那么它会给我 -1

mysql --login-path=${LP_wallet_0} -e "select row_count()"

即使我在同一个删除查询中尝试 row_count()，它只会附加此行select row_count() -------------- 2 1 row in set Bye

--vv --silent --skip-column-names我尝试了和awk和也是的不同组合sed，但无法得到计数。

我对诸如刷新和写入磁盘以及同步到磁盘之类的术语感到困惑。有人能解释一下刷新和写入以及同步到磁盘之间的区别吗，特别是在 MySQL 环境中。

我过去认为它们是相同的操作，但是在阅读各种概念时我感到困惑。

例如。文档中说，对于rpl_semi_sync_source_wait_point变量，如果我将其设置为AFTER_SYNC，则源会先将事务写入 bin 日志和副本，然后将 binlog 同步到磁盘。

https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_rpl_semi_sync_source_wait_point

AFTER_SYNC (the default): The source writes each transaction to its binary log and the replica, and syncs the binary log to disk. 

这句话本身就令人困惑。它先是说source writes each transaction to its binary log and the replica，然后又说syncs the binary log to disk.

那么在这种情况下究竟发生了什么。在 MySQL 上下文中，刷新、同步和写入磁盘究竟是什么？问题不在于 sync_binlog 或 MySQL 中的任何其他参数。问题在于刷新、同步和写入，以及这些活动何时发生？

当我再次尝试阅读更多内容时，在以下链接中，它解释说，有一个 binlog 缓存、一个页面缓存和一个 fsync 操作。因此，如果我理解正确的话，当发生事务时，缓冲区会变脏，事务的内容将写入 binlog 缓存（在 MySQL 级别），这是每个会话的单独缓存，然后 binlog 缓存将写入页面缓存（在 Linux 级别），然后根据sync_binlog值，将其刷新/写入/同步到磁盘上的文件。

https://betterprogramming.pub/understanding-mysqls-binlog-4ac8de4d20ee

MySQL 服务器：8.0.16 商业操作系统：RHEL 7.5

我编写了一个简单的脚本，将某些信息插入到数据库表中。

以下是我的脚本

# cat ~mysqldba/bin/connections.sh

   #!/bin/bash

log_file=~mysqldba/stats/connections_info.log


threads_connected=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf  -e "show global status like 'threads_connected';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`
echo "Number of open connections are ${threads_connected}." >> ${connections} 

threads_running=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e "show global status like 'threads_running';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`
echo "Number of connections that are not sleeping out of ${threads_connected} are ${threads_running}." >> ${connections} 

app1=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist;" | grep 'ip-address1'| wc -l`

sleep_app1=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist where command like 'sleep';" | grep 'ip-address1'|wc -l`

app2=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist;" | grep 'ip-address2'| wc -l`

sleep_app2=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select host from information_schema.processlist where command like 'sleep';" | grep 'ip-address2'|wc -l`

max_used_connections=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf  -e "show global status like 'max_used_connections';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`

max_used_connections_time=`/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf  -e "show global status like 'max_used_connections_time';" | tr -d "| " | egrep -iv 'variable_name' | awk -F " " '{ print $2 }'`

idle=`mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e "select count(*) from information_schema.processlist where command='sleep';" | grep -v '+'`

/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e "insert into backup_info.connection_info (serial_no, run_date, run_time, total_number_of_connections, number_of_running_connections, no_of_connections_from_app27, no_of_sleeping_from_app27, no_of_connections_from_app28, no_of_sleeping_from_app28, max_used_connections, number_of_idle_connections) values (0, curdate(), curtime(), ${threads_connected}, ${threads_running}, ${app1}, ${sleep_app1}, ${app2}, ${sleep_app2}, ${max_used_connections}, ${idle})" 2>> ${log_file}

该脚本应该在每次运行时在数据库表中插入一行。

以下是数据库表：

CREATE TABLE `connection_info` (
  `SERIAL_NO` mediumint(9) NOT NULL AUTO_INCREMENT,
  `run_date` date DEFAULT NULL,
  `run_time` time DEFAULT NULL,
  `TOTAL_NUMBER_OF_CONNECTIONS` mediumint(9) DEFAULT NULL,
  `NUMBER_OF_RUNNING_CONNECTIONS` mediumint(9) DEFAULT NULL,
  `NO_OF_CONNECTIONS_FROM_APP1` mediumint(9) DEFAULT NULL,
  `NO_OF_SLEEPING_FROM_APP1` mediumint(9) DEFAULT NULL,
  `NO_OF_CONNECTIONS_FROM_APP2` mediumint(9) DEFAULT NULL,
  `NO_OF_SLEEPING_FROM_APP2` mediumint(9) DEFAULT NULL,
  `MAX_USED_CONNECTIONS` mediumint(9) DEFAULT NULL,
  `NUMBER_OF_IDLE_CONNECTIONS` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`SERIAL_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED

当我运行脚本时，它确实运行成功并在表中插入一行。

    # sh -x ~mysqldba/bin/connections.sh
+ log_file=/home/mysqldba/stats/connections_info.log
++ date +%F_%T
+ echo '--------##### -------- 2020-02-14_13:04:16 -------- #####-------'
+ echo '--------##### ------------------------------------------ #####-------'
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''threads_connected'\'';'
++ tr -d '| '
++ egrep -iv variable_name
++ awk -F ' ' '{ print $2 }'
+ threads_connected=271
+ echo 'Number of open connections are 271.'
/home/mysqldba/bin/connections.sh: line 17: ${connections}: ambiguous redirect
++ tr -d '| '
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''threads_running'\'';'
++ egrep -iv variable_name
++ awk -F ' ' '{ print $2 }'
+ threads_running=3
+ echo 'Number of connections that are not sleeping out of 271 are 3.'
/home/mysqldba/bin/connections.sh: line 20: ${connections}: ambiguous redirect
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist;'
++ wc -l
++ grep ip-address1
+ app1=156
++ wc -l
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist where command like '\''sleep'\'';'
++ grep ip-address1
+ sleep_app1=156
++ grep ip-address2
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist;'
++ wc -l
+ app2=107
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select host from information_schema.processlist where command like '\''sleep'\'';'
++ grep ip-address2
++ wc -l
+ sleep_app2=107
++ tr -d '| '
++ awk -F ' ' '{ print $2 }'
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''max_used_connections'\'';'
++ egrep -iv variable_name
+ max_used_connections=282
++ tr -d '| '
++ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'show global status like '\''max_used_connections_time'\'';'
++ egrep -iv variable_name
++ awk -F ' ' '{ print $2 }'
+ max_used_connections_time=2020-02-1313:29:08
++ mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf --skip-column-names -e 'select count(*) from information_schema.processlist where command='\''sleep'\'';'
++ grep -v +
+ idle=269
+ /mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'insert into backup_info.connection_info (serial_no, run_date, run_time, total_number_of_connections, number_of_running_connections, no_of_connections_from_app27, no_of_sleeping_from_app27, no_of_connections_from_app28, no_of_sleeping_from_app28, max_used_connections, number_of_idle_connections) values (0, curdate(), curtime(), 271, 3, 156, 156, 107, 107, 282, 269)' 

它确实在表中插入了一行，但是，当我在 crontab 中安排它时，当 crontab 执行它时，它会抛出一个错误，如下所示：

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', , 158, 158, 106, 106, , )' at line 1

如果我只是选择 INSERT 语句并在数据库上运行它，它确实会运行。如果我只是选择所有 mysql 命令并从命令行单独运行它，它确实会运行，唯一的问题是当我从crontab运行它时。

/mysql_binaries/mysql/bin/mysql --defaults-extra-file=/mysql_data/config/extra_options.cnf -e 'insert into backup_info.connection_info (serial_no, run_date, run_time, total_number_of_connections, number_of_running_connections, no_of_connections_from_app27, no_of_sleeping_from_app27, no_of_connections_from_app28, no_of_sleeping_from_app28, max_used_connections, number_of_idle_connections) values (0, curdate(), curtime(), 271, 3, 156, 156, 107, 107, 282, 269)

佩罗斯 说

# perror 1064
MySQL error code MY-001064 (ER_PARSE_ERROR): %s near '%-.80s' at line %d

我已经花了将近 3 天的时间，没有任何进展。