MySQL

DROP all MySQL tables from the command line

I had a situation today where I wanted to drop all the tables in a database, but didn't have access to a UI like phpMyAdmin. The easiest thing to do would have been to drop the entire database then re-create it, but I didn't have permissions to drop an entire database.

After searching around online, this is the best solution I found:

Export a single row from a mysql table

While it doesn't happen that often, occasionally I'll need to export a certain subset of data from a MySQL table instead of the entire dataset. You can do this with mysqldump using the --where option which allows you to apply a WHERE clause that filters the data in your tables based on a certain condition.

Let's say in Drupal, you'd like to copy a single comment from one table into another.

Making long SQL output easier to read

I've been doing a ton of MySQL work lately, migrating millions of records of content to a new Drupal platform.

One problem I ran into using the MySQL command-line client was trying to read the output of columns with long text content.

Tags:

Joining MySQL tables across multiple databases

I ran across a scenario today in which I needed to purge records from a table based on what was in a similar table in another database.

I had 2 user tables in different databases but the tables had some overlapping data.

Preventing Encoding Issues with mysqldump

Creating a MySQL dump file of your database can be fairly straightforward, but, if you aren't careful, you could corrupt the character set in your backup file.

I used to created a database dump using a command like the following:

mysqldump -u nathan -p database1 > database1.backup.sql

The above command is very straightforward in that it generates a dump file using default options and redirects the output to a file instead of to standard output.

However, I want all my data to maintain a UTF-8 character set.