Restore Slave When it broke

When something goes wrong, slave throws error, one symptom will be that a lot of mysqld-relay-bin.xxxx files are accumulated in /var/lib/mysql/. So you try to restart slave, but got error,

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

And you check mysql error log (usually at /var/log/mysqld.log), it says "111014 3:30:47 [ERROR] Error reading slave log configuration". How to do? The simple solution is that,

• Stop slave, by "slave stop;" at mysql command line.
• Remove all mysqld-relay-bin.xxxx.
• Remove master.info and relay-log.info
• Then start slave by "slave start"

That should work, but you may lost some positions. And if you wanted to start slave at current replcate log file, then you can run one more command before starting slave.

CHANGE MASTER TO MASTER_LOG_FILE='your_current_replog.000693', MASTER_LOG_POS=certain_pos (I used 4);

Good luck!

mySQL date function

The SQL to get total order number and total order amount from orders table:

SELECT count(*), sum(order_total) 
FROM orders 
WHERE DAYOFMONTH(last_accessed) = DAYOFMONTH(now()) 
      AND MONTH(last_accessed) = MONTH(now()) 
      AND YEAR(last_accessed) = YEAR(now()) 
      AND record_state = 1

Note:

1. DAYOFMONTH is synonym of DAY used to get day (ex: 1-31) from a data and time string (ex: 2009-04-14 11:44:02)
2. Complete mysql date function can be found at here

Solutions of database replication failure

MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. (instrested in how this being set up, check it out here, http://www.howtoforge.com/mysql_database_replication)

My compare have two web servers, and the replication is turning on. We use below php file to check the replication status.  

$link = mysql_connect('localhost', 'root', 'password')

   or die('Could not connect: ' . mysql_error());

mysql_select_db('production') or

    mail('example@email.com', 'www cannot connect', $message);

$query = 'SHOW SLAVE STATUS';

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

$line = mysql_fetch_array($result, MYSQL_ASSOC);

if ( $line["Slave_SQL_Running"]  != "Yes"  or

     $line["Slave_IO_Running"]  != "Yes" ) {

    $message = $line["Last_Error"];

    mail('example@email.com', 'www replication failure', $message);

}

?>

During the development, we created a database on slave DB, and today we were going to move the code to production, so we created a database on master DB. It used the samed name as the same one on slave DB, but having nothing in there. A few minutes later, we got email telling us the repliation failed.

Here is a solution:

On Slave:

1. In phpMyAdmin export the Database (For example, named A)

2. Drop the Database (A)

3. logon mysql, run command: START SLAVE (It would try to rerun the queries)

On Master:

1. Drop the Database (A);

2. Create a Database using different name (B)

On Slave:

Import the Database using the Export file.