使用load这种底层的迁移方式,会让移动速度非常快。将已经导出为txt的7.2G数据合成为接近1亿行的总表,大致耗时2分钟。
其中的sql语句字段需要自定义,其次需要在同一目录下放入你mysql连接的配置文件
格式大概是:名为mysql.ini
[CONFIG]
host = ip
uesr = user_name
password = user_password
database = database_name
post = 3306
导出为txt的python代码为:
#!/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()
使用sh bash.sh 运行下列命令 将数据表带
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
load也同理
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
同样需要导入数据库连接的Mysql.ini配置文件
#!/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
原创文章,转载请注明: 转载自URl-team
本文链接地址: mysql-使用load两分钟-千万行表快速迁移合成亿行总表