MySql - oldest to most recent
Help and howto do things in MySQL
MySQL is the world's most popular open source database, recognized for its speed and reliability, and is frequently used with PHP to create websites. The Electric Toolbox website uses MySQL as its database backend, and I have personally been using MySQL since 1999. In that time it has proven itself to me to be an extremely fast and reliable database management system, although the earlier versions lacked some features found in other database systems, such as stored procedures, triggers, sub selects and joined table updates. Most of these are available from the 4.1 and 5.0 releases.
Both the e3 Commerce System and Minerva Mail were coded using MySQL and PHP, as were numerous other websites I have worked on.
Post sort order: Post Date (Newest First) | Post Date (Oldest First) | Alphabetical | Date Updated
MySQL Control Center
Posted December 10th, 2003 in MySql (Updated August 19th, 2004)
The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.
MySQL Control Center - Source Install Text File
Posted December 10th, 2003 (Updated August 19th, 2004)
The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.
MySQL Control Center - Installing on Linux
Posted December 10th, 2003 (Updated August 19th, 2004)
The MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.
phpMyAdmin
Posted December 10th, 2003 in MySql and PHP (Updated February 24th, 2004)
phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL using a web browser and is one of the most active projects on Sourceforge.
MySQL Control Center Returns 1000 Rows
Posted January 14th, 2004 in MySql (Updated May 24th, 2005)
The MySQL Control Center is a cross platform GUI for running queries on a MySQL database server. This article shows how to get mysqlcc to return more than the default 1000 rows by editing the server registration preferences or using the MySQL LIMIT syntax
Cross Table Delete with MySQL
Posted March 1st, 2004 in MySql
Deleting records with MySQL can be done by referencing records in another table with a cross join, allowing data to be deleted from one table based on values in another, or where there are no associated records in the other table.
Cross Table Update with MySQL
Posted March 1st, 2004 in MySql
Using MySQL version 4.0 or higher you can update a table by joining two or more tables together; this allows one table to be updated based on fields in associated records in another table.
Errors Using mysqlimport to Import Data Into MySQL
Posted March 21st, 2004 (Updated May 24th, 2005)
The article covers the errors that can occur when using mysqlimport to import data into the popular open-source MySQL database server.
Delete All Data in a MySQL Table
Posted July 16th, 2004 in MySql (Updated May 24th, 2005)
This article looks at how to delete all the data from a MySQL database table and how it affects auto incremental fields. The final section questions whether you really do want to delete all the data from that table.
Formatting Dates and Times with MySQL
Posted July 17th, 2004 in MySql (Updated May 24th, 2005)
Dates and times are stored in MySQL in the format "YYYY-MM-DD" and "YYYY-MM-DD HH:MM:SS" which is not necessarily the format you want to display in your web page or application. There are two methods to reformat the date and time into the desired format.
mysql_upgrade relocation error
Posted September 30th, 2007 in MySql
After upgrading openSUSE 10.2 to 10.3 my MySQL database server wouldn't start. I didn't even notice it wasn't running until I went to do some development work on one of my websites and it wouldn't connect to the MySQL database.
When attempting to start the MySQL database server I would get the error message: Updating MySQL privilege database... /usr/bin/mysql_upgrade: relocation error
Logging queries with MySQL
Posted October 24th, 2007 in MySql
It is possible to log all queries done by the MySQL server. To enable MySQL logging, the MySQL manual indicates you should add --log[=file_name] when starting mysqld. This isn't really the best options, as most Linux distributions use init scripts to start up MySQL, and this means either modifying them, or calling mysqld from the command line, which isn't really the best way of doing this when the init scripts do the job of starting and stopping services so nicely. So instead, it is easier to modify the my.cnf file (located at /etc/my.cnf on many Linux distros) and add the logging option there.
MySQL Backups with a Command Line PHP Script
Posted December 9th, 2007 in MySql and PHP
I run a number of web servers with PHP and MySQL and have a PHP command line script that runs on a daily basis to back up MySQL databases using the mysqldump command. It would be possible to do this using a simple bash script as well, and I know I used to use a bash script in the past but for some reason switched it to PHP at some stage.
SQL query to work out top selling categories
Posted December 11th, 2007 in MySql
I sell Linux on CD on my Linux CD Mall website, and it has a page showing the most popular Linux and BSD distributions that it sells. For some reason, I'd never set this up to query the information from the database, instead manually updating it periodically. Today I decided to actually get around to making it read the information from the database and have decided to share the SQL queries etc used to get the information for top sellers by category.
MySQL Query Cache
Posted December 12th, 2007 in MySql
MySQL has a query cache which caches the results of SELECT queries, if enabled. This means that frequently used database queries will run much faster, because the data resultset will be read from the cache instead of having to run the query again. The MySQL query cache is available from MySQL 4.0.1. Whenever tables in the database are modified the relevant entries in the query cache are flushed so you can be certain that even with the query cache enabled only up to date data is returned.
Backing up MySQL with mysqldump
Posted December 14th, 2007 in MySql
A simple way to back up MySQL databases is with the mysqldump command line tool. Mysqldump can be used to back up a single database or multiple databases, and can backup MySQL databases into a text file conatining multiple SQL statements, or into CSV or tab delimited text files.
Using mysqldump to save data to CSV files
Posted December 15th, 2007 in MySql
Yesterday I looked at basic use of mysqldump for backing up MySQL databases. Today I will look at how to use mysqldump to dump the data from a MySQL database into CSV and tab delimited text files, instead of using SQL insert queries which is the default dump method.
Restore data from a tab delimited file to MySQL
Posted December 16th, 2007 in MySql
Yesterday I looked at using mysqldump to save data to CSV files, which by default saves the exported data as tab delimited files. Today we will look at how to restore data from these files into a MySQL database. It's very simple to do, using the mysqlimport command line tool.
Showing running queries in MySQL
Posted February 19th, 2008 in MySql
MySQL has a statement called "show processlist" to show you the running queries on your MySQL server. This can be useful to find out what's going on if there are some big, long queries consuming a lot of CPU cycles, or if you're getting errors like "too many connections".
Executing shell commands from within the MySQL command line client
Posted April 11th, 2008 in MySql
The MySQL command line client allows you to quickly and easily run sql queries from a console session, as well as load sql script files etc. Once you have started up the MySQL command line client it is possible to execute shell commands from within the console and even drop out to another bash (or similar) session. This post looks at how to do this.
Using the MySQL command line tool
Posted April 13th, 2008 in MySql
MySQL has a useful command line tool which can be used to query and manage databases and users. This command is simply "mysql" and will usually be in the command path on Linux and BSD distributions, although to use it on Windows you would normally first need to change to the directory/folder that the MySQL binary applications are before running "mysql". This post gives a brief overview of logging in and a couple of commands for listing and changing databases, and running queries
Running queries from the MySQL Command Line
Posted April 16th, 2008 in MySql
The MySQL command line tool allows you to run queries and administer databases from the command line. In previous posts I have looked at the basics of using the MySQL command line tool and executing shell commands. In this post I will look at running queries and the output from these.
Find the length of the longest string in MySQL
Posted May 9th, 2008 in MySql
There are a number of string functions in MySQL for extracting text, working out the position of a substring, calulating the length of text and so on. This post looks at how to work out the length of the longest string in a field in a MySQL table.
Listing tables and their structure with the MySQL Command Line Client
Posted May 18th, 2008 in MySql
The MySQL Command Line client allows you to run sql queries from the a command line interface. This post looks at how to show the tables in a particular database and describe their structure. This is the continuation of a series about the MySQL Command Line client. Previous posts include Using the MySQL command line tool and Running queries from the MySQL Command Line.
Using "group by" and "having" with MySQL
Posted June 5th, 2008 in MySql
With SQL queries you can combine the "GROUP BY" syntax with "HAVING" to return rows that match a certain count etc. This post looks at how to return rows based on a count using having specifically tested on MySQL but it should work for other database servers as well.
Check if a MySQL table exists
Posted June 13th, 2008 in MySql
MySQL has a couple of ways (that I know of, there may be more) of working out if a table exists. This post looks at how to check if a table exists in the MySQL database.
PHP function to check if a MySQL table exists
Posted June 14th, 2008 in MySql and PHP
Yesterday I posted how to check if a MySQL table exists using show tables or the MySQL information schema. Today I am posting a simple PHP function I created which you can use to test if a table exists.
Reset the auto increment value for a MySQL table
Posted June 28th, 2008 in MySql
It is possible to reset the auto increment value of a MySQL auto incremental primary key to a new value, either higher or lower than what it would otherwise next be. This post looks at how to do this using a MySQL query and also with phpMyAdmin.
Renaming a table in MySQL
Posted July 2nd, 2008 in MySql
Sometimes you need to rename a table in MySQL. You either run an SQL query to do this or do it with phpMyAdmin if you don't want to bother remembering the SQL to do so. This post looks at how to rename a table in MySQL either with a SQL query or using phpMyAdmin.
RSS Feed