What is Magento Database, and How To Manage It?

• Which Database Does Magento Use?

• What is MySQL?

• What is the Entity-Attribute-Value (EAV) model?

• Main Modules of Database System in Magento

• Managing Magento Database

• Connect to the Database

• Connect to the Database Port

• FAQs

• Summary

Which Database Does Magento Use?

Magento operates on the MySQL database management system.

In version 2.4, Magento now supports MySQL 8. This newer version is up to twice as fast as the before supported MySQL 5.7.

What is MySQL?

MySQL is an open-source relational database managed by Oracle Corporation. It uses SQL for content management. Users can change the software under the GNU GPL v2 license. Oracle provides commercial MySQL servers for easier database management. MySQL offers high-performance databases suitable for heavy-load production systems. 

Companies like Uber, Amazon, Netflix,  Airbnb, use MySQL in their tech stacks. The client-server architecture of MySQL is ideal for networked environments. It allows remote client systems to communicate with the server.

What is the Entity-Attribute-Value (EAV) model?

The Entity-Attribute-Value (EAV) model is a data model. It encodes entities with many attributes. It's also called a vertical database model. In EAV, each attribute-value pair describing an entity has to sort in a single row. While entities may have many attributes, the actual values have limitations.

In Magento, entities include products, categories, customers, and orders. These entities have attributes. Attributes cover name, price, SKU, and size. The EAV data model offers flexibility by allowing vertical growth in database tables.

It helps avoid frequent schema redesigns when addition of new attributes occurs. This scalability benefits Magento store owners. It enables custom attributes without disrupting the database structure.

Main Modules of Database System in Magento

1. Customer

Organization of the customer database occurs in Magento under the EAV model. It includes many tables.

The customer_entity table stores main customer information, including various fields:

• entity_id: primary key

• is_active: active status

• entity_type_id: primary key of table eav_entity_type

• email: customer’s email

• attribute_set_id: primary key of table eav_attribute_set

• create_at, update_at: time created, time last updated for customer

• website_id, store_id, group_id: foreign keys linking to corresponding tables: core_website, core_store_id, customer_group.

Customer address tables use a storage method similar to primary table for customer_address_entity They also include the parent_id field to link to the customer_entity table.

2. Catalog

In Magento, the module catalog database utilizes the EAV model. This model organizes data into categories and products.

To store category information, use the catalog_category_entity as the primary table. Use tables prefixed with catalog_category as secondary tables. The parent_id in this table signifies the category relationships.

The tables catalog_category_product display the product-category relationship. They show which product belongs to each category.

3. Sales

The module Magento Sales database stores essential purchasing information. It includes Quote, Invoices, Order, Shipment, & Creditmemo. A relational database model stores this data.

• Quote

Contains order details in a shopping cart. Primary table: sales_flat_quote. More tables: sales_flat_quote_address, sales_flat_quote_item, sales_flat_quote_payment for address, items, and payment info.

• Invoice

Storage of invoice information post-order processing in three main tables:

sales_flat_invoice for primary data, sales_flat_invoice_item for item details. And sales_flat_invoice_grid for reporting and analysis data.

• Order

Includes order information after customer confirmation. Primary table: sales_flat_order. Secondary table stores extra necessary details.

• Shipment & Creditmemo

The table structuring is like an invoice. It stores order details after administrative actions like shipment or cancellation. Key tables involved include sales_flat_shipment and sales_flat_creditmemo. The system details resemble those of an invoice.

Managing Magento Database

The Cloud Docker environment offers MySQL services via MariaDB or MySQL in Docker containers. Access the database with docker compose commands and import data from Adobe Commerce projects using magento-cloud db:dump.

Connect to the Database

