Tag Info

Hot answers tagged

114

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 ...


31

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 ...


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 ...


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

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 ...


11

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 ...


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 ...


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

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


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.


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

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

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 ...


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

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

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). ...


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.


5

Unfortunately, no. You'll need to upgrade the default instance to SQL 2005, remove the 2005 named instance and reinstall SQL 2000 as a named instance. Or just wipe clean and install SQL 2005 first as the default and SQL 2000 as a named instance. Any reason you need to keep the SQL 2000 instance around?


5

personally i like the official mysql tools best! Mysql Workbench quoting from their website: MySQL Workbench provides DBAs and developers an integrated tools environment for: Database Design & Modeling SQL Development (replacing MySQL Query Browser) Database Administration (replacing MySQL Administrator)


5

As others have said, there are lots of built-in ways in SQL Server 2008 to make two servers synchronize, real-time or on a schedule. Some points you should take into account: How "far" are the two servers (i.e. how much bandwidth and latency between them)? How big are the two databases? How often and how much do data change? Do data actually change, or ...


5

SQL Management Studio Express will work just fine. The normal setup pattern would be something like this: enter the server's IP address (and optional \instancename) in the connect window select SQL or Windows authentication as appropriate. Hosters typically assign SQL logins. username and password as assigned. This connection should work on any home ...


5

For this example, let's assume your database is called "zig", and your backed up database is called "zig_backup". I'll also assume that your backup database and your live database have the same schema. I'll also assume that your live database is being updated constantly and you can not perform the merge "offline" and then flip a switch to make the merged ...



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