title: Mysql Server Crash While copying single database using phpmyadmin?
body: <p>Im starting a project in Cloud Azure. My machine is a 3.5GB RAM , 30Gb size. 4G os Swap just in case.  Application we run uses mysqlnd server 5.6.28, and Apache/2.4.7 (Ubuntu) (preFork).</p>
  
  <p>My database size is around 1GB. </p>
  
  <p><strong>Problem:</strong>
  yesterday i was trying to copy a whole database using phpmyadmin to another name, and all the server starts to slowdown ( the mysql) using around 100% CPU and making my beta users requests to mysql start to lag like hell, and i had to restart the mysql server. </p>
  
  <p>I dont understand what's wrong. I tried to optimize the my.cnf file using percona wizard. </p>
  
  <p>I only have around 3 beta users on that machine, and the number of requests per second is around 15 as my apache server-status says. Can't a 3.5GB , where the allocated to db size is 2GB , accomplish this copy database task!? A simple command...</p>
  
  <pre><code>Parent Server Config. Generation: 5
  Parent Server MPM Generation: 4
  Server uptime: 12 days 19 hours 47 minutes 6 seconds
  Server load: 0.25 0.26 0.27
  Total accesses: 598877 - Total Traffic: 2.2 GB
  CPU Usage: u697.64 s206.04 cu0 cs0 - .0816% CPU load
  .54 requests/sec - 2180 B/second - 4033 B/request
  10 requests currently being processed, 39 idle workers
  .___.WW_W__________________W....W......W..W..._______W_C_W______
  __..............................................................
  ................................................................
  ................................................................
  ................................................................
  ................................................................
  ................................................................
  ....................................................
  </code></pre>
  
  <p>this is my.cnf:</p>
  
  <pre><code># The MySQL database server configuration file.
  
  [client]
  port        = 3306
  socket      = /var/run/mysqld/mysqld.sock
  
  # Here is entries for some specific programs
  # The following values assume you have at least 32M ram
  
  # This was formally known as [safe_mysqld]. Both versions are currently parsed.
  [mysqld_safe]
  socket      = /var/run/mysqld/mysqld.sock
  nice        = 0
  
  [mysqld]
  #
  # * Basic Settings
  #
  user        = mysql
  pid-file    = /var/run/mysqld/mysqld.pid
  socket      = /var/run/mysqld/mysqld.sock
  port        = 3306
  basedir     = /usr
  datadir     = /var/lib/mysql
  tmpdir      = /tmp
  lc-messages-dir = /usr/share/mysql
  skip-external-locking
  skip-name-resolve
  #skip-locking
  
  #
  # Instead of skip-networking the default is now to listen only on
  # localhost which is more compatible and is not less secure.
  
  #disabled to allow remote access
  #bind-address       = 127.0.0.1
  bind-address = 10.0.0.4
  
  
  #
  # * Fine Tuning
  #
  key_buffer      = 16M
  max_allowed_packet  = 16M
  thread_stack        = 256K
  thread_cache_size       = 32 
  
  #thread_cache_size=0
  host_cache_size=0
  
  
  # This replaces the startup script and checks MyISAM tables if needed
  # the first time they are touched
  myisam-recover         = BACKUP
  max_connections        = 800
  #table_cache            = 64
  thread_concurrency     = 100
  join_buffer_size=128
  
  #
  # * Query Cache Configuration
  #
  query_cache_limit   = 128M
  #query_cache_size   = 64M
  #query_cache_size   = 128M
  
  
  #
  # * Logging and Replication
  #
  # Both location gets rotated by the cronjob.
  # Be aware that this log type is a performance killer.
  # As of 5.1 you can enable the log at runtime!
  #general_log_file        = /var/log/mysql/mysql.log
  #general_log             = 1
  #
  # Error log - should be very few entries.
  
  log_error = /var/log/mysql/error.log
  
  
  # Here you can see queries with especially long duration
  #log_slow_queries   = /var/log/mysql/mysql-slow.log
  slow-query-log=1
  long_query_time = 10
  #log-queries-not-using-indexes
  
  #server-id      = 1
  #log_bin            = /var/log/mysql/mysql-bin.log
  expire_logs_days    = 7
  max_binlog_size         = 60M
  #binlog_do_db       = include_database_name
  #binlog_ignore_db   = include_database_name
  
  #
  # * InnoDB
  #
  
  innodb_buffer_pool_size=2G
  #innodb_log_file_size = 2G
  
  
  
   #innodb_buffer_pool_instances=1
  # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  # Read the manual for more InnoDB related options. There are many!
  #
  # * Security Features
  #
  # Read the manual, too, if you want chroot!
  # chroot = /var/lib/mysql/
  #
  # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
  #
  # ssl-ca=/etc/mysql/cacert.pem
  # ssl-cert=/etc/mysql/server-cert.pem
  # ssl-key=/etc/mysql/server-key.pem
  
  
  
  [mysqldump]
  quick
  quote-names
  max_allowed_packet  = 16M
  
  [mysql]
  #no-auto-rehash # faster start of mysql but no tab completition
  
  [isamchk]
  key_buffer      = 16M
  
  #
  # * IMPORTANT: Additional settings that can override those from this file!
  #   The files must end with '.cnf', otherwise they'll be ignored.
  #
  !includedir /etc/mysql/conf.d/
  </code></pre>
  
  <p>And on that conf.d folder i have also this one (the percona wizard):</p>
  
  <pre><code>#
  # The MySQL 5.6 database server configuration file.
  #
  # This custom MySQL 5.6 specific configuration file
  # adds on top of the existing default my.cnf file at
  # - /etc/mysql/my.cnf.
  # 
  # Please add any extra MySQL 5.6 options in this file
  # for sake of clarity.
  #
  # You may uncomment any existing option to enable it
  #
  # sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  
  
  # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
  
  
  [mysql]
  
  # CLIENT #
  #port                           = 3306
  #socket                         = /var/lib/mysql/mysql.sock
  
  [mysqld]
  
  # GENERAL #
  user                           = mysql
  default-storage-engine         = InnoDB
  #socket                         = /var/lib/mysql/mysql.sock
  #pid-file                       = /var/lib/mysql/mysql.pid
  
  # MyISAM #
  key-buffer-size                = 32M
  myisam-recover                 = FORCE,BACKUP
  
  # SAFETY #
  max-allowed-packet             = 16M
  max-connect-errors             = 1000000
  sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
  innodb                         = FORCE
  innodb-strict-mode             = 1
  
  # DATA STORAGE #
  datadir                        = /var/lib/mysql/
  
  # BINARY LOGGING #
  log-bin                        = /var/lib/mysql/mysql-bin
  expire-logs-days               = 14
  sync-binlog                    = 1
  
  # CACHES AND LIMITS #
  tmp-table-size                 = 32M
  max-heap-table-size            = 32M
  query-cache-type               = 0
  query-cache-size               = 0
  max-connections                = 500
  thread-cache-size              = 50
  open-files-limit               = 65535
  table-definition-cache         = 4096
  table-open-cache               = 4096
  
  # INNODB #
  innodb-flush-method            = O_DIRECT
  innodb-log-files-in-group      = 2
  innodb-log-file-size           = 128M
  innodb-flush-log-at-trx-commit = 1
  innodb-file-per-table          = 1
  innodb-buffer-pool-size        = 2G
  
  # LOGGING #
  log-error                      = /var/lib/mysql/mysql-error.log
  log-queries-not-using-indexes  = 1
  slow-query-log                 = 1
  slow-query-log-file            = /var/lib/mysql/mysql-slow.log
  </code></pre>
  
  <p><strong>This is the error log, of a "cron" backup, that also, creates this kind of thing.</strong></p>
  
  <pre><code>2016-04-25 06:42:13 32387 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
  2016-04-25 06:42:13 32387 [Note] Plugin 'FEDERATED' is disabled.
  2016-04-25 06:42:13 32387 [ERROR] Function 'innodb' already exists
  2016-04-25 06:42:13 32387 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
  2016-04-25 06:42:13 32387 [ERROR] Function 'federated' already exists
  2016-04-25 06:42:13 32387 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
  2016-04-25 06:42:13 32387 [ERROR] Function 'blackhole' already exists
  2016-04-25 06:42:13 32387 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
  2016-04-25 06:42:13 32387 [ERROR] Function 'archive' already exists
  2016-04-25 06:42:13 32387 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
  2016-04-25 06:42:13 32387 [Note] InnoDB: Using atomics to ref count buffer pool pages
  2016-04-25 06:42:13 32387 [Note] InnoDB: The InnoDB memory heap is disabled
  2016-04-25 06:42:13 32387 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
  2016-04-25 06:42:13 32387 [Note] InnoDB: Memory barrier is not used
  2016-04-25 06:42:13 32387 [Note] InnoDB: Compressed tables use zlib 1.2.8
  2016-04-25 06:42:13 32387 [Note] InnoDB: Using Linux native AIO
  2016-04-25 06:42:13 32387 [Note] InnoDB: Not using CPU crc32 instructions
  2016-04-25 06:42:13 32387 [Note] InnoDB: Initializing buffer pool, size = 2.0G
  2016-04-25 06:42:13 32387 [Note] InnoDB: Completed initialization of buffer pool
  2016-04-25 06:42:14 32387 [Note] InnoDB: Highest supported file format is Barracuda.
  2016-04-25 06:42:14 32387 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2666162556
  2016-04-25 06:42:14 32387 [Note] InnoDB: Database was not shutdown normally!
  2016-04-25 06:42:14 32387 [Note] InnoDB: Starting crash recovery.
  2016-04-25 06:42:14 32387 [Note] InnoDB: Reading tablespace information from the .ibd files...
  2016-04-25 06:42:22 32387 [Note] InnoDB: Restoring possible half-written data pages 
  2016-04-25 06:42:22 32387 [Note] InnoDB: from the doublewrite buffer...
  InnoDB: Doing recovery: scanned up to log sequence number 2666163324
  2016-04-25 19:01:42 35591 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
  2016-04-25 19:01:42 35591 [Note] Plugin 'FEDERATED' is disabled.
  2016-04-25 19:01:42 35591 [ERROR] Function 'innodb' already exists
  2016-04-25 19:01:42 35591 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
  2016-04-25 19:01:42 35591 [ERROR] Function 'federated' already exists
  2016-04-25 19:01:42 35591 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
  2016-04-25 19:01:42 35591 [ERROR] Function 'blackhole' already exists
  2016-04-25 19:01:42 35591 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
  2016-04-25 19:01:42 35591 [ERROR] Function 'archive' already exists
  2016-04-25 19:01:42 35591 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
  2016-04-25 19:01:42 35591 [Note] InnoDB: Using atomics to ref count buffer pool pages
  2016-04-25 19:01:42 35591 [Note] InnoDB: The InnoDB memory heap is disabled
  2016-04-25 19:01:42 35591 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
  2016-04-25 19:01:42 35591 [Note] InnoDB: Memory barrier is not used
  2016-04-25 19:01:42 35591 [Note] InnoDB: Compressed tables use zlib 1.2.8
  2016-04-25 19:01:42 35591 [Note] InnoDB: Using Linux native AIO
  2016-04-25 19:01:42 35591 [Note] InnoDB: Not using CPU crc32 instructions
  2016-04-25 19:01:42 35591 [Note] InnoDB: Initializing buffer pool, size = 2.0G
  2016-04-25 19:01:42 35591 [Note] InnoDB: Completed initialization of buffer pool
  2016-04-25 19:01:42 35591 [Note] InnoDB: Highest supported file format is Barracuda.
  2016-04-25 19:01:42 35591 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2666162556
  2016-04-25 19:01:42 35591 [Note] InnoDB: Database was not shutdown normally!
  2016-04-25 19:01:42 35591 [Note] InnoDB: Starting crash recovery.
  2016-04-25 19:01:42 35591 [Note] InnoDB: Reading tablespace information from the .ibd files...
  2016-04-25 19:01:42 35591 [Note] InnoDB: Restoring possible half-written data pages 
  2016-04-25 19:01:42 35591 [Note] InnoDB: from the doublewrite buffer...
  InnoDB: Doing recovery: scanned up to log sequence number 2666163324
  2016-04-25 19:01:42 35591 [Note] InnoDB: Starting an apply batch of log records to the database...
  InnoDB: Progress in percent: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
  InnoDB: Apply batch completed
  2016-04-25 19:01:43 35591 [Note] InnoDB: 128 rollback segment(s) are active.
  2016-04-25 19:01:43 35591 [Note] InnoDB: Waiting for purge to start
  2016-04-25 19:01:43 35591 [Note] InnoDB: 5.6.28 started; log sequence number 2666163324
  2016-04-25 19:01:43 35591 [Note] Server hostname (bind-address): '10.0.0.4'; port: 3306
  2016-04-25 19:01:43 35591 [Note]   - '10.0.0.4' resolves to '10.0.0.4';
  2016-04-25 19:01:43 35591 [Note] Server socket created on IP: '10.0.0.4'.
  2016-04-25 19:01:43 35591 [Warning] 'user' entry 'triconsulte@triconsulte' ignored in --skip-name-resolve mode.
  2016-04-25 19:01:43 35591 [Warning] 'proxies_priv' entry '@ root@beautivmazure' ignored in --skip-name-resolve mode.
  2016-04-25 19:01:43 35591 [Note] Event Scheduler: Loaded 0 events
  2016-04-25 19:01:43 35591 [Note] /usr/sbin/mysqld: ready for connections.
  Version: '5.6.28-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
  2016-04-25 19:01:44 35591 [ERROR] /usr/sbin/mysqld: Table './beautipt_clean/bea_agenda' is marked as crashed and should be repaired
  2016-04-25 19:01:44 35591 [Warning] Checking table:   './beautipt_clean/bea_agenda'
  2016-04-25 19:01:45 35591 [ERROR] /usr/sbin/mysqld: Table './beautipt_clean/cms_adminlog' is marked as crashed and should be repaired
  2016-04-25 19:01:45 35591 [Warning] Checking table:   './beautipt_clean/cms_adminlog'
  2016-04-25 19:01:45 35591 [ERROR] /usr/sbin/mysqld: Table './beautipt_clean/cms_email_logs' is marked as crashed and should be repaired
  2016-04-25 19:01:45 35591 [Warning] Checking table:   './beautipt_clean/cms_email_logs'
  2016-04-25 19:01:46 35591 [ERROR] /usr/sbin/mysqld: Table './beautipt_clean/cms_siteprefs' is marked as crashed and should be repaired
  2016-04-25 19:01:46 35591 [Warning] Checking table:   './beautipt_clean/cms_siteprefs'
  2016-04-25 19:01:46 35591 [ERROR] /usr/sbin/mysqld: Table './beautipt_clean/ger_apilog' is marked as crashed and should be repaired
  2016-04-25 19:01:46 35591 [Warning] Checking table:   './beautipt_clean/ger_apilog'
  </code></pre>