The database tool, phpMyAdmin is NOT the only mysql database tool but it is and has been, the most popular and most awarded among MySQL administrators and developers. It was mentioned by the author of the first php and MySQL book I read. The administrators and developers I have met since, all use it. It is also one which can be installed on the website. Over time, I have learned ways to customize it, and recently, I learned how to easily, totally, secure it on the site.
SECURITY: You can install it on the web site, but you – the database administrator or developer – and maybe a few others are the only ones who should ever gain access to your database administration tool. Even more, you can stop the hackers from even hacking at it. Lock out the thugs simply and easily, with an .htaccess file that only lets you in from your computer, your IP address.
Into your phpMyAdmin folder, whatever you named it, upload a file named htaccess (without the leading period, and then, add it on, rename it, after you have it uploaded onto the server) containing this:
order deny,allow
deny from all
allow from 123.123.20.10
where 123.123.20.10 is your IP address. to list 2 or 3 addresses just add a space between them like this:
allow from 123.123.20.10 123.124.20.20 123.125.20.30
keywords in field names
Some developers may not be alert and illegally use reserved, “key”, or “reserved”, words for names of fields and that can cause errors when we try to import a file we have exported with phpmyadmin. Un-check “Extended inserts” to avoid errors. Keywords in the extended inserts will cause errors and our file will not import or “upload”.
Note: by also un-checking “Enclose table and field names with backquotes” phpmyadmin will only put the illegal “reserved words” in back-quotes making it easy to spot the field names that are going to cause trouble.
I also check the “add drop table” so that I can replace my tables, entire database, with new data, and un-check the “if not exists” and the “auto increment value” which are both redundant.
Enhancing phpmyadmin with config.inc.php
you can copy /config.sample.inc.php to /config.inc.php file where you can add these lines:
phpmyadmin’s minimalist default of only showing 30 records at a time is maddening.
change it: ( 300 or whatever you like! )
$cfg[‘MaxRows’] = 300; # the max number of rows displayed by default
these will make the suggestions above, our defaults:
$cfg[‘Export’][‘file_template_database’] = ‘__DB__-%Y-%m-%d’;
$cfg[‘Export’][‘file_template_table’] = ‘__TABLE__-%Y-%m-%d’;
$cfg[‘Export’][‘sql_drop_table’] = true;
$cfg[‘Export’][‘sql_if_not_exists’] = false;
$cfg[‘Export’][‘sql_auto_increment’] = false;
$cfg[‘Export’][‘sql_backquotes’] = false;
$cfg[‘Export’][‘sql_extended’] = false;
I found that this will somewhat enlarge the input text boxes. (just interesting)
$cfg[‘TextareaRows’] = 20;
Expanding the phpmyadmin 2 meg upload limit
If you have a db that is only 3 or 4 megs, the 2 meg limit for uploading (importing) files can feel like a severe limit. It is set in the server’s php.ini file. In phpMyAdmin’s docs., one user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.
example:
memory_limit = 12M
post_max_size = 9M
upload_max_filesize = 6M
To expand it from 2 to 6 is more than enough. a file larger than 4 to 6 megs takes so long to run that the only reasonable import option is to get to a command line and execute a mysql command – with which the import will only take a few seconds. (50meg, maybe 15sec?)
mysqldump -u user -p dbname1 > dbname1_dump.sql mysql -u user -p dbname2 < dbname1_dump.sql
insecure unless in a script outside the web site or protected by .htaccess :
mysqldump -u user -ppassword dbname1 > dbname1_dump.sql mysql -u user -ppassword dbname2 < dbname1_dump.sql
There is generally no file size or time limit in phpMyAdmin on exporting a file - database or table, however, if this is your way of getting periodic backups of your database, you could change the "File name template" to include the date, to make it easier:
You can click on strftime, to see more options.
from
__DB__
to
__DB__-%Y-%m-%d
and
__TABLE__
to
__TABLE__-%Y-%m-%d