Mysql large tables are split into csv files for export, and mysql is split into csv files for export.

Source: Internet
Author: User

Mysql large tables are split into csv files for export, and mysql is split into csv files for export.

Recently, the company has tens of millions of rows of large tables that need to be split into different csv files according to the city id field.

I wrote an automated shell script.

Under/home/hdh

Linux-xud0:/home/hdh # ll
Total 16
-Rwxrwxrwx 1 root 902 Dec 28 07:47 cf. sh
-Rwxrwxrwx 1 root 6 Dec 28 07:47 id_1.txt
-Rwxrwxrwx 1 root 6 Dec 27 20:00 log

Vim cf. sh

#! /Bin/bash
DATE = $ (date + '% Y % m % D ')
Cd/home/hdh

Rm-r/home/hdh/file # delete the file folder generated by the last shell execution
Mkdir/home/hdh/file # create a file folder to store the generated csv file
Chmod 777/home/hdh/file-R

Echo 'mysql-uroot-proot-e "select distinct id FROM xizang. test; "| grep-Evi" id "'>/home/hdh/id_1.txt # Save the value of the id field of the table to/home/hdh/id_1.txt.
# Cat id_1.txt | while read line
For line in $ (<id_1.txt );
Do
# Echo $ {line }>> id_2.txt
# Echo 'mysql-uroot-proot-e "select distinct id FROM xizang. test where id = '$ {line}';" '> id_2.txt
# Mysql-uroot-proot-e "select distinct id FROM xizang. test where id = '$ {line}' into outfile'/home/hdh/c.csv 'fields TERMINATED ', 'enabledby' "'Lines TERMINATED by' \ r \ n ';"
Mysql-uroot-proot <EOF
SELECT * FROM xizang. test where id = '$ {line}' into outfile'/home/hdh/file/${line=_c.csv 'fields TERMINATED ', 'enabledby' "'Lines TERMINATED by' \ r \ n'; # generate the condition where id is used to the corresponding csv file.
Exit
EOF
Done

 

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.