Accessing MariaDB tables using CONNECT

Using the CONNECT storage engine, we can set up connections to local or remote MariaDB database tables and have them appear as if they are part of our MariaDB database.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe from Chapter 2, Diving Deep into MariaDB. Then, install and enable the CONNECT storage engine as described in the Installing the CONNECT storage engine recipe at the beginning of this chapter.

How to do it...

1. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

2. Run the following CREATE TABLE statement by altering the user:pass part of the CONNECTION option with a username and password that has rights to the isfdb database:

   CREATE TABLE websites_2 ( 
     site_id int(11), 
     site_name varchar(255), 
     site_url varchar(1024), 
     PRIMARY KEY (site_id) 
   ) ENGINE=CONNECT TABLE_TYPE=MYSQL 
   CONNECTION='mysql://user:pass@localhost/isfdb/websites...