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.
If you are looking for MySQL databases – for example geographical databases, I suggest you take a look at SQL Dumpster
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.
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.
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.
The MySQL server daemon wasn’t running, although it’s set to start up automatically by the init scripts when the system first boots up, so I was a little surprised. I dropped to a command prompt and issued the command:
/etc/init.d/mysql start
and was surprised to get the following message:
Updating MySQL privilege database...
/usr/bin/mysql_upgrade: relocation error: /usr/bin/mysql_upgrade: symbol dynstr_append_os_quoted, version libmysqlclient_15 not defined in file libmysqlclient.so.15 with link time reference
failed
I was a little puzzled by the error message, so decided to check out the /usr/bin/mysql_upgrade file, thinking it might be some sort of readable shell script, but it was a binary file, which obviously had some sort of error interfacing with the MySQL libraries.
Then followed a lot of messing around, including taking a backup copy of the databases and loading them into a fresh MySQL data directory, ie replacing what was at /var/lib/mysql with a new default database layout, and then creating and loading the databases from the backup files. I didn’t have any issues with the fresh copy of the database, so went back to the version that wasn’t working.
For some reason or other, I decided to check the files in the MySQL data directory, like so:
sudo ls -la /var/lib/mysql
and got a listing similar to the following:
total 28900 drwx------ 2 mysql mysql4096 2007-09-21 11:42 adatabase drwx------ 2 mysql mysql4096 2007-09-21 11:42 another_db -rw-rw---- 1 mysql mysql6 2007-09-21 11:36 desktop.pid drwx------ 2 mysql mysql4096 2007-09-21 11:42 efghijklmn -rw-rw---- 1 mysql mysql 18874368 2007-10-05 18:55 ibdata1 -rw-rw---- 1 mysql mysql5242880 2007-10-05 18:55 ib_logfile0 -rw-rw---- 1 mysql mysql5242880 2007-10-05 18:55 ib_logfile1 drwx------ 2 mysql mysql4096 2007-09-21 11:44 mysql drwx------ 2 mysql mysql4096 2007-09-21 11:44 mysql_bak -rw-rw---- 1 mysql mysql1594 2007-10-05 18:55 mysqld.log -rw-rw---- 1 mysql mysql1859 2007-09-28 13:17 mysqld.log-20070921 -rw-rw---- 1 mysql mysql1026 2007-10-02 10:39 mysqld.log-20070928 -rw-rw---- 1 mysql mysql1539 2007-10-04 11:01 mysqld.log-20071002 -rw-rw---- 1 mysql mysql1026 2007-10-05 17:57 mysqld.log-20071004 drwx------2 mysql mysql4096 2007-10-05 18:55 .protected -rw-r--r--1 mysql mysql0 2007-10-05 17:57 .run-mysql_upgrade drwx------ 2 mysql mysql4096 2007-09-21 12:47 test drwxr-xr-x2 mysql mysql4096 2007-10-05 18:55 .tmp
The file that interested me the most was the one named .run-mysql_upgrade. Clearly from the name this is what was causing the /usr/bin/mysql_upgrade to run, and for whatever other reason it was erroring out. So I reasoned it would just be a matter of deleting (or backing up somewhere else) this file and then attempting to start MySQL again.
$ sudo rm /var/lib/mysql/.run-mysql_upgrade $ sudo /etc/init.d/mysql start Starting service MySQLdone
And sure enough, that fixed the problem.
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
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.
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.
