WP Data Access
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
Select Page
Popular Search importsqlformqueryexport

WP Data Access

  • Getting started
  • Installation
  • What’s new?
  • Patches
  • Known limitations
  • FAQ

Data Tables

  • Getting Started
  • Creating and publishing
  • Responsive data tables
  • SEO friendly data tables
  • Adding hyperlinks
  • Extension Manager
  • Style Manager
  • Language settings
Styling
  • Style Manager
  • Premium styling
  • Global styling
  • Code Manager Styling
  • Manual styling
  • Styling issues
Filters
  • Static filters
  • Interactive filters
    • URL parameters
    • Search Panes
    • Search Builder
    • Search form
    • Advanced search
    • Geolocation search
Export & other buttons
  • Export to PDF, CSV, Excel, SQL
  • Print & copy buttons
  • Column selection buttons
  • Custom buttons
Advanced features
  • Advanced settings
  • Large tables & performance
  • Progressively SHOW MORE
  • Custom queries
  • Custom Post Types
  • Row grouping
  • Responsive master-detail
  • Master-detail tables
  • Non admin user access
Demos & tutorials
  • Demos
  • Tutorials

Data Apps

  • Data Projects
  • Projects and templates
  • Supported Data Types
  • One-To-Many relationships
  • Many-To-Many relationships
  • Registration page
  • Manage table options
  • Shortcode usage
Project Templates
  • Project Templates
  • Creating templates
  • Table Settings
  • Relationships
  • List Table
  • Data Entry
  • Reconcile
Data Forms
  • Data Forms
  • Overview
  • Project Demo
  • Page Demo
Advanced features
  • Adding filters
  • Custom validation
  • Add less/more button
  • User roles
  • Managing roles and users
  • Advanced table options
  • Advanced lookups
  • Front-end toolbar
  • Transfer to other WP site
Demos & tutorials
  • Demos
  • Tutorials

Data Explorer

  • Getting started
  • Navigation
  • Explore tables & views
  • Manage tables & views
  • Naming conventions
Manage Actions
  • Actions
Manage Settings
  • Settings
  • Search Settings
  • Geolocation Settings
  • Table Settings
  • Column Settings
  • Dynamic Hyperlinks
  • Dashboard Menus
Data Management
  • Import CSV & SQL files
  • Run SQL script files
  • Data Backup
Problems & solutions
  • InnoDB #rows estimation
  • Import file too large
  • Transfer to other WP DB

Remote Databases

  • MySQL | MariaDB
Other DBMS
  • Heterogeneous Connections
  • Using ODBC | JDBC
  • Connect to SQL Server
Premium Data Services
  • Remote wizard
  • START HERE
  • Errors and messages
  • Limitations
  • Tutorials
  • Remote Databases
    • Remote databases
    • SQL Server
    • PostgreSQL
    • Oracle
    • MariaDB | MySQL
    Remote Data Files
    • Remote data files
    • CSV files
    • JSON files
    • MS Access
    • XML files
    • Public URL
    • Synchronization
    Advanced features
    • Performance
    • Indexes

SQL

  • Getting Started
  • Tutorials
  • Visual Query Builder
  • Writing queries
  • Data exchange
  • Stored procedures
  • Database triggers
  • Scheduled queries

Business Intelligence

  • Getting started
  • Dashboards
  • Sharing dashboards
Dashboards Widgets
  • Dashboard Widgets
  • Publication Widget
  • Project Widget
  • Chart Widget
  • Custom Code Widget
  • Database Widget
  • Sharing Widgets

Table Design

  • Getting started
  • Introduction
  • Basic mode

Plugin Settings

  • Getting started
  • Plugin
  • Back-end
  • Front-end
  • Dashboard
  • Data Forms
  • Data Tables
  • Data Backup
  • Uninstall
  • Manage Repository
  • Manage Roles
  • System Info

Code

Shortcodes
  • Overview
  • wpdataaccess
  • wpdadataproject
  • wpdadataforms
  • wpdadiehard
  • wpdageomap
  • wpdawidget
Hooks & filters
  • Overview
  • Hooks
    • Overview
    • wpda_add_search_actions
    • wpda_add_search_filter
    • wpda_before_list_table
    • wpda_after_list_table
    • wpda_wpdataaccess_prepare
    Filters
    • Overview
    • wpda_column_default
    • wpda_before_simple_form
    • wpda_after_simple_form
    • wpda_construct_where_clause
API
  • WP Data Access API
  • Remote database access
  • CRUD Example
  • Extensions
    • Alternative search algorithm
    • Alternative buttons extension
Code Manager
  • Overview
  • Write code
  • Share(d) code

MySQL | MariaDB

Table of Contents
  • Local databases
  • Remote databases
  • Connecting to a local database
  • Connecting to a remote database
  • Restrictions
  • Remote connection management
  • Restricting access
  • Sensitive data
  • Remote SSL connections
  • Risks
  • Common Issues

Your WordPress installation connects to your MySQL or MariaDB (WordPress) database, which is usually installed on the same server as your web server. Custom tables and views are often created in the WordPress database. WP Data Access allows you to create and use tables and views in other (local and remote) databases as well.

Local databases #

A local database (also called schema) is located on the same physical server as your WordPress database. With WP Data Access you can directly connect to other local databases.

