Raju Ginni

wordpress tutorials seo hosting etc

You are here: Home / MySQL Tutorial (create,connect database, update tables, export etc) Documentation & TIPS / mysqldump import /export mysql database command line, phpmyadmin, Cpanel, mysql workbench, xamp

mysqldump import /export mysql database command line, phpmyadmin, Cpanel, mysql workbench, xamp




To export all databases into a dump:


mysqldump –all-databases > all_databases_export.sql


with password


mysqldump -u root -p –all-databases > /var/www/html/all.sql




Export single database


mysqldump -u root -p spro > /var/www/html/spro3.sql




importing single database from a dump


To import one of these mysql databases from the dump into a database:










mysql –one-database database_name < all_databases_export.sql






mysql -u root -p –one-database destdbname < alldatabases.sql




mysql -u root -ppassword –one-database uanm < D:\servver\nov2k19\alldb.sql








export database mysql command line ubuntu


mysqldump -u root -p database_name > /var/www/html/databasename.sql

Enter Mysql password to export


ex: mysqldump -u root -p raaz > /var/www/html/razz.sql


root@localhost:~# mysqldump -u root -p naukri > /var/www/html/sresult.sql

Enter password:

root@localhost:~#












import all databases in mysql dump




mysql -u root -p –add-drop-database mysql < D:\servver\nov2k19\alldb.sql






or


mysql -u root -p < alldb.sql


Importing single database


 


root@murali:/var/www/html# mysql -u root -p

Enter password:


mysql> create database naukri;

Query OK, 1 row affected (0.00 sec)


mysql -u root -p naukri < /var/www/html/srt.sql


ERROR 1046 (3D000) at line 22: No database selected




mysql export all database except  some databases (mysql,performance schema, sys)




DATABASES_TO_EXCLUDE


All you need to do is put the databases you do not want mysqldump’d into DATABASES_TO_EXCLUDE


DATABASES_TO_EXCLUDE=”db1 db2 db3″


EXCLUSION_LIST=”‘information_schema’,’mysql'”for DB in `echo “${DATABASES_TO_EXCLUDE}”`


do


    EXCLUSION_LIST=”${EXCLUSION_LIST},’${DB}'”


done


SQLSTMT=”SELECT schema_name FROM information_schema.schemata”


SQLSTMT=”${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})”


MYSQLDUMP_DATABASES=”–databases”for DB in `mysql -ANe”${SQLSTMT}”`


do


    MYSQLDUMP_DATABASES=”${MYSQLDUMP_DATABASES} ${DB}”


done


MYSQLDUMP_OPTIONS=”–routines –triggers”


mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql






mysqldump.exe tutorials




mysqldump only used for exporting single, multiple or all


shell> mysqldump [options] db_name [tbl_name …]


shell> mysqldump [options] –databases db_name …


shell> mysqldump [options] –all-databases


–ingonre-database not working.


simple mysql commands useful for export




 




mysql -u root -p


show databases;


db1


db2


bd3


use db3;


show tables; more mysql commands cheat sheet


mysqldump -u root -p uan > /var/www/html/uan.sql


cd /var/www/

