How to import and export a database via SSH
- Category : MySQL Question
- Posted on : May 18, 2021
- Views : 1,515
- By : HostSEO
You can easily import/export your database using phpMyAdmin menu in cPanel. To do so, follow the instructions here. It allows processing only the databases that do not exceed 1 GB. If your database is bigger, you will need to use SSH commands.
Shell access is disabled on Shared servers by default, so if shell access was not enabled for your account previously, first of all, enable it with the help of this article.
The next step is to log into your account via SSH. This tutorial will guide you through this process.
Note that before performing an export or import, you will need to assign a database to a user. You can do it in cPanel > MySQL Databases menu > Add a User to a Database option:
Make sure that you enable All Privileges option:
You can find more instructions on database creation and assigning it to a user here.
In order to import a database, use the following command:
mysql -u dbusername -pdbpassword dbname < /path/to/file/file.sql
You can also use:
mysql -u dbusername -p'dbpassword' dbname < /path/to/file/file.sql
or
mysql -u dbusername -p dbname < /path/to/file/file.sql
In order to export, use this one:
mysqldump -u dbusername -pdbpassword dbname > /path/to/file/file.sql
The variable in italics are the following:
- dbusername: the name of a database user assigned to this database (it should be in the format of CPANELUSERNAME_DATABASEUSER), or your cPanel username
- dbpassword: the database user password (if you use a database user as a username) or your cPanel password (if you use your cPanel username as a username)
NOTE: No space between -p key and the password if you enter it in the command.
- dbname: the name of your database you are importing to or exporting from. It should be created in your cPanel. The format is the following: CPANELUSERNAME_DATABASENAME
- /path/to/file/file.sql: the path to the mysql dump that you are importing to (then a new file will be created) or exporting from your cPanel account. Here you can use both relative and absolute path formats. If you are in the folder with the file, you can just type the file name with the extension. With the absolute path, for example, it will be the following: /home/CPANEL_USERNAME/public_html/database.sql
If you do not receive any error or notification after pressing Enter, it means that the command was performed successfully.
Let us provide with an example.
We will use the following settings:
- dbusername: nctests_user (as a database user), nctests (as a cPanel username)
- dbpassword: 123qwe
- dbname: nctests_database
- path to the file: /home/nctests/public_html/databases/db_file.sql
After defining all the variables, the command will look the following way:
Database import
mysql -u nctests_user -p123qwe nctests_database < /home/nctests/public_html/databases/db_file.sql
NOTE: You can also use the following command:
mysql -u nctests_user -p nctests_database < /home/nctests/public_html/databases/db_file.sql
This way, you need to enter the password in the next line:
Database export
Â
mysqldump -u nctests_user -p123qwe nctests_database > /home/nctests/public_html/databases/db_file.sql
NOTE: You can also use the following command:
mysqldump -u nctests_user -p nctests_database > /home/nctests/public_html/databases/db_file.sql
This way, you need to enter the password in the next line:
That's it!
             Â
                     Need any help? Contact our HelpDesk
Categories
- cPanel Question 47
- cPanel Software Management 29
- cPanel Tutorials 13
- Development 29
- Domain 13
- General 19
- Linux Helpline (Easy Guide) 156
- Marketing 47
- MySQL Question 13
- News 2
- PHP Configuration 14
- SEO 4
- SEO 42
- Server Administration 84
- SSL Installation 54
- Tips and Tricks 24
- VPS 3
- Web Hosting 44
- Website Security 22
- WHM questions 13
- WordPress 148
Subscribe Now
10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!Archive Calendar
Sat | Sun | Mon | Tue | Wed | Thu | Fri |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 | 31 |
Recent Articles
-
Posted on : Sep 17
-
Posted on : Sep 10
-
Posted on : Aug 04
-
Posted on : Apr 01
Tags
- ts
- myisam
- vpn
- sql
- process
- kill
- tweak
- server load
- attack
- ddos mitigation
- Knowledge
- layer 7
- ddos
- webmail
- DMARC
- Development
- nginx
- seo vpn
- Hosting Security
- wireguard
- innodb
- exim
- smtp relay
- smtp
- VPS Hosting
- cpulimit
- Plesk
- Comparison
- cpu
- encryption
- WHM
- xampp
- sysstat
- optimize
- cheap vpn
- php-fpm
- mariadb
- apache
- Small Business
- Error
- Networking
- VPS
- SSD Hosting
- Link Building
- centos
- DNS
- optimization
- ubuntu