tips for MySQL newbies

Are you a brand-newbie to MySQL? Coming from another database (or databases)? If so, here are some concepts to read up on right away to ease your confusion down the road. These are the top things I wish someone had told me about before I did anything else with MySQL.

(I’ve had a lot of questions along these lines, and have posted on all of each of these items somewhere, but thought it’d be more helpful to gather the points together into one place.)

MY TOP 2 “BEFORE YOU BEGIN” TOPICS

#1: storage engines. Before you do any big planning, if you don’t know what a “storage engine” is, find out!
I can’t think of a good analogy for what a storage engine is. The best I can come up with is, you know how with Oracle you choose your index types (btree? bitmap? etc)? In MySQL, you choose your table types (and with them, the index types). Your choice determines a lot. Each storage engine (table type) has various features and limitations. You need to know them. For instance, do you want ACID-compliant transactions or foreign-key constraints? You’ll most likely be using the InnoDB storage engine then. In which case, clustering is not supported – that would require the NDB storage engine (which, by the way, is an in-memory storage engine – ie, all your tables must fit into memory).
You can read more in my post on the subject, which includes further links for even more information.

#2: scale-out vs scale-up: plan your strategy (it’ll affect your schema and a lot more)
Plan your scaling strategy. Not only is it crucial to do, it might affect your schema design and much more! (Eg, if you “scale out”, you’ll need to plan your schema accordingly.)
You can read more in my post on that subject, which includes further linds for even more information.

Before you roll anything into production, you’ll need to read up on these topics (among others):

TWO VERY IMPORTANT THINGS THAT ARE DIFFERENT THAN YOU’RE USED TO

#1 backups
– read the documentation (on mysql.com) on mysqldump. It’s something like export, but you can use it with archived binary logs for point-in-time recovery, too.
– or use LVM if you have linux, to take snapshots
– or buy add-ons like MySQLHotCopy (?)
– or all of the above!
more notes with further links in this post.

#2 permissions
there are no roles; permissions are based on a userid and (if desired) the host or hosts they connect from. It’s a bit laborious, and you’ll want to read up on it and play with it. (I got snagged on it a few times; see this for an example.)

AND 5 OTHER THINGS I THINK YOU SHOULD KNOW ABOUT RIGHT OFF THE BAT

#1 collations – by default, if you compare strings, it’s not case sensitive (‘a’=’A’). Read up on collations and character sets.
#2 autocommit is turned on by default. If you want to change this, read up on init_connect. Or see this post for more.
#3 the optimizer is not as developed/fine-tuned/complex be sure to test and QA and “analyze” your statements as much as possible. Use the slow query log to find longer-running queries and analyze them. Use “force index” in your select statements where needed (hopefully you know your data better than the optimizer).
#4: SQL_MODE. If you’re concerned about strict data integrity, you should read up on SQL_MODE. MySQL can insert “bad” data silently by default… (0’s for null dates, etc)
#5: isolation levels. If you’re going to use transactions (or even if you’re not), read up on isolation levels. This can affect locking, transactional integrity, etc. The default setting is not quite like Oracle’s.

where to get further info:
– download the software from mysql.com
– dev.mysql.com has the documentation, papers, etc
– planetmysql.com is a blog aggregator for MySQL-related blogs
– do lots of google’ing
– recommended books: Pro MySQL, MySQL 5.0 Certification Study Guide, MySQL Internals (and more…)

– buy MySQL support (it’s actually very good! Not at all like Oracle’s support. Best software support I’ve ever experienced by far, personally.)

Advertisements

7 Responses to “tips for MySQL newbies”

  1. Useful tips for MySQL newbies « François Schiettecatte’s Blog Says:

    […] Useful tips for MySQL newbies Some useful tips for MySQL newbies. […]

  2. George Trujillo Says:

    I think it is very important that DBAs (especially new ones) do sufficient planning, physical design, risk analysis and backup/recovery planning before installing MySQL. MySQL is an easy database to install and configure on a wide range of platforms.

    However if sufficient planning is not performed up front, a DBA is going to have problems as their databases grows, issues come up and more users start accessing the system. A top 10 list of things to do before installing a MySQL database include:
    1) Understand the purpose and potential growth of your database.
    2) Determine the appropriate physical layout of your database. The defaults are not always the best long term layout.
    3) Select the appropiate storage engines and how they impact performance and your backup/recovery strategies.
    4) Layout your backup and recovery strategy. Pick the appropriate backup/recovery tools for your environment. Then test your backup and recovery strategies sufficiently.
    5) Understand the number of databases that will be created and the maintenance ramifications of the database layout.
    6) What are the business costs and ramifications of this environment being down? Perform appropriate risk analysis.
    7) Understand how security is being managed and maintained for your users and your databases.
    8) Set the appropiate SQL_MODE and make sure you understand the ramifications of the SQL_MODE selection.
    9) Understand your physical storage layout. On your using separate disks, storage arrays, etc.
    10) Define a set of best practices that determine your physical layouts, naming conventions, management tools, infrastructure management and document everything.

    The five P’s apply to database configurations. Proper Planning Prevents Pitiful Performance.

    George Trujillo

  3. ben Says:

    George – agreed! Thanks for your advice!

    Ben

  4. Dave Busse Says:

    George – Very helpful. What tutorial (book, whitepaper, blog, etc) would you recommend for the MySQL newbie. I’m thinking in terms of a college student here.

    Dave

  5. Diamond Notes » Log Buffer #64: a carnival of the Vanities for DBAs Says:

    […] experienced Oracle DBA who is transitioning to MySQL blogs about his nine tips for new MySQL DBAs. They are quite enlightening. Along with that, the new DBA should take a look at […]

  6. Chris Says:

    MOving from a simple Access/Jet based system to a grown up MYSQL database backend supporting a numberof front end programs used in the manufacturing industry, I have found a number of issues not previoiusly considered, that I should be analysing and researching. I imagine your comments will have saved me days in the long run, and cost me weeks in the learning process! 😉

    Many thanks

  7. John Says:

    Very helpful. Will give me a head start before I take the jump.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: