11:39:00 7/10/2024 - 0 views

Enable the Bookmark feature in PHPMyAdmin to store SQL query

Default, this feature is disabled, once you configure the "phpMyAdmin configuration storage" features, you can use the bookmarked query feature to save and recall arbitrary SQL queries.

But the document of PHPMyAdmin is so difficult to understand, this post will help you get it.

Get started

Step 1: Create Configuration Storage Tables

phpMyAdmin provides a SQL script to create the necessary tables for the configuration storage. You can find this script inside the phpMyAdmin folder.

Locate the SQL script at:

phpMyAdmin/sql/create_tables.sql

2. Run this SQL script on your database using a tool like MySQL command line or any other MySQL client (e.g., MySQL Workbench):

mysql -u root -p < /path/to/phpMyAdmin/sql/create_tables.sql

Or a simple way you can copy all the content of this file and paste it to SQL query tab in PHPMyAdmin to run

This will create a set of tables in the database that phpMyAdmin will use to store various configuration settings, including bookmarks.

Step 2: Configure phpMyAdmin to Use the Configuration Storage

Open your config.inc.php file, usually located in the phpMyAdmin directory. In my case, there is a file config.sample.inc.php, so I cloned it to config.inc.php
Add or update the following settings to enable configuration storage. Ensure that the $cfg['Servers'][$i]['pmadb'] variable is pointing to the database where the configuration tables are stored:

/* phpMyAdmin configuration storage settings */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';  // Database for phpMyAdmin configuration storage
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark'; // Table for bookmarks
$cfg['Servers'][$i]['relation'] = 'pma__relation'; // Table for relation features
$cfg['Servers'][$i]['table_info'] = 'pma__table_info'; // Table for table information
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords'; // Table for table positioning
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages'; // Table for PDF schema export
$cfg['Servers'][$i]['column_info'] = 'pma__column_info'; // Table for column information
$cfg['Servers'][$i]['history'] = 'pma__history'; // Table for SQL history
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords'; // Table for designer feature
$cfg['Servers'][$i]['tracking'] = 'pma__tracking'; // Table for tracking feature
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig'; // Table for user preferences
$cfg['Servers'][$i]['recent'] = 'pma__recent'; // Table for recently used tables

In my case, the code section above commented in config.inc.php, I only need to uncomment it

Step 3: Grant Privileges to phpMyAdmin User

Ensure that the MySQL user that phpMyAdmin uses has sufficient privileges to read and write to the configuration storage tables. You can grant privileges by running:

GRANT SELECT, INSERT, UPDATE, DELETE ON `phpmyadmin`.* TO 'your_phpmyadmin_user'@'localhost';
FLUSH PRIVILEGES;

Step 4: Test the Bookmark Feature

After completing the steps, you should be able to use the bookmark feature in phpMyAdmin. You can bookmark your SQL queries and retrieve them later in the "SQL" tab.

That's it! This enables the configuration storage, which powers features like bookmarks, relation views, and user-specific settings in phpMyAdmin.

Conclusion

Enabling phpMyAdmin's configuration storage unlocks several powerful features, such as the ability to bookmark SQL queries, track database changes, and customize user settings. By following the steps outlined in this guide, you can easily set up the necessary configuration tables and ensure that your MySQL user has the right privileges. Once configured, features like bookmarks will streamline your workflow, allowing you to store and retrieve queries effortlessly. Whether you're managing small projects or complex databases, these additional tools enhance phpMyAdmin’s flexibility and functionality, making your database management more efficient.

Reference

https://stackoverflow.com/questions/40984620/phpmyadmin-how-to-save-queries