GoAdmio

mysql

Create, edit, or delete a MySQL/MariaDB database and user for a hosting account

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


Overview

/go/mysql manages the MariaDB databases that belong to GoAdmio hosting accounts. It has four modes:

Mode What it does
Create (default) Creates a database and a database user with full access to it, records them in GoAdmio, and prints the connection details.
edit Changes the database user and password, and can switch the user between local-only and remote access.
delete Drops a database that belongs to the account and removes its record in GoAdmio.
system Sets up a separate MariaDB instance for GoAdmio's own use (see System mode).

The index lists this command as "Create a MySQL database". The command also edits and deletes databases.


Quick start

# Create the database shop_db with user shop_user for the account example.com
/go/mysql example.com shop_db shop_user 'S3cureP4ssw0rd'

Syntax

# Create a database and user
/go/mysql <domain> <database> <user> <password> [remote]

# Edit the user and password of a database
/go/mysql <domain> <database> <user> <password> [remote] edit

# Delete a database
/go/mysql <domain> <database> "" "" "" delete

# Set up GoAdmio's own MariaDB instance
/go/mysql system

Arguments are positional. To pass an action you must fill the remote position. Use "" for the default (local-only access).


Arguments

# Argument Used by Notes
1 domain All modes An existing GoAdmio account. The literal word system selects System mode.
2 database All modes The database name. Not root, go, mysql, horde, or roundcube.
3 user Create, edit The database user. Not root, go, mysql, horde, or roundcube.
4 password Create, edit The user's password. See Security.
5 remote Create, edit 1 lets the user connect from any host. Anything else, or empty, means local-only.
6 action All modes delete or edit. Empty, or any other value, means create.

Use plain names (letters, numbers, and underscores) for the database and user. The command does not add the account name as a prefix and does not validate names beyond the reserved list. The values are inserted into SQL and shell commands without quoting, so avoid quotes, spaces, and shell special characters, including in the password.


Output

Mode Outcome Message
Create Success Database '<name>' created successfully., then the connection details.
Edit Success Database '<name>' was successfully modified.
Delete Success Database <name> deleted successfully...
Delete No match Nothing is printed.
Any Validation failure A MYSQL: ... message and exit status 1. See Validation.
Any No arguments No output, exit status 1.
system Finished mariadb --port=3307 and exit status 1.

The connection details printed after a create look like this:

Database 'shop_db' created successfully.

MySQL Hostname: localhost
MySQL Database Name: shop_db
MySQL Database Username: shop_user
MySQL Database Password: S3cureP4ssw0rd

With remote set to 1, the hostname is the server's public IPv4 address, taken from checkip.amazonaws.com. If that lookup fails, the hostname is blank. The address may differ from the one clients use, for example behind NAT.

[!WARNING] The success messages are printed whether or not the MariaDB commands worked. The command does not check the result of the SQL it runs, so a success message does not prove the database or user exists. Check with mariadb -e 'SHOW DATABASES;'.

Security

[!WARNING]

  • The password appears in plain text in the command's output, and is stored in plain text in GoAdmio's database records (/var/lib/sqlite/mysql.db).
  • The password is passed as a command-line argument, so it can be visible in the process list and saved in your shell history.
  • The MariaDB root password is passed on the command line to mariadb, so it is briefly visible in the process list while each SQL command runs.
  • The log hides the password.

Examples

# Create a database with local-only access
/go/mysql example.com shop_db shop_user 'S3cureP4ssw0rd'
# Create a database whose user can connect from any host
/go/mysql example.com shop_db shop_user 'S3cureP4ssw0rd' 1
# Change the password only (pass the existing user name)
/go/mysql example.com shop_db shop_user 'N3wP4ssw0rd' "" edit
# Switch the user to remote access
/go/mysql example.com shop_db shop_user 'N3wP4ssw0rd' 1 edit
# Delete a database (user and password arguments are ignored)
/go/mysql example.com shop_db "" "" "" delete
# Set up GoAdmio's internal MariaDB instance
/go/mysql system
# Verify the result
mariadb -e 'SHOW DATABASES;'
mariadb -e "SHOW GRANTS FOR 'shop_user'@'localhost';"

Troubleshooting

Symptom Likely cause Fix
No output, exit status 1 No arguments were given. Pass the arguments.
MYSQL: MySQL Database Username <user> already exists. The user is one of the reserved names. The wording says "already exists", but it is a reserved-name check. Choose another user name.
MYSQL: MySQL Database Name <name> already exists. The database name is reserved. Same wording issue. Choose another database name.
MYSQL: Account <domain> not exists The domain isn't a hosted account on this server. Check the domain, or create the account with create.
MYSQL: Database '<name>' already exists. The name is already registered in GoAdmio. Choose another name.
MYSQL: Username '<user>' already exists. The user name is already used by another database. User names must be unique across the server. Choose another user name.
MYSQL: Username <user> already exists. when editing Another database's record has this user name. Choose another user name. The check is unreliable (see Edit).
Delete prints nothing The database isn't registered, or it belongs to a different account. Check with dbls and use the right domain.
The user still exists after a delete Delete drops the database only. Drop the user by hand: mariadb -e "DROP USER 'shop_user'@'localhost';".
The old user still works after an edit Edit creates or updates the user you pass and never removes the previous one. Drop the old user by hand.
A user can still connect remotely after switching to local Edit doesn't remove the '%' user. Drop it: mariadb -e "DROP USER 'shop_user'@'%';".
Remote connections time out The database port isn't reachable from outside. Open it with allow and check checkfirewall.
A password in MySQL hash form (* and 40 hex characters) doesn't work The hash path runs ALTER USER, which fails if the user doesn't exist yet, and it doesn't grant privileges. Use a plain password, or grant access by hand. Test this path before relying on it.
The command fails or misbehaves with a quote, space, or $ in a name or password Values are inserted without quoting. Use plain names and a password without special characters.
Other databases of the account stopped accepting their old password Create runs phpmyadmin for the account, which may change the default password (see Create). Confirm what it does before using this on a live account.

