Tag Archives: database

How to Duplicate a Table in FlameRobin CodeUnit 03 OCT 2011

FlameRobin is a lightweight, cross-platform database administration tool for Firebird databases. It is also NOT one of my favourite tools to use because of its very lightweight nature! Nevertheless, there are times I must use it, and use it I shall, and besides, other people seem to like it, so it’s worth a post or two. Today I’ll quickly jot down a note on how to duplicate a table within FlameRobin.

First, connect to the database instance by selecting it from the explorer window and double clicking on it. Once the table list is loaded, select the table that you wish to duplicate. Click on it to launch a new information window which happens to have the following headers at the top: Summary, Constraints, Triggers, Indices, Privileges, Dependencies and DDL.

Click on the DDL link.

Copy the resulting SQL statement.

Now right click on the Tables menu option in the main FlameRobin window and select the “Create new…” menu option.

Paste the copied SQL into the window, change the table name sections to the duplicate’s desired name, and click on the Run (Play icon) button. Pressing F4 will also cause the statement to execute. If you’re happy with the report, click on the green tick button to commit your transaction, and smile as the table list in the main window magically gets updated with your new table!

Nifty.

Ubuntu Terminal: Efficient Way to Copy Across a MySQL Database from One Server to Another CodeUnit 23 MAY 2011

I often need to pull down a copy of a live MySQL database instance for development work on my local machine. Because my access to bandwidth is limited, I need to come up with the most efficient way of achieving this, and my method for achieving this is chronicled below – mainly so that I can refer to it when I forget!

Anyway, the process is simple enough to explain. First, we ssh into the target server and we use mysqldump to create a SQL dump text file of the required MySQL database. Next, we compress it using the powerful 7-Zip to create a nice and compact 7z file. Exit ssh. To pull it down from the server, we use plain old scp and do a secure copy down to our local machine. Next we uncompress the newly copied 7z archive and once extracted, import it into our local MySQL server using the mysql command.

Nifty.

And a nice example for copy and paste purposes:

ssh #REMOTEUSERNAME#@#HOSTNAME#

mysqldump -u #USERNAME# -p#PASSWORD# -c --add-drop-table --all --quick #DATABASE# > dump.sql 

7za a -t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on dump.7z dump.sql 

exit

scp -v -P #PORT# #REMOTEUSERNAME#@#HOSTNAME#:/home/server/dump.7z /home/craig/dumpscp.7z 

7za e /home/craig/dumpscp.7z 

mysql -u #USERNAME# -p#PASSWORD# database < /home/craig/dump.sql

MySQL: How to Show the Collation and Character Set of a MySQL Database CodeUnit 25 MAR 2011

To find out what collation and character set your database is using turns out to be pretty trivial thanks to MySQL’s SHOW VARIABLES SQL statement that reveals all the inner workings of your setup.

First, select your database. If you are working from the command line:

USE mydatabase

Then, run the SHOW VARIABLES statement against your database, using a LIKE conditional to narrow down to what we want to see:

SHOW VARIABLES LIKE 'character_set_database'
SHOW VARIABLES LIKE 'collation_database'

These two statements will return the corresponding values that you are interested in.

If you want to see even more of the settings on your database, just run:

SHOW VARIABLES

Nifty.

Ubuntu: How to Delete a MySQL Database via the Terminal CodeUnit 21 JAN 2011

To delete a database from a MySQL server instance, we need to make a DROP DATABASE call against the server.

First, fire up MySQL in your terminal and login as an administrator account with:

mysql -u root -p

You will be prompted for your root password before being granted access to the MySQL monitor.

Now to grab a list of available databases on the system. Running:

show databases;

Will result in quite a list of available databases being drawn on the screen (note, it only lists the databases your user account has privileges to see). Once you have located the database you wish to delete in this list we can proceed:

drop database if exists mydatabase;

This will now delete the mydatabase database from the MySQL server if it exists. If not, the drop database statement is simply ignored.

Nifty.

Ubuntu Terminal: How to Quickly Create a SQL Dump File from a MySQL Database CodeUnit 05 JUL 2010

Backing up your MySQL database or generating a copy of it to shift around is quite a simple affair thanks to the powerful mysqldump command that ships with MySQL.

To generate a backup sqldump, simply execute:

mysqldump -h localhost -u [MySQL user, e.g. root] -p[database password] -c --add-drop-table --add-locks --all --quick --lock-tables [name of the database] > sqldump.sql

Note the lack of a space between -p and the password! Obviously if you don’t have a password assigned, simply omit the -p switch.

And that is it, all done! :)

Note, restoring a database from a mysqldump is as simple as: mysql -u [MySQL user, e.g. root] -p[database password] < sqldump.sql

PHP Script to Trim and Replace Apostrophes for Each Value in Each Column of Each Table in a MySQL Database CodeUnit 31 MAY 2010

Annoyingly, I got saddled with a database for a system which contained a whole lot of “uncleaned data”, in other words data with a lot of trailing and leading, uncessary whitespace characters, as well as a generous helping of apostrophes and quotation marks – the cause of many a headache when working on a web-based application using PHP and MySQL.

Needless to say, inherent relationships and thus joins were failing left, right and center thanks to this badly formed data and as such I needed to quickly whip up a script that would strip all leading and trailing whitespace plus replace any apostrophes or quotation marks with their web code equivalent for each and every value that appeared in every row, column and table in the database.

And this is the little PHP script that did it for me:

