GoAdmio

mariadbquota

Apply the saved connection, queries-per-hour, and updates-per-hour limits to every MariaDB user except system accounts, then regenerate the MariaDB configuration and restart MariaDB

On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also


Overview

/go/mariadbquota stops one database user from using all of the server's database capacity. It reads three limits from GoAdmio's settings and applies them to every MariaDB user on the server, except system accounts:

When it finishes, it runs my, which regenerates the MariaDB configuration and restarts MariaDB. Expect a short database interruption every time you run this command.


Quick start

# Apply the saved limits to all database users
/go/mariadbquota

Syntax

/go/mariadbquota
Form Behavior
/go/mariadbquota Applies the limits to every non-system MariaDB user.

The script stores its first argument as a domain, but never uses it. Passing a domain does not limit a single account. All users are always changed.


Options

This command has no options. The limits come from GoAdmio's settings database, not from the command line.

Stored value Applied to each user as What it limits
MAX_USER_CONNECTIONS MAX_USER_CONNECTIONS Simultaneous connections
MAX_QUESTIONS MAX_QUERIES_PER_HOUR Statements per hour
MAX_UPDATES MAX_UPDATES_PER_HOUR Statements that change data, per hour

The values are read from row 1 of the MariaDB table in /var/lib/sqlite/mariadb.db. A value of 0 means no limit. For connections, 0 means the server-wide max_user_connections setting applies.

The command skips these accounts: root, mysql, mariadb.sys, mysql.session, mysql.sys, and PUBLIC. Every other account is limited, including any extra accounts you created yourself (for example a monitoring or backup user).


Output

The command prints nothing when it works, so a quiet run is normal. Errors from MariaDB are printed as they happen, followed by anything my prints. The exit status is the one returned by my.

The run is recorded in /var/log/go/mariadbquota.log.

Security

[!WARNING] The MariaDB root password is passed to the mariadb client on its command line, so it is briefly visible in the process list while the command runs.


Examples

# Apply the saved limits
/go/mariadbquota
# Show the limits that the command will apply
sudo sqlite3 /var/lib/sqlite/mariadb.db "select MAX_USER_CONNECTIONS, MAX_QUESTIONS, MAX_UPDATES from MariaDB where MariaDBID='1'"
# Check the limits that are now set on each user
sudo mariadb -e "SELECT user, host, max_user_connections, max_questions, max_updates FROM mysql.user"

Troubleshooting

Symptom Likely cause Fix
You have an error in your SQL syntax ... near ';' One of the stored limits is empty, so the ALTER USER statement has no number. Check the stored values (see Examples) and make sure all three are set.
Operation ALTER USER failed for '<user>'@'localhost' The user exists, but not for localhost (for example only for %). Set its limits yourself with ALTER USER, or create the user for localhost.
Websites briefly lose their database connection The command ends by running my, which restarts MariaDB. Run it at a quiet time.
A user gets an error saying it has exceeded a resource The user reached its connection, query, or update limit. Raise the stored limit, then run the command again.
A newly created database user has no limits Limits are applied only to users that exist when the command runs. Run the command again.
A domain was passed, but every user changed The argument is ignored. There is no per-domain mode. Use ALTER USER for one user.
An extra account (monitoring, backup) was limited Only the fixed system accounts are skipped. Remove its limits with ALTER USER '<user>'@'localhost' WITH MAX_USER_CONNECTIONS 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;. Running the command again will limit it again.
You want to remove all limits The limits are stored values. Set all three stored values to 0, then run the command.

Details

What it does

  1. Writes the command and its arguments to /var/log/go/mariadbquota.log.
  2. Reads the MariaDB root password from GoAdmio's config.php.
  3. Reads the three limits from /var/lib/sqlite/mariadb.db.
  4. Lists all MariaDB users, leaving out the system accounts above.
  5. For each user, runs three ALTER USER '<user>'@'localhost' ... statements: connections, queries per hour, and updates per hour.
  6. Runs my to regenerate the MariaDB configuration and restart MariaDB.

What the limits mean


See also

Command Description
my Regenerate the MariaDB configuration file with settings tuned to the server's RAM, CPU, and database usage, then restart MariaDB
mysql Create, edit, or delete a MySQL/MariaDB database and user for a hosting account
mysqltop Report current MariaDB activity and today's heaviest queries, and respond automatically when the server load is high
querytop Show the ten most frequently executed SELECT queries from MariaDB's query log, turning on query logging first if needed
repair Repair the tables of one MySQL/MariaDB database, or of all databases
installmariadb Install or upgrade MariaDB from the official repositories (default version 12.3, or a version you choose) and apply basic hardening