Details

Validation

These checks apply to the modes shown. Anything else is not checked.

Check Applies to Failure
At least one argument All Silent exit 1.
user is not root, go, mysql, horde, or roundcube All except system MYSQL: MySQL Database Username <user> already exists.
database is not one of the same names All except system MYSQL: MySQL Database Name <name> already exists.
domain is a hosted account Create MYSQL: Account <domain> not exists
database isn't registered in GoAdmio Create MYSQL: Database '<name>' already exists.
user isn't registered in GoAdmio Create MYSQL: Username '<user>' already exists.
No other database uses the new user Edit MYSQL: Username <user> already exists.

The reserved-name checks run in every mode, so you can't delete a database with a reserved name either. The duplicate checks use GoAdmio's own records, not MariaDB itself, so a database or user that exists in MariaDB but isn't registered in GoAdmio isn't caught.

How it works

flowchart TD
    A["mysql ..."] --> B["Write log line"]
    B --> C{"First argument is system?"}
    C -- "Yes" --> S["System mode"]
    C -- "No" --> D{"Reserved user or database name?"}
    D -- "Yes" --> X["Message, exit 1"]
    D -- "No" --> E{"Action?"}
    E -- "delete" --> DEL["Delete"]
    E -- "edit" --> ED["Edit"]
    E -- "empty or other" --> CR["Create"]

Create

  1. Checks that the account exists and that the database name and user aren't already registered.
  2. Runs CREATE DATABASE IF NOT EXISTS. A database that already exists in MariaDB but isn't registered in GoAdmio is reused without warning.
  3. Creates the user and grants it ALL PRIVILEGES on that database only.
    • With remote set to 1, the user is '<user>'@'%' and can connect from any host.
    • Otherwise, the user is '<user>'@'localhost'.
    • If the password looks like a MySQL hash (* followed by 40 uppercase hex characters), the local path runs ALTER USER ... IDENTIFIED BY PASSWORD instead of GRANT. That statement fails for a user that doesn't exist yet, so this path likely creates no user and grants nothing.
  4. Records the database, user, password (plain text), account, and a timestamp in GoAdmio's database list.
  5. Runs /go/phpmyadmin <domain>. The script's own comment describes it as changing the default MySQL password for all databases of the domain. This page doesn't describe that behavior independently. See phpmyadmin and test on a non-production account first.
  6. Prints the connection details.

Edit

Edit takes the new user and password. It does not check that the account or the database exists, and the domain argument isn't used.

Edit never removes the previous user, and never removes the user for the other host type. Switching users, or switching between local and remote, leaves the earlier user in place with its access.

The duplicate-user check reads the users of all other databases and compares the whole result with the new user name. With two or more other databases the result has several lines and never matches, so the check effectively works only when exactly one other database is registered.

Delete

Delete runs only if the database is registered and belongs to the account's system user. It then:

  1. removes the record from GoAdmio's database list
  2. runs DROP DATABASE <database>
  3. prints Database <database> deleted successfully...

If the database isn't registered, or belongs to another account, nothing happens and nothing is printed. The database user is never dropped.

System mode

/go/mysql system sets up a second MariaDB instance, separate from the main one, and exits with status 1 (even when it works). It:

  1. runs /go/my system
  2. initializes a data directory at /var/go/var/lib/mysql with mariadb-install-db
  3. starts mariadbd in the background with /var/go/etc/my.cnf, logging to /var/log/go/mariadbd.log, and waits 5 seconds
  4. sets the instance's root password to GoAdmio's MariaDB root password
  5. prints mariadb --port=3307, the command to connect to it

The script starts the instance once. It doesn't register a service or restart it after a reboot.

GoAdmio's database records

Created databases are tracked in /var/lib/sqlite/mysql.db (table Mysql) with the database name, user, password, domain, system user, and creation time. Account lookups use /var/lib/sqlite/site.db. The server's public IP is looked up from checkip.amazonaws.com on every run, which needs internet access.

Exit status

Situation Exit status
Validation failure or no arguments 1
system mode 1
Anything else 0, including when a MariaDB command failed

Log file

Each call appends a line to /var/log/go/mysql.log, with the password replaced by ********:

[Nov 19 12:00:00]# mysql example.com shop_db shop_user ******** 1 edit

See also

Command Description
sql Create a MySQL database and import an SQL file into it
copydb Copy an existing MySQL/MariaDB database into a new database and user for a hosting account on this server
dbls List all databases and their sizes
repair Repair a MySQL database
mysqlset Change the MySQL root password
phpmyadmin Install the latest phpMyAdmin on a GoAdmio server
postgre Create, edit, or delete a PostgreSQL database and user
allow Open a port in your server's firewall