//just to help prevent the script from timing out
ini_set('memory_limit', '100M');
ini_set('default_socket_timeout',600);
ini_set('max_input_time',600);
ini_set('max_execution_time',600);

//give us something to look at on the screen
echo "Start Process...";

//create a database connection
$mysql_hostname = 'localhost';
$mysql_user = 'username';
$mysql_password = 'password';
$mysql_database = 'databaseName';
$conn = mysql_connect($mysql_hostname,$mysql_user,$mysql_password);
mysql_select_db($mysql_database, $conn);

//build up the list of tables to process
$sql = "SHOW TABLES";
$tables = array();
echo 'Building audit tables list...';
$tablestemp = $db->query($sql,2);
foreach ($tablestemp as $tabletemp)
{		
  $tables[] = $tabletemp;
}
echo 'Audit table list built.';

//run through each table, build up a column list and then run an update SQL statement against the column
foreach($tables as $table)
{
  set_time_limit(80);
	$sql = "SHOW COLUMNS FROM `$table`";
	$columns = array();
	echo 'Building audit columns list for ' . $table . '...';
	$columnstemp = $db->query($sql,2);
	foreach ($columnstemp as $columntemp)
	{		
			$columns[] = $columntemp;
	}
	echo 'Audit column list for ' . $table . ' built.';
	
	foreach($columns as $column)
	{
		set_time_limit(80);
		//trim and replace in one foul SQL swoop!
    $sql = "UPDATE `$table` SET `" . $column['Field'] . "` = TRIM(REPLACE(REPLACE(REPLACE(`" . $column['Field'] ."`,"'","&#039;"),'"','&#034;'),'~','&#039;'))";
    echo "$sql";
    mysql_query($sql);
	}
}
mysql_close($conn);
echo "End Process.";

And there you go, pretty handy little helper script to have in your possession when you are presented a database with some dirty data included inside! :)

Monitor Your MySQL Database: Spotlight on MySQL CodeUnit 24 MAY 2010

Spotlight on MySQL, or Spotlight for short, is a fantastic tool for monitoring the status of all your MySQL databases via one single screen display.

It basically allows you to specify a number of MySQL connection strings to various databases that you control and once connected, displays a host of valuable, real time information on a nice big black and green screen. It highlights essentially all database activity, from data flowing to and from the database, the number of active sessions, sql statments being executed and a host of more, indepth views of just what exactly is going on inside your MySQL server installation.

On top of all this, it also features a number of other graphical reports detailing various aspects of the MySQL server’s usage and allows you to set certain thresholds, triggering alarms when those thresholds do get crossed if you so wish.

An essential tool in monitoring the performance and stability of your MySQL installation, this really is one of those indispensable applications that you beg your boss to put up on a big screen over your work area, just so that someone at least can keep an eye on the all important backbone of your operation!

Oh, and in case you were wondering, Spotlight works on MySQL server versions 5.0.3 and up (or else basically any that employ the InnoDB storage engine).

Also, although you can purchase the product for Commercial usage, there is a freeware version available for download which you can grab here.

Related Link: http://www.quest.com/spotlight-on-mysql/

PHP: Optimize and Analyze All Your MySQL Tables CodeUnit 19 MAR 2010

20090910-MySQL-logoWhen you start working with sizeable datasets and the like, things like database optimization become more and more important for you to pay attention to.

So for today’s quick tutorial I present to you a script which can be used as either a cron or scheduled task and will run MySQL’s nifty analyze and optimize functions against all the tables in your database. Looking at it, you’ll see it is a pretty simple affair, basically we connect to our database in the usual fashion (note I’m simply using a connect function of my own design here) and then run a query to return the list of all tables in the selected database (again obscured by the connect function).

Once you have the list, it is a simple matter of looping through all the tables and executing the ANALYZE and OPTIMIZE statements against each table.

$conn = createconnection();

$alltables = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       mysql_query("ANALYZE TABLE `".$tablename."`")
       or die(mysql_error());
       mysql_query("OPTIMIZE TABLE `".$tablename."`")
       or die(mysql_error());
   }
}
closeconnection($conn);

Simple, but effective.

Inserting Backslashes into a Database Table with PHP CodeUnit 06 MAR 2010

Sometimes you need to store things like file paths into a database table during your PHP script’s execution. However, on going back to the database after running your script, you might come across your path with all of its backslashes () missing!

So just what is going on here?

In most cases you will be using a string construct to send your SQL command, in other words, mysql_query() will be sending along a string SQL statement to be processed by the database. However, recall that backslashes usually need to be escaped in order to display a backslash, and in PHP for example, escaping characters is done by using a backslash – so in other words to display a backslash in a string, you actually need to put down two of the things: \

However, when pushing your escaped string through to the database, remember that mySQL also needs to escape the backslashes it receives via your SQL statement, meaning that in actual fact you need to be double escaping your escaped backslashes!

To make this simpler to understand, you want to use this in your SQL string: \\. So what happens now is that the PHP script escapes the above to \ which is then passed along to mySQL which then further escapes it to , leaving you with a nice shiny backslash in your record as a result.

Got it?

Anyway, naturally PHP makes things a little easier for us by providing the handy mysql_real_escape_string() function which will handle the escaping of all special characters for use in a SQL statement, even taking into account the current character set of the connection specified!

Now you know.

Related Link: http://www.php.net/manual/en/function.mysql-real-escape-string.php