Import and insert sql gz file into database with putty

Question

I want to insert a sql gz file into my database with SSH  What should I do   For example I have a database from telephone numbers that name is numbers sql gz  what is this type of file and how can I import this file into my database

User · Answer

Creating a Dump File SQL gz on the current server    sudo apt-get install pigz pv    pv   mysqldump --user  lt yourdbuser gt  --password  lt yourdbpassword gt   lt currentexistingdbname gt  --single-transaction --routines --triggers --events --quick --opt -Q --flush-logs --allow-keywords --hex-blob --order-by-primary --skip-comments --skip-disable-keys --skip-add-locks --extended-insert --log-error  var log mysql  lt dbname gt  backup log   pigz  gt   path to folder  lt dbname gt   date    Y  m  d   H  M  sql gz   Optional  Command Arguments for connection  --host 127 0 0 1   localhost   IP Address of the Dump Server --port 3306   Importing the dumpfile created above to a different Server    sudo apt-get install pigz pv    zcat  path to folder  lt dbname gt   date    Y  m  d   H  M  sql gz   pv   mysql --user  lt yourdbuser gt  --password  lt yourdbpassword gt  --database  lt yournewdatabasename gt  --compress --reconnect --unbuffered --net buffer length 1048576 --max allowed packet 1073741824 --connect timeout 36000 --line-numbers --wait --init-command  SET GLOBAL net buffer length 1048576 SET GLOBAL max allowed packet 1073741824 SET FOREIGN KEY CHECKS 0 SET UNIQUE CHECKS   0 SET AUTOCOMMIT   1 FLUSH NO WRITE TO BINLOG QUERY CACHE  STATUS  SLOW LOGS  GENERAL LOGS  ERROR LOGS  ENGINE LOGS  BINARY LOGS  LOGS     Optional  Command Arguments for connection  --host 127 0 0 1   localhost   IP Address of the Import Server --port 3306   mysql   Warning  Using a password on the command line interface can be insecure  1 0GiB 00 06 51  8 05MiB s    lt             The optional software packages are helpful to import your database SQL file faster    with a progress view  pv  Parallel gzip  pigz unpigz  to gzip gunzip files in parallel   for faster zipping of the output

User · Answer

If you have scp then   To move your file from local to remote    scp  home user file gz user ipaddress path to file gz    To move your file from remote to local    scp user ipaddress path to file gz  home user file gz   To export your mysql file without login in to remote system    mysqldump -h ipaddressofremotehost -Pportnumber -u usernameofmysql -p  databasename   gzip -9  gt  databasename sql gz   To import your mysql file withoug login in to remote system    gunzip  lt  databasename sql gz   mysql -h ipaddressofremotehost -Pportnumber -u usernameofmysql -p    Note  Make sure you have network access to the ipaddress of remote host  To check network access    ping ipaddressofremotehost

User · Answer

Login into your server using a shell program like putty   Type in the following command on the command line  zcat DB File Name sql gz   mysql -u username -p Target DB Name   where  DB File Name sql gz   full path of the sql gz file to be imported  username   your mysql username  Target DB Name   database name where you want to import the database  When you hit enter in the command line  it will prompt for password  Enter your MySQL password   You are done

User · Answer

The file is a gzipped  compressed  SQL file  almost certainly a plain text file with  sql as its extension  The first thing you need to do is copy the file to your database server via scp   I think PuTTY s is pscp exe    Copy it to the server via pscp C   gt  pscp exe numbers sql gz user serverhostname  home user   Then SSH into your server and uncompress the file with gunzip  user serverhostname   gunzip numbers sql gz user serverhostname   ls   numbers sql   Finally  import it into your MySQL database using the  lt  input redirection operator   user serverhostname   mysql -u mysqluser -p  lt  numbers sql   If the numbers sql file doesn t create a database but expects one to be present already  you will need to include the database in the command as well   user serverhostname   mysql -u mysqluser -p databasename  lt  numbers sql   If you have the ability to connect directly to your MySQL server from outside  then you could use a local MySQL client instead of having to copy and SSH   In that case  you would just need a utility that can decompress  gz files on Windows   I believe 7zip does so  or you can obtain the gzip gunzip binaries for Windows

User · Answer

For an oneliner  on linux or cygwin   you need to do public key authentication on the host  otherwise ssh will be asking for password     gunzip -c numbers sql gz   ssh user host mysql --user user name --password your password db name   Or do port forwarding and connect to the remote mysql using a  local  connection   ssh -L some port host local mysql port user host  then do the mysql connection on your local machine to localhost some port   The port forwarding will work from putty too  with the similar -L option or you can configure it from the settings panel  somewhere down on the tree

User · Answer

If you ve got many database it import and the dumps is big  I often work with multigigabyte Gzipped dumps    There here a way to do it inside mysql     mkdir databases   cd databases   scp user orgin   sql gz      Here you would just use putty to copy into this dir    mkfifo src   mysql -u root -p Enter password  Welcome to the MySQL monitor   Commands end with   or  g  Your MySQL connection id is 1 Server version  5 5 41-0 Copyright  c  2000  2014  Oracle and or its affiliates  All rights reserved  Oracle is a registered trademark of Oracle Corporation and or its affiliates  Other names may be trademarks of their respective owners Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt  create database db1  mysql gt       zcat  db1 sql gz  gt  src  amp    mysql gt  source src     mysql gt  create database db2  mysql gt       zcat  db2 sql gz  gt  src  amp    mysql gt  source src   The only advantage this has over  zcat db1 sql gz   mysql -u root -p    is that you can easily do multiple without enter the password lots of times

User · Answer

If the mysql dump was a  gz file  you need to gunzip to uncompress the file by typing   gunzip mysqldump sql gz  This will uncompress the  gz file and will just store mysqldump sql in the same location   Type the following command to import sql data file     mysql -u username -p -h localhost test-database  lt  mysqldump sql password

User · Answer

Without a separate step to extract the archive     import gzipped-mysql dump gunzip  lt  DUMP FILE sql gz   mysql --user DB USER --password DB NAME   I use the above snippet to re-import mysqldump-backups  and the following for backing it up     mysqldump and gzip  -9   highest compression  mysqldump --user DB USER --password DB NAME   gzip -9  gt  DUMP FILE sql gz

[mysql] Import and insert sql.gz file into database with putty

Examples related to mysql

Examples related to import

Examples related to gzip

Examples related to putty