Close [x]

MySQL

Edit this page on GitHub

Contents

Help if you're just starting out

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

General guidelines

Magento requires MySQL 5.6.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 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.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. 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. Start MySQL.

    service mysqld start

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

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

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

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