Database Archives - BinaryTides

How to Setup MySQL Replication on Ubuntu/Debian – A Step-by-Step Guide for Beginners

Silver Moon — Mon, 15 May 2023 05:48:57 +0000

In production database setups, mysql replication is commonly used to duplicate a single database over many database servers. The primary database is referred to as the master database, while the other replicated databases are referred to as slave databases. To put it another way, we can use it to duplicate a database. Replication is used... Read More »

The post How to Setup MySQL Replication on Ubuntu/Debian – A Step-by-Step Guide for Beginners appeared first on BinaryTides.

Optimise your database design for speed and efficiency – Part 1

Silver Moon — Wed, 10 Apr 2013 06:12:33 +0000

Database schemas Databases are present in almost all kinds of application that need to store information in some form or the other. Web applications like blogs, cms, social networking sites or business applications etc all have a database along with the code. The database design or schema determines how the tables and their relationships are... Read More »

The post Optimise your database design for speed and efficiency – Part 1 appeared first on BinaryTides.

Mysql autoincrement column that resets every month

Silver Moon — Sat, 22 Oct 2011 10:17:20 +0000

The autoincrement feature in mysql allows us to create numeric fields that automatically increment by 1 for every row inserted. It does it atomically so we don't have to worry about any duplicates or collision. However, there are situations when we need an autoincrement field to reset at regular intervals like every month or so.... Read More »

The post Mysql autoincrement column that resets every month appeared first on BinaryTides.

How to List foreign keys in Mysql

Silver Moon — Sat, 05 Sep 2009 16:00:46 +0000

information_schema The following query will list out the foreign keys in mysql. It finds it out from the information_schema database. select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references' from information_schema.key_column_usage where referenced_table_name is not null; The output is a clean table listing out all foreign keys from all databases +-----------------------+-------------+ |... Read More »

The post How to List foreign keys in Mysql appeared first on BinaryTides.

Create foreign key using Phpmyadmin

Silver Moon — Tue, 25 Aug 2009 12:03:01 +0000

The innodb storage engine supports foreign keys in Mysql. To create foreign keys in phpmyadmin : 1. Convert both tables into innodb, if they are not already. 2. View the structure of the table which will have a foreign key. Make the referencing field an INDEX. 3. Now come back to structure view and click... Read More »

The post Create foreign key using Phpmyadmin appeared first on BinaryTides.

Import mysql dump files with view definer and sql security

Silver Moon — Sat, 22 Aug 2009 11:08:58 +0000

Views and mysqldump A little back I tried to backup a database from my webhost and restore it on my localhost mysql. It had a few views. The import of the sql file seemed to work fine. But on accessing the views in phpmyadmin mysql gave the error : #1449 - There is no 'projects'@'localhost'... Read More »

The post Import mysql dump files with view definer and sql security appeared first on BinaryTides.

Create AutoIncrement column/field in Apache Derby

Silver Moon — Mon, 06 Jul 2009 10:30:54 +0000

While creating a table a particular column / field can be made autoincrement as : CREATE TABLE students ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), name VARCHAR(24) NOT NULL, address VARCHAR(1024), CONSTRAINT primary_key PRIMARY KEY (id) ) ; The value of an autoincrement column increments automatically with... Read More »

The post Create AutoIncrement column/field in Apache Derby appeared first on BinaryTides.

How to Change Column / Field order in OpenOffice Base ( HSQL )

Silver Moon — Fri, 23 Jan 2009 12:07:00 +0000

Altering the sequence of fields of a table was a feature I was looking for in OpenOffice Base. It is not directly possible to drag the columns or fields and alter their sequence. We need a sql workaround to change the order of the columns. Lets say the sequence or order of the columns is... Read More »

The post How to Change Column / Field order in OpenOffice Base ( HSQL ) appeared first on BinaryTides.