Backing up your database is a very important system administration task, and should generally be run from a cron job at scheduled intervals. We will use the mysqldump utility included with mysql to dump the contents of the database to a text file that can be easily re-imported. Syntax: mysqldump -h localhost -u root -pmypassword [...]
Read the rest of this entry »Archive for the 'Databases' Category
Feb 24
Feb 11
Jun 22
Copy an existing MySQL table to a new table
This is a great set of two commands that allow the creation and population of a new table with the structure and data of an existing table. This provides a quick means of making a point-in-time copy of a table and is a safe, easy way to make a quick copy of a table for [...]
Read the rest of this entry »May 16
Import CSV file directly into MySQL
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax. To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty [...]
Read the rest of this entry »Mar 14
Ignore duplicate entries in MySQL select using DISTINCT keyword
Sometimes every occurance of a value which may be duplicated multiple times in a result set is not needed. For example, if making a pulldown menu list of options, each option should be seen only once. The DISTINCT keyword in a select statement eliminates duplication in the result set. The column party from the presidents [...]
Read the rest of this entry »Mar 05
Rename or change name of MySQL table
If you change your mind and want to rename an existing MySQL table, with or without data in it, it is no problem. One simple command will change the table’s name. To change the name of an existing table old to new, use this command as a user with adequate privileges: RENAME TABLE old TO new; It is [...]
Read the rest of this entry »