From the perspective of the plugin, a local database runs in the same database instance as your WordPress database. If your server runs multiple database instances, the connection to another local instance is regarded as a remote connection.

Remote databases #

A remote database is a database that is located on another physical server than your WordPress installation or a local database running in another instance.

Connecting to a local database #

Local databases are available from the listbox in the Data Explorer as shown in the image below. Only databases to which the user has access are shown.

WP Data Access - Data Explorer - Local Databases

The plugin allows users to create and drop databases from the Data Explorer in the WordPress dashboard. The plugin needs to have access and the necessary priveleges to perform these actions (CREATE and DROP). Grant access to local database wpda_demo:

grant all privileges on wpda_demo.* To 'wordpress'@'localhost';

HINT: On my development machine my WordPress user is allowed to create and drop databases. On my web server, I use remote database connections.

Connecting to a remote database #

Remote database connections have to be added manually. Click on the plus icon behind the database listbox at the left top of the Data Explorer main page:

Dashboard > WP Data Access > Data Explorer

The database name is for your own administration to identify your remote database and has to start with the prefix rdb:. This is enforced by the plugin. Below is an example of a remote database connection named rdb:remote.

WP Data Access - Remote Database Connection

IMPORTANT: The MySQL username must have access to the database (or schema) entered in the field MySQL schema. In the example above MySQL user wpda must have access to schema my_database.

The add remote database connection form allows you to test your connection. When a remote connection is saved, it becomes available in all database listboxes.

Once a remote connection is established, all plugin features available for local databases are available for the remote database as well (presuming the remote account has the necessary privileges). The remote database is available through the listbox at the top of the Data Explorer main page.

The remote connection defined above is similar to mysql cli:

mysql -h 193.168.192.101 -u wpda -p -P 3306 my_database
Enter password: wpda

You can use a remote database just like your WordPress database. The plugin handles both types of databases the same way. If you have the necessary privileges you can create, alter and drop tables, views and so on in the Query Builder, Data Explorer and Data Designer.

Remote databases are available in all tools: Data Explorer, Query Builder, Data Designer, Data Projects, Data Tables and Data Backup.

Restrictions #

A remote database must be a MySQL or MariaDB database. Connections to other database management systems are supported through the MariaDB CONNECT storage engine using ODBC and JDBC. Read more…

Remote connection management #

  • What to do if a remote database is temporarily unavailable?
  • What to do if a remote database is no longer available?
  • How to disable or modify an existing remote database connection?
  • How to remove an existing remote database connection?

The plugin settings page (available from the WordPress settings menu) allows you to disable, modify or delete remote databases.

WP Data Access - Remote Database Connections in plugin settings

Restricting access #

Make sure your remote database user is only allowed to connect from your webserver. Since your webserver has a public IP address, you can use that address to improve security:

HINT You should also do this for non-sensitive data!

grant all privileges on remote_schema.* To 'remote_user'@'211.180.78.111';

Sensitive data #

To manage sensitive data, use a local database or a secured (SSL) connection to a remote database.

Remote SSL connections #

WP Data Access - Remote SSL Connections

To use SSL the plugin needs to have access to the client key and certificate and the ca certificate. Make sure these files are readable!

When activated, connections using SSL are available in the same way as standard connections. SSL connections are available for all tools, presuming the remote account has the necessary privileges.

Risks #

By default, remote data is sent over the network in plain text. So does your database username. Only your password is encrypted. Use SSL if you want to prevent sending your username over the network.

If you are publicly showing data you retrieve from a remote database, sending your data in plain text does not seem to be an issue.

You SHOULD NOT, however, use an unsecured remote database connection to manage sensitive data!

Common Issues #

In some cases, a connection to the remote database can be established, but the connection returns no tables. The most common reason for seeing no tables is the lack of privileges. Please check:
  • If the IP address of your WordPress server is allowed to connect
  • If the user has access to the schema used in your remote database connection
  • If the user has access to the tables in that schema
Share This Article :
  • Facebook
  • Twitter
  • LinkedIn
  • Pinterest
Still stuck? How can we help?

How can we help?

Updated on 2023-01-13

2 Comments

  1. Robert Coles
    Robert Coles on 2022-12-29 at 8:05 pm

    I don’t know sql. I want to offer my website visitors access and queries to a database of boats with their characteristics. How do I start? How do I learn?

    Reply
    • Peter Schulz
      Peter Schulz on 2022-12-30 at 1:17 pm

      You could start with the tutorial page if you want to create your own pages:
      https://wpdataaccess.com/tutorials/

      Please scroll down to beginner and work through the levels down as far as you need. But you might as well find yourself a database developer who does it for you. We don’t offer that kind of service, but we can help finding a developer. Feel free to drop a message from the contact form.

      Hope this helps,
      Peter

      Reply

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

Table of Contents
  • Local databases
  • Remote databases
  • Connecting to a local database
  • Connecting to a remote database
  • Restrictions
  • Remote connection management
  • Restricting access
  • Sensitive data
  • Remote SSL connections
  • Risks
  • Common Issues

Links

Blogs
Tutorials
Demos

Socials

LinkedIn
Twitter

Get in touch

Premium support
Free support forum
Contact us

Resources

WordPress plugin directory
YouTube tutorials

Passionate Programmers B.V. © 2017 - 2023 All rights reserved