Prepare your Mattermost MySQL database

plans-img Available on all plans

deployment-img self-hosted deployments


PostgreSQL is our preferred database of choice. See the database software documentation for details on database version support.

Set up the Mattermost MySQL database

To set up a MySQL database for use by the Mattermost server:

  1. Log into the server that will host the database, ands install MySQL.

  2. Log in to MySQL as root by running sudo mysql.

  3. Create the Mattermost user mmuser by running mysql> create user 'mmuser'@'%' identified by 'mmuser-password';.

  • Use a password that is more secure than mmuser-password.

  • The % means that mmuser can connect from any machine on the network. However, it’s more secure to use the IP address of the machine that hosts Mattermost. For example, if you install Mattermost on the machine with IP address, then use the following command: mysql> create user 'mmuser'@'' identified by 'mmuser-password';

  1. Create the Mattermost database by running mysql> create database mattermost;.

  2. Grant access privileges to the user mmuser by running mysql> grant all privileges on mattermost.* to 'mmuser'@'%';.


This query grants the MySQL user we just created all privileges on the database for convenience. If you need more security, use the following query to grant the user only the privileges necessary to run Mattermost: mysql> GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE, REFERENCES ON mattermost.* TO 'mmuser'@'%';

  1. Log out of MySQL by running mysql> exit. Once the database is installed and the initial setup is complete, you can install the Mattermost server.


If you have installed MySQL on its own server, edit the /etc/mysql/mysql.conf.d/mysqld.cnf file and comment out the bind-address = using the # symbol, then restart your database server.

Back up the database

Back up your Mattermost database using standard procedures depending on your database version. MySQL backup documentation is available online. Use the selector on the page to choose your MySQL version.

Upgrade Mattermost

Mattermost v7.1 introduces schema changes in the form of a new column and its index. Our test results for the schema changes include: 12M Posts, 2.5M Reactions - ~1min 34s (instance: PC with 8 cores, 16GB RAM).

You can run the following SQL queries before the upgrade that obtains a lock on Reactions table.

ALTER TABLE Reactions ADD COLUMN ChannelId varchar(26) NOT NULL DEFAULT "";

UPDATE Reactions SET ChannelId = COALESCE((select ChannelId from Posts where Posts.Id = Reactions.PostId), '') WHERE ChannelId="";

CREATE INDEX idx_reactions_channel_id ON Reactions(ChannelId) LOCK=NONE;

Users’ reactions posted during this time won’t be reflected in the database until the migrations are complete. This is fully backwards-compatible.

If your connection collation and table collations are different, this can result in the error Illegal mix of collations. To resolve this error, set the same collation for both the connection and the table. There are different collations at different levels - connection, database, table, column, and database administrators may choose to set different collation levels for different objects.

High availabiilty configuration setting recommendations

For MySQL, we recommend the following configuration options for high performance:

  • innodb_buffer_pool_size: Set to ~70% of your total RAM.

  • innodb_log_file_size: Set to 256 MB. Increasing this helps in write intensive operations. Recovery times will be longer.

  • innodb_flush_log_at_trx_commit: 2. This can potentially cause up to one second of loss of transaction data.

  • max_heap_table_size: 64 MB.

  • tmp_table_size: 64 MB.


Encryption-at-rest is available for messages via hardware and software disk encryption solutions applied to the Mattermost database, which resides on its own server within your infrastructure. See the MySQL database documentation for details on encryption options at the disk level.

Use sockets for the database

$ mysql -u root -p
CREATE DATABASE mattermostdb;
CREATE USER mmuser IDENTIFIED BY 'mmuser_password';
GRANT ALL ON mattermostdb.* TO mmuser;

Mattermost is configured in /etc/webapps/mattermost/config.json, and strings need to be quoted.

  • set DriverName to mysql.

  • set DataSource to mmuser:mmuser_password@unix(/run/mysqld/mysqld.sock)/mattermostdb?charset=utf8mb4,utf8.

Mattermost configuration in the database

You can use the database as the single source of truth for the active configuration of your Mattermost installation. This changes the Mattermost binary from reading the default config.json file to reading the configuration settings stored within a configuration table in the database.


Create an environment file


