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.
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.
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: wpdaYou 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 #
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.
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 #
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 #
- 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
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?
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