• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

Learning MySQL

C

Carlosinfl

Loves the juice
Joined
Sep 25, 2002
Messages
6,633
I have a server at home and would really like to learn basic DBA tasks and administration so I installed MySQL on Linux & read their FAQ page which shows me how I can secure and properly configure MySQL post installation. That was very fun and straight forward. My question is now that I have MySQL up and running, I really don't have any need or functionality for it yet simply because I am still learning how it works and how to properly use it. I would like to know if anyone has any suggestions for easy tasks I can do with MySQL that will give me some goals and learn how to become a DBA in the process? I am just looking for some generic basic tasks I can use MySQL for that will allow me to create tables, databases, queries, and other tasks.

Thanks for any assistance...
 
I would suggest learn how to connect to the DB as an admin, either via command line or through some GUI DB editor.

Learn about the different table types in mySQL (myISAM, InnoDB,...) and what they are good for, etc.

Create some tables with multiple fields. Let some of the tables have relationships. For a simple example, if you create a table called `countries` you could have another table called `state_province` that would contain any states or provinces for a particular country. The two tables would be related by a primary key for the `countries` table to a foreign key to the `state_province` table. Fill the tables with data using INSERT statements and do SELECT statements with and without a JOIN to retrieve the data from the table(s).

Probably read through this manual: http://dev.mysql.com/doc/refman/5.1/en/index.html
to learn more detailed information about mySQL. Scour the web for mySQL tutorials, etc. Also, maybe get some books on DB administrations and mySQL.

Are you going to do any development or just want to learn how to admin mySQL. If you knew a little about development (PHP, .Net, Java...) You could put the database to work for you by creating simple applications that would read and write to the DB. This would also give you knowledge of how to make connections to the DB with various connectors (PHP, ODBC, JDBC,...) and give you insight into how databases fit in to the overall realm of IT.

Hope this helps for a start.
 
I also recommend that once you start messing around, you look at database security too : )

This is a pretty good book to grab.
"Sql Injection Attacks and Defense - Clark" Syngress
 
Yep, I agree, SQL injection attacks are probably one of the most common methods of exploit used against web based applications along with Cross Site Scripting (XSS) but basically if you escape all data going in and transform all user supplied data to its proper HTML codes coming out of the DB, it should mitigate most of these attacks.
 
As a DBA you will need to know Entity Relationships and Data Modeling
You may be able to find a local class that will teach you those concepts... IMO they're hard to pick up from just a book. I took an Oracle class on the topic, followed by a 9i SQL class. It was very helpful.

Also, get the MySQL Workbench (http://wb.mysql.com/). The other MySQL GUI tools have been rolled into the 5.2 beta in Dec 2009 so you shouldn't need anything extra there.

If you're analyzing performance, the first step is:
http://hackmysql.com/mysqlreport

When analyzing PHP + MySQL code I used XDebug to get output in the Cachegrind format, then I used KCacheGrind to analyze the code. I found queries that were poorly designed that were eating up 15% of execution time, huge queries that were poorly positioned, being executed inside a foreach loop that only needed to be executed once, and made other minor changes to speed up execution by 1% here and there.

I admin a really DB-heavy online game called Warring Factions, we really push a lot of single-server limits in MySQL (tri-core, 3GB RAM) and we're always streamlining processes, trying things 800 different ways to make do on the box we have.

If you get into clustering we can help as well.
mod_security can nuke a lot of the injection attacks. Frameworks like CodeIgniter, Kohana can validate, escape and format input. sprintf(), strip_tags() and htmlentities() are your friends on the XSS stuff.
 
You must log in or register to reply here.
Back
Top