Rotate huge tables to old_tablename without affecting continued writes to these tables and keeping existing indexes and auto_increment counters.

mysql_rotate_large_table.sql

DROP PROCEDURE IF EXISTS rotateAudit;
delimiter ;;
CREATE PROCEDURE rotateAudit(
  pv_database VARCHAR(64),
  pv_table VARCHAR(64)
)
BEGIN
        SET @createDbStatement := CONCAT('CREATE DATABASE IF NOT EXISTS archive_',pv_database);
        SET @createStatement := CONCAT('CREATE TABLE ',pv_database,'.new_',pv_table,' LIKE ',pv_database,'.',pv_table);
        SET @insertStatement := CONCAT('INSERT INTO ',pv_database,'.new_',pv_table,' SELECT * FROM ',pv_database,'.',pv_table,' ORDER BY id DESC LIMIT 50000');
        SET @renameStatement := CONCAT('RENAME TABLE ',pv_database,'.',pv_table,' TO archive_',pv_database,'.old_',pv_table,', ',pv_database,'.new_',pv_table,' TO ',pv_database,'.',pv_table);

        PREPARE createDbStatement FROM @createDbStatement;
        PREPARE createStatement FROM @createStatement;
        PREPARE renameStatement FROM @renameStatement;
 
        EXECUTE createDbStatement;
        EXECUTE createStatement;
        PREPARE insertStatement FROM @insertStatement;
 
        START TRANSACTION;
                SELECT AUTO_INCREMENT+1000 INTO @autoInc
                        FROM information_schema.tables
                        WHERE table_name=pv_table
                        AND table_schema=pv_database;
                SET @changeStatement := CONCAT('ALTER TABLE ',pv_database,'.new_',pv_table,' AUTO_INCREMENT = ',@autoInc);
                PREPARE changeStatement FROM @changeStatement;
                EXECUTE insertStatement;
                EXECUTE changeStatement;
                -- EXECUTE renameStatement;
        COMMIT;
        DEALLOCATE PREPARE changeStatement;
        DEALLOCATE PREPARE renameStatement;
        DEALLOCATE PREPARE insertStatement;
        DEALLOCATE PREPARE createStatement;
        DEALLOCATE PREPARE createDbStatement;
END;;
delimiter ;