Hot answers tagged

130

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function: mygrants() { mysql -B -N $@ -e "SELECT DISTINCT CONCAT( 'SHOW GRANTS FOR ...


34

the same as you do for any other repetitive task that can be automated - you write a script to do the backup, and then set up a cron job to run it. a script like the following, for instance: (Note: it has to be run as the postgres user, or any other user with the same privs) #! /bin/bash # backup-postgresql.sh # by Craig Sanders # this script is public ...


24

There are two methods for extracting SQL Grants from a MySQL Instance METHOD #1 You can use pt-show-grants from Percona Toolkit MYSQL_CONN="-uroot -ppassword" pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql METHOD #2 You can emulate pt-show-grants with the following MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A ...


21

Sites Most of these are multiple purpose. For instance, articles + forums + blogs. This is by no means exhaustive. It's a starting point as you'll find a lot of other blogs and resources from these sites. SQL Server Central SQL Blog SQLServerPedia SQL Skills SQL Team SQL Server Performance IT Knowledge Exchange Books This really depends on your role. ...


17

Assuming it's a user database, detach the database, move the .mdf / .ldf files to the new location and reattach the database. You might need to browse to the new location of the .ldf file when you choose your .mdf file in the reattach screen. To detach a database, right-click on it in management studio and choose Tasks | Detach. To reattach, right-click ...


16

Read Dynamic Management Views and Functions Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions: Server-scoped dynamic management views and functions. These require VIEW ...


15

Here's my top 10 interview questions for senior database administrators, and here's Tom LaRock's top 10 questions for junior DBAs. I've noticed other people suggest that the candidate should troubleshoot a server. If you take that approach, use a virtual machine with a snapshot. Set up a server a specific way with certain config or performance problems, ...


13

There's already plenty of information(Questions/Posts) in ServerFault for a beginner Linux administrator: Linux How-to/Tutorial sites Setting-up Linux at home Tools you should absolutely know as a Windows/Linux Admin Good Linux Podcasts/Links for beginners Hidden Features in Linux How to recruit a Linux Guru What a beginner should know about a SysAdmin Job ...


12

Change the recovery mode of the database named "model".


11

This is kind of opinion-ish, but I would put the SQL Server binaries on the slow disk. It's quite common to put the binaries on the OS disk (although some people hate that), or on a slower disk. You definitely want to remember to put your system databases, especially tempdb, on the faster disk, however. In fact, it's also common to put tempdb by itself. ...


10

If you've got access to the mysql database, you can change the grant tables directly: UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username'; FLUSH PRIVILEGES; ...and an analagous UPDATE-statement to change it back.


10

4 GB ram is a joke these days. So, no - sorry. I think you are done on that end. It can work, but that requires specific usage patterns. I would not run a db server on a 4 GB hardware out of principle - 16 GB of RAM costs pretty much nothing to start with. SCSI RAID 5 is not optimal. Depending on usage patterns, you should have a minimum of TWO groups - one ...


10

Richard Bronosky's answer was extremely useful for me. Many thanks!!! Here is a small variation which was useful for me. It is helpful for transfering users e.g. between two Ubuntu installations running phpmyadmin. Just dump privileges for all users apart from root, phpmyadmin and debian-sys-maint. The code is then mygrants() { mysql -B -N $@ -e "SELECT ...


9

you should remove the "shrink database" part of your maintenance plan. "shrink database" will fragment your index! here is a great blog post by Paul S. Randal which explains this in detail. you can rebuild your statistics with: EXEC sp_updatestats update your statistics always after your database reindex. here is a blog post by Colin Stasiuk which ...


8

I got orders of magnitude performance improvement only by indexing (although I didn't really fiddle with it much) After you handle the obvious (decent hardware, indexing and defrag'ing) you're best source for performance is looking at your own code.


8

Start with the DB you have to administer. Learn it inside and out. Then, decide what you want to learn. Applying the same principles to a new system will not be as hard as trying to learn several at the same time. Ask yourself, "Why do I want to learn this?" There are many other types of database models beyond relational. chouchdb is a great example of a ...


8

Yes, it will run. How fast however depends on your working data set. 3GB RAM (and about 1GB used for the OS) is not much to run SQL on.


8

alter authorization on schema::[db_datareader] to [dbo] alter authorization on schema::[db_datareader] to [db_datareader] alter authorization on schema::[db_datawriter] to [dbo] alter authorization on schema::[db_datawriter] to [db_datawriter]


7

It depends on what you're looking for, as it may have a bit too much of an academic slant, but I'd have to go for Introduction to Database Systems by C.J. Date.


7

I would set up: the stock administration account that comes OOB. Use this for things that no other account can do. Keep it locked up and only haul it out when you don't have a choice. a secondary admin account, one for each administrator. Do all of your day-to-day admin work in this account, but on these admin accounts, omit privileges for the really ...


7

When messing with MySQL's filesystem you have to stop the MySQL server. To avoid downtime on your live machine, use a backup/virtualised machine with the SAME VERSION of MySQL server. While the BACKUP MySQL server is stopped, copy the tables (I assume .FRM, .MYI etc?) into the filesystem in /var/lib/mysql/BACKUP_DB (the BACKUP_DB's corresponding directory). ...


7

What do you need to know about databases to be a DBA? Everything (or at least most things, and where to look for answers to everything else). Seriously though, there is always a market for good DBAs. If you want to become a good DBA the best advice I can give you is: If your "databases class" didn't get into the theory behind the relational model & ...


7

At the low end, it basically boils down to "can you absolutely say that you have no shared data?" Unlike mysql, the database is an absolute boundry in postgresql. You cannot SELECT zip_code FROM common.city_zip WHERE city=... if you go with separate databases (at least not without dblink). If you have any shared data at all, postgresql's "schema" is ...


6

I don't know if taking an individual DB offline and then putting it back online is equivalent to restarting the SQL service (from the DB's standpoint, at least - it's definitely not for the service), but it will "reset" the DB to the extent that it will close all existing connections and rollback any open transactions. If that's the effect you're after, ...


6

Think about scale, audit trail and backouts. In the real world you need to do stuff often and you often need to do it to lots of servers. So scripting and automation become important. The level of rigour tends to be higher than what you might use at home. So you need to think about how you can record your changes (the better to identify what change it was ...


6

Or, utilize percona-toolkit (former maatkit) and use pt-show-grants (or mk-show-grants) for that purpose. No need for cumbersome scripts and/or stored procedures.


6

If you think MYSQL is an "install and forget experience" SQL 2008 should be a breeze relatively speaking. While there are patches, most DBA's put them on (if they think they require them) at scheduled times.


6

First, please read our canonical question on Capacity Planning. The specific advice you're asking for is capacity planning advice, and you're going to have to work that out on your own, for your particular environment. Second, You are looking at this wrong. The amount of memory (or any other resource) you have doesn't dictate the number of connections ...



Only top voted, non community-wiki answers of a minimum length are eligible