Announcement

Collapse
No announcement yet.

vBulletin 5 Database Best Practices

Collapse
X
Collapse
  •  

  • vBulletin 5 Database Best Practices

    This is part of a best practices series to manage your vBulletin installation.

    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.

    More Information

    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'] = '';
    
      Posting comments is disabled.

    About the Author

    Collapse

    Wayne Luke A curious juxtaposition of nature, technology and sustainability. Find out more about Wayne Luke

    Article Tags

    Collapse

    advanced (5) android (2) api (29) array (17) beginner (17) blog (4) calendar (2) cloud (1) cms (2) create (1) customization (1) database (1) forum (3) forums (4) Intermediate (7) iphone (3) mapi (30) methods (10) mobile (34) poll (1) security (2) style (2) threads (4) vb5howto (5) vBulletin (5)

    Latest Articles

    Collapse

    • vBulletin 5 Database Best Practices
      by Wayne Luke
      This is part of a best practices series to manage your vBulletin installation. 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. ...
      Wed 31st Oct '18, 7:18am
    • vBulletin Password Handling
      by Wayne Luke
      Note: vBulletin Cloud sites cannot use custom password schemes at this time. The core security of your site is the User Password and how it is stored. In the beginning, vBulletin used a simple MD5 hash to represent the password. However as Floating Point Processors (i.e. GPU and ASICs) have become more powerful, this method proved to be risky and reduced security. If we significantly changed the password scheme, then users wouldn't be able to login and would need to change their passwords fir...
      Sat 27th Oct '18, 12:34pm
    • Creating the Sitemap XML for your vBulletin
      by Wayne Luke
      The XML Sitemap specification allows search engines to index your site more efficiently. vBulletin 5 Connect can create the Sitemap automatically so you can submit it to your favorite search engines. Using the default path If you are using vBulletin Cloud, you must use this option. The default value for this is core/store_sitemap. Make sure the directory is CHMOD 0777 on your server. In the AdminCP, go to Settings -> Options -> XML Sitemap and set Enable Automatic Sitemap Generation to Yes. ...
      Sat 27th Oct '18, 12:18pm
    • Installing Memcached for vBulletin
      by Wayne Luke
      Note: Installing and using Memcached requires access to the command line and the ability to install software on your server. If you are using a Shared Hosting Package, then you may not have access to this capability. If you are in doubt, please contact your hosting provider. If you are running vBulletin in a Virtual Machine or on a Dedicated Server, you can improve performance with Memcached. This allows you to move some of the caching systems from vBulletin's database and into memory. On the s...
      Sat 27th Oct '18, 12:00pm
    • Using Tools.php
      by Wayne Luke
      Within your vBulletin Download Package, we provide a file called tools.php, this file isn't uploaded to the server by default as it is considered to be a significant security risk. However, there are times when you need to change specific settings and aren't able to access your AdminCP directly. Uploading to the Server It is recommended that tools.php is installed in the /core/install directory. To do this, follow the steps below: Connect to your server with your favorite SFTP or SCP client. In you...
      Sat 27th Oct '18, 11:45am
    • Third-Party Logins: Twitter
      by Wayne Luke
      You will need to use an existing twitter account in order to use this functionality.

      Create an app using your twitter account

      In your browser go to https://apps.twitter.com/app/new

      Check App Settings

      Go to the application settings (e.g. https://apps.twitter.com/app/12345/settings where 12345 references the app created in step 2. You can access the settings by going to the list of
      your apps (https://apps.twitter.com), clicking on the app link, then clicking...
      Tue 10th Apr '18, 9:00am
    Working...
    X