Archive: mysql

If you are using XAMPP on Ubuntu (or probably other) Linux and are also trying to use the MySQL Administrator and MySQL Query Browser GUI tools available in the repo's, you have probably hit an error message like this:
PLAIN TEXT
CODE:

Could not connect to host 'localhost'.

MySQL Error Nr. 2002

Can't connect to local MySQL server through socket [...]

This is a nice little script I just knocked together that helps to synchronise databases when the table structures might not be exactly the same (for example different versions of the same system).
You would need to edit the top section to put the correct DB credentials etc.
This is definitely not to be used on a [...]

Just came across this script today whilst evaluating a clients MySQL performance.
http://mysqltuner.pl
Its a perl script, just drop it in your home directory via SSH and run it to get some useful info regarding your MySQL configuration and any changes that you should make to improve performance.
I love stuff like this. No frills Just Works. Not [...]

If you are feeling lazy, or would like to build in some future proofness into your system, you can use the MySQL Desc query to get table column information and then use this information to create dynamic SQL insertion strings.
For example:
PLAIN TEXT
PHP:

$cols_query = db_query("desc table");

while($cq = mysql_fetch_assoc($cols_query)){

    $cols[]=$cq;

}

foreach($cols as $c){

    if(!empty($_POST[$c['Field']])){

      [...]

My MySQL dump splitting script has come in handy countless times. Recently I also needed to remove keys from an SQL dump to enable me to import a dump that had keys associated with text columns.
Here is the updated script
*note - now updated again to strip out any drop table statements if splitting
PLAIN TEXT
PHP:

<?php

/*MySQL Dump [...]

If you are extremely puzzled with MySQL error messages relating to the number 2147483647, this is actually because you are trying to use an integer number higher than 2147483647 and MySQL's maximum integer value is 2147483647.
more info
More Reading:no matching posts found..

Some times you are working on a database that is just too big to export and import via phpMyAdmin. In those circumstances the best alternative is to use the Linux command line, usually via SSH.
If you want to be able to download the file easily via FTP, you need to first of all go to [...]

Don't get me wrong, I love phpMyAdmin and use it every day. However sometimes on client servers the only access to the database is via phpMyAdmin and it hasn't been set up properly. One really irritating example is when trying to dump a database so that it can be installed on my local development server. [...]

If your host doesn't provide you with phpMyAdmin access, or if you simply want a faster lighter version of phpMyAdmin for simple DB admin tasks and running custom SQL queries, then take a look at phpMiniAdmin. Its a single script so its incredibly easy to install - simply upload it.
I would be tempted to beef [...]

If you find yourself laboriously building SQL queries by typing each field = 'value' statement... think again.
Imagine this:
PLAIN TEXT
PHP:

$sql_query = mysql_query("select * from table");

$insert_elsewhere_sql = "INSERT INTO other_table SET ";

while($s = mysql_fetch_assoc($sql_query)){

    foreach($s as $k=>$v){

    $insert_elsewhere_sql .= "$k = '$v', ";

}

$insert_elsewhere_sql = substr($insert_elsewhere_sql, 0, -2);

This will generate a valid SQL insert statement [...]