Welcome to Centmin Mod Community
Register Now

MariaDB Any tips on Importing large Database SQL File?

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

  1. RoldanLT

    RoldanLT Well-Known Member

    3,828
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    6:06 AM
    1.11
    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. RoldanLT

    RoldanLT Well-Known Member

    3,828
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    6:06 AM
    1.11
    10.2
  3. RoldanLT

    RoldanLT Well-Known Member

    3,828
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    6:06 AM
    1.11
    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"