zip -r bak.zip /var/www/html/*


//after download & upload to new server & creating databases with same names as uan, uid




 how to import large sql file in phpmyadmin?




phpmyadmin import-database


Increase max file upload size in php.ini (in dedicated shared serer,


Phpmyadmin max upload size limit 8MB-to 40MB,


Upload once script timeout again reupload same file it will process further apply the same thing until you get all the databases. (Not recommended).


 


Uploading Mysql to Phpmyadmin library (dedicated server)




Based on web server configuration there are limits,


Php Max-Upload-Size


php Max Execution Time


Nginx – Fastcgi Read timeout & Max Client Body size




We can configure these at our own serer but in shared serer (upload to root directory contact support to import)






i am trying upload databases from phpmyadmin.


hostgator and bluehost have 50Mb limit for sql file.


time i split my databases using


Importing Databases using sql dump splitter.


 and uploads parts one by one. it worked fine last time.


but when try to upload getting connection timeout. tried to Upload database from.


Editing mysql File with Text Editor


upto 50 MB or 500MB notepad work fine. 1GB Large text editors (EMeditor) useful, not text iewers.


edit text file, search and identify the Database start & end  copy and save as new database.


Useful to replace Coalition. or strings in whole database.




 




Export & Import Using MySQL workbench Server Admin




SIze: 80MB


very Useful for DBA or frequently manipulated databases.


COnnect to Server download all databases or single.


Export & restore options (checkout Mysql Workbench tutorial here)


mysql workbench database connect


 


Importing Large databases in CPanel




Importing large databases through TCP/http Hae many limits like timeouts.
Script timeout phpmyadmin again reupload so it will continue. same thing applies when you import by cpanel.

Bestway in shared environment

Upload through FTP.
Enable SSH & Import through command (Difficulty level High)
Ask support to import (easier you get what you paid)






I have found 4 methods to uploaded but those also not working because of shared server limits I think.


    

  1. using PHPMyAdmin.
    2. 

  2. from the Cpanel control panel
    3. 

  3. by splitting DB to small files
    4. 

  4. MySQL workbench these 4 options not worked for me.
    5. 

  5. using Putty ssh now I am going to try this. (simple & quick way worked later)
    6. 



Really I don’t Know what to do every time searches on google new results and new methods.

Every time I get a problem like this I need to search research on google. sometimes may not found the perfect solution and many times not able to understand and do that. because some of them require technical knowledge.


Totally disappointed registered hosting plan 2 days still not uploaded database files which are 150 MB and 194 Mb. just created a thread on web hosting talk forum.


Also, I tried to compare all methods at one place below still confused. HoIe i think may of the newbies like me facing.  they may understand my problem.


Ways to Import /Export database Large ad small fewer than 50 Mb


    

  1. Using Php Admin
    2. 

  2. Using cpanel backup options
    3. 

  3. SQL dump splitter by large database into Parts
    4. 

  4. Php script to import large database files
    5. 

  5. Mysql workbench
    6. 

  6. Using SSH putty commands Very easy.
    7. 



Import exporting database bu SSH Command line Process




    

  1. Enable SSH Access
    2. 

  2. Generate SSH Key
    3. 

  3. Login with putty by entering below details
    4. 

  4. 

    Username: Cpanel user name
    

    Password: Your cPanel password
    

    Hostname: your website.com or IP
    

    Port: 22
    

    5. 

  5. 

    Sometimes you have to whitelist your ip to gain Jail Shell Access only from your IP.
    

    6. 

  6. Upload your database to your cpanel Home Directory by gzip compressing to reduce database size.
    7. 

  7. Create database and username and password to access.
    8. 

  8. Run Import or export commands on Putty client as shown below.
    9. 





Finally I’ve Uploaded my database to phpmyadmin?


    

  • 

    uploaded to root folder
    

    • 

  • 

    logged in putty by entering the shared ip, port 2222, cpanel username and password.
    

    • 

  • 

    and i entered this command
    

    • 

  • 

    mysql -u root -p db-name < backup.sql
    
ex u database username
    
p password
    
d
    
database< path of sql file
    
mysql -u seobackl_final -pRg22tg22 seobackl_bmarks < /home2/seobackl/public_html/DBS/2.sql.
    

    password no space after P.
    

    this is also got some error connection timed out. also updated a ticket with hostgator. they uploaded. they uploaded but database size bigger than original sql file.
    
i need to repair db using cpanel>>databases>>checkDB>Repair DB.
    • 



Importing database In shared Server?


The Only way is contact support they will do it for you in case file size is larger than 50 MB.


Because SSH / Shell access not works on major shared providers. use SQL dump splitter.


Check Phpmyadmin max upload SIZe limit.


How to use ssh in the shared server to import database from home directory to phpmyAdmin?


    

  • To access SSH, download WinSCP or PuTTY. Enter your IP address and port 2222; login with your cPanel username and password.
    • 



ssh access you can try this command:

mysql -u {database username} -p -h localhost -D {database name} < YourBackupName.sql

You will be prompted for the password for that database user.


upload to root folder


and try this command


mysql -u<user> -p<password> <database name> < /path/to/dump.sql




Try to import it from mysql console as per the taste of your OS.

mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql path}


mysqldump -u username -ppassword –all-databases > dump.sql


import export database using ssh


How to import and export a MySQL database using ssh.



To export a database use:



mysqldump -u root -p db-name > backup.sql


To import a database:


mysql -u root -p db-name < backup.sql

Ex:mysqldump –u seobackl –pg93s9wYjC2 seobackl_333 > /home2/seobackl/public_html/DBS/manabadi_atmarks.sql


mysql -u [username] -p [database_name] < [dumpfilename.sql]


Example: mysql -u seobackl_final -pRg22tg22 seobackl_bmarks < /home2/seobackl/public_html/DBS/2.sql




    

  • 

      

    •  https://help.1and1.com/hosting-c37630/databases-c85147/mysql-database-c37730/import-a-mysql-database-using-ssh-a704884.html
      • 

    

    • 



    

  • 

      

    • http://www.itworld.com/it-management/359857/3-ways-import-and-export-mysql-database
      • 

    

    • 



    

  • 

      

    • http://www.inmotionhosting.com/support/website/database-setup/upload-large-database-over-50-mb
      • 

    

    • 



    

  • 

      

    • http://support.hostgator.in/articles/hosting-guide/lets-get-started/how-do-i-get-and-use-ssh-access
      • 

    

    • 



    

  • 

      

    • https://www.namecheap.com/support/knowledgebase/article.aspx/9184/90/how-can-i-import-and-export-a-database-using-ssh-command
      • 

    

    • 



    

  • 

    connected database from standard tcp connection database username and
    

    • 





 mysql import database command line


SQLDumpSplitter 2




Export & Import Mysqldump in windows cmd Xampp /wamp




imort large sql file in local host xamp wamp bitnami


Edit php.ini & Apache CLinebody max size & timeout limit  for phpmyadmin or use command line below.




first set the path variable


location mysql.exe




Windows path variable




set path=C:\path\to\mysql\executable




C:\wamp\bin\mysql\mysql\mysql5.6.12\bin.




C:\Bitnami\nginxstack-1.18.0-4\mysql\bin




set path=C:\Bitnami\nginxstack-1.18.0-4\mysql\bin




C:\Bitnami\nginxstack-1.18.0-4\mysql\bin\mysql.exe




C:\Users\HP>set path=C:\Bitnami\nginxstack-1.18.0-4\mysql\bin




C:\Users\HP>mysql


ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO)




C:\Users\HP>






C:\Users\HP>set path=C:\Bitnami\nginxstack-1.18.0-4\mysql\bin




C:\Users\HP>mysql


ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO)




C:\Users\HP>mysql -u root -pRajuginne23


mysql: [Warning] Using a password on the command line interface can be insecure.


Welcome to the MySQL monitor.  Commands end with ; or \g.


Your MySQL connection id is 74


Server version: 5.7.30 MySQL Community Server (GPL)




Copyright (c) 2000, 2020, 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.








Note: Zip version file not import to mysql.


ubuntu, debian, centos, fedora, redhat, windows etc

Note: DB Name < Import, > for export.