时间戳

首页 > 解决方案 > 无法将 max_allowed_pa​​cket 大小设置为 2G

linux - 无法将 max_allowed_pa​​cket 大小设置为 2G

问题描述

我的 mariadb10.2 数据库不允许我将max_allowed_packet大小设置为2G. 它在my.cnf文件中设置，但在我查询时保留，它仍然位于1G. 我需要2G工作mysqldump。这一直正常工作，直到发生意外重新启动。

my.cnf file[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
ssl = true
ssl-cert = /opt/certs/mysql/client-cert.pem
ssl-key = /opt/certs/mysql/client-key.pem

[isamchk]
key_buffer_size = 16M

[mysqld]
basedir = /usr
bind-address = *
datadir = /mnt/mariadb/data/db
expire_logs_days = 10
innodb_buffer_pool_size = 4G
innodb_file_per_table = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 2G
innodb_stats_on_metadata = 0
key_buffer_size = 16M
log-error = /var/log/mariadb/mariadb.log
log_warnings = 4
max_allowed_packet = 2G
max_binlog_size = 100M
max_connections = 151
myisam_recover = BACKUP
pid-file = /var/run/mariadb/mariadb.pid
plugin-load = auth_pam.so;auth_socket.so
port = 3306
query_cache_limit = 1M
query_cache_size = 16M
skip-external-locking
socket = /var/lib/mysql/mysql.sock
sql-mode = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ssl = true
ssl-ca = /opt/certs/mysql/ca.pem
ssl-cert = /opt/certs/mysql/server-cert.pem
ssl-key = /opt/certs/mysql/server-key.pem
thread_cache_size = 8
thread_stack = 256K
tmpdir = /tmp
user = mysql

[mysqld_safe]
log-error = /var/log/mariadb/mariadb.log
nice = 0
socket = /var/lib/mysql/mysql.sock
    
[mysqldump]
max_allowed_packet = 2G
quick
quote-names

尝试临时设置时的输出：

MariaDB [(none)]> set global max_allowed_packet=2147483684; ERROR 1231
(42000): Variable 'max_allowed_packet' can't be set to the value of
'2147483684'

automysqlbackup使用下载的脚本，我的备份用完了 cron 。所有 vars 都由 puppet 管理，我已禁用临时功能以尝试再次进行此工作。

标签： linuxmariadb

解决方案

您必须找到另一种方法来进行备份，因为 max_allowed_pa​​cket 无法设置为该值。

文档指出最大值为 1GB。

你确定你需要一个这么大的值吗？该值应该与您最大的 blob 一样大，而不是数据库的大小。拥有这么大的斑点可能很难处理。

推荐阅读