Category Archives: MySQL

How can I join the count in 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)

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)

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)

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

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

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

Here is an example function