How To Configure MySQL For phpMyAdmin
erics, Posted July 29th, 2011 at 1:26:24pm
Securely, allow access ONLY from the local server NOT from the network
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass'; 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'; GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost'; |
Insecurely, allow access via the network
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
GRANT USAGE ON mysql.* TO 'pma'@'%' IDENTIFIED BY 'pmapass'; 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'@'%'; GRANT SELECT ON mysql.db TO 'pma'@'%'; GRANT SELECT ON mysql.host TO 'pma'@'%'; GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'%'; |
With Table Creation SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 |
-- -------------------------------------------------------- -- -- Database : `phpmyadmin` -- CREATE DATABASE IF NOT EXISTS `phpmyadmin` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; USE phpmyadmin; -- -------------------------------------------------------- -- -- Privileges -- GRANT SELECT, INSERT, DELETE, UPDATE ON `phpmyadmin`.* TO 'pma'@localhost; -- -------------------------------------------------------- -- -- Table structure for table `pma_bookmark` -- CREATE TABLE IF NOT EXISTS `pma_bookmark` ( `id` int(11) NOT NULL auto_increment, `dbase` varchar(255) NOT NULL default '', `user` varchar(255) NOT NULL default '', `label` varchar(255) COLLATE utf8_general_ci NOT NULL default '', `query` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM COMMENT='Bookmarks' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_column_info` -- CREATE TABLE IF NOT EXISTS `pma_column_info` ( `id` int(5) unsigned NOT NULL auto_increment, `db_name` varchar(64) NOT NULL default '', `table_name` varchar(64) NOT NULL default '', `column_name` varchar(64) NOT NULL default '', `comment` varchar(255) COLLATE utf8_general_ci NOT NULL default '', `mimetype` varchar(255) COLLATE utf8_general_ci NOT NULL default '', `transformation` varchar(255) NOT NULL default '', `transformation_options` varchar(255) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `db_name` (`db_name`,`table_name`,`column_name`) ) ENGINE=MyISAM COMMENT='Column information for phpMyAdmin' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_history` -- CREATE TABLE IF NOT EXISTS `pma_history` ( `id` bigint(20) unsigned NOT NULL auto_increment, `username` varchar(64) NOT NULL default '', `db` varchar(64) NOT NULL default '', `table` varchar(64) NOT NULL default '', `timevalue` timestamp NOT NULL, `sqlquery` text NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`,`db`,`table`,`timevalue`) ) ENGINE=MyISAM COMMENT='SQL history for phpMyAdmin' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_pdf_pages` -- CREATE TABLE IF NOT EXISTS `pma_pdf_pages` ( `db_name` varchar(64) NOT NULL default '', `page_nr` int(10) unsigned NOT NULL auto_increment, `page_descr` varchar(50) COLLATE utf8_general_ci NOT NULL default '', PRIMARY KEY (`page_nr`), KEY `db_name` (`db_name`) ) ENGINE=MyISAM COMMENT='PDF relation pages for phpMyAdmin' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_recent` -- CREATE TABLE IF NOT EXISTS `pma_recent` ( `username` varchar(64) NOT NULL, `tables` text NOT NULL, PRIMARY KEY (`username`) ) ENGINE=MyISAM COMMENT='Recently accessed tables' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_table_uiprefs` -- CREATE TABLE IF NOT EXISTS `pma_table_uiprefs` ( `username` varchar(64) NOT NULL, `db_name` varchar(64) NOT NULL, `table_name` varchar(64) NOT NULL, `prefs` text NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`username`,`db_name`,`table_name`) ) ENGINE=MyISAM COMMENT='Tables'' UI preferences' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_relation` -- CREATE TABLE IF NOT EXISTS `pma_relation` ( `master_db` varchar(64) NOT NULL default '', `master_table` varchar(64) NOT NULL default '', `master_field` varchar(64) NOT NULL default '', `foreign_db` varchar(64) NOT NULL default '', `foreign_table` varchar(64) NOT NULL default '', `foreign_field` varchar(64) NOT NULL default '', PRIMARY KEY (`master_db`,`master_table`,`master_field`), KEY `foreign_field` (`foreign_db`,`foreign_table`) ) ENGINE=MyISAM COMMENT='Relation table' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_table_coords` -- CREATE TABLE IF NOT EXISTS `pma_table_coords` ( `db_name` varchar(64) NOT NULL default '', `table_name` varchar(64) NOT NULL default '', `pdf_page_number` int(11) NOT NULL default '0', `x` float unsigned NOT NULL default '0', `y` float unsigned NOT NULL default '0', PRIMARY KEY (`db_name`,`table_name`,`pdf_page_number`) ) ENGINE=MyISAM COMMENT='Table coordinates for phpMyAdmin PDF output' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_table_info` -- CREATE TABLE IF NOT EXISTS `pma_table_info` ( `db_name` varchar(64) NOT NULL default '', `table_name` varchar(64) NOT NULL default '', `display_field` varchar(64) NOT NULL default '', PRIMARY KEY (`db_name`,`table_name`) ) ENGINE=MyISAM COMMENT='Table information for phpMyAdmin' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_designer_coords` -- CREATE TABLE IF NOT EXISTS `pma_designer_coords` ( `db_name` varchar(64) NOT NULL default '', `table_name` varchar(64) NOT NULL default '', `x` INT, `y` INT, `v` TINYINT, `h` TINYINT, PRIMARY KEY (`db_name`,`table_name`) ) ENGINE=MyISAM COMMENT='Table coordinates for Designer' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_tracking` -- CREATE TABLE IF NOT EXISTS `pma_tracking` ( `db_name` varchar(64) NOT NULL, `table_name` varchar(64) NOT NULL, `version` int(10) unsigned NOT NULL, `date_created` datetime NOT NULL, `date_updated` datetime NOT NULL, `schema_snapshot` text NOT NULL, `schema_sql` text, `data_sql` longtext, `tracking` set('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') default NULL, `tracking_active` int(1) unsigned NOT NULL default '1', PRIMARY KEY (`db_name`,`table_name`,`version`) ) ENGINE=MyISAM ROW_FORMAT=COMPACT COMMENT='Database changes tracking for phpMyAdmin' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; -- -------------------------------------------------------- -- -- Table structure for table `pma_userconfig` -- CREATE TABLE IF NOT EXISTS `pma_userconfig` ( `username` varchar(64) NOT NULL, `timevalue` timestamp NOT NULL, `config_data` text NOT NULL, PRIMARY KEY (`username`) ) ENGINE=MyISAM COMMENT='User preferences storage for phpMyAdmin' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; |
Categories: How-To's, Technology Config, Install, mysql, phpmyadmin
Leave Your Comment
All fields marked with "*" are required.