MySQL 5.6Archive large tables

Environment: MySQL 5.6 master-slave environment (Keepalived architecture)

4000W row table for historical data archiving.

Programme: in order to minimize the impact on the business, it was decided to adopt the following options.

1. Establish four history table structures of 2016, 2017, 2018 and 2019 in the main database.

2. Establish the test database in the slave database, and establish four history table structures of 2016, 2017, 2018 and 2019. On the master table of the slave database, use the insert into statement to insert the data into the history tables of 2016, 2017, 2018 and 2019 of the test database according to the time field. Split into 2016, 2017, 2018, 2019.

3. Use Navicat to export 2016, 2017, 2018, 2019 to SQL files, and generate TXT files of DELETE statements of the main table.

4. Use Python script to process SQL file and TXT file, import them into four history tables of 2016, 2017, 2018 and 2019 in batches, and delete the historical data of the main table.

5. Shrink the main table.

Complete the filing.

1. Establish the table structure of the history table in the main database.

CREATE TABLE `upload_order_header_ 2016` (

`id`bigint (22) NOT NULL AUTO_INCREMENT COMMENT 'self-adding id'

`company` varchar (25) DEFAULT NULL COMMENT 'owner'

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Establish the test library from the library, and also establish the table structure of the history table.

Insert the 2016 history data into the 2016 history table of the test library with the insert into statement on the slave database.

Insert into test.upload_order_header_2016 select * from log_db.upload_order_header

Where add_time

< unix_timestamp('2017-01-01 00:00:00'); insert into test.upload_order_header_2017 select * from log_db.upload_order_header where add_time >

= unix_timestamp ('2017-01-01 00 0000') and

Add_time

< unix_timestamp('2018-01-01 00:00:00'); 3、用 Navicat把 2016导出为SQL文件，举例：

What is exported is a pure insert SQL script.

Export Delete statements:

4. Use the Python script to run the above scripts in batch.

First insert to the history table of the target main database, and then delete the historical data of the target main database.

The Python script is as follows:

Load_sql_v1.py:

# coding:utf8

"

1. Update the database configuration

2. Change the file to be executed and call it SQL.sql.

3. Execution file

"

Import pymysql

Import time

DB_IP = "192.168.22.10"

DB_USER = "DBA"

DB_PWD = "XXXXXX"

DB_DATABASE = "log_db"

WaitTime = 10

FilePath = [

'2016.sql'

]

For file in FilePath:

F = open (file, mode='r')

Print (file)

Content=f.readlines ()

# Open database connection

Db = pymysql.connect (DB_IP, DB_USER, DB_PWD, DB_DATABASE, charset='utf8')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# execute SQL statements using the execute method

Cursor.execute ("SELECT VERSION ()")

# use the fetchone () method to get a piece of data

Data = cursor.fetchone ()

Print ("Database version:% s"% data)

For index, sql in enumerate (content):

If index% 10000 = 0:

Print ('executed d'%index)

If index% 20000 = 0:

Time.sleep (WaitTime)

Try:

# execute sql statement

Db.ping (reconnect=True)

Cursor.execute (sql)

# submit to the database for execution

Db.commit ()

Except Exception as e:

# Rollback in case there is any error

Print (sql)

Print (e)

# # db.rollback ()

F.close ()

# close database connection

Db.close ()

5. Shrink the main table.

Do it with the pt-osc tool.