MySQL insert into different databases tables based on PK value

insertMySQLsharding

Lets assume i have two databases, both having a table 'user_feedback', sharding data between the two databases. My goal is to insert all records where Primary key Id%2=0 goes to db1.user_feedback, and Id%2=1 goes to db2.user_feedback.

Is there a condition in the INSERT statement that can allow me to do this? How can i achieve simply achieve this, both if the two databases are located on the same MySQL server, and when they both reside on a different MySQL server?

** UPDATE **
Ok, what i am trying to achieve is to have several tables in the same MySQL server to shard data and limit one table from becoming too big, hence slowing up queries on this table. The table is expected to grow very large (billion records) with heavy writes. That is the reason we have decided to resort to sharding. The table does not currently exist, but we want to create this shard solution in advance before we hit production with this table.

Best Answer

I am extremely dubious that you have the problem you describe, but here goes:

CREATE TEMPORARY TABLE t (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ...   (the rest of the columns)
    );

INSERT INTO t
    SELECT * FROM source;

INSERT INTO db1.x  SELECT ... FROM t WHERE id % 2 = 0;
INSERT INTO db2.x  SELECT ... FROM t WHERE id % 2 = 1;

If the data is coming from clients that are feeding the data in fast and furious, then I recommend ping-ponging between two temp tables, not between even/odd. The clients will INSERT into one temp table while a separate task is copying the other temp table into one of the two 'real' tables. Then that task will TRUNCATE the table it copied, then RENAME TABLE to atomically swap the temp tables.

I discuss that in more detail in http://mysql.rjweb.org/doc.php/staging_table

Related Solutions

Mysql – 3 Databases, Best Replication Setup

I wrote up an interesting layout last year which features DRBD pairs in two data centers (DC1,DC2) with as follows

DRBD Pair in DC1 (db1 and db2)
DBVIP for Primary of DRBD Pair1 is 10.1.2.30
DRBD Pair in DC2 (db3 and db4)
DBVIP for Primary of DRBD Pair2 is 10.1.2.40
Have MySQL Circular Replication Between DRBD Primaries
Have the 10.1.2.40 as Master_Host for DBRD Pair 1
Have the 10.1.2.30 as Master_Host for DBRD Pair 2

MySQL high availability, failover and replication with Latency

MySQL Replication : 1 Slave / Multiple Masters

Here is why I suggested this: Using two data centers, you setup automatic failover for DRBD Pair in one data center. Let the other DRBD Pair in the second data center be for DB disaser site with it own local redundancy and failover. Should you ever loses one data center, the other data center is fully read with it own local failover setup. Your app would just have to use the DBVIP of the other database center in such a catastrophic case.

Please keep in mind that using DRBD in conjunction with MySQL is only beneficial if all of your data uses the InnoDB Storage Engine. Hard failovers in DRBD could easily result in crashed MyISAM tables.

Here is another setup to consider:

As with DRBD setups, any DRBD Secondary would provide just a Disk-Level copy of your MySQL Folder. It is available as a warm standby. MySQL is not being run on the DRBD Secondary. If you want the third DB server to become hot standby, ditch DRBD altogether and use pure MySQL Replication. With three DB servers, using db3 at a remote site, simply setup the following:

+--> db1 --> db2 --> db3 -->+
^                           |
|                           V
+<--------------------------+

Using your rudimentary failover, now you have two hot standby servers. You just have to make sure each DB server has a unique server_id value. I also recommend using MySQL 5.5 because it uses SemiSync Replication which is more sensitive to communication failures and stop Replication better. You will have to setup the appropriate heartbeats and timeouts.

Mysql – Database migration, how to

Data Extraction into a Sandbox

CREATE DATABASE IF NOT EXIST sms_sandbox;
CREATE TABLE IF NOT EXISTS sms_sandbox.mo_sms LIKE DB2.mo_sms;
CREATE TABLE IF NOT EXISTS sms_sandbox.mt_sms LIKE DB2.mt_sms;
#
# Drop All Indexes From the Sandbox
#
ALTER TABLE sms_sandbox.mo_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_sms_proceso_state`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`,
    DROP INDEX `ix_varios`;
ALTER TABLE sms_sandbox.mt_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`;
ALTER TABLE sms_sandbox.mo_sms AUTO_INCREMENT=1;
ALTER TABLE sms_sandbox.mt_sms AUTO_INCREMENT=1;
#
# Load the Data From 2013-04-19
#
INSERT INTO sms_sandbox.mo_sms
SELECT * FROM DB2.mo_sms
WHERE fecha_sms >= '2013-04-19 00:00:00'
AND fecha_sms <= '2013-04-19 23:59:59';
INSERT INTO sms_sandbox.mt_sms
SELECT * FROM DB2.mt_sms A INNER JOIN sms_sandbox.mo_sms B USING (id_MO);

Data Insertion From the Sandbox

#
# Get Maximum id_MO from DB1.mo_sms
# Increment all id_MOs by the Max Value
#
SELECT MAX(id_MO) INTO @Max_id_MO FROM DB1.mo_sms;
UPDATE sms_sandbox.mo_sms SET id_MO = id_MO + @Max_id_MO;
UPDATE sms_sandbox.mt_sms SET id_MO = id_MO + @Max_id_MO;
#
# Load the New Data into DB1
#
ALTER TABLE DB1.mo_sms DISABLE KEYS;
INSERT INTO DB1.mo_sms SELECT * FROM sms_sandbox.mo_sms;
ALTER TABLE DB1.mo_sms ENABLE KEYS;
ALTER TABLE DB1.mt_sms DISABLE KEYS;
INSERT INTO DB1.mt_sms
(`sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
`fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
`id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`)
SELECT 
    `sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
    `fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
    `id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`
FROM sms_sandbox.mt_sms;
ALTER TABLE DB1.mt_sms ENABLE KEYS;

WARNING : Practice this on a Staging Server, Please.

SUGGESTION

All the mt_sms tables should have an index on id_MO

ALTER TABLE mt_sms ADD INDEX (id_MO);