MySQL data sheet is implemented by the day rotation shell script

Source: Internet
Author: User
Tags mysql client

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.