MySQL | Magento 2 Developer Documentation

Help if you are just starting out

If you are new to all this and need some help getting started, we suggest the following:

General guidelines

Magento version 2.4.0 and later requires minimum stable MySQL 5.7.9. Magento is also compatible with MySQL NDB Cluster 7.4.x, MariaDB 10.2, 10.4, and other binary compatible MySQL technologies. Support for MySQL 8.0 provides the opportunity for merchants to deploy MariaDB 10.4 with Magento. Although merchants can still use MariaDB 10.2 with Magento 2.4.0, we recommend upgrading to MariaDB 10.4 for improved performance and reliability. MariaDB 10.0 and 10.1 are no longer supported due to the removal of support for MySQL 5.6 in this release.

Magento strongly recommends you observe the following standard when you set up your Magento database:

Magento uses MySQL database triggers to improve database access during reindexing. These get created when the indexer mode is set to schedule. Magento does not support any custom triggers in the Magento database because custom triggers can introduce incompatibilities with future Magento versions.
Familiarize yourself with these potential MySQL trigger limitations before you continue.
If you use MySQL database replication, be aware that Magento does not support MySQL statement-based replication. Make sure you use only row-based replication.

Magento 2 currently utilizes CREATE TEMPORARY TABLE statements inside transactions, which are incompatible with database implementations utilizing GTID-based replication, such as Google Cloud SQL second-generation instances.

If your web server and database server are on different hosts, perform the tasks discussed in this topic on the database server host then see Set up a remote MySQL database connection.

Installing MySQL on Ubuntu

Magento 2.4 requires a clean installation of MySQL 5.7.9 or 8.0. Follow the links below for instructions on installing MySQL on your machine.

[Ubuntu][https://ubuntu.com/server/docs/databases-mysql]
[CentOS][https://dev.mysql.com/doc/refman/8.0/en/linux-installation-yum-repo.html]

If you expect to import large numbers of products into Magento, you can increase the value for max_allowed_packet that is larger than the default, 16MB.

To increase the value, open /etc/mysql/mysql.cnf in a text editor and locate the value for max_allowed_packet. Save your changes to mysql.cnf, close the text editor, and restart MySQL (service mysql restart).

To optionally verify the value you set, enter the following command at a mysql> prompt:

SHOW VARIABLES LIKE 'max_allowed_packet';

Then, Configure the Magento database instance.

MySQL 8 changes

For Magento 2.4, we added support for MySQL 8. This section describes major changes to MySQL 8 that Magento developers should be aware of.

Removed width for integer types (Padding)

The display width specification for integer data types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) have been deprecated in MySQL 8.0.17. Statements that include data-type definitions in their output no longer show the display width for integer types, with the exception of TINYINT(1). MySQL Connectors assume that TINYINT(1) columns originated as BOOLEAN columns. This exception enables them to continue to make that assumption.

Example:

Describe admin_user at mysql 5.6

Field	Type	Null	Key	Default	Extra
user_id	int(10) unsigned	NO	PRI	NULL	auto_increment
firstname	varchar(32)	YES		NULL
lastname	varchar(32)	YES		NULL
email	varchar(128)	YES		NULL
username	varchar(40)	YES	UNI	NULL
password	varchar(255)	NO		NULL
created	timestamp	NO		CURRENT_TIMESTAMP
modified	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
logdate	timestamp	YES		NULL
lognum	smallint(5) unsigned	NO		0

Describe admin_user at mysql 8.19

Field	Type	Null	Key	Default	Extra
user_id	int unsigned	NO	PRI	NULL	auto_increment
firstname	varchar(32)	YES		NULL
lastname	varchar(32)	YES		NULL
email	varchar(128)	YES		NULL
username	varchar(40)	YES	UNI	NULL
password	varchar(255)	NO		NULL
created	timestamp	NO		CURRENT_TIMESTAMP	DEFAULT_GENERATED
modified	timestamp	NO		CURRENT_TIMESTAMP	DEFAULT_GENERATED on update CURRENT_TIMESTAMP
logdate	timestamp	YES		NULL
lognum	smallint unsigned	NO		0

With the exception of TINYINT(1), all integer padding (TINYINT > 1, SMALLINT, MEDIUMINT, INT, BIGINT) should be removed from the db_schema.xml file.

For more information, see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html#mysqld-8-0-19-feature.

Default ORDER BY behavior

Before 8.0, entries were sorted by the foreign key. Default sort order depends on the engine that is used. Always specify a sort order if your code depends on a specific sort.

Deprecated ASC and DESC qualifiers for GROUP BY

As of MySQL 8.0.13, the deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

Magento and MySQL 8

There have been some changes to Magento to properly support MySQL 8.

Query and Insert Behavior

Magento disabled the regular validation behavior by setting SET SQL_MODE=’’ in /lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php:424.. With validation disabled, it is possible that MySQL will truncate data. In MySQL, the Query behavior has changed: Select * on my_table where IP='127.0.0.1' will no longer return any results because the IP address is now properly seen as a string, rather than an integer.

Upgrading from MySQL 5.7 to MySQL 8

To properly update MySQL from version 5.7 to version 8, you must follow these steps in order:

Upgrade Magento to 2.4.0. Test everything and make sure your system works as expected.

Enable maintenance mode:

bin/magento maintenance:enable

Make a database backup:

bin/magento setup:backup --db

Update MySQL to version 8.
Import the backed-up data into MySQL.
In the Magento admin, set the database engine to MySQL 8.

Clean the cache:

bin/magento cache:clean

Disable maintenance mode:

bin/magento maintenance:disable

Configuring the Magento database instance

This section discusses how to create a new database instance for Magento. Although a new database instance is recommended, you can optionally install Magento into an existing database instance.

To configure a MySQL database instance:

Log in to your database server as any user.
Get to a MySQL command prompt:
```
1
mysql -u root -p
```
Enter the MySQL root user’s password when prompted.

Enter the following commands in the order shown to create a database instance named magento with username magento:

create database magento;

create user magento IDENTIFIED BY 'magento';

GRANT ALL ON magento.* TO magento@localhost IDENTIFIED BY 'magento';

flush privileges;

Enter exit to quit the command prompt.
Verify the database:
```
1
mysql -u magento -p
```
If the MySQL monitor displays, you created the database properly. If an error displays, repeat the preceding commands.
If your web server and database server are on different hosts, perform the tasks discussed in this topic on the database server host then see Set up a remote MySQL database connection.

We recommend you configure your database instance as appropriate for your business. When configuring your database, please keep the following in mind:
- Indexers require higher tmp_table_size and max_heap_table_size values (e.g., 64M). If you configure the batch_size parameter, you can adjust that value along with the table size settings to improve indexer performance. Refer to the Magento Optimization Guide for more information.
- For optimal performance, make sure all MySQL and Magento index tables can be kept in memory (e.g., configure innodb_buffer_pool_size).
In order for MySQL TIMESTAMP fields to follow the preferences and composition expected by Magento’s declarative schema architecture, the system variable explicit_defaults_for_timestamp must be set to on.

References;
- MySQL 5.7
- MariaDB
If this setting is not enabled, setup:db:status will always report that Declarative Schema is not up to date.