How to export and import a sql file from command line with options

Question

Not Duplicate  looking for some feature have phpmyadmin during export in command line  I want to export and import a  sql file to and from a MySQL database from command line   Is there any command to export  sql file in MySQL  Then how do I import it   When doing the export import  there may be constraints like enable disable foreign key check or export only table structure   Can we set those options with mysqldump   some example of Options

User · Answer

Dump an entire database to a file   mysqldump -u USERNAME -p password DATABASENAME  gt  FILENAME sql

User · Answer

Well you can use below command to export      mysqldump --databases --user root --password your db name     export into db sql   and the generated file will be available in the same directory where you had ran this command   Now login to mysql using command      mysql -u username  -p    then use  source  command with the file path

User · Answer

mysqldump will not dump database events  triggers and routines unless explicitly stated when dumping individual databases   mysqldump -uuser -p db name --events --triggers --routines  gt  db name sql

User · Answer

If you re already running the SQL shell  you can use the source command to import data   use databasename  source data sql

User · Answer

You can use this script to export or import any database from terminal given at this link  https   github com Ridhwanluthra mysql import export script blob master mysql import export script sh  echo -e  Welcome to the import export database utility n  echo -e  the default location of mysqldump file is   opt lampp bin mysqldump n  echo -e  the default location of mysql file is   opt lampp bin mysql n  read -p  Would like you like to change the default location  y n     location change read -p  Please enter your username    u name read -p  Would you like to import or export a database   import export     action echo  mysqldump location  opt lampp bin mysqldump mysql location  opt lampp bin mysql  if     action      export     then     if     location change      y     then         read -p  Give the location of mysqldump that you want to use    mysqldump location         echo     else         echo -e  Using default location of mysqldump n      fi     read -p  Give the name of database in which you would like to export    db name     read -p  Give the complete path of the  sql file in which you would like to export the database    sql file      mysqldump location -u  u name -p  db name  gt   sql file elif     action      import     then     if     location change      y     then         read -p  Give the location of mysql that you want to use    mysql location         echo     else         echo -e  Using default location of mysql n      fi     read -p  Give the complete path of the  sql file you would like to import    sql file     read -p  Give the name of database in which to import this file    db name      mysql location -u  u name -p  db name  lt   sql file else     echo  please select a valid command  fi

User · Answer

Type the following command to import sql data file     mysql -u username -p -h localhost DATA-BASE-NAME  lt  data sql   In this example  import  data sql  file into  blog  database using vivek as username     mysql -u vivek -p -h localhost blog  lt  data sql   If you have a dedicated database server  replace localhost hostname with with actual server name or IP address as follows     mysql -u username -p -h 202 54 1 10 databasename  lt  data sql   To export a database  use the following   mysqldump -u username -p databasename  gt  filename sql   Note the  lt  and  gt  symbols in each case

User · Answer

since I have no enough reputation to comment after the highest post  so I add here   use     on linux platform to save disk space   thx  Hariboo  add events triggers routints parameters  mysqldump -x -u  uname  -p pass   -C --databases db name  --events --triggers --routines   sed -e  s DEFINER                          awk    if  index  0  GTID PURGED      getline  while  length  0   gt  0    getline      else   print  0         grep -iv  set       trickle -u 10240 mysql -u username -p -h localhost DATA-BASE-NAME   some issues tips   Error        not exist when using LOCK TABLES    --lock-all-tables -x   this parameter is to keep data consistency because some transaction may still be working like schedule    also you need check and confirm  grant all privileges on     to root     identified by  Passwd     ERROR 2006  HY000  at line 866  MySQL server has gone away mysqldump  Got errno 32 on write    set this values big enough on destination mysql server  like  max allowed packet 1024 1024 20   use compress parameter  -C    use trickle to limit network bandwidth while write data to destination server   ERROR 1419  HY000  at line 32730  You do not have the SUPER privilege and binary logging is enabled  you might want to use the less safe log bin trust function creators variable     set SET GLOBAL log bin trust function creators   1    or use super user import data   ERROR 1227  42000  at line 138  Access denied  you need  at least one of  the SUPER privilege s  for this operation mysqldump  Got errno 32 on write    add sed awk to avoid some privilege issues   hope this help

User · Answer

Try      mysqldump databaseExample  gt  file sql

[mysql] How to export and import a .sql file from command line with options?

Examples related to mysql

Examples related to command-line

Examples related to command-line-arguments

Examples related to sqlcommand