Regular table sharding for large data volumes for Data Backup

The amount of data in a table in the database is large at work, which is a log table. Under normal circumstances, there will be no query operations, but if there is not much data in the table sharding, the execution of a simple

The amount of data in a table in the database is large at work, which is a log table. Under normal circumstances, there will be no query operations, but if there is not much data in the table sharding, the execution of a simple

The amount of data in a table in the database is large at work, which is a log table. Under normal circumstances, there will be no query operation, but if there is not much data in the table sharding, it takes several minutes to execute a simple SQL statement ..

Table sharding tool: management commands provided by shell and mysql in linux

Principle: replace the original table with a table with the same data structure as the original table.

The Linux Shell content is as follows:

====================================== Start

DATE = 'date + % Y % m % d' # current date backup

BACKUP_DIRECTORY = "/var/db_backup" # Backup Directory, which stores the temporary tables required for backup.

DB_USER = "root" # Database User

DB_PWD = "123456" # Database Password

WSM_APPENTRYREQLOG_SHELL = "$ BACKUP_DIRECTORY/db_appentryreqlog_shell. SQL" # Replace the storage location of the SQL command file executed in the db_appentryreqlog table

WSM_ADENTRYSHOWRECORD_SHELL = "$ BACKUP_DIRECTORY/db_adentryshowrecord_shell. SQL" # Replace the storage location of the SQL command file executed in the db_adentryshowrecord table

WSM_APPENTRYREQLOG_FILE = "$ BACKUP_DIRECTORY/db_appentryreqlog_nodata. SQL" # file storage location when the db_appentryreqlog structure is exported

WSM_ADENTRYSHOWRECORD_FILE = "$ BACKUP_DIRECTORY/db_adentryshowrecord_nodata. SQL" # file storage location when exporting the db_adentryshowrecord Structure

Rm-f $ WSM_APPENTRYREQLOG_FILE # delete an existing file

Rm-f $ WSM_ADENTRYSHOWRECORD_FILE # delete an existing file

Mysqldump-u $ DB_USER-p $ DB_PWD-d db db_appentryreqlog> $ WSM_APPENTRYREQLOG_FILE # export the table structure

Mysqldump-u $ DB_USER-p $ DB_PWD-d db db_adentryshowrecord> $ WSM_ADENTRYSHOWRECORD_FILE # export the table structure

Sed-I "s/wsm_appentryreqlog/db_appentryreqlog_new/" $ logs # Replace the name wsm_appentryreqlog In the exported table structure with the temporary name db_appentryreqlog_new

Sed-I "s/wsm_adentryshowrecord/db_adentryshowrecord_new/" $ WSM_ADENTRYSHOWRECORD_FILE # Same as above

Sed-I's/AUTO_INCREMENT = [0-9] \ +/AUTO_INCREMENT = 1/'$ WSM_APPENTRYREQLOG_FILE # New table structure, ID auto-increment value reset to 1

Sed-I's/AUTO_INCREMENT = [0-9] \ +/AUTO_INCREMENT = 1/'$ WSM_ADENTRYSHOWRECORD_FILE

Sed-I "s/db_appentryreqlog_bak/db_appentryreqlog _ $ DATE/" $ WSM_APPENTRYREQLOG_SHELL # Replace the backup table name in the db_appentryreqlog_shell. SQL file dynamically by DATE

Sed-I "s/db_adentryshowrecord_bak/db_adentryshowrecord _ $ DATE/" $ WSM_ADENTRYSHOWRECORD_SHELL # Same as above

# Cat $ WSM_APPENTRYREQLOG_FILE

# Echo '------------------------------------------------------------------------------------- 1'

# Cat $ WSM_ADENTRYSHOWRECORD_FILE

# Echo '------------------------------------------------------------------------------------- 2'

# Cat $ WSM_APPENTRYREQLOG_SHELL

# Echo '------------------------------------------------------------------------------------- 3'

# Cat $ WSM_ADENTRYSHOWRECORD_SHELL

# Echo '------------------------------------------------------------------------------------- 4'

# After the above preparations are completed, start to replace the table

Mysql-u $ DB_USER-p $ DB_PWD db <$ WSM_APPENTRYREQLOG_FILE # import the new table structure first.

Mysql-u $ DB_USER-p $ DB_PWD db <$ WSM_APPENTRYREQLOG_SHELL

Mysql-u $ DB_USER-p $ DB_PWD db <$ WSM_ADENTRYSHOWRECORD_FILE # Run the replace table command

Mysql-u $ DB_USER-p $ DB_PWD db <$ WSM_ADENTRYSHOWRECORD_SHELL

# Restore the file db_appentryreqlog_shell. SQL and db_adentryshowrecord_shell. SQL before modification

Sed-I "s/db_appentryreqlog _ $ DATE/db_appentryreqlog_bak/" $ WSM_APPENTRYREQLOG_SHELL

Sed-I "s/db_adentryshowrecord _ $ DATE/db_adentryshowrecord_bak/" $ WSM_ADENTRYSHOWRECORD_SHELL

# The execution is complete.

====================================== End

The content of the db_appentryreqlog_shell.sq file is

Rename table db_appentryreqlog TO db_appentryreqlog_bak, db_appentryreqlog_new TO db_appentryreqlog;

The content of the db_adentryshowrecord_shell. SQL file is

Rename table db_adentryshowrecord TO db_adentryshowrecord_bak, db_adentryshowrecord_new TO db_adentryshowrecord; # RENAME the old TABLE and change the new TABLE name TO the old TABLE name.

Place the shell command files and db_appentryreqlog_shell.sq and db_adentryshowrecord_shell. SQL files in the BACKUP_DIRECTORY = "/var/db_backup" directory.

Then, configure the shell command to the scheduled task cron. OK.