Setting Up WooCommerce MySQL Integration: 3 Easy Methods

WooCommerce is an open-source E-Commerce platform that is built on top of WordPress. WooCommerce helps businesses to set up eCommerce websites easily. It supports all the standard functionalities needed by small and medium businesses. WooCommerce offers great customizability while creating storefront designs. It provides many plugins that can be used to easily integrate functionalities like dynamic pricing, subscriptions, etc. 

WooCommerce also provides a number of add-on services to handle other tasks related to running an E-Commerce organization like shipping, delivery, order tracking, etc. In this article, you will learn about the 3 methods to set up WooCommerce MySQL Integration.

Prerequisites

• Access to WooCommerce instance through FTP.
• Access to WooCommerce admin dashboard. 
• Basic understanding of MySQL.

What is WooCommerce?

WooCommerce is an open-source E-Commerce plugin by WordPress. Using the WooCommerce plugin, you can add basic E-Commerce functionalities to your WordPress site such as payments, shipping, order management, etc. WooCommerce allows users to set up their E-Commerce site or online store without any coding knowledge. 

WooCommerce also offers Reporting and Data Analytics features to get an overview of the performance of the E-Commerce site. The customizable dashboards and advanced segmenting tools add more value to it. Around 90% of the E-Commerce sites on WordPress use WooCommerce.

Key Features of WooCommerce

Some of the main features of WooCommerce are listed below:

• Inventory Management: WooCommerce helps users to manage their inventory and keep track of stock levels, auto-hide out-of-stock items, and get notification alerts of low stock, etc.
• Customizable Themes: WooCommerce allows its users to flexibly customize the store using pre-built themes options with the drag-drop feature.
• Reporting: WooCommerce makes it easier for users to keep track of store performance and analyze the Sales, refunds, winning products, and other metrics using its Reporting tool.

To know more about WooCommerce, click here.

What is MySQL?

MySQL is an open-source Relational Database Management System (RDBMS) that allows users to store and organize data into one or more tables that are related to each other using some logical relation. It uses SQL (Structured Query Language) to create, modify, and extract data from the Relational Database. 

MySQL helps organizations store and retrieve their business data using SQL queries or connecting it with applications. It supports OS and platforms such as Linux, Mac OS X, Windows, Free BSD, Solaris, and others. 

Key Features of MySQL

Some of the main features of MySQL are listed below:

• Data Protection: MySQL allows only authorized users to access data and offers high-grade security features. 
• Scalability: MySQL Database is capable to handle embedded applications and Data Warehouses that run terabytes of data. 
• High Performance: MySQL delivers high-performance ad it is easy to configure. It allows users to configure databases for applications to increase performance.

To know more about MySQL, click here.

Need for WooCommerce MySQL Integration

Since WooCommerce is built on top of WordPress, it uses the same database used by WordPress, which is MySQL. The question then is, if WooCommerce already stores its data in MySQL, what is the need to connect it separately with MySQL. 

This is because the storefront database is a transactional database and it is not a good idea to use it for analytics. Having the data in a separate database allows analysts to query the data without worrying about putting a load on the database that powers the storefront. 

Hence most organizations choose to extract, transform and load the data from a transactional database to a separate database in a format suitable for analysis. Since MySQL has a strong querying layer, it finds itself used as an analytical database too in many cases. Hence the need to integrate WooCommerce MySQL.

Setting Up WooCommerce MySQL Integration

You can set up WooCommerce MySQL Integration in 3 ways listed below:

• Method 1: WooCommerce MySQL Integration using Hevo’s No-Code Data Pipeline
• Method 2: Using the REST APIs
• Method 3: Directly Accessing the WooCommerce Database

Method 1: WooCommerce MySQL Integration using Hevo’s No-Code Data Pipeline

Hevo Data helps you directly transfer data from WooCommerce via MySQL to Data Warehouse in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Hevo takes care of all your data preprocessing needs required to set up WooCommerce MySQL Integration and lets you focus on key business activities.

Follow Hevo’s WooCommerce MySQL Integration guide for connecting WooCommerce MySQL using No-Code Data Pipeline. 

Advantages of using Hevo Data Platform:

• Minimal Setup – You will require minimal setup and bandwidth to load data from WooCommerce MySQL using the Hevo platform. 
• No Data Loss – Hevo architecture is fault-tolerant and allows easy, reliable, and the seamless transfer of data from WooCommerce MySQL without data loss. 
• 100’s of Out of the Box Integrations – Hevo platform brings data from other sources such as SDKs, Cloud Applications, Databases, and so on into Data Warehouses and Databases. So, Hevo is the right partner for all your growing data needs.
• Automatic Schema Detection and mapping – The schema of incoming data is scanned automatically.  If there are changes detected, they are handled seamlessly and the changes are incorporated into the Database or Data Warehouse. 
• Exceptional Support – Hevo has 24×7 Technical support through emails, calls, and chat.

Sign up here for a 14-Day Free Trial!

Method 2: Using REST APIs

