Create a database and a user

Almost every application needs a database. SrvYard creates the database and the login that goes with it on your server, so you never have to open a terminal and type CREATE DATABASE. You choose a name and what the login may do; SrvYard does the rest and generates a strong password for you.

By the end of this article you will have a new database, a database user linked to it, and the password saved in your password manager. The next article, Connect a site to its database, shows how to hand those details to your application.

Before you begin#

• A database engine on the server. MySQL, MariaDB or PostgreSQL must be installed. Servers created as a database or app server get one during setup; if the Databases tab says No database engine installed, install one from the Services tab first. A server can run MySQL or MariaDB (not both), and PostgreSQL can run beside either.
• A server in the Ready state. While a server is installing or disconnected the buttons on this page are disabled.
• Permission to manage databases. Members who can only view a server see the lists but not the buttons.

1. Open the Databases tab#

Open the server and choose Databases. The page has two lists, Databases and Users, and a row of buttons.

1. Databases and Users switch between the two lists. The number next to each is how many there are. When the server runs more than one engine, a filter appears next to them so you can look at one engine at a time.
2. Sync from server reads the databases and users that exist on the server and adds any that SrvYard does not know yet. Use it after someone created a database by hand over SSH.
3. New database opens the panel from step 2. On the Users tab the same place holds New user.
4. Every row shows the name, the character set and collation, the size, how many users are linked and the status. A new database shows Creating for a few seconds and then Ready. Refresh sizes re-measures the databases, because sizes are not updated live.

2. Create the database#

Press New database. A panel opens from the right.

1. Name. Letters, numbers and underscores, up to 48 characters, for example shop_production. The name must be unique on the server for that engine. The names mysql, information_schema, performance_schema, sys, postgres, template0 and template1 belong to the engines and are refused.
2. Character set. utf8mb4 is the default and the right choice for almost everything: it stores every language and emoji. The other options (utf8mb3, latin1, ascii) exist for old applications. PostgreSQL databases do not have this field.
3. Collation. How text is sorted and compared. The default utf8mb4_unicode_ci is case-insensitive and sorts naturally in most languages. The list only offers collations that belong to the chosen character set, and the MySQL 8 0900 collations are hidden on MariaDB.
4. Also create a user. Switch it on to create a login in the same step. Step 3 explains the extra fields.
5. Create database. Creates it. The panel closes and the new row appears.

If the server has more than one engine, an Engine field appears at the top of the panel. Choose it before you type the name.

3. Create a user with the database#

The quickest way to get a working login is to switch on Also create a user.

1. Username. Letters, numbers and underscores, up to 32 characters, for example shop_user. Names such as root, postgres, mysql, mariadb and srvyard are reserved.
2. Permissions. Three presets:
   • Read only: SELECT and SHOW VIEW. The user cannot change data or structure. Good for reporting tools.
   • Read and write: reads and writes data and changes tables, but cannot drop or truncate them.
   • Full access: all privileges on the linked database, including dropping it. This is the default for a user created with a database, because applications usually run their own migrations.
3. Allow remote access. Off by default, which means the user can only log in from the server itself. Switch it on only when something outside the server must connect. See Remote access, phpMyAdmin and SSH tunnels.

Press Create database. SrvYard creates the database, creates the user, links the two and generates a password. A dialog titled Save this password now shows it once, with a copy button.

4. Create a user on its own#

Switch to the Users tab to create a login without a new database, for example a read-only user for a reporting tool or a second login for a different application.

1. New user opens the panel. Sync from server is still available here.
2. Host shows where the login may connect from. A plain localhost login has no badge; a login with a remote host shows the host (for example 10.0.%) and a Remote badge. The other columns show the permission preset, the databases the user is linked to and its status.

1. Username. The same rules as before. It must be unique on the server for that engine.
2. Password (optional). Leave it empty and SrvYard generates one. If you type your own, use 8 to 64 characters without spaces. Either way the password is shown once after you save.
3. Permissions. The same three presets.
4. Databases. Tick every database this user may reach. The permission preset applies to each of them. A user with no database ticked can log in but cannot see anything, which is how the demo readonly_reports user starts.
5. Allow remote access. With the switch on, an Allowed host or IP field appears. Enter one address such as 203.0.113.10, or a pattern such as 10.0.0.%. A single % allows any host, which is almost never what you want.

Press Create user. The password dialog appears, exactly as in step 3.

5. Reset, reveal or delete a password#

Open the menu at the end of a user's row. It has four entries:

• Edit changes the permission preset, the linked databases and the remote host. Saving applies the permissions again on every linked database. Leave New password empty to keep the current one.
• Reset password generates a new password and shows it once. Every application that used the old password stops connecting until you update it, so do this in a quiet moment.
• Reveal password shows the stored password again. SrvYard asks for your own SrvYard password first, then writes the reveal to the audit log. You must have a password set under Settings > Security to use it. For a user that was imported with Sync from server the entry is disabled, because SrvYard never knew that password: reset it instead.
• Delete user removes the login from the server. Applications that use it stop connecting.

To delete a database, open the menu at the end of its row on the Databases tab and choose Delete database. SrvYard asks you to type the database name before it drops it. This removes the database and all its data from the server and cannot be undone. Users linked to it stay, but lose access to it.

Troubleshooting#

• The buttons are greyed out. The server is not in the Ready state, or your role does not allow managing databases. Check the status next to the server name, or ask an owner or admin.
• "This name is reserved by the database engine." You chose a name the engine uses itself, such as mysql or sys. Pick another name.
• "The name has already been taken." A database or user with that name already exists on the server for the same engine. Run Sync from server if you expected it to be new.
• The row stays on Creating. The server did not answer. Open the Activity tab of the server for the log of the operation, then try Sync from server.
• Reveal password is disabled or says to set a password. The user was imported, so SrvYard has no copy, or your own account has no password yet. Reset the user's password, or set one under Settings > Security.
• The application says access denied. Check that the user is linked to the database, that the permission preset allows what the application does, and that the host matches where it connects from. See Connect a site to its database.

Related#

• Connect a site to its database: put the new details into your application's environment.
• Remote access, phpMyAdmin and SSH tunnels: reach the database from your own computer safely.
• Schedule database backups: protect the data you are about to store.
• Create a site on your server: create a database at the same time as the site.