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.
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.
A remote database is a database which 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 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 22.214.171.124 -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 Publisher and Data Backup.
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.
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:
grant all privileges on remote_schema.* To 'remote_user'@'126.96.36.199';
HINT You should also do this for non 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 privilieges.
By default remote data is send 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 a issue. You should however not use an unsecured remote database connection to manage sensitive data!
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