» SQL

Copy an entire database:

Copy a single table:

This shortcut only works if you can access the local DB without a password. If you have to login to both local and remote MySQL servers, the MySQL password prompts get mashed together. You could specify the password on the command line like

but your shell may keep the password in its history so that anyone with access to your account could pick through your shell history and retrieve the password. Using the MySQL password prompt doesn’t do this.

Popularity: 100%

Share This

For a new client project I was provided with database of users consisting of names, email addresses and managers.

The name column contained both first and last names formatted last name first like:

MySQL’s http://dev.mysql.com/doc/refman/5.0/en/string-functions.html” rel=”external”>TRIM() functions allow us to make quick work of this data manipulation. Using the query below and a new table similar to the first except with separate fields for first and last name we can copy the data to the new table and split the names in the process.

Popularity: 15%

Share This

For formatting the date see MySQL's date functions information.

Popularity: 20%

Share This

If you have a compound ORDER BY clause with a direction (ASC or DESC), make sure to specify the directions of all columns.

Correct:

Wrong:

The latter forces a filesort.

Popularity: 13%

Share This

I previously posted a Quick and Dirty MySQL Backup snippet that has gotten a bit of traffic. It works well in a pinch, but it doesn’t have any features. I use it primarily as a safety backup when performing maintenance on a MySQL server.

Several of my clients have requested a script that they can use to do on demand one-off backups. I rolled the snippet up into a very quick-and-dirty MySQL backup.

Download mysql_quick_back.sh

Installation

1. Copy mysql_quick_back.sh to a directory in your path (~/bin/ for me).
2. Set execute bits with
   chmod 755 mysql_quick_back.sh
3. Set MySQL user/pass, directory to save backups to and notification email in script configuration
   # START configure
   # MySQL user, needs SELECT privileges
   # Create a new user with these permissions!  More secure than using a user
   # with full permissions
   user=

   # MySQL password
   pass=

   # admin email address
   adminemail=

   # backup location without trailing slash
   # set to current directory
   backuppath=.
   # or set to your home directory
   #backuppath=/home/admin/db_backs
    

Usage

Simply execute the script

NOTE

For a full MySQL backup application, check out the original MySQL Backup by Peter Falkenberg Brown. MySQL Backup is robust enough to use as a daily backup. I use it on most of the servers I manage.

Popularity: 22%

Share This

In ZenCart versions immediately prior to 1.3.6 using many of the public side manufacturers functions may produce this error:

The error is caused by a missing database column that was added in the official 1.3.6 release. If upgrade is not immediately possible the following SQL snippet will correct the problem in most cases:

If you are using other product types, some additional UPDATE statements will be required for your new product types.

I found this solution after some searching on the ZenCart forums.

Popularity: 27%

Share This