<?php
$db = new mysqli( 'localhost', 'user', 'pass' );
$db->select_db( 'user' );
$db->set_charset( 'utf8' );
function query( $sql )
{
global $db;
echo "$sql\n";
$r = mysqli_query( $db, $sql ) or die( mysqli_error($db) );
return $r;
}
function cleanup()
{
foreach( array( "a_test_myisam",
"c_test_innodb",
"b_test",
"c_test_myisam",
"a_test_innodb" ) as $table )
query( "DROP TABLE IF EXISTS $table;" );
}
function shutdown()
{
cleanup();
exit(0);
}
// start on clean db
cleanup();
// destroy tables after Ctrl-C to make restoring of test backup easier
declare(ticks = 1);
register_shutdown_function('shutdown'); // Catch Fatal Error (Rollback)
pcntl_signal(SIGTERM, 'shutdown'); // Catch Ctrl+C, kill and SIGTERM
pcntl_signal(SIGINT, 'shutdown');
// create test tables
query( "CREATE TABLE a_test_myisam (
a_id INTEGER PRIMARY KEY AUTO_INCREMENT,
x INTEGER NOT NULL DEFAULT 0
) ENGINE=MyISAM;");
query( "CREATE TABLE a_test_innodb (
a_id INTEGER PRIMARY KEY AUTO_INCREMENT,
x INTEGER NOT NULL DEFAULT 0
) ENGINE=InnoDB;");
query( "CREATE TABLE b_test (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
x INTEGER NOT NULL DEFAULT 0
) ENGINE=MyISAM;");
query( "CREATE TABLE c_test_myisam (
c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
x INTEGER NOT NULL DEFAULT 0
) ENGINE=MyISAM;");
query( "CREATE TABLE c_test_innodb (
c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
a_id INTEGER NOT NULL,
FOREIGN KEY (a_id) REFERENCES a_test_innodb( a_id ),
KEY( a_id )
) ENGINE=InnoDB;
");
// fill b_test with some dummy rows
query( "INSERT INTO b_test (x) VALUES (0);" );
for( $i=0; $i<18; $i++ )
query( "INSERT INTO b_test (x) SELECT x FROM b_test;" );
$r = mysqli_fetch_row(query( "SELECT count(*) FROM b_test" ));
echo "b_test contains ${r[0]} rows\n";
echo "start backup now and ctrl-c this program when backup is finished\n";
for( $i=0; $i<1000000; $i++ )
{
// myisam test
mysqli_query( $db, "LOCK TABLES a_test_myisam WRITE, c_test_myisam WRITE" ) or die( mysqli_error($db) );
mysqli_query( $db, "INSERT INTO a_test_myisam (x) VALUES ($i)" ) or die( mysqli_error($db) );
mysqli_query( $db, "INSERT INTO c_test_myisam (x) VALUES ($i)" ) or die( mysqli_error($db) );
mysqli_query( $db, "UNLOCK TABLES" ) or die( mysqli_error($db) );
// innodb test
mysqli_query( $db, "BEGIN" ) or die( mysqli_error($db) );
mysqli_query( $db, "INSERT INTO a_test_innodb (x) VALUES (0)" ) or die( mysqli_error($db) );
$id = mysqli_insert_id($db);
mysqli_query( $db, "INSERT INTO c_test_innodb (a_id) VALUES ($id)" ) or die( mysqli_error($db) );
mysqli_query( $db, "COMMIT" ) or die( mysqli_error($db) );
}