In the case of a completely managed WooCommerce instance, it is better to use the REST APIs exposed by the WooCommerce database. You will now try to access the REST APIs and load the customer data to your MySQL table.

• Head to the ‘Advanced’ section in your WooCommerce dashboard to create an API key for accessing the REST API.

• Click ‘Add key’ , enter the user details and description, and click ‘Generate API Key’

• Note the generated Consumer Key and Consumer secret to use with your REST API calls.
• Use the below command to access the list of customers from your WooCommerce instance

curl https:/<your_woocommerce_instance>/wp-json/wc/v3/customers 
    -u consumer_key:consumer_secret >> cust_list.json

• Ensure that you replace the consumer_key and secret with what was generated in the previous step.
• Use the below command to extract the id, email and first_name from the JSON file to CSV.

jq -r '.[] | [.id,.email,.first_name] | @csv' cust_list.json >> cust_list.csv

The above command uses a utility called ‘jq’ to convert the JSON to CSV from Linux command line.

• This CSV can then be loaded to a table using the ‘LOAD DATA’ command. Assuming you have already created a table with the required structure, this can be done using the below command in the MySQL shell.

LOAD DATA INFILE '<path_to_csv_file>'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';

Once the last command is successfully executed, you can find the customer data inserted into MySQL.

Limitations of REST API Method

While the REST API gave us the data in a structured format, this was still not a seamless exercise. In summary, this method to WooCommerce MySQL Integration has the below limitations.

• The above approach is good for a one-off load, but to make this production-ready, a script has to be created using the above building blocks. This will require quite a lot of developer effort.
• Synchronizing only the newly added entries and not the complete customer table every time will require even more steps to be added to this approach. 
• For large tables, this will be a time-consuming process, the script will need logic to restart in case it fails or loses connection. 

Method 3: Directly Accessing WooCommerce Database

Accessing the WooCommerce database directly requires one to have knowledge of how WooCommerce uses WordPress to get things done.

WordPress uses MySQL as the default database. It uses a table called ‘wp_posts’ to store the blog posts. It may seem counter-intuitive to read about storing blog posts while talking about an ECommerce plugin, but the answer to that lies in the fact that WordPress is primarily a content management system.

Hence its table structure is designed accordingly.  Since WooCommerce is built on top of WordPress, it uses the same tables that WordPress uses. Most WooCommerce entities like Orders, Products, etc are stored in the ‘wp_posts’ table with a post type to distinguish between them. 

For example, orders are stored in the ‘wp_posts’ table with post_type as ‘shop_order’. If you want the details of your customers, you need to find the order information first, then join the post_id of that order with the ‘wp_meta’ table that stores metadata about posts. The ‘wp_meta’ table is a tall table with ‘meta_key’ and ‘meta_value’ as tables. You will find all the required attributes for the customers like first name, last name, address, etc in this table. 

Now that we understand the table structure, let us explore how data from this MySQL database can be loaded to your own MySQL instance.

1. Use the FTP client that you use to administer your WordPress instance and go to the root folder. Locate the wp-config.php file and open it.
2. Scroll down to find the details of the MySQL instance used by WordPress. It will look as below.

3. Note the values for DB_HOST, DB_NAME, DB_USER, and DB_PASSWORD and use the below query. 
4. Use SSH to log in to the instance where WooCommerce is installed and type the below command.

sudo mysqldump -u root --databases 'DB_NAME' --single-transaction --compress --order-by-primary --password='DB_PASSWORD' | sudo mysql -u 'targetDBusername' --host 'targetDBHost' --password

Ensure to replace use the username and hostnames of the source database and target database as appropriate places. You may also want to add the IP of your WordPress instance to the allowed_hosts list in your target database. 

Once the command is executed, a copy of the WooCommerce database will be created in your target MySQL instance. 

Limitations of Accessing WooCommerce Database Method

As evident from the process detailed so far, this is not the easiest to execute. This method to WooCommerce MySQL Integration also comes with a number of other limitations

1. This is possible only on self-hosted WooCommerce instances. If you are using a completely managed instance, it will be tough to use this method. 
2. This method takes a complete dump of all the data every time you need to sync WooCommerce MySQL data.

Conclusion

In this article, you learnt about the 3 two approaches to provide basic building blocks to set up WooCommerce MySQL Integration. The last 2 methods to WooCommerce MySQL Integration require a lot of development effort to transform this into a production-ready form. If you want to integrate WooCommerce MySQL through a code-free method, consider using Hevo. It is a completely managed ETL tool that can facilitate data transfer between most of the common sources and the target database. 

Visit our Website to Explore Hevo

To stay ahead of your competition, E-Commerce site and Marketing data should be available in the analysis-ready form to perform analysis. Hevo Data offers a No-code Data Pipeline solution to transfer all your WooCommerce data to MySQL or any Data Warehouse. It fully automates the process of transferring data from WooCommerce and 100+ sources to any Data Warehouse without writing a single line of code. 

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Share your experience of learning about WooCommerce MySQL Integration in the comments section below!