Learn about Centmin Mod LEMP Stack today
Become a Member

MariaDB Issue Importing Database

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Jake, Mar 5, 2015.

  1. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Hey all,
    I am really stuck on this one. I am trying to import a database into phpmyadmin, (also tried ssh) and it hangs. Meaning nothing happens, I have even tried a really small database and I cannot get it to import. I am not even sure where to start... Any help would be greatly appreciated!
     
  2. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    You should ideally use mysql client on SSH command line to import and backup databases. You can see note at bottom of page at phpmyadmin.sh Centmin Mod Addon

    what's the database sql file size ? server specs ?
     
  3. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    I have tried via SSH, it just sits there and doesnt do anything, Server specs wise its a 2.8 quad core dedicated server with 32 gigs of ram and a tb of hd.The size of the file is a mere 118MB, so its not a large database
     
  4. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    you sure it's a valid working complete .sql file you are importing ? how was it backed up/exported to sql file ? via phpmyadmin or mysqldump ? could be phpmyadmin gave you an incomplete sql backup dump of your database.

    try mysqldump backup and restore via SSH
     
  5. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Good Morning,
    I did try that, whenever I do that it just hangs, nothing happens. :/. I tried both via phpmyadmin and mysqldump.
     
  6. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    list your exact commands you use to mysqldump back and mysql restore via SSH ?
     
  7. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    mysql -u tower -p thecpforum > mytest.sql
    mysqldump -u jake -p thecpforum > mytest.sql
     
  8. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Here is a error,
    SQL query:


    /*!40000 ALTER TABLE `archived_import_log` DISABLE KEYS */;



    MySQL said:

    #2006 - MySQL server has gone away
     
  9. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
  10. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Code:
    [mysqld]
    character-set-server=utf8
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    #bind-address=127.0.0.1
    # optimized my.cnf for MariaDB 5.5.x
    # by eva2000
    # vbtechsupport.com
    
    tmpdir=/home/mysqltmp
    
    skip-innodb
    #skip-federated
    #skip-pbxt
    #skip-pbxt_statistics
    #skip-archive
    #skip-name-resolve
    #old_passwords
    back_log = 75
    max_connections = 300
    key_buffer_size = 32M
    myisam_sort_buffer_size = 32M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 64K
    read_buffer_size = 64K
    sort_buffer_size = 128K
    table_definition_cache = 4096
    table_open_cache = 2048
    thread_cache_size = 64
    wait_timeout = 120
    connect_timeout = 10
    tmp_table_size = 32M
    max_heap_table_size = 32M
    max_allowed_packet = 32M
    max_seeks_for_key = 1000
    group_concat_max_len = 1024
    max_length_for_sort_data = 1024
    net_buffer_length = 16384
    max_connect_errors = 100000
    concurrent_insert = 2
    read_rnd_buffer_size = 256K
    bulk_insert_buffer_size = 8M
    query_cache_limit = 512K
    query_cache_size = 16M
    query_cache_type = 1
    query_cache_min_res_unit = 2K
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM
    
    log_warnings=1
    slow_query_log=0
    long_query_time=1
    slow_query_log_file=/var/lib/mysql/slowq.log
    log-error=/var/log/mysqld.log
    
    # innodb settings
    #innodb_extra_rsegments = 4
    innodb_purge_threads=1
    innodb_doublewrite = 1
    
    innodb_file_per_table = 1
    innodb_open_files = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 48M
    innodb_additional_mem_pool_size = 32M
    
    innodb_log_files_in_group = 2
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 0
    innodb_lock_wait_timeout=50
    #innodb_flush_method = O_DIRECT
    innodb_support_xa=1
    
    # 200 * # DISKS
    innodb_io_capacity = 100
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    
    # mariadb settings
    [mariadb]
    #thread-handling = pool-of-threads
    #thread-pool-size= 20
    #mysql --port=3307 --protocol=tcp
    #extra-port=3307
    #extra-max-connections=1
    
    userstat = 0
    key_cache_segments = 1
    aria_group_commit = none
    aria_group_commit_interval = 0
    aria_log_file_size = 32M
    aria_log_purge_type = immediate
    aria_pagecache_buffer_size = 8M
    aria_sort_buffer_size = 8M
    
    [mariadb-5.5]
    #ignore_db_dirs=
    query_cache_strip_comments=0
    
    innodb_read_ahead = linear
    innodb_adaptive_flushing_method = estimate
    innodb_flush_neighbor_pages = 1
    innodb_stats_update_need_lock = 0
    innodb_log_block_size = 512
    
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    [mysqld_safe]
    socket=/var/lib/mysql/mysql.sock
    log-error=/var/log/mysqld.log
    #nice = -5
    open-files-limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 32M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 16M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    I have not changed anything, and this is a brand new server :(
     
  11. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    optimal timeouts depend on server specs, current server resource loads, mysql data set size as well as how it's imported via phpmyadmin or direct ssh (later is faster)..

    you'll have to play with wait_timeout that is optimal for your usage.. Centmin Mod is provided as is, so optimisation is left to the end user :)

    there're your problem for import you have reversed the direction of import should be <

    Code:
    mysql -u tower -p thecpforum < mytest.sql
     
  12. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Awesome,
    I will keep trying, but is it normal for a database so small to take so long like this? Like I just tried again and changed it to 500, and it just sits there and spins in phpmyadmin, (if I try it that way)
     
  13. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    it ain't normal that is for sure for phpmyadmin, but then again it's reason why phpmyadmin isn't reliable as mysqldump and mysql restore via ssh
     
    • Like Like x 1
  14. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Ugh still isnt working,
    Do you have any other ideas?
     
  15. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    what type of sql backup is this for ? or is it something else ? like a sql file with commands to alter table properties given the alter like in your above error ?

    if it's an sql file to alter tables of an existing database, and that database is large then it can take a while to import due to alter table commands without further optimisation of mysql settings in my.cnf (you're on your own for that) and thus can take longer than wait_timeout value and hence times out.
     
  16. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    I am moving servers, but there is only 234 tables. unzipped its only at 135 mb
     
  17. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    try a blank empty database backup and restore to see if there's other problems

    in ssh

    create new database

    Code:
    mysqladmin create newdb
    backup

    Code:
    mysqldump newdb > newdb.sql 
    create new database newdb2

    Code:
    mysqladmin create newdb2
    restore newdb backup into newdb2

    Code:
    mysql newdb2 < newdb.sql
     
  18. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    Ok, I did that but what data am I supposed to add into them?
     
  19. eva2000

    eva2000 Administrator Staff Member

    29,034
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,783
    Local Time:
    11:02 PM
    Nginx 1.13.x
    MariaDB 5.5
    nothing, it's just to confirm that back and restore commands do work themselves

    it's your sql backup that is having problems

    if you have access to original database/server, redo a new backup via mysqldump to sql file and try importing the new sql file via ssh mysql client command line.
     
  20. Jake

    Jake Member

    76
    10
    8
    Feb 3, 2015
    Ratings:
    +11
    Local Time:
    11:02 PM
    NA
    Maria DB 5.5
    It appeared to work fine,
    I have attempted to do that as well. With no luck, I also still have the other server that I am attempting to migrate from, and it appears to be super slow as well. (Which this is a completely different provider) Its gotta be a incorrect setting somewhere,
    Heres what I get from the new server when I run this command,
    Code:
    ./mysqlreport
    MariaDB 5.5.42-MariaDB      uptime 0 0:6:8      Thu Mar  5 18:52:12 2015
    
    __ Key _________________________________________________________________
    Buffer used         0 of    5.86G   %Used:   0.00
      Current       1.10G              %Usage:  18.75
    Write hit       0.00%
    Read hit        0.00%
    
    __ Questions ___________________________________________________________
    Total             229       0.6/s
      Com_            128       0.3/s  %Total:  55.90
      DMS              79       0.2/s           34.50
      COM_QUIT         18       0.0/s            7.86
      +Unknown          4       0.0/s            1.75
    Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log: OFF
    DMS                79       0.2/s           34.50
      SELECT           69       0.2/s           30.13         87.34
      INSERT           10       0.0/s            4.37         12.66
      REPLACE           0         0/s            0.00          0.00
      DELETE            0         0/s            0.00          0.00
      UPDATE            0         0/s            0.00          0.00
    Com_              128       0.3/s           55.90
      set_option       54       0.1/s           23.58
      show_tables      27       0.1/s           11.79
      change_db        12       0.0/s            5.24
    
    __ Rows ________________________________________________________________
    Rows            3.29k       8.9/s
      Using idx         0         0/s  %Index:   0.00
    Rows/question   14.36
    
    __ SELECT and Sort _____________________________________________________
    Scan               86       0.2/s %SELECT: 124.64
    Range               0         0/s            0.00
    Full join           0         0/s            0.00
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan          11       0.0/s
    Sort range          0         0/s
    Sort mrg pass       0         0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   17.12k of   16.00M  %Usage:   0.10
    Block Fragmnt 100.00%
    Hits                0         0/s
    Inserts             1       0.0/s
    Insrt:Prune       1:1         0/s
    Hit:Insert     0.00:1
    
    __ Table Locks _________________________________________________________
    Waited              0         0/s  %Total:   0.00
    Immediate          46       0.1/s
    
    __ Tables ______________________________________________________________
    Open               33 of 524288    %Cache:   0.01
    Opened              7       0.0/s
    
    __ Connections _________________________________________________________
    Max used            2 of    300      %Max:   0.67
    Total              20       0.1/s
    
    __ Created Temp ________________________________________________________
    Disk table          2       0.0/s   %Disk:   2.15
    Table              93       0.3/s    Size:   2.0G
    File                0         0/s
    
    __ Threads _____________________________________________________________
    Running             1 of      2
    Created             2       0.0/s
    Slow                0         0/s
    Cached              0 of     64      %Hit:     90
    
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects            1       0.0/s
    
    __ Bytes _______________________________________________________________
    Sent           59.06k     160.5/s
    Received      198.25k     538.7/s
    
    __ Aria Pagecache ______________________________________________________
    Buffer used         0 of    8.00M   %Used:   0.00
      Current     328.00k              %Usage:   4.00
    Write hit       0.00%
    Read hit        0.00%