Bienvenido! - Willkommen! - Welcome!

Bitácora Técnica de Tux&Cía., Santa Cruz de la Sierra, BO
Bitácora Central: Tux&Cía.
Bitácora de Información Avanzada: Tux&Cía.-Información
May the source be with you!

Thursday, October 7, 2010

Import MySQL dumpfile, SQL datafile into my database

mysqlhotcopy is MUCH superior to mysqldump for MyISAM tables! Read More
"Run theirfilename.sql to generate the necessary tables in the database"
The *.sql file is a text file with SQL statements to be executed by the mysql server.
You can run it through a tool like phpMyAdmin - just open a query window there and copy and paste the content of that text file in there. phpMyAdmin and possibly other frontends will also allow to upload a *.sql file through the web browser. In phpMyAdmin click on the SQL tab.
If you don't have any such front end installed, you can also go to the shell (assuming you're on a Linux box here). If your site is hosted somewhere, you'll need putty (or a similar tool) to connect to the server.
Ftp your *.sql file to the (remote) machine if not done yet.
Execute this command and you'll be fine:
mysql -u -p -------
You can use following commands at your mysql> prompt:
mysql> source file_name.sql
mysql> \. file_name.sql
of course you must either select database in the .sql file or you must 'use' the database first before running a script.

Q. How can I import a MySQL dumpfile into my database?
My old hosting provider gave me data.sql file. I do have access via ssh to server. I'm using CentOS Linux 5 server.
A. You can easily restore or import MySQL data with mysql command itself. First you need to login to your system using ssh or putty (from Windows based system). For example:
Type the following command at the shell prompt:
$ ssh
Now type following command to import sql data file:
$ mysql -u username -p -h localhost data-base-name < data.sql
If you have dedicated database server, replace localhost name with actual server name or IP address:
$ mysql -u username -p -h databasename < data.sqlOR use hostname such as
$ mysql -u username -p -h database-name < data.sql
If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h < data.sql
There are 2 ways to restore your MySQL database from sql dump file.
1st way to restore mysql database from sql dump file is using 
mysql web control panel – phpMyAdmin
- Log into phpMyAdmin.
- Select your preference database on the left database navigation drop down list.

- Click on Import tab on the top.
- Select your sql dumb file at File to import
- Then select your mysql database charset (ex: Latin1, utf-8)
- and click GO and it’s done!
Do not use phpMyAdmin to import or restore your MySQL database if your MySQL database file is large. This is because, phpMyAdmin has limit on total upload size which depend on php setting. Besides, there is also maximum execution time which may cause browser to time out.
The solution to restore large mysql database from sql dump file is using unix/linux shell command.
To restore mysql database from a dump file, just type the command below:
mysql -u myusername -p mydatabasename < mydump_file.sql
Of course you need to replace #myusername# to your database username and #mydatabase# to your target database. and rename #mydump_file.sql# to your dump file file name (Ex: dump.sql) Once you enter the command, the linux/unix shell will prompt you for your database user password, just key in your database password and you are done

No comments: