phpMyAdmin is an powerful tool to help folks manage their MySQL databases. Generally this tool is well documented and there is an active forum if folks want to ask questions to the community.
Setting up Sub-Account Permissions
MySQL (which is the database which is controlled by phpMyAdmin) has a great many levels of permissions which allows a lot of flexibility for securing your data.
If you have a number of databases which you would like to administer, you can do this under a single MySQL user account. However, you can also give access to specific databases and tables to different user accounts. (The following assumes that you have set up the new MySQL user account.) To allow other accounts access to these databases, use the GRANT SQL command: http://www.mysql.com/doc/G/R/GRANT.html
eg. log in to phpMyAdmin as 'YourMainMySQLAccount' then issue the command:
GRANT ALTER,SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON MyNewSQLDatabase.* TO YourNewMySQLAccount;
or for tables
GRANT ALTER,SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON SomeNewTable TO YourNewMySQLAccount;
in any of the 'issue SQL command' boxes.
Setting MySQL Account Passwords
phpMyAdmin also allows you to change your MySQL account passwords, you may want to change the password on your account using the 'SET PASSWORD' SQL command: http://www.mysql.com/doc/S/E/SET_OPTION.html
eg. log into phpMyAdmin as 'YourMySQLAccount' and then issue the command:
SET PASSWORD = PASSWORD('newpassword');
Backing up Your Data
phpMyAdmin has a 'dump' option which allows you to download the entire SQL of the database. By selecting a specific table you can also download one specific table as well. To do this you will need to select the 'Structure and data' option. If you want to save it to your hard drive (rather than just view the data), you will also need to select the 'Send' option. Having access to the raw MySQL data is also an easy way to do a global and replace on all of the content of your site or to move databases.
Often php scripts will contain critical MySQL information within them. To ensure the integrity of your data it is important that you be careful with the availability of this information. Configuration files with MySQL data in them should not be visible via the web. To keep your data safe, consider using one of the following options:
- Store your data in a directory which is not visible through the web
- Place a password on the directory which contain your MySQL info
- Save your configuration file with a file extension which is parsed (ie. .php) so that no critical data is displayed via the browser
Sometimes you may have large database tables which will time out with phpMySQL. If this happens you can use the following from the shell prompt on your server:
mysql -h localhost -u username -p database
About The Author
Mike Gifford is the founder of OpenConcept Consulting Inc, which he started in 1999. Since then, he has been particularly active in developing and extending open source content management systems to allow people to get closer to their content. Before starting OpenConcept, Mike had worked for a number of national NGOs including Oxfam Canada and Friends of the Earth.