This tutorial explains how you can install MySQL Proxy on a CentOS 5 (x86_64) system. MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; [...]

MySQL is one of the most important programs on a server, unfortunatly it is also pretty resource intensive. One a server it is not uncommon for a single user or even a query to take up the bulk of the servers cpu cycles. Mytop is a very useful program to see what queries a server [...]

MySQL replication will stop if an error occurs when running a query on the slave. The reason is so you can resolve the problem, thus keeping the data consistent with the master. You can skip those errors if you know those queries and why they are failing. Skip one query mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; [...]

How about backing up all the databases in the server? That’s an easy one, just use the –all-databases parameter to backup all the databases in the server in one step. mysqldump –all-databases> alldatabases.sql How to Backing up only the Database Structure in MySQL You can backup only the database structure by telling mysqldump not to [...]

This comes up in discussions almost every new project I work on, because it’s a very important thing to consider when designing a database. When deciding when and how to create an index in your MySQL database, it’s important to consider how the data is being used. Let’s say you have a database of employees. [...]

Unofficial, No mess, No thrills, No fluff, No Explanation, Absolutely No Guarantee, Ruff and Gruff howto fix MySQL Databases – Make a temp directory to work in cd /root mkdir mysql_tmp cd mysql_tmp – Locate the database, ussualy a directory in /var/mysql or /var/lib/mysql ls -la /var/mysql/_database_name_ – Stop the mysql service, might be called [...]

Changing the collation for all tables in a MySQL database can be time consuming depending on how many tables you have. That’s why we recommend using the following PHP script for changing the collation for all tables at a time: <?php $db = mysql_connect(‘localhost’,’myuser_mydbuser‘,’mypassword‘); if(!$db) echo “Cannot connect to the database – incorrect details”; mysql_select_db(‘myuser_mydbname’); [...]

After performing a mysql upgrade, you may run into a problem where Mysql won’t start successfully and will result in a “Table ‘mysql.plugin’ doesn’t exist” error message. The mysql error logs will state something like follows: /usr/libexec/mysqld: Table ‘mysql.plugin’ doesn’t exist 110620  01:01:01 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it. [...]

Recently I was configuring PHP with Mysql manually and it was really annoying to see the “Cannot find libmysqlclient under /usr” error message. The configure line I used to install PHP was ./configure –with-mysql  –with-libdir=/usr/lib and the error that I received was checking for MySQL UNIX socket location… /var/lib/mysql/mysql.sock configure: error: Cannot find libmysqlclient under [...]

Somtimes you may want to change the Mysql table from MyISAM to InnoDB engine to setup foreign keys, to use row level locks, improve performace etc. The conversion of the MyISAM table to InnoDB is easy however, if the table is setup with “FULLTEXT indexes”, it cannot be converted as this feature is not supported [...]