If you’re running Mattermost in a High Availability cluster, this step must be done on all servers in the cluster.

Create the file /opt/mattermost/config/mattermost.environment to set the MM_CONFIG environment variable to the database connection string. For example:



Be sure to escape any single quotes in the database connection string by placing a \ in front of them like this \'. For example: MM_CONFIG='mysql://mmuser:it\'s-a-password!@tcp(,utf8&writeTimeout=30s'


Finally, run this command to verify the permissions on your Mattermost directory:

sudo chown -R mattermost:mattermost /opt/mattermost

Modify the Mattermost systemd file

First, find the mattermost.service file using:

sudo systemctl status mattermost.service

The second line of output will have the location of the running mattermost.service.

Loaded: loaded (/lib/systemd/system/mattermost.service; enabled; vendor preset: enabled)

Edit this file as root to add the below text just above the line that begins with ExecStart:


Here’s a complete mattermost.service file with the EnvironmentFile line added:




Technical notes about searching

By default, Mattermost uses full text search support included in MySQL. Select the product menu |product-list| then select About Mattermost to see which database you’re using.

  • Stop words are filtered out of search results. See MySQL database documentation for a full list of applicable stop words.

  • Hashtags or recent mentions of usernames containing a dot don’t return results.

  • Avoid using underline _ symbol to perform a wildcard search. Use the asterisk * symbol instead.

  • Stop words that are excluded from search in MySQL include: "a", "about", "an", "are", "as", "at", "be", "by", "com", "de", "en", "for", "from", "how", "i", "in", "is", "it", "la", "of", "on", "or", "that", "the", "this", "to", "was", "what", "when", "where", "who", "will", "with", "und", "the", "www".

Perform searches in Chinese, Korean, and Japanese

The best experience for searching in Chinese, Korean, and Japanese is to use MySQL 5.7.6 or later with special configuration. See the Chinese, Japanese and Korean Search documentation for details.

You can perform searches without this configuration by adding wildcards * to the end of search terms.

Migrate from Bitnami to a self-hosted Mattermost deployment

If you’re planning a migration from Bitnami to a self-hosted Mattermost installation with a MySQL database, read these notes in our migration guide: Migrating from Bitnami.

Downgrade Mattermost v6.0 to v5.38

INSERT INTO Systems (Name,Value) VALUES ('Version','5.38.0') ON DUPLICATE KEY UPDATE Value = '5.38.0';

CREATE INDEX idx_status_status ON Status (Status);
DROP INDEX idx_status_status_dndendtime ON Status;
CREATE INDEX idx_channelmembers_user_id ON ChannelMembers (UserId);
DROP INDEX idx_channelmembers_channel_id_scheme_guest_user_id ON ChannelMembers;
DROP INDEX idx_channelmembers_user_id_channel_id_last_viewed_at ON ChannelMembers;
CREATE INDEX idx_threads_channel_id ON Threads (ChannelId);
DROP INDEX idx_threads_channel_id_last_reply_at ON Threads;
CREATE INDEX idx_channels_team_id ON Channels (TeamId);
DROP INDEX idx_channels_team_id_type ON Channels;
DROP INDEX idx_channels_team_id_display_name ON Channels;
CREATE INDEX idx_posts_root_id ON Posts (RootId);
DROP INDEX idx_posts_root_id_delete_at ON Posts;

ALTER TABLE CommandWebhooks ADD COLUMN ParentId varchar(26);
UPDATE CommandWebhooks SET ParentId = '';
ALTER TABLE Posts ADD COLUMN ParentId varchar(26);
UPDATE Posts SET ParentId = '';

ALTER TABLE Users MODIFY Timezone text;
ALTER TABLE Users MODIFY NotifyProps text;
ALTER TABLE Users MODIFY Props text;
ALTER TABLE Threads MODIFY Participants longtext;
ALTER TABLE Sessions MODIFY Props text;
ALTER TABLE Posts MODIFY Props text;
ALTER TABLE LinkMetadata MODIFY Data text;
ALTER TABLE ChannelMembers MODIFY NotifyProps text;


The inverse of the final v6.0 upgrade query is intentionally omitted from these downgrade queries because its result is backwards compatible, and running the query would unnecessarily delay the downgrade process.