mysql-使用load两分钟-千万行表快速迁移合成亿行总表

[CONFIG]
host = ip
uesr = user_name
password = user_password
database = database_name
post = 3306

#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#-------------------------------------------------------------------------
#   程序：mysql_to_txt.py
#   版本：1
#   作者：ly
#   日期：编写日期2017/2/18
#   语言：Python 2.7.x
#   操作：python mysql_to_txt.py 表名
#   功能：从数据库中读取有用数据存入txt，，之后用于load
#-------------------------------------------------------------------------
import MySQLdb
import time
from ConfigParser import SafeConfigParser
from sys import argv

conf = SafeConfigParser()
conf.read('./mysql.ini')
Host = conf.get('CONFIG', 'host')
User = conf.get('CONFIG', 'uesr')
Passwd = conf.get('CONFIG', 'password')
DB = conf.get('CONFIG', 'database')
Post = int(conf.get('CONFIG', 'post'))#需要int类型

starttime = time.time()
filename = './GD_1_1.txt'
tablename =  argv[1] #导入表名

file_ter = ';' # 分隔符
line_ter = '\n' #不同系统可能行分隔符不同

#ip转换的函数----无用
ip_to_num = lambda ip:sum([256**j*int(i) for j,i in enumerate(ip.split('.')[::-1])])
ip_to_ip3 = lambda ip:'.'.join(ip.split('.')[0:3])

if __name__ == '__main__':
    conn = MySQLdb.connect(host=Host,user=User,passwd=Passwd,db=DB,port=Post)
    cur = conn.cursor()

    select_sql = "SELECT ip,lat_gd,lon_gd,datetime FROM %s WHERE flag = 1 Limit 30;"  # 可修改
    cur.execute(select_sql % tablename)

    for now in cur.fetchall():
        pri_str = str(ip_to_num(now[0]))+','+str(now[0])+','+str(ip_to_ip3(now[0]))+','+str(round(float(now[1]),8))+','+str(round(float(now[2]),8))+','+str(now[3])
        print pri_str
    conn.close()

python read_mysql_to_txt.py 表名1 > 导出文件1.txt
python read_mysql_to_txt.py 表名2 > 导出文件2.txt
python read_mysql_to_txt.py 表名3 > 导出文件3.txt

python txt_load_mysql.py 文件1.txt
echo '1'
python txt_load_mysql.py 文件2.txt
echo '2'
python txt_load_mysql.py 文件3.txt

#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#-------------------------------------------------------------------------
#   程序：mysql_to_txt.py
#   版本：1
#   作者：ly
#   日期：编写日期2017/2/18
#   语言：Python 2.7.x
#   操作：python mysql_to_txt.py txt文件名
#   功能：读取txt用于load
#-------------------------------------------------------------------------
import MySQLdb
import time
from ConfigParser import SafeConfigParser
from sys import argv

conf = SafeConfigParser()
conf.read('./mysql.ini')
Host = conf.get('CONFIG', 'host')
User = conf.get('CONFIG', 'uesr')
Passwd = conf.get('CONFIG', 'password')
DB = conf.get('CONFIG', 'database')
Post = int(conf.get('CONFIG', 'post'))#需要int类型
#print Host,User,Passwd,DB,Post
starttime = time.time()
filename = argv[1] # 外部传参数
tablename = '输出表名需要与txt文件对应'

file_ter = ","
line_ter = '\n'

if __name__ == '__main__':
    conn = MySQLdb.connect(host=Host,user=User,passwd=Passwd,db=DB,port=Post)
    cur = conn.cursor()

    load = "load data local infile '{0}' ignore into table {1} fields terminated by '{2}' lines terminated by '{3}' (num_ip,ip,ip3,lat_GD,lon_GD,time)"
    print load.format(filename,tablename,file_ter,line_ter)
    cur.execute(load.format(filename,tablename,file_ter,line_ter))

    conn.commit()
    conn.close()
    print time.time()-starttime