Tag Archives: export

Exporting Gitlab project through the API

Posted on by
Reply

To export a project through Gitlab’s API, first create a Personal Access Token (PAT) and give api and repository permissions.


Export PAT as an environment variable.

Then find the project id (main page of the project – 1111).

To start an export use

curl --request POST --header "PRIVATE-TOKEN: $PAT" "https://your.gitlab.repo/api/v4/projects/1111/export" --data "upload[http_method]=PUT"

Then check the status and get the download link when it has finished

curl --header "PRIVATE-TOKEN: $PAT" "https://your.gitlab.repo/api/v4/projects/1111/export"
{"id":1111,"description":"Project description","name":"project","name_with_namespace":"MyOrg / project","path":"project","path_with_namespace":"myorg/project","created_at":"2021-11-01T10:57:23.195+02:00","export_status":"finished","_links":{"api_url":"https://your.gitlab.repo/api/v4/projects/1111/export/download","web_url":"https://your.gitlab.repo/myorg/project/download_export"}}

Exporting SQL structure commands from MySQL Workbench

Posted on by
Reply

Problem

You would like to export the structure of some specific MySQL tables using MySQL Workbench (ie to create same tables in another database).

Solution

Go to the table name, right click, select ‘Copy To Clipboard’, then ‘Create Statement’ and finally go to a new file or editor and paste the command from the clipboard.
Just have in mind that you may need to delete the AUTO_INCREMENT from the SQL.
Solution was described here

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Posted on by
Reply

Problem

You want to export a MySQL database table as a csv with something like:

mysql> select * from table_name into outfile '/tmp/table_name.csv';

but you get the error that MySQL cannot execute this command.

Solution

Find out the location that MySQL can use to export files by running the following:

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

and then use the path to change the outfile path (ie /var/lib/mysql-files/table_name.csv).

Taken from the answer here

Exporting query results from MySQL

Posted on by
Reply

Problem

You would like to export some query results from MySQL into a format that can be imported in LibreOffice, OpenOffice or Excel.

Solution

Try first with the default export options, as by doing a google search, there are a lot of different options for specifying field delimeters, lines terminated, etc.
So by not specifying any additional options the export is a tab delimited, linefeed-terminated file which should work in most of the programs.
So in order to do that you would need a folder in the MySQL server that should have write access. Usually /tmp should do. Then you can build your query and at the end add the INTO OUTFILE ‘/path’, like:

SELECT one,max(two) max_two,min(three) min_three 
FROM table 
WHERE one=1 
GROUP BY one 
INTO OUTFILE '/tmp/my_query.csv';

You can use any file ending but usually by specifying .csv the program that you will try to open will understand it.

Comparing data from two big MySQL tables

Posted on by
Reply

Problem
You have two big tables in MySQL (>640K records), that maybe differ in the number of fields, but you want to make sure that the data in the common fields in both tables are the same.

Solution

  1. Use mysql to export the data from the first table in a csv file, selecting only the common fields.
    We use the /tmp folder on the server to make sure we have the right permissions to create the file:

    mysql>SELECT common_field1, common_field2, ... 
INTO OUTFILE '/tmp/first_table.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table1;
  2. If the tables are in different databases remember to switch db: 
    use seconddb;

    Export the second table in the second file:

    mysql>SELECT common_field1, common_field2, ... 
INTO OUTFILE '/tmp/second_table.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table2;
  3. now use the diff. You can use any of the following options:
    • diff -q first_table.txt second_table.txt
    • diff first_table.txt second_table.txt > diff.txt