I don’t usually blog about tech stuff other than Access/VBA because there’s so much already available elsewhere on some of the more common technologies (MySQL, SQL Server, .NET, web dev, etc). Nevertheless, someone asked me for a “getting started with MySQL”, so here it is. This assumes some basic familiarity with some other database, namely Access and/or SQL Server.
What is it?
Just to be clear, MySQL is a popular RDBMS (Relational Database Management System) found primarily on Linux machines operating with Apache web servers. That is, if a website is hosted on non-windows platform, chances are it uses MySQL as a database. Wordpress, Wikipedia, etc etc. MySQL can also be run in a Windows environment, but by and large it seems to be far more popular on Linux systems. MySQL can handle a good bit of volume and while it has it quirks, it’s suitable for many web applications. MySQL started as an open source initiative and has since been acquired by Oracle, who continues to fund its progression. At the time of writing, v5.x.x is very common, and v6.x.x is either going to be released soon or has already been released.
If you want to play with MySQL, you’ll need to decide where you’re going to host it. Typically, as part of a project’s production rollout, the MySQL database will be hosted on a linux based web server (via GoDaddy or the like, or your own server if you’re handy with that stuff). Creating a database on a hosted server is usually as easy as going to your web server control panel and finding the “create database” button, where you’ll be prompted to give it a name and create/assign a user to it. This is fairly straightforward.
If you read the local hosting stuff below and decide you want nothing to do with it, or if you want remote access to the hosted MySQL database for some other reason, you’ll likely have to set up an IP allowance. Since MySQL databases are usually part of a website, the web server (located as part of the same stack that the rest of the website is hosted on) is the only one that needs it, thus remote connections are typically closed off. Most web hosts will have a place to allow remote connections in their cPanel, where you’ll add your IP to the whitelist and then set up your client (see Tools below) to connect to it.
Dev Machine/Local Hosting
When you’re working up your development stuff before it hits production, you’ll often want to have a local copy of the MySQL database (although it’s certainly possible – and sometimes desirable – to create a dev copy on the final server and use that instead of hosting locally). Anyway, assuming you’re writing some sort of PHP/MySQL web application (PHP is the most common web server scripting language that makes use of MySQL backends… not necessarily recommended, but popular nonetheless), you’ll probably want to download and install what’s called the WAMP stack. The more typical LAMP stack stands for Linux, Apache, MySQL, PHP, where with WAMP you replace the Linux with Windows. That said, WAMP is both a platform acronym as well as a program: there’s a wamp server program you install to a Windows machine that sets up the softwares and servers so you can develop in PHP/MySQL on your local machine. This is a common setup. See here: http://www.wampserver.com/en/
Like most things in PHP/MySQL development, wamp itself has a few quirks: for one, getting the versions of each component to match your web server’s setup exactly might be difficult, though in most places you can get it pretty close (and if you don’t develop with off the wall features/syntaxes you can usually be safe enough where minor versioning differences don’t really matter), and for another, WAMP makes use of a number of your system’s ports for communicating with the locally installed services (the apache web service and the MySQL database service, namely). Because Linux and Windows are two entirely separate worlds, neither has given thought to making sure they don’t step on the other’s toes, so to speak. Which is to say, often times Windows program (SQL Server, Skype) ports can interfere with the ports that WAMP wants to be using, and if you don’t know much about setting up ports and such, this can be a bit of a pain to get squared away (and is subject to resurface with various updates from either end). This may be one reason to consider using a hosted database via GoDaddy or the like for database development, rather than having it done locally, but it depends a lot on what you’re doing and whether it makes more sense to develop the whole solution locally or not.
Users & Permissions
If you’ve never worked with a database server’s user permissions before, setting up your user might be a bit strange, at least in terms of permissions. MySQL’s user and permission system doesn’t have much depth to it compared to some others. Essentially, you have a list of databases on the MySQL server, a list of Users on the same MySQL server, and you assign appropriate users to appropriate databases. Each user has a list of permissions for actions that they’re allowed to do.
Usually, when you set up a user you’ll do so through some GUI (cPanel has its own, other tools as well), but regardless of which tool you’re using, you’ll see all sorts of options for what permissions to grant. Stuff like READ, CREATE, ALTER and about two dozen others.
For development purposes, you’re likely to want all of these as they allow you do everything you need to do. However, for production databases, you may want to set up a user that’s restricted only to the required permissions in an attempt to tighten up on security a bit (assuming a website, typically the “website” itself has a user account, which only need be able to read and write data, not create and alter objects, etc.)
Also, just as a side note, you can create users and assign permissions via SQL as well, though I typically find the GUI tools easier as I don’t use the SQL syntax often enough to remember it all off the top of my head.
Default User Accounts
You should be aware that MySQL does create some default user accounts, and that these accounts don’t have passwords applied. The specifics depend on which platform the server is installed on (Linux or Windows), but generally speaking there’s a “root” account that the localhost (e.g., your computer or hosted server) is given access to, and there’s also generally one or more anonymous accounts created that have read access. You’ll most certainly want to button these up first off… they could leave possible security hazard if not. (a little later I’m going to complain about the official documentation in general, but nevertheless, I’ll direct you here for this important topic: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html)
Ok, so we need some tools. Regardless of how this is hosted, you can get commandline access to the MySQL Shell. I don’t mind this myself for some tasks, but a nice IDE will do well. Most often, you’ll see phpMyAdmin by default from your web host. Don’t bother with this, please: phpMyAdmin is garbage, IMNSHO. I could go on for a bit on why, but I won’t… just do yourself a favor and forget it.
Instead, download MySQL Workbench (http://www.mysql.com/products/workbench/), which is somewhat comparable to SSMS for SQL Server. It’s a far, far superior tool (you can also install an excel addin that works with MySQL Workbench for uploading excel data to MySQL… handy).
As far as tools for MySQL goes, that’s all you need. If you’re going to work with server scripting (e.g., PHP or whatever), you’ll want to look into an IDE for that – which often has MySQL integration – though I still prefer MySQL Workbench to any other IDE that I’ve worked with.
One other thing I’ll touch base on here is the documentation. MySQL has plenty of official documentation, but I can’t stand it, myself. I find it very hard to read and for all the times I’ve landed on one of the pages, I’ve never been able to easily and quickly find what I needed. Instead, a google search with a StackOverflow question has almost always fit the bill and gotten me on my way. This is uncharacteristic for me… I almost always prefer official documentation, but not for MySQL. If you like it, great, but I don’t, myself. With that said, MySQL Workbench does provide context sensitive documentation while you’re writing SQL statements, which is quite nice.
Considerations & Comparisons
Just a couple of quick notes on this end of things:
- The security model in MySQL is quite lacking compared to something like SQL Server. Where SQL Server can have various user roles and group policies and IIS/AD integration and a whole host of other models (including encryption, object level and more recently row level restrictions, etc), MySQL is basically limited to creating a user with a set of permissions. This isn’t bad per se, but if you’re familiar with SQL Server security, you’ll find the MySQL model a bit lacking.
- MySQL tends to be developer friendly, but it also tends to stray away from the standards quite a bit more than other RDBMSs. If you’re familiar with SQLite (which is a very “loose” RDBMS) and SQL Server (which is a fairly “tight” one), MySQL falls somewhere between those two. I don’t think this is either good nor bad per se, just that it is.
- MySQL performance on subqueries is pretty bad. In most cases, you’ll be far better to create a view of the subquery, then reference the view instead. Supposedly this should be improving with v6, but I haven’t looked into it yet myself.
- MySQL (v5, apparently loosened up some in v6) can only have one trigger per table action, unlike most other trigger-ready RDBMSs. This usually isn’t a problem given that triggers are most often best avoided anyway, but it’s worth noting.
- TIMESTAMP and dates tend to be a bit more of a pain to get under your thumb in MySQL than elsewhere
Aside from that, MySQL is just another RDBMS with yet another slightly different SQL syntax. It’s a very capable RDBMS for the right scenarios, and ODBC connections to it can make it easy to integrate some on premise software with web hosted data while each can use their “natural environment” so to speak. Once the database is created and you have access to it, the rest is straightforward enough. You can use DDL or GUIs to create your tables, with a syntax that only slightly varies from the others (eg, it’s about as different from Access and SQL Server as Access and SQL Server themselves are different from each other… think equilateral triangle. Not too bad).