Connect to the database via Docker container or database port. Locate database credentials in .docker/config.php file in database section before starting. Default credentials used in the procedure are:

    'MAGENTO_CLOUD_RELATIONSHIPS' => base64_encode(json_encode([
        'database' => [
            [
                'host' => 'db',
                'path' => 'magento2',
                'password' => 'magento2',
                'username' => 'magento2',
                'port' => '3306'
            ],
        ],
        // The following configuration is available if you are using the split database architecture.
        'database-quote' => [
            [
                'host' => 'db-quote',
                'path' => 'magento2',
                'password' => 'magento2',
                'username' => 'magento2',
                'port' => '3306'
            ],
        ],
        'database-sales' => [
            [
                'host' => 'db-sales',
                'path' => 'magento2',
                'password' => 'magento2',
                'username' => 'magento2',
                'port' => '3306'
            ],
        ],

For connecting to the database with Docker commands:

1. Access the CLI container

docker compose run --rm deploy bash

2. Log in to the database using a username and password

mysql --host=db --user=magento2 --password=magento2

In case of split database architecture: mysql --host=db-quote --user=magento2 --password=magento2

mysql --host=db-sales --user=magento2 --password=magento2

3. Check the version of the database service

SELECT VERSION(); +--------------------------+ | VERSION() | +--------------------------+ | 10.0.38-MariaDB-1~xenial | +--------------------------+

Connect to the Database Port

1. Locate the database port,

It may vary each time Docker is restarted. docker compose ps

Sample response can be:

--------------------------------------------------------------------------------------------------
magento-cloud_db_1          docker-entrypoint.sh mysqld      Up       0.0.0.0:32769->3306/tcp

The following lines are available if you are using the split database architecture.

magento-cloud_db-quote_1    docker-entrypoint.sh mysqld      Up       0.0.0.0:32873->3306/tcp
magento-cloud_db-sales_1    docker-entrypoint.sh mysqld      Up       0.0.0.0:32874->3306/tcp

2. Connect to the database using the port information

mysql -h127.0.0.1 -P32769 -umagento2 -pmagento2

In case you choose the split database architecture, you need to use the following ports for connecting: For 'db-quote' service:

For 'db-sales' service:
mysql -h127.0.0.1 -32874 -umagento2 -pmagento2

Check the database service version:
SELECT VERSION();
+--------------------------+
| VERSION()                |
+--------------------------+
| 10.0.38-MariaDB-1~xenial |
+--------------------------+

FAQs

1. How can a developer configure Magento to use a different database server?

A developer can configure Magento to use a different database server by editing the env.php file located in the <Magento_root>/app/etc directory. 

In this file, you'll find the database connection details under the 'db' array. Update the 'host', 'username', 'password', and 'dbname' keys with your new database server's information. After saving these changes, clear the cache for the updates to take effect.

2. What is the best way to upgrade Magento to the latest version while ensuring database integrity?

The best way to upgrade Magento while ensuring database integrity is first to ensure that you have a complete backup of your files and database. After that, use the Magento 2 command line interface to upgrade the system. 

During the upgrade process, Magento manages the changes in the database structure. It's also recommended that this occurs on a development platform first before deploying it to production to test for any issues.

3. Where can I find the Magento 2 database structure for developing custom modules?

The Magento 2 database structure is available within the Magento Developer Documentation. Exploring the vendor/magento folder in your Magento installation directory will reveal various module directories. 

These directories include Setup scripts that define their respective database schemas and data. Understanding these scripts can provide insights into Magento's database structure, which is essential for developing custom modules.

4. How do I install Magento 2 on Adobe Commerce on Cloud Infrastructure?

To install Magento 2 on Adobe Commerce on Cloud Infrastructure, start by cloning the Magento project to your local environment. Navigate to your project directory. Use the Magento CLI to install the software.  Ensure you have the correct permissions set for your pub and var folders. Detailed installation instructions and prerequisites are available in the official Adobe Commerce documentation.

5. How do I configure the admin panel to manage folders and directories after installing Magento?

After you install Magento, you can configure the admin panel. It lets you manage media storage, folders, and directories. You can do this via the System Configuration settings.

Navigate to Stores -> Configuration -> Advanced -> System. 

Find the section on File Storage Configuration. Here, you can manage media file storage in Magento. Also, manage other configuration settings related to directory management.

Summary

A MYSQL Magento database with an EAV model enhances data management. Key features are:

• Ensures flexibility and scalability

• Facilitates large data volumes

• Management through Docker

• Includes modules: Customer, Catalog, Sales

Discover the benefits of managed Magento hosting for seamless database management.