phpMyAdmin is popular with both individuals and enterprise users who want a graphical interface for administering MySQL databases. Although the app has an expansive list of features, most people don't use it for much beyond basic tasks such as creating new databases. Here are some features tucked beneath phpMyAdmin's folds that can make you more efficient.
Manage users, check statistics
One of the most important tasks for a MySQL server administrator is to manage which users can access the database server. Although MySQL offers a flexible permissions system, mastering it isn't a trivial task.
You can save yourself the hassle of constantly referring to the MySQL documentation by creating and managing users via phpMyAdmin. The Users tab gives you an overview of existing users and their privileges. From this section, you can add and delete users and employ a simple series of checkboxes to set and modify user privileges.
MySQL's SHOW STATUS command displays all aspects of the server along with statistics on network traffic and database queries. You can get ahold of this information and more via phpMyAdmin from the Status tab, which lists all the run-time information about the server. Under Status, the All status variables tab lets you browse through all the variables that the server tracks to assess things such as cache performance and memory usage. The Monitor tab shows all the statistics as live charts that can help you track down resource-intensive queries. The Advisor tab analyzes the different variables and provides recommendations to tune and optimize the server.
Create a Control user
In addition to these features hidden in its graphical interface, phpMyAdmin also has some advanced features. For example, you can use phpMyAdmin to bookmark complex SQL queries and execute them later, or keep a log of executed queries, and even track changes made to tables and databases. To use these additional features you need to create a database known as the phpMyAdmin configuration storage or pmadb to store the configuration information for these additional features.
But to administer this database, you first need to create a control user. The control user is a special MySQL user that's set up with limited permissions and only has read-only access to the user and db tables of the database named mysql.
To create the user, head to phpMyAdmin's SQL tab, where you can run SQL queries on your server, or you can run the following commands from the shell using the MySQL command-line client. To use "pma" as the control user and "pmapass" as this user's password, run the commands:
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';Finally, specify the details for the control user in phpMyAdmin's configuration file, config.inc.php, available under the directory you've installed the software in. Look for the strings that read
GRANT SELECT (
Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
Execute_priv, Repl_slave_priv, Repl_client_priv
) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
ON mysql.tables_priv TO 'pma'@'localhost';
$cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass'] and replace them with $cfg['Servers'][$i]['controluser'] = 'pma'; and $cfg['Servers'][$i]['controlpass'] = 'pmapass';.Enable configuration storage
After you create the control user you need to create a special database to store the settings for the phpMyAdmin configuration storage, and ensure that this database can be accessed only by the control user.
To create the database, change into the script/ or examples/ directory inside phpMyAdmin's install directory; you'll have one or the other depending on how you have installed phpMyAdmin. Look for a file called create_tables.sql. If you're running a more recent version than MySQL version 4.1.2, first run mysql < upgrade_tables_mysql_4_1_2+.sql, then import create_tables.sql into your MySQL server with the command mysql < create_tables.sql. This will create a database called "phpmyadmin" with tables for each feature.
Next, allow the control user to make changes to this database. You can either do this graphically via phpMyAdmin Users tab or enter the following SQL command:
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
To enable an advanced feature, such as the query bookmark feature, you need to specify the name of the table that controls that feature in config.inc.php. To do that, first specify the name of the configuration storage database by hunting for the string
$cfg['Servers'][$i]['pmadb'] and replacing it with $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';.Now if you wish to use the bookmark function, enter the bookmarktable name in
$cfg['Servers'][$i]['bookmarktable'] such as $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';.
Browse the phpmyadmin database and make sure you enter the correct table name in the configuration file, then log out and log into phpMyAdmin again to bring the new configuration into effect.
Track changes
After activating the phpMyAdmin configuration storage mechanism you should enable the track changes feature, which keeps track of every phpMyAdmin-executed MySQL command. You can then create multiple versions of individual tables. You can create a version of a table, for example, before you manipulate its data, so that you have a reliable copy to revert to in case the changes corrupt the table. Each version is a snapshot of the table and logs all data manipulation statements (such as INSERT, UPDATE, and DELETE) and data definition statements (such as CREATE, DROP, and ALTER) and links these commands with the version number.
To enable the tracking mechanism, edit the config.inc.php file and replace the
$cfg['Servers'][$i]['tracking'] string with $cfg['Servers'][$i]['tracking'] = 'pma_tracking';.
After the feature has been enabled you'll get an additional Tracking tab when browsing a database. When you click on the tab you can select which data definition and manipulation statements you'd like to track for that particular table.
After you've enabled tracking for a table and created a version, the tracking page will show all versions for all tables. From here you can also view a complete report for every version. You can revert any table to a specific version.
Securing phpMyAdmin
While you might have gone to great lengths to secure your MySQL server and secure your web server, it all comes to naught if you haven't secured phpMyAdmin.
The good news is that securing phpMyAdmin doesn't take much effort. phpMyAdmin supports a variety of authentication methods. If you use the simple "config" authentication type, anyone with the URL of your phpMyAdmin can log in without a password and have the same rights to your data as you! This authentication mechanism works best if your server is behind a firewall or you limit access to the data using your web server's access mechanisms, such as Apache's .htaccess.
If you use the "cookie" authentication type (which is the default authentication mechanism), only authentic MySQL users can access the phpMyAdmin interface. There's also "http" authentication, which prompts for a MySQL username and password but only uses basic HTTP access authentication, and the "signon" authentication, to integrate phpMyAdmin with single sign-on systems.
Another way to secure phpMyAdmin is to block access to it by constructing rules that allow or deny access after verifying the IP address of the machine from which the request is received. To enable this option, edit the config.inc.php configuration file. Look for the host authentication order string $cfg['Servers'][$i]['AllowDeny']['order'] = 'allow,deny';. This denies access to everyone except for the users you've allowed in the authentication rules.
Or you can change the order to $cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';, which tells MySQL to apply all deny rules first, followed by allow rules. If a case is not mentioned in the rules, then access will be granted. Then, to block a user from logging into phpMyAdmin, look for host authentication rules, and change it to read:
$cfg['Servers'][$i]['AllowDeny']['rules'] = array(
'deny some_user from all',
);
This will deny access to the user called some_user. You can specify a list of rules to allow only a very limited number of users (even ones from remote hosts) while denying access to the rest (including some from local hosts).
Another important aspect to securing phpMyAdmin is to deny access to the almighty root user. To prevent the root user from logging into phpMyAdmin, add this line to the configuration file: $cfg['Servers'][$i]['AllowRoot'] = FALSE;.
Although taking backups of databases is no easy task, you can use phpMyAdmin to easily take a snapshot of a database or individual tables and export them in more than a dozen formats, including CSV, Open Document, Excel, JSON, YAML, and PDF.
To export a database, head to the Export tab. By default the Quick export mode is toggled, which exports all the databases from the current server in the format you select from the pull-down list. If you switch to the Export tab after selecting a database, the Quick export mode allows you to select the tables you want to export (by default all are selected) in addition to the export format.
If you want more control over the export process, select the Custom export method under the Export tab. This gives you lots of choices, including the option to compress the output, export just the table structures, just the data, or both, and a lot more.
phpMyAdmin packs in a lot of functionality. I hope these tips help you exploit the tool to its full potential.
Source : openlogic
.jpg)
0 comments:
Post a Comment