MySQL Administrator on migrated server
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Help with my Plusnet services
- :
- Everything else
- :
- MySQL Administrator on migrated server
MySQL Administrator on migrated server
22-03-2014 3:54 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
I used MySQL Administrator on the old server (rumpus) to make daily backups of my database. However, when used on the migrated server (with the new host, user, password settings), it can't find my database.
Thanks in advance for any insight
Re: MySQL Administrator on migrated server
24-03-2014 10:45 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Can't say I'm overly familiar with the client to be honest.
Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵
Re: MySQL Administrator on migrated server
24-03-2014 3:54 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Anyone know how to get around this?
Re: MySQL Administrator on migrated server
24-03-2014 4:37 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Can this be done via phpMyAdmin?
Re: MySQL Administrator on migrated server
24-03-2014 4:43 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
As an interim measure, you could use phpMyAdmin or Adminer to periodically dump your database content?
Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵
Re: MySQL Administrator on migrated server
24-03-2014 8:25 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
You have a good point, but I still think it's a privileges issue.
Using phpMyAdmin:
If I use the SQL query SHOW DATABASES on the old server, RUMPUS, it works and lists my database name
But If I use the same query on the new server, I get an error message:
#1227 - Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation
My guess is that MySQL Administrator can't find the database because I don't have any SHOW DATABASES privilages, hence it crashes.
Is it possible to set the privilege on the new server?
Paul
Re: MySQL Administrator on migrated server
24-03-2014 8:36 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
I think this is why mySQLAdministrator can't find my databases.
I guess I need to raise a ticket to see if I can get it set to OFF?
Paul
Re: MySQL Administrator on migrated server
25-03-2014 3:34 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵
Re: MySQL Administrator on migrated server
25-03-2014 3:54 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
jelv (a.k.a Spoon Whittler) Why I have left Plusnet (warning: long post!) Broadband: Andrews & Arnold Home::1 (FTTC 80/20) Line rental: Pulse 8 Home Line Rental (£14.40/month) Mobile: iD mobile (£4/month) |
Re: MySQL Administrator on migrated server
25-03-2014 4:32 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵
Re: MySQL Administrator on migrated server
26-03-2014 7:09 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Re: MySQL Administrator on migrated server
26-03-2014 4:25 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Quote Due to the way our environment is configured, the show database privilege would allow a user to receive list of all databases on the server cluster. This would probably take several minutes to execute and it's not preferred from a security perspective. Table SCHEMATA from database information_schema can be used to retrieve a database list instead of SHOW DATABASES.
That obviously doesn't help as far as the aforementioned SQL clients are concerned. I've asked whether or not Hostopia can suggest a work around for this. I'm not convinced there is one though without resorting to another client.
Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵
Re: MySQL Administrator on migrated server
26-03-2014 7:38 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
select * from information_schema.SCHEMATA
in phpMyAdmin only shows the database currently selected in the left-hand panel for me. If I choose a different one it shows that instead.
David
Re: MySQL Administrator on migrated server
26-03-2014 11:33 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Ask them to turn the --skip-show-database option off!
[quote=https://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_show-databases]The SHOW DATABASES privilege enables the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option. Note that any global privilege is a privilege for the database.
This is not any sort of security risk as users will only see their own databases - in fact I suspect they might only see the one database which has the user and password they used to connect.
Edit: This isn't something I knew about but it took me all of about two minutes to find that information using Google! Not impressed that apparently neither they nor you found this.
jelv (a.k.a Spoon Whittler) Why I have left Plusnet (warning: long post!) Broadband: Andrews & Arnold Home::1 (FTTC 80/20) Line rental: Pulse 8 Home Line Rental (£14.40/month) Mobile: iD mobile (£4/month) |
Re: MySQL Administrator on migrated server
27-03-2014 10:29 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Quote from: jelv Ask them to turn the --skip-show-database option off!
That's exactly what I've done and I'm fairly sure they're aware of the consequences. Hostopia's have quite a unique set up though, they develop from source and requests for changes are typically evaluated on customer demand (bear in mind any changes would be applied to everybody sharing their European server farm). There may be some aspect of their set-up that wouldn't play nicely with this variable disabled.
This is essentially what I said when asking for clarification:
[quote author="Bob Pullen"]Looking at the MySQL manual here - http://dev.mysql.com/doc/refman/5.0/en/show-databases.html
Suggests that you could disable the --skip-show-database variable and allow customers the ability to run SHOW DATABASES *without* it listing all databases on your server i.e. customers would only see the databases that they have some sort of privilege for.
Indeed, the Plusnet platform our customers have been migrated from does not exhibit this problem and neither do users have the global SHOW DATABASES privilege e.g. SHOW GRANTS for a user on the Plusnet platform returns:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `pnhosting2\_pn`.* TO 'pnhosting2'@'%' WITH GRANT OPTION
Yet SHOW DATABASES is possible and the query output only lists the customer’s database (rather than every database on our server)
Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Help with my Plusnet services
- :
- Everything else
- :
- MySQL Administrator on migrated server