Import a large sql dump file to a MySQL database from command line

How to import a very large SQL dump file (6 Gb) to a MySQL database using windows command line. If you are using linux it is the same. The process is the following:

Open a command prompt (or shell in Linux) with administrative privilleges

Connect to a mysql instance using command line


# mysql -h 192.168.1.1 --port=3306 -u root -p

if you are in localhost you do not need host and port


# mysql -u root -p

Or if plesk,

# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

You are now in mysql shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files


mysql> set global net_buffer_length=1000000;

Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.

mysql> set global max_allowed_packet=1000000000;

Disable foreign key checking to avoid delays,errors and unwanted behaviour

mysql> SET foreign_key_checks = 0;
mysql> SET UNIQUE_CHECKS = 0;
mysql> SET AUTOCOMMIT = 0;

Import your sql dump file

mysql> use db_name;
mysql> source backup-file.sql;

Remember to enable foreign key checks when procedure is complete!


 mysql> SET foreign_key_checks = 1;
 mysql> SET UNIQUE_CHECKS = 1;
 mysql> SET AUTOCOMMIT = 1;

If you are in Linux you can create a Bash script which will do the dirty job and write to stdout start and end time of import:


  #!/bin/sh 

  # store start date to a variable
  imeron=`date`

  echo "Import started: OK"
  dumpfile="/home/bob/bobiras.sql"

  ddl="set names utf8; "
  ddl="$ddl set global net_buffer_length=1000000;"
  ddl="$ddl set global max_allowed_packet=1000000000; "
  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 jetdb; "
  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 -proot -e "$ddl"

  # store end date to a variable
  imeron2=`date`

  echo "Start import:$imeron"
  echo "End import:$imeron2"

Other solutions:
Big Dump
http://www.ozerov.de/bigdump/ seems good

Split Files
http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/

Mariadb default logs location & –log-error

I am using mariadb and I to investigate some issue I wanted to check the logs. To my surprise, log file is not generated for mariadb.

I suspect this cannot be the case so I am doubting my search skills.

MariaDB [(none)]> show variables like 'log_error'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.00 sec)

I have added the entry in my.cnf still above field is coming to be empty.

[root@cslcodev11-oem ~]# cat /etc/my.cnf
[mysqld]
!includedir /etc/mysqld/conf.d
datadir=/mnt/mgmt/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mnt/mgmt/var/lib/mysql_tmp
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
### TRT-3229 #####
sync_binlog=1
innodb_flush_method=O_DIRECT
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
###################
innodb_file_per_table=1
innodb_log_buffer_size = 8M
table_open_cache=256
max_heap_table_size=256M
### TRT-4685 ###
max_connections=500
################
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@cslcodev11-oem ~]# 

So, I want to know do we have any default location where these logs are getting generated, if the path cannot be read from config files.

INFO: Reference: https://mariadb.com/kb/en/mariadb/error-log/

On the MariaDB Knowledge base page for the error-log there is a paragraph that states.

systemd has its own logging system, and Linux distributions running systemd may log errors there instead. To view the systemd logs, use:

# journalctl -u mariadb.

This answers the question because –log-error would be an option that could be added to MY_SPECIAL.conf file and journalctl is where mariadb logs errors by default on a systemd system.

MySQL shows warning: Using unique option is deprecated and will be removed in a future release

