MySQL

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

The Magento application requires MySQL 5.6.x. Magento versions 2.1.2 and later are compatible with MySQL 5.7.x. Magento is also compatible with MySQL NDB Cluster 7.4.x, MariaDB 10.0, 10.1, 10.2, Percona 5.7 and other binary compatible MySQL technologies.

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

See one of the following sections for more information:

Installing and configuring MySQL 5.7 on Ubuntu 16

This section discusses how to install MySQL 5.7 on Ubuntu 16.

The Magento application 2.1.2 and later are compatible with MySQL 5.7.

To install MySQL 5.7 on Ubuntu 16:

  1. Enter this command:

    1

    sudo apt install -y mysql-server mysql-client

  2. Start MySQL:

    1

    sudo service mysql start

  3. Secure the installation:

    1

    sudo mysql_secure_installation

  4. Test the installation:

    1

    mysql -u root -p

    Sample output:

    1
2
3
4
5
6
7
8
9
10

    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45 Server version: 5.6.19-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

  5. 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:

1

SHOW VARIABLES LIKE 'max_allowed_packet';
  1. Configure the Magento database instance.

Installing MySQL 5.6 on Ubuntu 14

To install MySQL 5.6 on Ubuntu 14:

  1. Enter this command:

    1

    apt-get -y install mysql-server-5.6 mysql-client-5.6

  2. Start MySQL:

    1

    sudo service mysql start

  3. Secure the installation:

    1

    mysql_secure_installation

  4. Test the installation by entering the following command:

    1

    mysql -u root -p

    Sample output:

    1
2
3
4
5
6
7
8
9
10

    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45 Server version: 5.6.19-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

  5. 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:

1

SHOW VARIABLES LIKE 'max_allowed_packet';
  1. Configure the Magento database instance.

Installing MySQL 5.6 on Ubuntu 12

To install MySQL 5.6 on Ubuntu 12, use the following instructions from askubuntu.com.

  1. Enter the following commands in the order shown:

    1

    apt-get -y update

    1

    apt-add-repository ppa:ondrej/mysql-5.6

    1

    apt-get -y update

    1

    apt-get -y install mysql-server

  2. Start MySQL:

    1

    sudo service mysql start

  3. Secure the installation:

    1

    mysql_secure_installation

  4. Test the installation:

    1

    mysql -u root -p

    Messages similar to the following display:

    1
2
3
4
5
6
7
8
9
10

    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43 Server version: 5.6.21-1+deb.sury.org~precise+1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

  5. 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:

1

SHOW VARIABLES LIKE 'max_allowed_packet';
  1. Configure the Magento database instance.

Installing and configuring MySQL 5.7 on CentOS

This section discusses how to install MySQL 5.7 on CentOS 6 or CentOS 7.

The Magento application 2.1.2 and later are compatible with MySQL 5.7.

Get MySQL 5.7 for CentOS 7

The following procedure is based on How to Install Latest MySQL 5.7.9 on RHEL/CentOS 7/6/5 and Fedora 23/22/21.

As a user with root privileges, enter the following commands in the order shown:

1

wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

1

yum -y localinstall mysql57-community-release-el7-7.noarch.rpm

Continue with Install and configure MySQL 5.7 on CentOS 6 or 7.

Get MySQL 5.7 for CentOS 6

The following procedure is based on How to Install Latest MySQL 5.7.9 on RHEL/CentOS 7/6/5 and Fedora 23/22/21.

As a user with root privileges, enter the following commands in the order shown:

1

wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

1

yum -y localinstall mysql57-community-release-el6-7.noarch.rpm

Continue with the next section.

Install and configure MySQL 5.7 on CentOS 6 or 7

  1. Enter the following commands in the order shown:

    1

    yum -y install mysql-community-server

    1

    service mysqld start

  2. Verify the version:

    1

    mysql --version

    Sample output follows:

    1

    mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper

  3. Get the temporary database root user password:

    1

    grep 'temporary password' /var/log/mysqld.log

  4. Secure the installation:

    1

    mysql_secure_installation

    1

    mysql_secure_installation

    Follow the prompts on your screen to set a new password and configure other options.

  5. Configure MySQL 5.7 as discussed in Configuring the Magento database instance.

Installing and configuring MySQL 5.6 on CentOS

The following procedure is based on Install MySQL Server 5.6 in CentOS 6.x and Red Hat 6.x Linux.

  1. CentOS 6 Install the MySQL database:

    1

    yum -y update

    1

    sudo wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm && sudo rpm -ivh mysql-community-release-el6-5.noarch.rpm

    1

    sudo yum -y install mysql-server

  2. CentOS 7 Install the MySQL database:

    1

    yum -y update

    1

    sudo wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm && sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

    1

    sudo yum -y install mysql-server

  3. Start MySQL:

    1

    service mysqld start

  4. Set a password for the root user and set other security-related options. Enter the following command and follow the prompts on your screen to complete the configuration:

    1

    mysql_secure_installation

  5. Verify the MySQL server version:

    1

    mysql -u root -p

    Messages similar to the following display:

    1
2
3
4
5
6
7
8

    Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15 Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  6. If you expect to import large numbers of products into Magento, you can configure MySQL to use the max_allowed_packet parameter. We recommend a value of at least 16MB.

    To increase the value, open /etc/mysql.cnf in a text editor and add search for max_allowed_packet. Set the value to 16M or larger.

If it does not exist, add it before [mysqld_safe].

Save your changes to mysql.cnf, close the text editor, and restart MySQL (service mysqld restart).

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

1

SHOW VARIABLES LIKE 'max_allowed_packet';
  1. Configure the Magento database instance as discussed in the next section.

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:

  1. Log in to your database server as any user.

  2. Get to a MySQL command prompt:

    1

    mysql -u root -p
  3. Enter the MySQL root user’s password when prompted.

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

    1

    create database magento;

    1

    create user magento IDENTIFIED BY 'magento';

    1

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

    1

    flush privileges;

  5. Enter exit to quit the command prompt.

  6. 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.

  7. 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).

Related topics

Updated
Edit this page on GitHub
Give us feedback
© 2020 Magento. All rights reserved.