To get the PubDate element of an RSS feed to validate, it needs to be in RFC-822 format. MySQL has flexible ways of working with dates and times which make this a simple task. Given a datetime column called pubdate, this select statement (which can be combined with other selections) will yield a column of dates named rfcpubdate formated in RFC-822 format: SELECT DATE_FORMAT(pubdate,'%a, %d %b %Y %T') AS rfcpubdate FROM tablename WHERE 1 The full RFC-822 date includes a timezone value which is not included in the MySQL output. If using PHP, this value can be appended to the output (assuming a variable $rfcpubdate exists containing a date string from the above select statment) using: echo "<pubDate>$rfcpubdate ".date('T')."</pubDate>"; This will yield output that looks like: <pubDate>Mon, 19 Jun 2006 07:41:18 PDT</pubDate>... Read More

 The mysql command provides a text-based interface into the MySQL database server. Once connected, SQL commands can be issued to the server to make queries, create or alter databases or tables, or many other operations.To connect to the MySQL server on the local system, issue the following command: mysql -u username -p Enter password: ******* Where username is the name of a user authorized to use the database (see Create a MySQL user account). The -p option will prompt for the user's password as shown below the command. If the MySQL server resides on a remote host, the mysql command, if present on the local system, can be used to connect to the MySQL server accross the network. To connect to a MySQL server on a host named 'elephant' use the following command: mysql -h elephant -u username -p... Read More

 MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other applciation which accepts data in CSV format.Given a query such as SELECT order_id,product_name,qty FROM orders which returns three columns of data, the results can be placed into the file /tmo/orders.txt using the query: SELECT order_id,product_name,qty     FROM orders     INTO OUTFILE '/tmp/orders.txt' This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query: SELECT order_id,product_name,qty     FROM orders     INTO OUTFILE '/tmp/orders.csv'         FIELDS TERMINATED BY ','         ENCLOSED BY '"'     &n... Read More

 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 sample table has many repeats in it. To select a list of the parties from the table, use: SELECT DISTINCT party FROM presidents; This returns a result set that looks like: +-----------------------+ | party                 | +-----------------------+ | no party              | | Federalist            | | Democratic-Republican | | Democratic            | | Whig                  | | Republican            | +-----------------------+ Using the DISTINCT keyword on q... Read More

 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 first to second, use this command as a user with adequate privileges: RENAME TABLE first TO second; It is good DBA manners to make sure that no one and no program are using this table before making the name change.... Read More

 Dump content of a database in MySQL to a text file. Will prompt for password of account used with '-p'mysqldump -u [username] -p [database_name] > /path/to/file.sql Example: mysqldump -u fred -p FredsAddresses > /tmp/addresses.sql... Read More

 Change the root user password for MySQL using mysqladminTo change the MySQL root password to PaSsWoRd, use: mysqladmin -u root password PaSsWoRd... Read More

 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 testing an application in development on live data without risking a production environment.To make a copy of the table recipes which is in a different database called production into a new table called recipes_new in the currently selected database, use these two commands: CREATE TABLE recipes_new LIKE production.recipes; INSERT recipes_new SELECT * FROM production.recipes; The first command creates the new table recipes_new by duplicating the structure of the existing table. The second command copies the data from old to new. The nomenclature production.recipes is a means of specifying the database and table in the same way that a file can be specified by its directory path. It is optional. If production was left off, ... Read More

 A nice shell script that can be run as a cron job to produce nice MySQL dumps for backup. I run several boxes with MySQL installed. My backup strategy for MySQL includes the following: - Each database needs to be dumped nightly to it's own file. - I keep all miscellaneous files (eg DB Dumps) under /backup - Since I retain the files for 4 or 5 days, I need to tag the files with the date they were dumped. Under /etc, create a subdirectory "db-backup": mkdir /etc/db-backup Create/edit the file /etc/db-backup/db-list.txt and put each database to be dumped in that file, one per line. The script, as posted here, can't deal with blank lines. A quick example would be (without the dashes): ---- mysql pdns mimerdesk phpbb ---- Then here's the backup script. I keep it in /etc/db-backup/db-backup.sh #!/bin/bash                                                      &... Read More

 A single instance of MySQL can hold many separate databases. It is important to specify the database to which subsequent commands will be issued.From within the mysql command (see Connect to a MySQL server using the mysql command for more information), the 'use' command selects the named database for use: use financial This command will select the database named 'financial' for use. Subsequent SQL commands will affect this database. The use command must be used on a separate line with no additional SQL commands.... Read More

 Creating tables in databases is an important first step to storing data. The CREATE TABLE statement is rich and sometimes confusing. This recipe describes the basics of creating a table in MySQL.Creating a table involves describing the columns and their attributes, whether they contain text, numbers, dates, and so on. In this recipe, we will create a table to hold contact information with four columns: contact_id, name, email, and birthdate. The contact_id column is an integer number that is 10 decimal places long (therefore, it is created with an INT(10) datatype). This column will act as the primary key for this table, although that is another recipe. The name column holds the full name of a contact, which we guess will be no longer than 40 characters long, so the datatype is VARCHAR(40). The contact's birthdate will be stored as a DATE datatype. The following SQL command will create a table called contacts as described above: CREATE TABLE contacts (    contact_id INT&#... Read More

 The SQL command in this recipe removes a column and the column's data from an existing MySQL table.To delete the column col_stuff from the table table_things, use the following SQL command: ALTER TABLE 'table_things' DROP 'col_stuff'... Read More

 A primary key uniquely identify a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like a person's social security number). When more than one column is used, the combination of column values must be unique.When creating the contacts table described in Create a basic MySQL table, the column contact_id can be made a primary key using PRIMARY KEY(contact_id) as with the following SQL command: CREATE TABLE `test1` (    contact_id INT(10),    name VARCHAR(40),     birthdate DATE,    PRIMARY KEY (contact_id) ); Additional columns can be identified as part of the primary key with a comma separated list in the PRIMARY KEY command, like PRIMARY KEY (contact_id, name).... Read More

 MySQL tables are easy to extend with additional columns.To add a column called email to the contacts table created in Create a basic MySQL table with a datatype of VARCHAR(80), use the following SQL statement: ALTER TABLE contacts ADD email VARCHAR(60); This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement: ALTER TABLE contacts ADD email VARCHAR(60) AFTER name; If you want the new column to be first, use this statement: ALTER TABLE contacts ADD email VARCHAR(60) FIRST;... Read More

 The best laid plans of mice and DBAs oft go awry, so it is sometimes necessary to change the characteristics of a column after it exists and contains data. Beware whenever you make changes to your database -- always make a backup first.After a week of using the contacts table created in Create a basic MySQL table, we may find that 40 characters for the column name doesn't cut it. To increase the size of the name column to 80 characters: ALTER TABLE contacts CHANGE name name VARCHAR(80); The first part of this statement (ALTER TABLE contacts CHANGE name) identifies that we want to change the column name in the table contacts. The second part of this statement (name VARCHAR(80)) redefines the column name. We could further define this column as NOT NULL, for example, with ALTER TABLE contacts CHANGE name name VARCHAR(80) NOT NULL;... Read More

 When using innodb tables and foreign key constraints, importing a mysqldump can sometimes generate foreign key errors. Add to the beginning of the dump file: SET FOREIGN_KEY_CHECKS=0; Add to the end of the dump file: SET FOREIGN_KEY_CHECKS=1; This disables foreign key checks for the mysqldump import session only, allowing the data to be imported without the error being generated.... Read More

 Once you have selected a database, you can list the tables in it by using the show command. To view all of the tables in the selected database, use: [code]SHOW TABLES; [code] To view all of the tables in a different database that isn't selected: [code]SHOW TABLES IN other_database; [/code] If you are looking for a specific table but don't remember it's exact name, you can use a wildcard with either of the above commands. For example, to find tables ending in "user" from the database "bigdb" use: [code]SHOW TABLES IN bigdb LIKE '%user'; [/code]... Read More

 A very cool and powerful capability in MySQL and other databases is the ability to incorporate regular expression syntax when selecting data. The regular expresion support in MySQL is extensive. This recipe reviews regular expression use in MySQL and lists the supported regular expression metacharacters.The basic syntax to use regular expressions in a MySQL query is: SELECT something FROM table WHERE column REGEXP 'regexp' For example, to select all columns from the table events where the values in the column id end with 5587, use: SELECT * FROM events WHERE id REGEXP '5587$' A more elaborate example selects all columns of the table reviews where the values in the column description contain the word excellent: SELECT * FROM reviews WHERE description REGEXP '[[:<:]]excellent[[:>:]]' MySQL allows the following regular expression metacharacters:    .         match any character    ?  &n... Read More

 A frequent error message received when using the mysql command line utility is: Can't connect to local MySQL server through socket '/tmp/mysql.sock' While this error message can be frustrating, the solution is simple.When connecting to a MySQL server located on the local system, the mysql client connects thorugh a local file called a socket instead of connecting to the localhost loopback address 127.0.0.1. For the mysql client, the default location of this socket file is /tmp/mysql.sock. However, for a variety of reasons, many MySQL installations place this socket file somewhere else like /var/lib/mysql/mysql.sock. While it is possible to make this work by specifying the socket file directly in the mysql client command mysql --socket=/var/lib/mysql/mysql.sock ... it is painful to type this in every time. If you must do so this way (because you don't have permissions to the file in the solution below), you could create an alias in your shell to make this work (like alias mysql="mysql --... Read More

 It is a good security policy to allow least privileges. Allowing all access to a system through a single account with all abilities is typically dangerous. Creating MySQL user accounts allows privileges to be granted as appropriate.To create a user jsmith with password Secret15 and allow them to do anything with the database named accounts, connect to the database with mysql and issue the command: grant all on accounts.* to jsmith@localhost identified by 'Secret15';... Read More