Error when exporting a dump in plesk databases (See:https://docs.plesk.com/en-US/onyx/reseller-guide/website-management/website-databases/exporting-and-importing-database-dumps.69538/):

Warning: Using unique option prefix database instead of databases is deprecated and will be removed in a future release. Please use the full name instead.

Cause

key_buffer and myisam-recover are deprecated in Mysql 5.5. myisam-recover option is renamed as of MySQL 5.5.3 to myisam-recover-options .
Resolution

1. Log in to Plesk server via SSH

2. Correct /etc/my.cnf or /etc/mysql/my.cnf as below:
Change pass to password
See – https://serverfault.com/questions/670648/mysqldump-complains-about-chosen-databases-regardless-of-command-line

Disable ONLY_FULL_GROUP_BY in Mysql on Ubuntu 16

Recently I ran a command in mysql that resulted in this error:

mysql: [ERROR] unknown variable 'sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

So I had to add a line to the mysql config file. In Ubuntu 16:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line under mysqld


[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart mysql:

# systemctl restart mysql

MariaDB cnf files

Here is the original /etc/my.cnf file for a default mariadb installation:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Location of other .cnf files you can use:

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

How do you calculate mysql max_connections variable

The basic formulas are:

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM – Global Buffers) / Thread Buffers

To get the list of buffers and their values:


SHOW VARIABLES LIKE '%buffer%';

Here’s a list of the buffers and whether they’re Global or Thread:

Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| aria_pagecache_buffer_size            | 134217728 |
| aria_sort_buffer_size                 | 134217728 |
| bulk_insert_buffer_size               | 8388608   |
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
| innodb_change_buffering               | all       |
| innodb_log_buffer_size                | 8388608   |
| join_buffer_size                      | 131072    |
| join_buffer_space_limit               | 2097152   |
| key_buffer_size                       | 16777216  |
| mrr_buffer_size                       | 262144    |
| myisam_sort_buffer_size               | 8388608   |
| net_buffer_length                     | 8192      |
| preload_buffer_size                   | 32768     |
| read_buffer_size                      | 262144    |
| read_rnd_buffer_size                  | 524288    |
| sort_buffer_size                      | 524288    |
| sql_buffer_result                     | OFF       |
+---------------------------------------+-----------+

Lets find out the RAM:

# free -b
              total        used        free      shared  buff/cache   available
Mem:     3975184384   978608128  1691045888     9445376  1305530368  2661937152

Lets get our data together.

RAM = 3975184384
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_length, query_cache_size
or, from above…
Global Buffers: 16777216 + 134217728 + 8388608 + 0 + 8192 + 0 = 159391744

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
or, from above…
Thread Buffers: 524288 + 8388608 + 262144 + 131072 + 524288 + 0 = 9830400

With this information, the following is the calculation:
max_connections = (Available RAM – Global Buffers) / Thread Buffers
max_connections = (3975184384 – 159391744) / 9830400

So the formula shows 378 Max Connections on this machine

Test info with mysqltuner

Log into your server with a root or sudo user via SSH.
Download MySQLTuner by executing the following command:


wget -O mysqltuner.pl https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl --no-check-certificate

Give the script 775 permissions:


chmod 775 mysqltuner.pl

Run the script with the following command:


perl mysqltuner.pl

Resources:
Handy Calculator Download: https://journeyontux.wordpress.com/2011/12/22/calculate-number-of-connections-for-mysql-server/
Another Calculator: http://www.mysqlcalculator.com/

List sizes of MySQL databases

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don’t represent the actual database sizes?

Run from a mysql root console:

mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-------------+
| Database name | Size (MB) |
+--------------------+-------------+
| information_schema | 0.00878906 |
| mysql | 0.62860394 |
| performance_schema | 0.00000000 |
| roundcube | 0.46875000 |
+--------------------+-------------+
8 rows in set (1.21 sec)

			

How do I disable MySQL strict mode on the server?

Please follow the under mentioned instructions to turn off the MySQL strict mode. Make the following changes in the “my.ini/my.cnf”:

Look for the following line:

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change it to:


sql_mode=""



Restart the MySQL service.

or

Look for the following line , this line will set MySQL strict mode


sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

To disable MySQL strict mode, you can change the above line as follow:


sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER,,NO_ENGINE_SUBSTITUTION"

Restart the MySQL service.

You may be able to run an SQL query within your database management tool such as phpMyAdmin which can normally be found from your web hosting control panel:


SET @@global.sql_mode= '';

Test

Run the following:

#mysql -uroot -p -e 'select @@GLOBAL.sql_mode;'
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+