Posts Tagged ‘MySQL’

How to backup and restore your MySQL database or table structure to in Linux

Whenever there is a database involved, an important task that should never be neglected is backing it up. In the case of MySQL on a Linux machine, backing up consists of executing a single command.
Backing up a MySQL database can be done with one simple command:

mysqldump -h <host> -u <user> -p<password> <db_name> > backup.sql

For example if you want to backup a local database called db1 using the user user1 with password pass1 you should write:

mysqldump -h localhost -u user1 -ppass1 db1 > backup.sql

The backup.sql file contains an SQL query for creating the exact same database. Restoring such a dump-file is just a matter of one command too:

mysql -h <host> -u <user> -p<password> <db_name> < backup.sql

Following the naming from our previous example will result in the following command to restore the database:

mysql -h localhost -u user1 -ppass1 db1 < backup.sql

In the perfect environment the backup should be created by a cronjob or anacronjob.
Another useful thing that one may want to backup is the table structure. For tables whose structure is relatively often changed this can prove to be very helpful. It is done by using one simple modification of the command for backing up an entire database:

mysqldump -d -h <host> -u <user> -p<password> <db_name> > backup.sql

Did you notice the difference? It is the -d option, which specifies that data should not be dumped. Using our previous examples this will be transform to:

mysqldump -d -h localhost -u user1 -ppass1 db1 > backup.sql
VN:F [1.9.18_1163]
Rating: 6.3/10 (3 votes cast)

Using SQL Check constraint

If you want to limit the range of the values a certain column can store you can do that by using check constraints. Check constraints can be defined over columns or over tables. A check constraint defined over a column limits only the values of the given column. A check constraint defined over a table can limit values in given columns based on the values of other columns. Let’s review some examples.

We can define the check constraint when we create the table using the following syntax:

For SQL Server, Oracle and Access:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 10.0/10 (1 vote cast)

Using SQL Primary Key constraint

In SQL the Primary Key is used to uniquely identify each row in a table. Therefore the values of primary keys must be unique and a column which is a part of the primary key cannot contain NULL. Every table has exactly one primary key. It is also possible for the primary key to be composed of different columns.

Primary keys can be specified when the table is created. For example:
For SQL Server, Oracle and Access:
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

How to Simplify Database Queries with DBAL

The main purpose of this is to cut down some of the typing as far as the more common MySQL commands and queries, making your code look a little cleaner and your time more productive. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

Create a dynamic menu with PHP

Ever wanted to create an unlimited levels menu using PHP and MySQL in Dreamweaver? Things are easier than it sounds if you use the Creative DW Menu Pack developer extension – you just need a database and the extension will do the rest. Let me show you how to do this with the latest Dreamweaver CS5. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.1/10 (10 votes cast)

Zen Cart Tutorials open source online store management system

Zen Cart is an open source online store management system. It is PHP-based, using a MySQL database and HTML components. Support is provided for several languages and currencies, and it is freely available under the GNU General Public License Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

MySQL Database Tutorials – creating a database, importing tables,

Many people tell me they aren’t very fond of scripts, because the install frightens them a bit. And it’s true: creating a database, importing tables, your site’s root path… if you don’t know what you’re doing, this looks pretty complicated.
Luckily, it is not, once you get the hang of it.
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

Setup a local testing server for Dreamweaver dynamic applications

In this tutorial I will show you how to setup the environment – server (Apache), server-side language (PHP) and database (Mysql) to create dynamic web applications in Dreamweaver. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

Setup MySQL cross replication

In this tutorial I will give you example configuration to setup cross-server replication in MySQL. The normal replication concept is pretty much explained in the term so I will not focus much on it. I will explain it just with a sentence so that you get familiar with the terminology.

Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves).

Cross-server replication is some small upgrade to the same concept. What it means is that the master and the slave change roles. Replication goes in something like a loop as shown in the picture below. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.2/10 (11 votes cast)

How to count unique records with sql

In tis tutorial we will show you some very basic SQL queries for extracting the exact information that we need from our database.

For our tutorial I have used a simple table with three fields over MySQL database server.

msisdn – is a string field and is the Primary key to the table. This means that values in that field are unique and no duplicates are allowed.
promo_sc – is an integer which will not be used in our demonstration
status – is also an integer number and will be the field we would want to count in the example. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)