Server Fault
Server Fault

Questions tagged [database-administration]

300 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,733
97
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,293
58
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
  • 7,266
39
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
  • 451
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
  • 10,179
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,203
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
  • 365
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
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
13
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,453
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
  • 557
13
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
12
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,230
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…
Saj
  • 613
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
2 3
…
19 20