In SQL Server 2008 there is a permissions VIEW SERVER STATE. What rights this permission give to user? What SQL Server mean by SERVER STATE?
I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing:
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'bob'@'10.1.1.1' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT, UPDATE, DELETE on MyDatabse.* to 'bob'@'localhost', 'bob'@'10.1.1.1';
This works fine, but is there any more elegant way to create a user account that is linked to multiple IPs or does it need to be done this way?
My main worry is that in the future, permissions will be updated for one 'bob' account but not the other.
I periodically need to make changes to tables in mysql 5.1, mostly adding columns. Very simple with the alter table command. But my tables have up to 40 million rows now and they are growing fast... So those alter table commands take several hours. In a couple months they'll take days I'm guessing.
Since I'm using amazon RDS, I can't have slave servers to play with and then promote to master. So my question is if there's a way to do this with minimal downtime? I don't mind an operation taking hours or even days if users can still use the db of course... Can they at least read while columns are being added? What happens if my app tries to write? Insert or update? If it fails immediately that's actually not so bad, if it just hangs and causes problems for the db server that's a big problem..
This must be a fairly common scaling issue, everyone needs to add columns.. What's typically done to a production db? Slave -> master migration?
Update - I forgot to mention I'm using the innodb storage engine
I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server.
For extra points, there are a couple of existing databases on the new one already, how do I import the old servers privileges without nuking the couple existing of ones.
Old server: 5.0.67-community
New server: 5.0.51a-24+lenny1
EDIT: I've got a dump of the db 'mysql' from the Old Server & now want to know the proper way to merge with the 'mysql' db on the New Server.
I tried a straight 'Import' using phpMyAdmin and ended up with an error regarding a duplicate (one that I've already migrated manually).
Anyone got an elegant way of merging the two 'mysql' databases?
What things should every SQL Server database administrator know?
Books, blogs, tools, you name it.