MySQL data sheet is implemented by the day rotation shell script

Recently there's a business that writes data to a table in a MySQL database,
The amount of data per day is not very large, and the business is always dependent on the last 1 days of data;
But with the accumulation of time, the data entry in the database becomes more and the query becomes slower;
If you can follow the talent table, things will be solved;
However, the business logic of using the table should be adjusted accordingly;
To achieve the tradeoff between the two, take the method of rotating the database table by day:
Implementation mechanism create like + rename.

But need to use mysql-client, go to manual operation, but operation once a day is also very tedious thing.
Can you write a script to rotate it automatically?

The answer is yes.

Found MySQL client + input redirected SQL file,
You can enable mysql-client to perform non-interactive execution of SQL statements in a redirected file;

But how do you pass the time stamp in?
Maybe you already think of it. You can generate a daily SQL statement file with a response timestamp;
But it seems a little Shejinqiuyuan;

In fact, this can be achieved through here Document:

See the following shell script for details:

#!/bin/shif["$#"!="6"] ; Then Echo"Usage $ db_host db_port db_name table_name db_user db_passwd"Exit1fidb_host=$1db_port=$2db_name=$3table_name=$4db_user=$5db_passwd=$6Last_day= ' date-d"1 day Ago"  "+%y%m%d"' Mysql-h $db _host-p $db _port-u $db _user $db _name-p $db _passwd<<eof show tables; CREATE TABLE ${table_name}_New like $table _name; Rename table $table _name to ${table_name}_$last _day, ${table_name}_New to $table _name; Show tables; Eof

This script requires the Crontab script to be passed in with the relevant parameters once a day;

Do not know whether there is a better way, welcome to correct;

MySQL data sheet is implemented by the day rotation shell script