MySql - most recent to oldest updated
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
How to restore the debian-sys-maint MySQL user
Posted November 18th, 2011 in MySql
My last post looked at how to fix ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' on MySQL and this article shows how to restore the debian-sys-maint user if you've accidentally deleted it. I accidentally deleted the user myself when I reset a MySQL database using the /usr/bin/mysql_install_db script.
ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Posted November 18th, 2011 in MySql
If you've moved a MySQL installation from one Debian server to another and/or replaced the user logins on your system, you might get the error message "ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)" when attempting to restart MySQL.
List stored procedures in MySQL
Posted October 5th, 2011 in MySql
This post shows how to get a complete list of stored procedures in a MySQL database and then to see what code is used in the stored procedure.
How to find and replace text in a MySQL database
Posted September 29th, 2011 in MySql
There are a few posts on this blog about the Facebox jQuery plugin, and as I discovered this morning the website URL for the plugin has changed so I needed to update all links to it. The quickest way to do this was to update the MySQL database directly using UPDATE and REPLACE to find the old URLs and replace them with the new URLs. So here's how to find and replace text in a MySQL database.
Location of MySQL's my.cnf file
Posted November 8th, 2010 in MySql (Updated August 11th, 2011)
MySQL's main configuration file is my.cnf and it is located in different locations depending on the operating system, distribution and version. This post is a quick reference to show where it is located on various different installations.
MySQL table is marked as crashed and should be repaired
Posted August 2nd, 2011 in MySql
How do you fix the error when running a query on a MySQL table and get the following error: "ERROR 145 (HY000) at line 1: Table '<tablename>' is marked as crashed and should be repaired". Very easily, but it may take some time depending how big the table is.
MySQL connections and PHP forked processes
Posted July 5th, 2011 in MySql and PHP
It is possible to fork a process with PHP and have one or more child processes running. If a connection has already been established to a MySQL database and the child processes run queries it's quite likely a "MySQL server has gone away" or a "Lost connection to MySQL server during query" type error will occur.
How to change the storage engine a MySQL table uses
Posted January 17th, 2009 in MySql (Updated June 27th, 2011)
The last MySQL post looked at how to tell which storage engine a MySQL table uses by running a SQL query or using phpMyAdmin. This post looks at how to change the storage engine used by a MySQL table using either a SQL query or phpMyAdmin.
How to tell which storage engine a MySQL table uses
Posted January 13th, 2009 in MySql (Updated June 27th, 2011)
MySQL supports multiple storage engines (e.g. MyISAM, INNODB, etc) each with its pros and cons, and each table in a MySQL database can have a different storage engine selected. This post looks at how to work out which table storage engine is used by a MySQL table, using either a SQL query or using the web browser tool phpMyAdmin.
PHP script to convert MySQL tables to a new storage engine
Posted June 27th, 2011 in MySql and PHP
MySQL supports multiple storage engines which each have their pros and cons; the two most commonly used are MyISAM and INNODB. It's easy to convert a table from one storage engine to another, and this post has a PHP script which convert all tables in a MySQL database from one engine to another.
Errors Using mysqlimport to Import Data Into MySQL
Posted March 21st, 2004 in MySql (Updated May 17th, 2011)
The article covers the errors that can occur when using mysqlimport to import data into the popular open-source MySQL database server.
PHP script to export table creation SQL from MySQL
Posted May 10th, 2011 in MySql and PHP
I was trying to export the structure of a MySQL database using phpMyAdmin but it kept timing out and not showing me the full create script. I think phpMyAdmin uses the information schema to get this information and its on a host with hundreds of databases and tables so querying the information schema runs very slowly. Instead I knocked together a quick PHP script to dump the structure instead and share it here.
Master master replication with MySQL
Posted January 14th, 2011 in MySql
MySQL databases can be replicated using master-slave replication and also with master-master replication. There are plenty of tutorials online showing how to do master-slave but not many showing master-master replication and the most popular one I found was a little hard to follow. Therefore I've written this post to show how to do master-master replication with MySQL.
Load JSON data with jQuery, PHP and MySQL
Posted March 2nd, 2010 in Javascript, MySql and PHP (Updated December 1st, 2010)
This post shows how to populate a select box based on the value of the another, by getting JSON data with jQuery from a PHP script that gets the data from a MySQL database.
Formatting Dates and Times with MySQL
Posted July 17th, 2004 in MySql (Updated August 16th, 2010)
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 SQL_SELECT_LIMIT
Posted March 24th, 2010 in MySql
I came across a MySQL setting recently called SQL_SELECT_LIMIT which limits the maximum number of rows returned from a SQL query without having to specify the limit in the query. It applies the limit to all queries from the current connection until reset or changed to a different value.
Ordering by specific field values with MySQL
Posted March 17th, 2010 in MySql
There may be times when a specific order is required in a SQL query which cannot be done using either ASC or DESC or using a special sort field. MySQL has a ORDER BY FIELD function which can be used to do this.
Load data into MySQL with foreign key constraint issues Part 1
Posted January 27th, 2010 in MySql (Updated March 10th, 2010)
One of the MySQL databases I work with has a couple of hundred INNODB tables set up with foreign key constraints. I needed to copy the database using mysqldump to export data from one server and then load that into another but kept getting foreign key constraint errors. This post looks at how to load data from a file with MySQL and ignore foreign key constraints.
Load data into MySQL with foreign key constraint issues Part 2
Posted March 10th, 2010 in MySql
I recently showed how to load data into MySQL with foreign key constraint issues when loading a backup from tab delimited text files. This post provides a BASH command line script to generate the SQL to load all the files to save some time.
Randomly ordering data with MySQL with a random value column
Posted March 3rd, 2010 in MySql and PHP
It's possible to order data randomly with MySQL using ORDER BY RAND() or doing a count on the table and then using LIMIT to choose a random offset. However neither of these are suitable for dealing with large tables with many rows. In this post I present an alternative to these but it does require modifications to the table and additional business logic and is still not perfect.
Fetching data using PHP and PDO with bound placeholders
Posted February 26th, 2010 in MySql and PHP
This post shows how to fetch data from a MySQL database using PHP's PDO library with bound placeholders. This is a fairly basic tutorial and the functions used in it will be used in a post tomorrow titled "Load JSON data with jQuery, PHP and MySQL".
Another alternative for randomly ordering data with MySQL
Posted February 24th, 2010 in MySql
This time last week I mentioned I had another solution to pulling records out of a table at random with MySQL (compared with ORDER BY RAND() and LIMIT). However I've had some issues with the actual "randomnesss" where it seems to favour a particular set of records so have had to postphone the article and will post it when I've ironed the wrinkles out.
An alternative to ORDER BY RAND() for MySQL
Posted February 3rd, 2010 in MySql and PHP (Updated February 23rd, 2010)
I've posted previously about how to randomly order a resultset with MySQL using RAND() but the issue with RAND() is it will be inefficient on large tables because each row needs to have the random number calculated before the resultset can be ordered. This post looks at an alternative which requires two queries but will be much more efficient for large tables.
MySQL's LIMIT syntax can be slow for large offsets
Posted February 17th, 2010 in MySql
A couple of weeks ago I posted an alternative to ORDER BY RAND() when using MySQL which uses MySQL's LIMIT syntax to jump to a record chosen at random based on the number of records in the table. I received a Tweet earlier this week which pointed out that LIMIT is slow when dealing with large offsets so take a look at this here.
Example table for MySQL
Posted February 10th, 2010 in MySql
In my MySQL posts I've tended to use fruit in my example tables, so in this post will standardize the table structure and data that appears in future examples. Those future posts will refer back to this post for the table structure and base data.
Using INSERT IGNORE with MySQL to prevent duplicate key errors
Posted January 20th, 2010 in MySql
An error will occur when inserting a new record in MySQL if the primary key specified in the insert query already exists. Using the "IGNORE" keyword prevents errors from occuring and other queries are still able to be run.
Convert a UNIX timestamp to a datetime with MySQL
Posted January 13th, 2010 in MySql
Dates and times can be stored as an integer value as a UNIX timestamp. This isn't a particularly human readable format but can be converted in MySQL to a datetime value using the FROM_UNIXTIME function.
MySQL: Using IF in a WHERE clause
Posted January 6th, 2010 in MySql
I recently needed to use an IF statment in a WHERE clause with MySQL. This isn't the most ideal situation and should probably be avoided normally but we needed to do it for one reason or another and this post shows how to do it.
MySQL utility commands
Posted December 30th, 2009 in MySql
This post is a summary of MySQL posts from this blog relating to MySQL utility commands, such as describing table structure, dropping columns from a table, and so on; and server settings.
String concatenation with MySQL - Part 1
Posted March 4th, 2009 in MySql (Updated December 16th, 2009)
MySQL has a number of string handling functions including functions to concatenate text or database columns into a single column in the resultset. I'm always forgettingthat the function is CONCAT and not CONCATENATE so maybe by writing about it I will actually remember...
String concatenation with MySQL - Part 2
Posted December 16th, 2009 in MySql
A few months ago I posted about string concatenation with MySQL using the CONCAT function and in this follow up post look at the CONCAT_WS function which glues the fields together with the specified string. CONCAT_WS means CONCATenate With Separator i.e. join the fields together with a specified separator string.
Auto increment a value on the fly with MySQL
Posted December 9th, 2009 in MySql
By assigning a value to a variable in MySQL and incrementing the variable as part of a select statement, it's possible to have anumber auto-incremented on the fly. This number has no other reference to the table other than the order the data is selected . It is also possible to auto increment the variable when running an update statement so a new set of incrementing values could be stored into the table.
MySQL Query Browser "The memory load of the system is extremely high" error
Posted December 2nd, 2009 in MySql
The MySQL Query Browser is a GUI tool for running queries on MySQL database servers. This post looks at an issue where the query browser reports an issue with memory load being extremely high and prevents the query from running. There are versions of the MySQL Query Browser for Windows, OS/X and Linux (or you can compile from source); I got this error on the Windows version and it may or may not affect the other platforms.
MySQL: SQL to drop a column from a table
Posted November 25th, 2009 in MySql
It's probably more common for people to edit MySQL tables using a more visual tool such as phpMyAdmin but it is possible to use a manually written SQL query instead to modify tables (this is what a tool like phpMyAdmin does in the background). This post shows how to drop a column from a MySQL table with a SQL query.
Use mysqldump to get the schema only
Posted November 14th, 2009 in MySql
The MySQL command line tool mysqldump is used to create backup copies (or dumps) of databases including the structure or schema and the data itself. There are a number of command line flags which can get MySQL to dump just the data or just the structure instead of everything. This post looks at how to dump just the schema of the MySQL database as a whole, for a single table, or for several tables.
MySQL: Using BETWEEN in a SQL query
Posted November 6th, 2009 in MySql
I mentioned in my Using + to merge arrays with PHP post the other day that I've recently started a new job and one of the interesting things about working with other people's code is the new stuff you learn.So I was surprised to discover that in 11 years of working with SQL databases that I've never come across the BETWEEN operator before. While this post is titled "MySQL" the SQL used here should work for most database systems.
Get MySQL status information from the command line
Posted October 7th, 2009 in MySql
This post shows how to get some MySQL status information from the command line on a Linux/Unix machine using either the mysqladmin command or the mysql init script. Information returned includes the version, connection type, socket file location, uptime and some other stats.
MySQL CONVERT_TZ returns null
Posted September 30th, 2009 in MySql
MySQL's CONVERT_TZ function converts datetime values from one timezone to another. If the timezones haven't been set up in the MySQL database then the value returned from the function will be null. This post shows how to fix the problem.
Drop multiple MySQL tables
Posted September 23rd, 2009 in MySql
Last week I looked at how to drop a table with MySQL showing how to do this with a query and then using the phpMyAdmin web based interface. This time I will show how to drop multiple tables with a single query and then how to do the same with phpMyAdmin.
How to drop a table with MySQL
Posted September 16th, 2009 in MySql
This post shows the SQL query for dropping a table with MySQL and also some screenshots showing how ot drop a table using phpMyAdmin.
How to drop a database with MySQL
Posted September 9th, 2009 in MySql
This post shows the SQL query used to drop a database with MySQL and also a couple of screenshots showing the same process using phpMyAdmin.
Show indexes for a table with the MySQL INFORMATION_SCHEMA
Posted September 2nd, 2009 in MySql
I've previously posted how to get the indexes for a MySQL table using a "SHOW INDEXES" SQL query and in this post show an alternative way to get the indexes for a table using MySQL's INFORMATION_SCHEMA.
Prevent a query from using MySQL's query cache
Posted August 19th, 2009 in MySql
If you have MySQL's query cache enabled there may be times when you do not want a particular query to use the query cache, for example for benchmarking a particular query. This post shows how to do it.
How to tell if the MySQL Query Cache is enabled
Posted August 12th, 2009 in MySql
The MySQL Query Cache enables query results to be cached so if the same query is run multiple times the second and subsequent times the query are run are amost instant. This short post shows how to tell if the MySQL Query Cache is enabled.
Get a MySQL table structure from the INFORMATION_SCHEMA
Posted August 6th, 2009 in MySql
There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE (which I have already covered in an earlier post) and the second by querying the INFORMATION_SCHEMA. This post deals with querying the INFORMATION_SCHEMA which has more information available than using DESCRIBE.
Get a MySQL table structure with DESCRIBE
Posted July 22nd, 2009 in MySql
There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE and the second by querying the INFORMATION_SCHEMA. This post deals with the DESCRIBE function and next week I'll look at the latter.
Logging slow queries with MySQL
Posted July 15th, 2009 in MySql
MySQL has a slow query log which can be enabled if you need to track down slow queries which are causing issues for a website or application.
Execute MySQL statements from a text file
Posted July 8th, 2009 in MySql (Updated July 14th, 2009)
It's possible to execute statements in MySQL from a text file from the command line or from the MySQL command line shell. This can be useful, for example, if you've saved data using mysqldump and need to load it back into the database.
Change the full text index minimum word length with MySQL
Posted July 1st, 2009 in MySql
The MySQL full text index by default only indexes words which are 4 characters or longer, which means on a blog like mine if you search for "PHP" nothing will be returned. This post shows how to change minimum word length in MySQL so words or 3 characters (or even 2 if you want) can be indexed as well.
MySQL "Incorrect key file for table" error
Posted June 24th, 2009 in MySql
When saving a record to a MySQL table the other day I got the error message "Incorrect key file for table 'mytable'; try to repair it". I am uncertain why the error occured and how to ensure it doesn't happen again in the future but a quick fix for the time being is simple.
Use one file per table with MySQL's INNODB storage engine
Posted June 17th, 2009 in MySql
By default MySQL's INNODB engine puts all the data for all the INNODB tables for all databases into the same file. This file will grow as it is used but will never shrink, even if you drop a very large INNODB table. This post shows how instead to have a data file per table in the database's directory which is the same as the way the MyISAM engine works.
Find which tables use INNODB with MySQL
Posted June 10th, 2009 in MySql
I recently needed to find which tables across all MySQL databases on a particular server used INNODB as the storage engine. This short post shows the query required to do this.
Selecting substrings with MySQL using LOCATE and SUBSTRING
Posted June 3rd, 2009 in MySql
The MySQL function SUBSTRING allows you to extract a substring from a string. This post looks at how to use substring to extract some information from a field in a database using an example column which contains XML data.
Finding the location of a string in a string with MySQL
Posted May 27th, 2009 in MySql
There may be times when you need to find the location of a string within a string with MySQL. The LOCATE() function allows you to do this and I show some examples in this post of how to do it. The next MySQL post on this blog (this time next week) will combine LOCATE() with SUBSTRING() to extract substrings from a string.
Rounding numbers with MySQL
Posted May 20th, 2009 in MySql
MySQL has a number of ways of rounding numbers with the CEILING() FLOOR() ROUND() and TRUNCATE() functions.
MySQL: Delete records in one table that are not in another
Posted May 13th, 2009 in MySql
Last week I looked at how to find records in a table with MySQL that are not in another table and this week look at how to delete records in a table with MySQL that are not in another table.
MySQL: Find records in one table that are not in another
Posted April 22nd, 2009 in MySql (Updated May 8th, 2009)
There may be times you need to check if there are records in one table that are not in another for example to check for data integrity or simply to find out if e.g. there are any products not assigned to a category or webpages to a tag. This post looks at how to do this with MySQL.
MySQL: Find records in one table that are not in another - revised
Posted May 8th, 2009 in MySql
A couple of weeks back I posted how to find records in one table that are not in another with MySQL and received an email from Greg Jorgensen with a more efficient way of approaching the same problem and therefore revise my original post with his suggestion.
Show indexes for a table with MySQL
Posted April 15th, 2009 in MySql
MySQL has a SQL query "SHOW INDEX FROM" which returns the indexes from a table. This post looks at some example usage of this query to get a list of indexes and a list of primary keys for a table with MySQL.
Create a CSV file from MySQL with PHP
Posted April 12th, 2009 in MySql and PHP
There are a couple of ways to export data from MySQL to a CSV file (refer to my using mysqldump to save data to CSV files and export data to CSV from MySQL posts for details) but neither of them supports adding a header row to the CSV which contains the column names. This post looks at how to export the data from MySQL into a CSV file with PHP including a header row.
Listing MySQL tables with PHP
Posted April 9th, 2009 in MySql and PHP
This post shows how to use the MySQL "SHOW TABLES FROM" SQL query to get a list of tables using PHP. This list could either be stored to an array or echoed out to web browser, command line etc.
Export data to CSV from MySQL
Posted April 8th, 2009 in MySql
MySQL has a couple of options for exporting data: using the command line tool mysqldump (read my using mysqldump to save data to CSV files post for more details) and using a "SELECT ... INTO OUTFILE" SQL query. This post looks at the latter to export data from MySQL into a CSV file.
How to enable remote access to a MySQL database server
Posted April 1st, 2009 in MySql
MySQL is often configured to be accessible from the local host only. Recently I moved all my websites from a CentOS box to a Debian one and needed to be able to connect to the MySQL server on the new box from the old one, but was not able to. This post looks at how to enable MySQL to be accessible from remote servers.
"Randomly" order data across multiple pages with MySQL
Posted March 25th, 2009 in MySql
Last week I looked at how to randomly order a MySQL result set by using ORDER BY RAND(). This is useful for a one-off query in which you need to draw a prize winner or similar but isn't so useful if you need to page through the resultset across several pages, because the order will change as you move from page to page.
Randomly ordering a MySQL result set
Posted March 18th, 2009 in MySql
There are times when you might need to randomly order a MySQL resultset, for example when choosing some winners at random for a prize draw. This post looks at how you would do this and also offers some advice if you need to "randomly" order data across multiple pages.
Importing data with mysqlimport from multiple files
Posted February 25th, 2009 in MySql
14 months ago I posted how to restore data from a tab delimited file to MySQL using the command line tool mysqlimport. When it came to importing multiple files at once I suggested using a for loop but have realised there's a much simpler way to do this using wildcards or mutiple filenames.
Copy a MySQL table with phpMyAdmin
Posted February 18th, 2009 in MySql
Last week I looked at how to copy a table with MySQL using some SQL queries and then on Sunday a PHP script to automate the process. This post looks at how to copy a table with phpMyAdmin so you can easily do the same thing using a webpage GUI.
Delete All Data in a MySQL Table
Posted July 16th, 2004 in MySql (Updated February 17th, 2009)
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.
PHP script to make a backup copy of a MySQL table
Posted February 15th, 2009 in MySql and PHP
Yesterday I looked at how to copy a table with MySQL using some SQL queries. This post looks at how to automate the process with a PHP script saving you having to type in and adjust queries each time you want to back up a table.
Copy a table in MySQL
Posted February 11th, 2009 in MySql (Updated February 14th, 2009)
Sometimes you might need to quickly and easily create a backup copy of a table in MySQL before doing some work to the existing table. That way you can easily copy the data back from the backup table into the original table if something goes wrong. This post looks at how to create a copy of a table in MySQL and then copy the data from the original table into the copy.
Copy a table in MySQL with CREATE TABLE LIKE
Posted February 14th, 2009 in MySql
On Wednesday I wrote a post about how to copy a table in MySQL using the SHOW CREATE TABLE sql query. Since then Paul Williams in the UK emailed me to let me know that there's a much easier way to do this using CREATE TABLE LIKE, a function which was depths of my brain somewhere but I'd since forgotten. This post revises and republishes my previous post using this simpler process.
Maximum length for MySQL TEXT field types
Posted February 4th, 2009 in MySql
MySQL supports 4 TEXT field types (TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT) and this post looks at the maximum length of each of these field types.
Using SELECT REPLACE with MySQL
Posted January 28th, 2009 in MySql
I recently needed to compare the content of two columns in a MySQL database that stored the large and small images for a blog post. All the small ones start with "small" followed by a number and the large ones "big" followed by a number. Enter the REPLACE() function to get rid of small/large and do the comparison! In this postI show how to use the replace function in MySQL.
Review: Mastering phpMyAdmin 2.11 for Effective MySQL Management
Posted January 21st, 2009 in MySql and PHP
The post has a look at the book "Mastering phpMyAdmin 2.11 for Effective MySQL Management" by Marc Delisle (who is one of the phpMyAdmin developers) and available to buy from the Packt Publishing website, where you can buy it as a hard copy book or an eBook. I am not affiliated with Marc Delisle or Packt Publishing in any way, but was supplied a free copy of the eBook to review.
Rename multiple tables in MySQL
Posted January 10th, 2009 in MySql
Some time back I posted how to rename a table in MySQL, either using a SQL query or using phpMyAdmin. In this post I will show how to rename multiple MySQL tables from a single query, including how you would swap two table names.
MySQL queries for article summaries part 2 of 2
Posted January 6th, 2009 in MySql
On Saturday I posted a summary of post information for 2008 and on Sunday shared the MySQL queries to get some of the information. This post is part 2, showing the queries to get the remaining information.
MySQL queries for article summaries part 1 of 2
Posted January 4th, 2009 in MySql
Yesterday I posted a summary of my posts for 2008 which counted the number of posts for 2008, number of days on which I posted, summary of posts by section. Today I've decided to show the MySQL queries behind the statistics. While your content management system / blogging system (or "off the shelf" one) will have different database structures you should be able to use similar queries to work similar numbers out for your own website.
Setting the MySQL timezone per connection
Posted December 20th, 2008 in MySql (Updated January 1st, 2009)
There may be instances when you need to set your MySQL database to a different timezone from the one the server is in, but only for a specific website or application. It is possible to set the timezone on a per connection basis with MySQL and this post looks at how to do it.
How to update the max_connections setting in MySQL
Posted December 10th, 2008 in MySql
If you are getting "too many connections" errors in MySQL you can change the max_connections setting to allow more connections, assuming you have enough RAM to handle the increased number. This post looks athow to update the max_connections setting in MySQL.
Insert multiple records into MySQL with a single query
Posted December 3rd, 2008 in MySql
It is possible to insert multiple records into MySQL using a comma separated list of fields. This post looks at how to do this using SQL - if you're using a nice ORM solution instead of writing SQL queries then this won't really apply.
Run a single MySQL query from the command line
Posted November 6th, 2008 in MySql
The MySQL command line utility allows you to run queries and view query results etc from an interactive command prompt. It is also possible to run a single query from the command line without actually going into the interactive prompt. This post looks at how to do this.
Hide selected databases in phpMyAdmin
Posted October 21st, 2008 in MySql and PHP
A few days ago I posted how to hide the information schema in phpMyAdmin and of course using the advice in that post you can hide any database you want. The hide_db phpMyAdmin configuration option is a regular expression so you can use it to hide several databases, and this post looks at how to do this.
Selectively dumping data with mysqldump
Posted October 17th, 2008 in MySql
mysqldump is a command line tool for outputting table structures and data and can be used for backups etc. By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump. This post looks at how to do this.
Hide the information schema in phpMyAdmin
Posted October 11th, 2008 in MySql and PHP
When you log into phpMyAdmin you can only see the databases for which you have access rights, as well as the information_schema database. It is possible to hide this database if you want to. This post looks at how to hide the information_schema database in phpMyAdmin.
Using date_add in MySQL to add intervals to dates
Posted September 25th, 2008 in MySql
Like other database management systems, MySQL has a range of date functions which allow you to change the formatting of dates, get day/week/month/etc parts of dates, and add or subtract intervals to a specified date. This post looks at how to add intervals to a date in MySQL.
Optimize tables in MySQL automatically with PHP
Posted September 7th, 2008 in MySql and PHP
In previous posts I looked at how to optimize a MySQL table from the MySQL command line interface and from phpMyAdmin by using the optimize [tablename] command to free up unused space. In this post I will look at how to do this with a PHP script which could be run periodically to optimise all non-optimal MySQL tables.
Optimize a table in MySQL from phpMyAdmin
Posted September 6th, 2008 in MySql
Yesterday I looked at how to Optimize a table in MySQL from the command line interface and today will look at how to do the same thing with phpMyAdmin. This means you can just point and click in a web based interface instead of having to remember the commands and type them in.
Optimize a table in MySQL from the command line interface
Posted September 4th, 2008 in MySql
If you have MySQL tables that grow large and then have a lot of deletes they will become fragmented and larger than they need to be. This post looks at how to look at check to see if a MySQL table is no longer optimal and how to optimize it.
Upper case and lower case strings with MySQL
Posted July 29th, 2008 in MySql
MySQL, as with other database servers, has a variety of text functions including functions for converting a string to upper case or to lower case. This post looks at how to convert a string to lower case or upper case with MySQL.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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".
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.
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.
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.
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.
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 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.
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_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
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
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.
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.
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.
RSS Feed
Facebook
Twitter