Before reading these instructions, determine if you really need to change the database prefix. Some claim it offers better security but that is not true. It doesn't take a savvy hacker long to determine what prefix you're using. A good firewall is a much better defense to stop SQL injection attacks.
That said there may be a situation where you want to move a database to a server with the same program or install the same program again to run an additional website. In this case you can rename the existing database and change the table prefixes to avoid conflicts.
Changing table prefixes requires changing the config file for your program. If you do not change the config file, your program will be unable to find any tables and may assume it is a new installation. For most config files, change the line $table_prefix = 'xx_'; where xx_ is the existing prefix to the new prefix.
These instructions were tested on MariaDB using a WordPress database. When you change prefixes, additional changes may be required depending on the program accessing the database and plugins installed. For example WordPress requires changes in the Options and UserMeta tables. If you don't change those entries, WordPress will not function correctly and the dashboard may not appear when logged in as administrator. Note: Some plugins require changes not shown here. If you have an issue with a plugin after changing the prefix, delete the plugin and it's data, then reinstall the plugin. If you are changing a database for a program other than WordPress, verify if additional modifications are required should the prefix change.
Login to phpMyAdmin and all databases are displayed on the left side of the screen. Select the database to change.
When the database opens, the tables are displayed in the larger window. Your database may have more tables if plugins are installed. Before making a change, confirm you are working in the correct place by referring to the top of the screen. This line displays the path information.
PhpMyAdmin has a number of built in queries to simplify changes. To use the built in query for changing prefixes, first select the Check all box just below the tables window. Any boxes you check are used to construct the query. If you do not select any boxes, phpMyAdmin will not let you select a query.
Click the With selected box to select a query from the popup menu. In the popup menu select the Replace table prefix option.
The Replace table with prefix window opens. phpMyAdmin will run the query based on the information entered here.
Type the existing prefix in the From box and the new prefix in the To box. Confirm the entries are correct and select continue.
phpMYAdmin runs the query changing all matching table prefixes to the new prefix. After the query completes, select the green reload navigation panel icon under the phpMyAdmin logo at the top left of the screen to refresh the screen. If you do not refresh the screen the old prefix names will remain in the left column.
If you are changing the prefix for a program other than WordPress you may be finished. Since this example uses a WordPress database, additional changes are required. Select the WordPress options table shown above.
Run a search query to determine additional changes needed. With the options table loaded select the Search tab near the top of the screen.
In the search window select the option_name pull down. Select the Link %...% option and enter your search criteria which in this case is wp_, then select GO.
The search will find all option names matching the search criteria. If you have a lot of plug-ins there could be many entries.
This WordPress installation has one entry because there are no additional plugins installed. The option_name column prefix remains wp_ because the earlier query changes table prefixes, not values in a table. There are no automatic queries to change these values so you will need to change each one manually. Select Edit to modify the option_name.
In the edit window change the prefix of the value to match the new prefix. Select Go when finished. You must perform this edit on each entry containing the old prefix.
Now select the user_meta table.
This table may require a number of changes depending on the number of plugins installed.
Now perform a search on the metakey column using Like %...% option and enter the old prefix.
Select and edit each entry containing the old prefix.
Enter the new prefix in the meta_key character value box and select GO. Edit each entry containing the old prefix.
When finished the meta keys will have the new prefix.
Changes are complete so exit phpMyAdmin. Start WordPress and make sure all features and plugins function correctly.