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:

• Is the Magento software installed already?
• What is the software that the Magento server needs to run?
• What operating system is my server running?
• How do I log in to my Magento server using a terminal, command prompt, or SSH?

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

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
• Installing MySQL 5.6 on Ubuntu 14
• Installing MySQL 5.6 on Ubuntu 12

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
   11
   12
   13
    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.

   1
    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';
   

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

   1
    mysql -u root -p
   

   Sample output:

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
    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.

   1
    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';
   

6. 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
   2
   3
   4
    apt-get -y update
    apt-add-repository ppa:ondrej/mysql-5.6
    apt-get -y update
    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
   11
   12
   13
    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.

   1
    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';
   

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

1
2
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:

   1
   2
    yum -y install mysql-community-server
    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
   

   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
   2
   3
    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:

   1
   2
   3
    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:

   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
   9
   10
   11
    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.

   1
    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';
   

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

   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.

Related topics

• Set up a remote MySQL database connection
• Installing optional software
• Apache
• PHP 5.5, 5.6, or 7.0—Ubuntu
• PHP 5.5, 5.6, or 7.0—CentOS
• Configuring security options
• How to get the Magento software