Category: MySQL

How can I join the count in MySQL?

November 24, 2013 at 11:59 pmCategory:MySQL

If I have two tables one is:

CAR

where id is the car id number & maker is the maker of the car

and the other is

Purchase

where custid is the id of the customer & carid is the id to a specific car

Is there a way to join the two together & then figure out which customers have bought ALL the Hondas?

Try this query:

SQL FIDDLE

How to create distributed/mirrored dynamic scripting website (Step – 4 Region detection and redirection)

May 18, 2012 at 9:03 pmCategory:Linux | MySQL | PHP

Step four: Region detection & redirection
First of all, download the latest database

Create a table in your website database & import the data from the csv file

Now just need add a few line of php code at the beginning of index.php file to handle the redirect.

How to create distributed/mirrored dynamic scripting website (Step – 2 Database synchronization)

May 18, 2012 at 4:25 pmCategory:Linux | MySQL

Step two: Database Syncrhonization
This is the key of the dynamic website synchronization, I will use mysql server 5.x for example, if you are using a different database server, you will need to find out how to configure their replication.

1. Configure Master & Slave Server
Create a mysql user for replication

Edit my.cnf file [mysqld] section
server-id=10
log-bin=mysql-bin
innodb_flush_logs_at_trx_commit=1
innodb_support_xa=1
sync_binlog=1

2. Copy the database:
We need to lock table on master server before we dump the database

Write down the bin log file record & the postion & leave this connection open, then create a new ssh connection to the master server & dump the databse with:

Copy all_database.sql.gz to slave server & extract the files & import into slave database

An alternative way is to copy the content of folder /var/lib/mysql/data/* from master to slave.

Now you received exactly the same database on the salve server, then run SQL command on slave server

If you see Thread IO & Thread SQL are both running, then your database should be synchronized
Now go back to master server mysql session & run

Next: How to create distributed/mirrored dynamic scripting website (Step – 3 Security)

How to create distributed/mirrored dynamic scripting website (Step – 1 File synchronization)

May 18, 2012 at 4:25 pmCategory:Linux | MySQL

This tutorial introduce a effortless way to build a distributed dynamic scripting website between Linux servers in two different zone.

To build up a dynamic website mirror for different zone/region/country without using Content Delivery Network, you should consider:

  1. File Synchronization
  2. Database Synchronization
  3. Security
  4. Region Detection And Redirection


Step one: File Synchronization

We will use rsync for file synchronization

It is recommended to create a batch file & run this command every 5 minutes, you may need to detect if rsync is already running, checkout here to find out how to detect if a process is already running. You may have muptiple hosts, yet the principle should be the same.

Next: How to create distributed/mirrored dynamic scripting website (Step – 2 Database synchronization)

Mysql Change current database characterset

April 20, 2012 at 9:14 amCategory:MySQL

Assuming you have a database with default character set latin-1 & wish to convert it to utf-8, it is very simple to convert it without any external tool

1. Dump the database:

parameter –compatible=mysql40 is to dump the database without charset settings.

2. Create a database utf8 default:

3. Reimport the database

Now you have received the utf8 character set database_new.

Run multiple SQL query in PHP when using SQL variables

September 16, 2010 at 9:37 amCategory:MySQL | PHP

Some times you may need to run mulitple query in PHP to obtain the query result you want. eg

Here is an example function