MySQL

Help if you're just starting out

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

General guidelines

The Magento application requires MySQL 5.6.x.

Magention versions 2.1.2 and later are compatible with MySQL 5.7.x.

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

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 the following command:

    sudo apt install -y mysql-server mysql-client
    
  2. Secure the installation.

    sudo mysql_secure_installation
    
  3. Test the installation by entering the following command:

    mysql -u root -p
    

    Sample output:

    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>
    
  4. 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';
    
  5. Configure the Magento database instance.

Installing MySQL 5.6 on Ubuntu 14

To install MySQL 5.6 on Ubuntu 14:

  1. Enter the following command:

    apt-get -y install mysql-server-5.6 mysql-client-5.6
    
  2. Secure the installation.

    mysql_secure_installation
    
  3. Test the installation by entering the following command:

    mysql -u root -p
    

    Sample output:

    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>
    
  4. 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';
    
  5. 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:

    apt-get -y update
    apt-add-repository ppa:ondrej/mysql-5.6
    apt-get -y update
    apt-get -y install mysql-server
    
  2. Secure the installation.

    mysql_secure_installation
    
  3. Test the installation by entering the following command:

    mysql -u root -p
    

    Messages similar to the following display:

    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>
    
  4. 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';
    
  5. Configure the Magento database instance.

Installing and configuring MySQL 5.7 on CentOS

This section discusses how to 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:

wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
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:

wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
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:

    yum -y install mysql-community-server
    service mysqld start
    
  2. Verify the version using the following command:

    mysql --version
    

    Sample output follows:

    mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper
    
  3. Enter the following command to get the temporary database root user password:

    grep 'temporary password' /var/log/mysqld.log
    
  4. Enter the following command to secure the installation:

    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:

    yum -y update
    sudo wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm && sudo rpm -ivh mysql-community-release-el6-5.noarch.rpm
    sudo yum -y install mysql-server
    
  2. CentOS 7 Install the MySQL database:

    yum -y update
    sudo wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm && sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
    sudo yum -y install mysql-server
    
  3. Start MySQL.

    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.

    mysql_secure_installation
    
  5. Verify the MySQL server version.

    mysql -u root -p
    

    Messages similar to the following display:

    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:

     SHOW VARIABLES LIKE 'max_allowed_packet';
    
  7. 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. Enter the following command to get to a MySQL command prompt:

    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 user name magento:

    create database magento;
    GRANT ALL ON magento.* TO magento@localhost IDENTIFIED BY 'magento';
    
  5. Enter exit to quit the command prompt.

  6. Verify the database:

    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.