Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [database-administration]

296 questions
99
votes
11 answers

How can I export the privileges from MySQL and then import to a new server?

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…
Gareth
  • 8,573
  • 13
  • 44
  • 44
89
votes
6 answers

Changing host permissions for MySQL users

I have the following grants for a user/database mysql> SHOW GRANTS FOR 'username'@'localhost'; +---------------------------------------------------------------------------+ | Grants for username@localhost …
f00860
  • 1,203
  • 1
  • 9
  • 12
53
votes
4 answers

MySQL: creating a user that can connect from multiple hosts

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…
DrStalker
  • 6,946
  • 24
  • 79
  • 107
37
votes
1 answer

What can user do with VIEW SERVER STATE permissions?

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?
hyty
  • 431
  • 1
  • 5
  • 6
28
votes
11 answers

Things every SQL Server DBA should know

What things should every SQL Server database administrator know? Books, blogs, tools, you name it.
cletus
  • 9,999
  • 9
  • 37
  • 40
27
votes
5 answers

What's the best way to automate backing-up of PostgreSQL databases?

I find it tedious to have to backup databases every week. And I also think weekly backups should be turned into daily backups. If I had to do that, I don't want to do it manually. What's the best way to automate the backing-up of PostgreSQL…
Randell
  • 1,173
  • 8
  • 18
  • 26
19
votes
5 answers

Modifying columns of very large mysql tables with little or no downtime

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…
apptree
  • 345
  • 1
  • 3
  • 10
16
votes
2 answers

How to calculate max_connections for PostgreSQL and default_pool_size for pgbouncer?

Is there a rule or something I can use to calculate a good number for max_connections, default_pool_size and max_client_conn? The defaults are odd. PostgreSQL defaults to max_connections=100 while pgbouncer defaults to default_pool_size=20.…
ChocoDeveloper
  • 422
  • 2
  • 5
  • 11
15
votes
3 answers

How to change default recovery for new databases?

How can i configure the database server on our development server so that when new databases are created that they are Simple recovery model by default? Currently if we remember, when creating a database we have to click on the options tab and…
Valamas
  • 365
  • 1
  • 4
  • 9
13
votes
2 answers

With MySQL, how long does an "ALTER TABLE ... DISABLE KEYS;" statement last?

If you disable the keys (suspending indexing) on a mysql INNODB table, how long does that setting last? For a query like: ALTER TABLE users DISABLE KEYS; Do the keys get re-enabled at the end of the script? or do they last until you explicitly…
Daniel Beardsley
  • 547
  • 6
  • 12
12
votes
2 answers

How to undo assigning ownership of db_datareader/db_datawriter schema?

i meant to assign an SQL Server login to the db_datareader db_datawriter database roles. But if a moment of sore tummy and tiredness, i accidentally give that user schema ownership of them instead: Ignoring for the moment what it can…
Ian Boyd
  • 5,293
  • 14
  • 60
  • 82
12
votes
6 answers

How do you interview a Database Programmer/ Admin applicant?

During the interview, I ask basic database design questions. Normalization (When-Why) is one of my concerns when it comes to database design. Some scenarios I site that involves synchronized servers and what/why/how they take consideration of…
Sajal Dutta
  • 613
  • 5
  • 18
12
votes
8 answers

Any good PostgreSQL client for linux?

stackoverflow points me "belongs-on-serverfault" on this, so crossposting. I am frustrated of not having a good Linux GUI administration and development tool for PostgreSQL. pgAdmin III is buggy and unusable piece of... hmm, software, compared to…
user3370
11
votes
5 answers

Can't Login to phpPgAdmin

I'm trying to set up phpPgAdmin on my test machine so that I can interface with PostgreSQL without always having to use the psql CLI. I have PostgreSQL 9.1 installed via the RPM repository, while I installed phpPgAdmin 5.0.4 "manually" (by…
Devin
  • 355
  • 1
  • 3
  • 10
11
votes
3 answers

After setting root password why does MYSQL still allow me to login without a password?

After setting root password why does MYSQL still allow me to login without a password from the command line? I can type "mysql" at a root unix prompt and it asks for no password and still allows me root access. I am not understanding why "mysql…
djangofan
  • 4,182
  • 10
  • 46
  • 59
1
2 3
…
19 20