This is a pre-release version of my book, MySQL Simplified. To sign-up to hear when it's ready to ship, enter your e-mail below!
MySQL has some basic, command-line tools you can use to develop a database model and maintain a server. Oracle even releases a GUI for running your queries, modeling your database, and administering your server. The MySQL community in general has created tons of clients, tools, and scripts, both free and paid, to fit your every need. The hardest part of MySQL is not finding a tool, it’s picking out which of the 10 tools there are will be best.
mysql. This is the standard command line app that you can use to run queries. To logon interactively:
$ mysql -u <username> -p <database>
This will prompt you for a password, and you can now login. You’ll see the mysql> prompt. This will allow you to run any valid SQL query (along with the MySQL-specific commands like Show Databases).
To list the databases on the current server, type:
> Show databases;
This lists out all the databases that have been created on the system. Most installations come with mysql, performanceschema, informationschema, and test. The mysql database is where all the server information is stored. To navigate to a database, you run the use command:
> Use mysql;
Now we’re in the mysql database. To see what tables are available to your, run:
> Show tables;
To peak into a table’s definition, just run:
> Desc db;
Now, this might feel like a text-based adventure game, but when you’re connecting to a remote server that’s overloaded, the lightweight nature of this client is invaluable.
The output is formatted into tables, which is possible since most terminals use fixed-length fonts so every character is the same length. If you have a wide table with many columns, or data that’s pretty long, you can also vertically align the results by adding \G to the end. You can use different pagers. This is any program (along with options) that takes text and spits it back out again. This means you can use less, more, or grep if you’d like. This is a *nix thing, so Windows users are out of luck. To change the pager, just run:
> pager less
If you just have a script to run, you can pipe it into mysql. For example, if you have a file called pre-migration.sql, you can run it against your server by typing:
$ mysql -u <username> -p <database> < pre-migration.sql
You can run a file while you’re inside of mysql by using the source command:
> Source pre-migration.sql
One other feature is that you can use this to write a file to CSV on your file system from any table (or dump out the results of query). If you have a bunch of data or maybe you want to copy parts of a table from one environment to another, this allows you to filter out data with a query:
> Select col1, col2, 'Sanitized data' as col3 Into Outfile '/tmp/bkp-sanitized.sql' Fields Terminated By ',' Optionally Enclosed By '"' Lines Terminated By '\n' From a_table;
mysqldump. This is the program that reads data in your server and then spits out the SQL commands to recreate that data. So, let’s say you have a database called “egypt” and that you have one table in that database, and it looks like this:
> Desc pharaohs; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.02 sec)
Let’s say it has one row in it:
> Select * From pharaohs; +------+ | name | +------+ | Ozzy | +------+ 1 row in set (0.00 sec)
Running mysqldump with now produce those statements to restore the database, this table, and its one row:
$ mysqldump egypt ... DROP TABLE IF EXISTS `pharaohs`; CREATE TABLE `pharaohs` ( `name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `pharaohs` VALUES ('Ozzy'); ...
This is the tool that is used to create logical backups of your data (logical in this case means that we’re creating SQL commands to recreate the data vs copying the exact bytes). It’s pretty fast, stable, and well-documented. We’ll get deeper into its use in backing up your system in Backups & Restore. Right now, however, we can see how to grab data from one system (like production) and copying it to another (like development).
The basic use for mysqldump to extract a database:
$ mysqldump -u <username> -p <database>
You can also just pipe that to a file:
$ mysqldump -u <username> -p <database> > /tmp/bkp.sql
To get all the databases:
$ mysqldump -u <username> -p --all-databases > /tmp/bkp.sql
You can also just target a single table:
$ mysqldump -u <username> -p <database> <table1> [<table2> … <tableN>] > /tmp/bkp.sql
If you’d like to get just a table definition, add --no-data
$ mysqldump -u <username> -p <database> > /tmp/schema-bkp.sql
This is useful if you want to do a diff on two environment’s schemas to make sure they’re the same. Sometimes, you want to extract just one database out of a dump of all of them. Here’s a tip to do that:
$ sed -n '/^-- Current Database: `<database>`/,/^-- Current Database: `/p' full-dump.sql > <database>.sql
This tip comes from Darren Mothersele.
mysqladmin. This tool is really useful for one thing: it can shutdown your database. Most of the other functions are merely shortcuts for SQL commands. Here’s how you shutdown your database:
$ mysqladmin -u <user> -p shutdown
There’s other functions, like creating a database:
$ mysqladmin -u <user> -p create <new_database>
Or dropping one:
$ mysqladmin -u <user> -p drop <database>
Sometimes when there’s been too many failed logins from a single host, MySQL will block it, so you can run flush-hosts to reset the counter:
$ mysqladmin -u <user> -p flush-hosts
[Workbench just has two modes in 6.0: management is rolled into the query mode]
Workbench is the standard GUI for using with MySQL databases. There are alternatives, but it’s the only GUI that works well and is consistent across Windows, Mac, and Linux. It’s not the most powerful nor the most stable, but it provides you a fairly useful tool, and it’s a darn sight better than some of the other tools out there.
There are three basic modes: SQL Development, Modeling, and Server Administration. The development mode allows you to write queries, see the current structure, create procedures, triggers, and functions, and run basic CRUD operations on data, using a table interface or just by writing SQL.
There’s also a couple of safety valves that come standard: you are limited to 1000 rows per update and you cannot use an update or delete statement unless you have a where clause that references a primary or unique key. To enable or disable these features, go to Edit -> Preferences -> SQL Editor. However, it also comes with a anti-safety feature: autocommit is on by default which means any statement outside of transaction will be saved to the database. You’ll can disable that by clicking on the icon with lightning bolt with checkmark next to it.
If you’ve used a SQL GUI before, this is a pretty standard layout. To use a database, double-click on it (if you haven’t set a default database, you’ll need to do this). You can run your queries either by clicking on the lightning bolt icon or by pressing CTRL+ENTER. If you’d like to run one statement at a time, just select that statement and then press CRTL+ENTER.
Once you select values, a result set will pop-up. From there, you can modify data, insert new rows, or delete a row. To save your changes, click “Apply”. This will show you the SQL that will run, so you can either save that off or just click execute.
The Modeling mode is great for creating a graphical tree representation of all your tables and the relationships between them. You can import an existing database, and it’ll setup the basic canvas for you to begin. Don’t get caught up on it too much since it’s just a fancy form of documentation, and documentation rarely gets read, but it can take a while to create.
Admin mode is pretty basic: it has a nice GUI for changing the server’s options, you can add or create users, and you can see the basic performance counters that MySQL exposes. Everything you can do with admin mode you can do with the command line. I’ve never really used it after an initial experiment with it.
Beyond MySQL Workbench, there are third-party apps to use with MySQL. Workbench is usable enough, so if you’re not too picky, I’d just skip over this section.
HeidiSQL. This is a small, fast client that interacts with MySQL as well as SQL Server. It’s Windows-only, but it works nicely for the basics like looking at table schema, running queries, and dumping a table out to SQL statements. A nifty feature is the find text on server. Just click Search -> Find text on server, and it’ll search through all columns on all tables. Needless to say, this should not be run on production.
Navicat for MySQL. This runs on Mac, Windows, and Linux. It’s a sleek program that is pretty much a commercial version of Workbench. There’s some added bonuses: you can create canned reports, manage batch jobs (like backing up), and transfer data between two instances.
Then there’s Navicat’s killer feature: synchronization. You can synchronize the schema between two instances, either by allowing the wizard to execute the SQL to sync the two or dumping the SQL and running it yourself. It can also sync data between two instances, only making running the SQL necessary to eliminate the differences instead of just copying the whole table over. Note that both of these are just one-way: they make the destination server like the source server.
MySQL Compare. This brings me to the Redgate’s MySQL Compare. This product allows you to copy database schema and data between two servers. Why would you spend so much on this feature when Navicat includes it in their product? Simple, Redgate offers you a little more security since they’re fairly well known in the database world, and they have more features in their synchronization programs. Choose Navicat if you just need simple comparison, but choose MySQL compare if you have more advanced needs.
If you already know the basics of using *nix commands, then you can skip over this section. If you have no idea what *nix, means, that’s just an abbreviation for Linux/Unix, so any system that uses Linux/FreeBSD/Mac OS X. I’m specifically covering Bash, which is the shell program that comes standard on CentOS, Ubuntu, Mac OS X, etc.
Open up Applications -> Utilities -> Terminal.app
I’m only going to cover Unity, because if you know enough to not use it, you know where Terminal is.
Just press: CTRL + ALT + t
The first thing you need to know is how to RTFM, which is short for Read the Fine Manual. The F is sometimes replaced by another word that we needn’t mention, especially when a newbie asks a question easily answered by the standard documentation provided by a program. So, how to access that documentation? Excellent question! That’s what the man command comes in.
$ man mysql
This will print out the basic manual for using the mysql program, including a description, the options you can pass to it, and maybe some examples. You can use the up and down arrow keys to scroll, and when you’re done, you can press q. To search, press /, type the word you’re looking for, and press enter. The man program is the gateway to all other commands.
When you first login to bash, you’re put into a directory, typically your home directory. Let’s see what files we have:
This prints out a nice list of files. To see more information about them, type:
$ ls -l
This shows the permissions, the owner user and group, the last modified date of the file, and how big it is. The dash l is called an option or flag. There’s often a short version that’s just a single character and prefixed by one dash, as well as a long version that’s a spelled out word or phrase prefixed by two dashes. To show everything in the directory, just add “a” to the flag, and you’ll see all the files and folders that begin with dots, which by default are hidden. You can also add “h”, and then the file sizes show up in human-readable sizes like kilobyte, megabyte, and gigabyte.
$ ls -lah
To copy files, you use the cp command, and simply write the source filename and destination filename:
$ cp oldfile newfile
To move (or to rename) a file, use the mv command:
$ mv /path/to/oldfile /new/path/to/newfile
Often, you’ll need to create a new folder, and you can use the mkdir command:
$ mkdir new_directory
Note that we didn’t include spaces. Spaces confuse Bash, so it’s generally good practice to avoid them in file and folder names.
When you want to peek inside a file, you can use the cat command:
$ cat file.txt
This just prints it out to the terminal. Don’t be silly and use this when you could use a file redirect:
Wrong: $ cat script.sql | mysql Right: mysql < script.sql
Sometimes, you want more than just to see a file, you need to modify a file. For this, you should use vim. It’s just a dead simple text editor that’s really handy and works over an SSH connection just as well as if it were being run locally. To edit a file:
$ vim somefile
To edit text, press i. This takes vim from command mode (where you, well, run commands) to edit mode (where you can type). You are then free to move about the text and make changes. To quit, press ESC (if you’re in command mode) and then type :q if you want to quit and you haven’t made changes, :q! if you’ve made changes but want to discard them, or :wq if you want to write the change. Getting to know how to best use vim is a whole book in and of itself. However, it’s worth it.
Now let’s say you want to remove a file. Type:
$ rm somefile
To remove an empty directory, enter:
$ rmdir some_directory
Now, if you have a directory and you’d like to delete it and all of its files and folders, run the rm -rf command:
$ rm -rf some_directory
This is the command that will cause you to prematurely age: if you don’t specify a folder, or type / by accident, it will wipe out your server or at least, all of your own data. So be very, very careful about running it. Think twice, press once, or something like that.
On Ubuntu and OS X, you often need to elevate your privilege level by running sudo. This runs whatever command you prefix as root (or the system superuser). So, this will run the remove command as root:
$ sudo rm -rf /var/lib/mysql
Use this sparingly, as root has permission to do anything. You can also use sudo -s which login you in as root or su -
Searching through files is also something you’ll do often, which is where grep comes in. To search a file for a phrase, type:
$ grep search_pattern a_file.sql
To run an inverse search, just add -v, and you’ll get all the lines that don’t match the pattern. You can also use regular expressions, if you’re into that kind of thing.
Unix is made for piping. Almost every program has a input and an output. To direct a file into a program, use the the less than symbol:
$ mysql < a_file.sql
To redirect a program’s output to a file, use the greater-than symbol (>).
$ mysqldump > /tmp/bkp.sql
If you want to connect the output of one program to the input of another program instead of a file, use the pipe (|):
$ mysqldump | gzip > /tmp/bkp.sql.gz
top. Shows you the running processes on the system, as well as the overall load and uptime.
vmstat. Running vmstat -SM shows you the basic counts in your system: memory use, swap space, IO, and CPU. You can also have it print out more detailed information with different options.
free. Gives you the breakdown of memory, and let’s you peak into the buffers and caches the system is using. The system is okay if there’s nothing in the free column in the Mem row, as long as the buffers/cache row has some free memory.
iotop. Similar to top, this shows you the IO activity in your system. This is generally an extra feature, so you might have to use your system’s package manager to get it.
netstat. This is takes a snapshot of the network ports open on your system. You can see which ports are listening by running netstat -ln.
du. Prints out how much disk is used. To find out which folder is using the most data, run as root:
Linux: $ du --max-depth=1 -h / Mac: $ du -d 1 -h /
df. Shows you by disk how much space is used. Add the -h flag to have it print in human readable sizes.
tar. This one’s a toughie to remember, so I’d recommend googling for tar commands when you get stuck. A tar file is just a method of making a directory look like a file. It can either be uncompressed or compressed. To extract a file:
$ tar -xf a_tarfile.tar
To extract a compressed file:
$ tar -xzf a_tarfile.tar.gz
To create an uncompressed tar file:
$ tar -cf a_tarfile.tar /a/directory
To create a compressed file:
$ tar -czf a_tarfile.tar.gz /a/directory/
gzip. This is a great little program. To compress a file, either pipe it through gzip, or run gzip
$ mysqldump <database> | gzip > /tmp/bkp.sql.gz
That’s a nice, compressed backup you have there.
tail. To peak at the tail end of a log or file, just run tail. To specify how much you want to see, just add a dash and number of lines you’d like, so to get 100 lines, type:
$ tail -100 <filename>
Another useful trick is tail -f. This keeps tail open so you can see log entries as they are written.
head. This is the converse of tail: it reads the first 10 lines of a file. You can also specify a different number with a dash.
lsof. This shows you the input/output handles that are open in the system. With grep, you can do something like:
$ lsof | grep mysql
To see what files mysql has open.
cron. This is the tool that’s used schedule tasks. I’m only going to mention the cron app briefly, since it is its own tutorial. I’ll show you how to use it when its necessary, but you’ll need to do some research on your own if you’re unfamiliar with it.
ssh. This is the app that allows you to securely talk to another server across an insecure network. Once again, this is a big subject, so please do your research on this topic if you haven’t already.
scp. You can use this to combine ssh and cp, allowing you to copy back and forth between to servers. The commands are pretty simple:
$ scp <source_host>:<source_file> <destination_host>:<destination_file>
So, for example:
$ scp bkp.sql.gz firstname.lastname@example.org:/tmp/bkp.sql.gz
netcat. This allows you open a port, read a file on one machine and then on another, read from that port and pipe the output to file. It’s basically a way to transfer files, but you can also do other fun stuff with it. Here’s an example:
Source server: $ nc -l -p
© Copyright 2011-2018 Noel Herrick. All rights reserved.