All MySQL records from yesterday

You can use subdate to indicate "yesterday" and use date() to indicate that you want records where just the date part of the column matches. So:

SELECT *
FROM tablename
WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)

Here is the same question with an answer. To summarize answer for you, use subdate() as suggested by Sajmon.

subdate(currentDate, 1)

using your table it should be.

select *
from tablename
where created_at between subdate(CURDATE(), 1)
and date (now() )

You can still use the index if you say

SELECT * FROM TABLE
WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY
  AND CREATED_AT < CURDATE();

Since you're only looking for the date portion, you can compare those easily using MySQL's DATE() function.

SELECT * FROM table WHERE DATE(created_at) = DATE(NOW() - INTERVAL 1 DAY);

Note that if you have a very large number of records this can be inefficient; indexing advantages are lost with the derived value of DATE(). In that case, you can use this query:

SELECT * FROM table
    WHERE created_at BETWEEN CURDATE() - INTERVAL 1 DAY
        AND CURDATE() - INTERVAL 1 SECOND;

This works because date values such as the one returned by CURDATE() are assumed to have a timestamp of 00:00:00. The index can still be used because the date column's value is not being transformed at all.