Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Search
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Brand Logo

Cloudron Forum

Apps | Demo | Docs | Install
  1. Cloudron Forum
  2. Support
  3. Import 5gb sql file into MYSQL database

Import 5gb sql file into MYSQL database

Scheduled Pinned Locked Moved Solved Support
mysqlimporttraccar
21 Posts 4 Posters 3.4k Views 4 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • vadimV Offline
    vadimV Offline
    vadim
    wrote on last edited by girish
    #1

    Hello,

    I'm currently in the process of migrating Traccar to Cloudron and have encountered a significant challenge. I have a MySQL database file that is around 5GB in size, and despite numerous attempts, I cannot seem to upload it to the tmp folder successfully. The folder remains empty after each attempt, which is puzzling since I've had no issues with smaller databases from other applications.

    Are there any specific settings or steps that I should follow to bypass this hurdle? Any guidance or recommendations would be greatly appreciated as I'm keen on completing this migration successfully.

    Thank you in advance for your assistance and advice.

    1 Reply Last reply
    1
    • BrutalBirdieB Offline
      BrutalBirdieB Offline
      BrutalBirdie
      Partner
      wrote on last edited by
      #2

      You can always directly upload it to the root server and move it into the /app/data/ folder.
      Just rsync the file to the root server and move it to /home/yellowtent/appdata/{ID}/data/

      Like my work? Consider donating a drink. Cheers!

      BrutalBirdieB 1 Reply Last reply
      1
      • girishG Offline
        girishG Offline
        girish
        Staff
        wrote on last edited by
        #3

        @vadim have you tried uploading via filemanager ? For historic reasons, Filemanager and Upload to tmp are separate upload functionalities. Once you upload the file in filemanager, you will find it in /app/data in the Web terminal.

        vadimV 1 Reply Last reply
        0
        • girishG girish

          @vadim have you tried uploading via filemanager ? For historic reasons, Filemanager and Upload to tmp are separate upload functionalities. Once you upload the file in filemanager, you will find it in /app/data in the Web terminal.

          vadimV Offline
          vadimV Offline
          vadim
          wrote on last edited by
          #4

          @girish It doesn't work also in file manager. It was waiting for long time on 100% and noting happened when finished.

          File_Manager.jpg

          1 Reply Last reply
          0
          • girishG Offline
            girishG Offline
            girish
            Staff
            wrote on last edited by
            #5

            @vadim by any chance, is your dashboard behind Cloudflare? I think Cloudflare has some upload limit.

            vadimV 1 Reply Last reply
            0
            • girishG girish

              @vadim by any chance, is your dashboard behind Cloudflare? I think Cloudflare has some upload limit.

              vadimV Offline
              vadimV Offline
              vadim
              wrote on last edited by
              #6

              @girish I have my own server, don't use Cloudflare. I got finally copied with 2 steps:

              1. Ubunti terminal
              cp /mnt/usb/traccar.sql /home/yellowtent/appsdata/836aeaf4-bc7b-44cb-9227-2ca70aa0f7df/data/traccar.sql
              
              1. Cloudron > Traccar terminal
              cp /app/data/traccar.sql /tmp/traccar.sql
              

              But! Dump import was not successful; it was aborted after a long time, and Traccar is not responding. I used:

              mysql --user=${CLOUDRON_MYSQL_USERNAME} --password=${CLOUDRON_MYSQL_PASSWORD} --host=${CLOUDRON_MYSQL_HOST} ${CLOUDRON_MYSQL_DATABASE} < /tmp/traccar.sql
              

              What is the best way to import this big file?

              1 Reply Last reply
              0
              • girishG Offline
                girishG Offline
                girish
                Staff
                wrote on last edited by
                #7

                @vadim I would give MySQL a lot of memory for a start - https://docs.cloudron.io/services/#configure . Was there any error message during abort?

                1 Reply Last reply
                0
                • girishG girish marked this topic as a question on
                • vadimV Offline
                  vadimV Offline
                  vadim
                  wrote on last edited by
                  #8

                  No any way. I have on test server 4gb RAM, i set to max for mysql. During long time it is importing, then web terminal resets.
                  1.
                  1.jpg
                  2.
                  2.jpg
                  3.
                  3.jpg

                  1 Reply Last reply
                  0
                  • girishG Offline
                    girishG Offline
                    girish
                    Staff
                    wrote on last edited by
                    #9

                    @vadim the systemctl command can only be run via SSH (and not the web terminal).

                    If you are comfortable with docker, you can try one more thing:

                    • SSH into server
                    • Find the container of the traccar app. You can find this from docker ps | grep traccar
                    • docker exec -ti <containerid> /bin/bash . Do the MySQL import here .

                    Thing is I suspect the web terminal is disconnecting because the command is running too long. Maybe there are proxies in the middle that disconnect the connection. There could be many reasons (OS/Network/Firewall/Proxy/Browser...). Doing docker exec directly atleast rules out most of the issues.

                    1 Reply Last reply
                    1
                    • scookeS Offline
                      scookeS Offline
                      scooke
                      wrote on last edited by
                      #10

                      FWIW, the few times I have a large DB which wouldn't import I just opened the sql file in a local DB app, and copied and pasted section by section. Took awhile, but ultimately quicker than banging my head against a wall.

                      A life lived in fear is a life half-lived

                      1 Reply Last reply
                      0
                      • girishG Offline
                        girishG Offline
                        girish
                        Staff
                        wrote on last edited by
                        #11

                        The issue here is that disk speed is quite low (90Mb/sec). To track the slow progress:

                        • Put app in recovery mode
                        • apt update && apt install pv
                        • pv traccar.sql | mysql --user=${CLOUDRON_MYSQL_USERNAME} --password=${CLOUDRON_MYSQL_PASSWORD} --host=${CLOUDRON_MYSQL_HOST} ${CLOUDRON_MYSQL_DATABASE}

                        image.png

                        FWIW, it imported in < 10 mins in my test installation on DigitalOcean.

                        1 Reply Last reply
                        1
                        • vadimV Offline
                          vadimV Offline
                          vadim
                          wrote on last edited by
                          #12

                          I have tried so many times, everything upgraded on source server, did dumps for many times and after import I don't see any data on Cloudron installation. Just blank traccar with demo login.

                          traccar.jpg

                          1 Reply Last reply
                          1
                          • BrutalBirdieB BrutalBirdie

                            You can always directly upload it to the root server and move it into the /app/data/ folder.
                            Just rsync the file to the root server and move it to /home/yellowtent/appdata/{ID}/data/

                            BrutalBirdieB Offline
                            BrutalBirdieB Offline
                            BrutalBirdie
                            Partner
                            wrote on last edited by
                            #13

                            @BrutalBirdie said in Import 5gb sql file into MYSQL database:

                            You can always directly upload it to the root server and move it into the /app/data/ folder.
                            Just rsync the file to the root server and move it to /home/yellowtent/appdata/{ID}/data/

                            @vadim this is one of the most save ways to get the file to the server.
                            Once on the root system you can copy it over and over for trying out different solutions so you don't have to wait for the upload over and over again.

                            Like my work? Consider donating a drink. Cheers!

                            vadimV 1 Reply Last reply
                            2
                            • BrutalBirdieB BrutalBirdie

                              @BrutalBirdie said in Import 5gb sql file into MYSQL database:

                              You can always directly upload it to the root server and move it into the /app/data/ folder.
                              Just rsync the file to the root server and move it to /home/yellowtent/appdata/{ID}/data/

                              @vadim this is one of the most save ways to get the file to the server.
                              Once on the root system you can copy it over and over for trying out different solutions so you don't have to wait for the upload over and over again.

                              vadimV Offline
                              vadimV Offline
                              vadim
                              wrote on last edited by vadim
                              #14

                              @BrutalBirdie I have sql files in /app/data and use this way. But every import is blank. No way to get data from source service.

                              I have Traccar installed for 4 years since very old version. DB works perfect with new version. Could be there a problem with "old" db trying to import to a new installation?

                              1 Reply Last reply
                              1
                              • girishG Offline
                                girishG Offline
                                girish
                                Staff
                                wrote on last edited by
                                #15

                                @vadim so the DB is importing but you have to make sure that you import the database into the right version of traccar. Which version of traccar were you using?

                                Also, after import, have you tried inspecting the tables? Make sure you were not using any table prefixes etc.

                                vadimV 1 Reply Last reply
                                0
                                • girishG girish

                                  @vadim so the DB is importing but you have to make sure that you import the database into the right version of traccar. Which version of traccar were you using?

                                  Also, after import, have you tried inspecting the tables? Make sure you were not using any table prefixes etc.

                                  vadimV Offline
                                  vadimV Offline
                                  vadim
                                  wrote on last edited by vadim
                                  #16

                                  @girish I have version 5.12. But when version is upgraded database and config left the same without changes.
                                  Now I got this situation during import and app not responding after!

                                  Terminal__Traccar_.jpg

                                  1 Reply Last reply
                                  0
                                  • vadimV Offline
                                    vadimV Offline
                                    vadim
                                    wrote on last edited by
                                    #17

                                    Actually I finally got DB, but I also got another problem 🙂
                                    No one user, also my old admin account, not able to login and it is not possible to recover and change password.

                                    1 Reply Last reply
                                    0
                                    • girishG Offline
                                      girishG Offline
                                      girish
                                      Staff
                                      wrote on last edited by
                                      #18

                                      @vadim when you installed traccar, did you install it with Leave user management to the app ? Otherwise, it will use LDAP by default. This flag can only be set at install time. So, start with a fresh installation and select Leave user management to the app at install time.

                                      1 Reply Last reply
                                      1
                                      • vadimV Offline
                                        vadimV Offline
                                        vadim
                                        wrote on last edited by
                                        #19

                                        After a fresh installation with the option "Leave user management to the app" (the same option was used previously), none of the existing users are able to log in. Additionally, it's impossible to change or recover their passwords. I cleared the old database and proceeded with the import. However, after doing so, the system user admin@cloudron.local, who shouldn't be present, remained in the system after the import.

                                        girishG 1 Reply Last reply
                                        0
                                        • vadimV vadim

                                          After a fresh installation with the option "Leave user management to the app" (the same option was used previously), none of the existing users are able to log in. Additionally, it's impossible to change or recover their passwords. I cleared the old database and proceeded with the import. However, after doing so, the system user admin@cloudron.local, who shouldn't be present, remained in the system after the import.

                                          girishG Offline
                                          girishG Offline
                                          girish
                                          Staff
                                          wrote on last edited by
                                          #20

                                          @vadim said in Import 5gb sql file into MYSQL database:

                                          However, after doing so, the system user admin@cloudron.local, who shouldn't be present, remained in the system after the import.

                                          I guess you found this out by checking the users table in traccar ? In Cloudron package, the table name is tc_users . What is the table name of users in your mysql dump?

                                          1 Reply Last reply
                                          0
                                          Reply
                                          • Reply as topic
                                          Log in to reply
                                          • Oldest to Newest
                                          • Newest to Oldest
                                          • Most Votes


                                          • Login

                                          • Don't have an account? Register

                                          • Login or register to search.
                                          • First post
                                            Last post
                                          0
                                          • Categories
                                          • Recent
                                          • Tags
                                          • Popular
                                          • Bookmarks
                                          • Search