Tweet
The database is the heart and soul of your vBulletin site. All content and user information is stored in the database. Protect the database and you protect your site. This document will go over the creation and usage of a MySQL database for the use of vBulletin 5 Connect. If you have shared hosting and are provided a web-based control panel like cPanel, you will need to access your hosting provider's documentation on how to carry out these operations. This document assumes a general familiarity with the command line operations of your Operating System. All commands listed assume that you are accessing your server via SSH.
For vBulletin 5 we recommend that you use MySQL 5.7.7 or higher. An Alternative is MariaDB 10.2 or higher. These instructions are written for MySQL. If they do not work on MariaDB, you will have to use your favorite search engine to find the equivalent.
Note: vBulletin can only be used with MySQL and MariaDB. Support is not provided for Postgres, SQL Server, Oracle or other database management systems.
To access MySQL from the command line you would use this command:
mysql -u username -p
You will be prompted for the password before logging in.
MySQL Settings
You might not be able to change these settings on shared hosting. If you have questions about them, you will need to ask your server administrator or hosting provider.
Several Settings within the MySQL server should be set to provide for a smooth installation and minimal maintenance issues in the future.
You can review your MySQL variables with this command:
show variables;
You can view specific variables by specifying a like clause with the variable name. Similar to this:
show variables like 'innodb_large_prefix';
In your my.cnf file make sure the following settings are enabled:
- default_storage_engine - This value should be set to INNODB. This is the default value of new installations of MySQL after 5.5 was released. If an older version was upgraded, this may have an incorrect value. vBulletin relies on this value to create its tables with the proper table engine.
- wait_timeout: The default value is 28800 seconds. However many hosting providers are lowering this value today. It should be at least 120 seconds to run vBulletin properly.
INNODB Tables
We recommend that you use the INNODB table engine as the default. If a table cannot be used with INNODB, the vBulletin Installation Wizard will make sure it is a proper table type. However, for the best experience some variables may need to be set.
- innodb_file_per_table: This should be set to 1 (On). This will help you manage your disk space better.
- innodb_large_prefix: Should be set to On if you are using a version of MySQL less than 5.7.7. In 5.7.7 this value defaults to On and is deprecated. It will be removed completely in a future version of MySQL. If this value is not set to On, the installation of vBulletin 5.4.0 and higher will fail. This value allows indexes to be larger than 797 bytes.
- innodb_lock_wait_timeout: Default value 50. If you encounter an error referencing this value, it should be increased to at least 100 seconds.
- innodb_file_format: We recommend using Barracuda file format for vBulletin. This will support dynamic rows in the database.
Character Set and Collation
This defines how your data is stored and sent to your vBulletin. On new installations, this is a crucial step if you want to store non-latin characters used in Languages other than English. Unfortunately, the optimal database configuration is rarely the default configuration on a MySQL Server. Especially if you're using an older version.
The query above sets the character set to UTF8MB4 with a corresponding collation. This allows vBulletin to properly handle all languages within the database.
Table Encryption
MySQL 5.7.11 and higher supports native encryption of INNODB tables. The data is encrypted when it is saved and decrypted automatically when a valid connection is made to the database server. This type of encryption, commonly called "At Rest Encryption" allows you to encrypt the stored data and prohibit access in the case of a server breach not involving a MySQL Connection.
You need to be able to edit the MySQL configuration files to enable Table Encryption.
View the MysQL Documentation about Table Encryption.
Note: MySQL does not encrypt log and history files. You would have to secure these through other means.
MariaDB also supports Table Encryption. Due to contributions from Google, the encryption in MariaDB is more robust and extends to log and history files.
Creating Your Database
cPanel
cPanel is a popular web-based hosting management tool. It is used by many hosting providers on their shared hosting plans. It gives you access to many server level configuration options in a manner more convenient than using SSH. While doing this it strives to maintain server security as well by isolating each user from each other. You can find instructions on creating a database in cPanel in their documentation.
Create Database and Assign Users
If you should need assistance with cPanel, please contact your server administrator or hosting provider.
Plesk
Plesk is another web-based hosting management tool. It is more popular in Windows environments than Linux or Unix. It allows you to manage your MySQL and MS-SQL databases right from within its GUI. You can use it to create your database.
Learn how to Create Databases and Manage Database User Accounts in Plesk.
vBulletin Installer
If the MySQL User used with vBulletin has create database permissions (not recommended for production), the installation script will create your database for you. The tables created will be innodb and support the UTF8MB4 character set and collation.
If the user cannot create the database, the installation will fail unless you have manually created the database with another user.
MySQL Command Line
To create a database for your vBulletin run this query:
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Assigning A Database User
For security purposes, you want to create and assign a unique user/password combination to each of your databases. This helps to prevent access from external sources. You do not want to use your MySQL root user to access databases in a production environment.
Creating a User
To create a new user to access your database you would use this query:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
If you are using MySQL 8.0 or higher, you should use this query:
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Replace username and password with the values you want to use. If your MySQL Server is at a different IP Address than your webserver, then you will need to replace localhost with the IP Address of your webserver. These would be the values you place within the /core/includes/config.php file for vBulletin.
Granting Permissions
Once you have created the database user, you will need to give it permissions to access and manipulate the database.
grant all privileges on mydb.* to myuser@localhost;
The placeholders of mydb, myuser, and mypassword should be changed to match the values that you want to use.
Remote Access
For security purposes, you will want to restrict remote access to your database server. In the best possible scenario remote access will not be allowed to MySQL in a production environment. However, this isn't always possible in real world installations. If you do need to access your MySQL Server remotely, i.e., it is a separate physical machine from your webserver, then limit access through your firewalls, iptables, and other security systems available to your OS.
If you manage your own server, you will also want to make sure that your MySQL super user has a very strong password and isn't used to access your vBulletin database directly.
For more information, just do an internet search on securing MySQL Remote Access.
Using phpMyAdmin
phpMyAdmin is probably the most secure way to access your database. Your hosting provider would have installed it on your server along with the management tools they provide. You can access phpMyAdmin in any browser. You can learn how to use this tool on the phpMyAdmin website.
SQL Query Permissions in the vBulletin AdminCP
vBulletin ships with basic functionality that allow Administrators to run queries from within the AdminCP. Despite its lack of an interface, this a powerful tool and should be used with extreme caution. If used improperly, it can be used to delete critical data and corrupt your site. Attackers can also use this tool to gain access to your database if they can log into the AdminCP. In a default installation, this feature is disabled for all Administrators. We recommend that you do no enable it under most circumstances. If you must, then you can enable it by following these by editing the core/includes/config.php file and including your User ID (number not name) in the following option:
// ****** USERS WITH QUERY RUNNING PERMISSIONS ******
// The userids specified here will be allowed to run queries from the control panel.
// See the above entries for more information on the format.
// Please note that the ability to run queries is quite powerful. You may wish
// to remove all user IDs from this list for security reasons.
$config['SpecialUsers']['canrunqueries'] = '';
