Want to subscribe to topics you're interested in?
Become a Member

MariaDB Any tips on Importing large Database SQL File?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by rdan, Feb 10, 2016.

  1. rdan

    rdan Premium Member Premium Member

    4,256
    1,035
    113
    May 25, 2014
    Ratings:
    +1,487
    Local Time:
    9:55 AM
    Mainline
    10.2
    I am importing 17GB database on Linode server, and somewhat it fails on large tables like post table.
    Any tips to overcome this?
     
  2. rdan

    rdan Premium Member Premium Member

    4,256
    1,035
    113
    May 25, 2014
    Ratings:
    +1,487
    Local Time:
    9:55 AM
    Mainline
    10.2
  3. rdan

    rdan Premium Member Premium Member

    4,256
    1,035
    113
    May 25, 2014
    Ratings:
    +1,487
    Local Time:
    9:55 AM
    Mainline
    10.2
    So far this is what I have:
    PHP:
    #!/bin/sh
    # store start date to a variable
    imeron=`date`
    echo 
    "Import started: OK"

    dumpfile="path_to_db.sql"
    databasename="database_name_here"

    ddl="set names utf8; "
    ddl="$ddl set global net_buffer_length=1048576;"
    ddl="$ddl set global max_allowed_packet=1073741824; "
    ddl="$ddl SET foreign_key_checks = 0; "
    ddl="$ddl SET UNIQUE_CHECKS = 0; "
    ddl="$ddl SET AUTOCOMMIT = 0; "
    # if your dump file does not create a database, select one
    ddl="$ddl USE $databasename; "
    ddl="$ddl source $dumpfile; "
    ddl="$ddl SET foreign_key_checks = 1; "
    ddl="$ddl SET UNIQUE_CHECKS = 1; "
    ddl="$ddl SET AUTOCOMMIT = 1; "
    ddl="$ddl COMMIT ; "
    echo "Import started: OK"
    time mysql -h 127.0.0.1 -u root -p"your_root_pass_here" -"$ddl"
    # store end date to a variable
    imeron2=`date`
    echo 
    "Start import:$imeron"
    echo "End import:$imeron2"
     
..