how to send a sql file to MySql

Hello,

I'm using MySql under Ubuntu.

If you have a file "file.sql" like:

insert into table values (.....);

How do you send that file to Mysql in order to insert the new row into the table from the OS?
Which user do you need to use?

Thanks in advance.
lulonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis AnifantakisSoftware EngineerCommented:
LOAD DATA INFILE
is what you want if I got your question right...
More info regarding it in the links bellow, and in the code bellow you see how its used (refered from 2nd link)

http://dev.mysql.com/doc/refman/5.1/en/load-data.html
http://www.wbluhm.com/MySQLTut.html
  mysql> LOAD DATA INFILE '/users/science/wbluhm/mysql/books.data'
      -> INTO TABLE books
      -> FIELDS TERMINATED BY '\t'
      -> LINES TERMINATED BY '\n'
      -> IGNORE 0 LINES
      -> (title, author, publisher, topic, comment, price);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
You have to have a MySQL username and password which is separate from your Ubuntu logon.  I use phpmyadmin in my webbrowser for working with my databases on Ubuntu.  You can also use the command line options in the terminal.  If everything is setup properly, you can type 'mysql -ppassword -uyouruser dbname < file.sql'.  If the database and/or tables already exist, then there must be statements in file.sql to 'drop' them because it won't just overwrite them.  Once you get it working, it's fairly easy.  But the details have to be correct.
0
lulonAuthor Commented:
yes, that is what I need...

'mysql -ppassword -uyouruser dbname < file.sql'

But what user do I need to create and with that privileges?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Dave BaldwinFixer of ProblemsCommented:
First, what are you trying to do?  What version of Ubuntu do you and is MySQL and the other software you need installed?  If you have the 'desktop' version of Ubuntu, MySQL is not installed by default but you can get it along with Apache and PHP.
0
Dave BaldwinFixer of ProblemsCommented:
I meant to include this link: https://help.ubuntu.com/  Click on your version to see what's there.
0
lulonAuthor Commented:
Hi,

I already have MySql installed and it is working fine. I'm also using phpmyadmin but I was wondering how can I run a script from the OS to, for instance, drop all the tables of my database and how to run a script to create the database and the tables in the database.

I know how to do it with phpmyadmin but what about commands?

Something like this makes sense:
# mysql -ppassword -uyouruser dbname < file.sql'

But what user do I have to create and with what privileges?

Right now if I do this:

# mysql -u root -p<password>

it works fine and it connects to MySQL
0
Dave BaldwinFixer of ProblemsCommented:
While 'root' can work with all the databases normally, each database has it's own privileges.  You normally don't want everyone else using 'root' to access the database, you give them their own username and password and give them user privileges on the database that they are allowed to use.  It's normal to give them both 'localhost' and '%' (anyhost) privileges with the same password.

'file.sql' is only going to work if it's done right.  You need to get a book that will go step by step and also tell you what the error messages mean and how to fix problems.  There are also websites that give you a lot of it.  The MySQL manual is complete of course but that makes it ok to research but hard to read thru.

Note that all the methods I've seen for controlling a MySQL database thru websites with PHP and ASP are basically generating command lines to pass to the mysql program.  So it's good to go thru the command line operation once or twice so you can see what's going on.
0
lulonAuthor Commented:
I have used this way and it works fine, but is it the best and fastest one?

# mysql -u root -p<password> < file.sql

Isn't it better to do it with LOAD DATA INFILE ?
0
lulonAuthor Commented:
I also realized one thing...

In the file.sql I have characters like 'á' or this onw 'ñ'.

If I run it with myphpadmin it stores them correctly in the database but if I run it through the command line, it stores them with strange characters. How can I avoid that?
0
lulonAuthor Commented:
As for DATA INFILE, this file is in charge of inserting some data in some tables of my tables. It does not affect only one table, so is it possible to use it?
0
cjl7freelance for hireCommented:
Hi,

You need to set the correct character set when connecting to the database (utf8 I guess).

Like so

mysql --default-character-set=utf8 -u root -p<password> < file.sql

//jonas
0
Ioannis AnifantakisSoftware EngineerCommented:
>>As for DATA INFILE, this file is in charge of inserting some data in some tables of my tables. It does not affect only one table, so is it possible to use it?

You cannot tell what part of the file belongs to what table.  Its LOAD DATA INFILE per file and table.
0
lulonAuthor Commented:
thanks to both of you, I will try the character set now...

so I guess I cannot use LOAD DATA INFILE if I want it to be done in only one file... Do you see any disadvantage in using the other way? What about if the file that contains data to insert is very big? Could it be slow? What is the way MySql perform the insert when doing this:

# mysql -u root -p<password> < file.sql
0
Steve BinkCommented:
You do not want to use LOAD DATA INFILE for this job.  That statement is used only for raw data imports.  What you have is a bunch of SQL commands in a text file, so you need to import it with command line redirection as DaveBaldwin demonstrated in #31971621.  The commands in the file should already specify which tables to use for which commands.

Likewise, cjl7 identified the character set issue in #32344430, but his command line options may not go far enough.  You have several different levels of character sets to worry about in MySQL, and you need to make sure they are all set appropriately.  You can probably identify which character set you need by looking at the CREATE TABLE statements in the file.  If not, you will need to experiment a little to figure out which one it is.  You will not be able to import the data correctly unless you use the correct character set.  The most common ones are utf8 for international or _latin1 for "standard" English.  Once you identify the character set, make sure you set the connection and client character sets to be the same.  You can do this by using the "SET NAMES" command:

http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html

For example, to make sure you set the utf8 character set for your import, insert this command at the top of the text file:

SET NAMES 'utf8';

I know this applies to MySQL v5.1, but there may be some differences with other versions.  If you run into issues, check the manual for your specific version.  
0
Steve BinkCommented:
Two more things:

1) When using command line redirection (i.e., mysql < file.sql), MySQL will read the file and execute it line by line just as if you were typing the commands yourself.  

2) If you put the password on the command line, then it will also show up in your bash history, and possibly some system log files.  Anyone viewing those files will then be able to log into your database service as the root user.  Eliminate the password and let MySQL prompt you for it:

mysql -u root -p < file.sql
0
lulonAuthor Commented:
that worked!

SET NAMES 'utf8';

in the beginning of the file.sql, and:

# mysql -u root -p<password> < file.sql

last doubt...

I created my database and the tables without specifying anything regarding characters. Are they configured with utf8 by default? how can I check that?

Thank you!
0
lulonAuthor Commented:
Good tips, thanks!
0
Ioannis AnifantakisSoftware EngineerCommented:
LOAD DATA INFILE was answer of how to make insert statements from a file.
this is the reverse of
SELECT * INTO OUTFILE FROM xxxx WHERE xxxx

MySQL Allows you to either export data into files that can later be used to import data.

Infact this OUTFILE/INFILE technique is awesome when it comes to exporting something from a MySQL Schema to import it to some other schema for example like my elections database, where I export the location data into the new election DB and I only initialize the votes and party stuff so I begin filling them up with data.

I present you bellow some of my Delphi code using mysql statements like that so that you see as a demo their use.


--------------------

As for UTF8, yes if you are using a database that can hold data other than the classic latin, you are adviced to setup your database to hold UTF8 charset.  Infact you can also set this up as default charset when you setup the server.  And you should (in my oppinion) always use that and only that, because you never know when you will have to store some name or any kind of information that belongs to another character set.

// This is a demo of use between OUTFILE and INFILE commands
// hope it helps you lulon :)

// you must delete a file before exporting it, or export will fail
DeleteFile(currentDrive+'/outfile.sql');

// Now export to the outfile.sql the contents of
// table electionlevels in the schema elections
mainQR.SQL.Text:='SELECT * INTO OUTFILE '''+currentDrive+'/outfile.sql'' FROM elections.electionlevels';
mainQR.Execute;

// now in the schema I have created that is elecXXX (where XXX number of schema id as I have it in main election DB)
// put the data of table electionlevels of schema elections that I have exported to my outfile
// into the table electionlevels of the schema elecXXX
mainQR.SQL.Text:='LOAD DATA INFILE '''+currentDrive+'/outfile.sql'' REPLACE INTO TABLE elec'+DataSet.FieldByName('idelections').AsString+'.electionlevels';
mainQR.Execute;


// -----  Same thing like above for table electiontypes

DeleteFile(currentDrive+'/outfile.sql');

mainQR.SQL.Text:='SELECT * INTO OUTFILE '''+currentDrive+'/outfile.sql'' FROM elections.electiontypes';
mainQR.Execute;

mainQR.SQL.Text:='LOAD DATA INFILE '''+currentDrive+'/outfile.sql'' REPLACE INTO TABLE elec'+DataSet.FieldByName('idelections').AsString+'.electiontypes';
mainQR.Execute;

Open in new window

0
Ioannis AnifantakisSoftware EngineerCommented:
To change the charset for a table do that
ALTER TABLE `myScema`.`myTable` CHARACTER SET utf8 COLLATE utf8_general_ci;

Since MySQL 5.1 you can also change the charset for a column only! So if you want to keep your table intact and change only a column you can do it like this
ALTER TABLE `myScema`.`myTable` MODIFY COLUMN `myColumn` INT(10) UNSIGNED CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

To setup your default characterset you must go into "my.ini" at your mysql folder and set this parameter
default-character-set=utf8
0
Steve BinkCommented:
>>> I created my database and the tables without specifying anything regarding characters. Are they configured with utf8 by default? how can I check that?

In MySQL, execute the command "SHOW VARIABLES LIKE '%character%';".  You will see a result similar to what is shown below.  Also, see here for information about the varied levels of character set configurations:

http://dev.mysql.com/doc/refman/5.1/en/charset-syntax.html

I *believe* the default installation on Ubuntu is latin1, but that may just be because I'm in America, and all Americans know that *everyone* speaks only English..  </sarcasm>  Whatever your default sets are, you will see them reflected in the output of that command.  Remember that the tables you just created can specify their own, as can columns, and even an individual data point if it uses explicit declarations.  You can see that information with "SHOW CREATE TABLE" or "DESCRIBE TABLE".

@ioannisa:

>>> LOAD DATA INFILE was answer of how to make insert statements from a file.
>>> this is the reverse of
>>> SELECT * INTO OUTFILE FROM xxxx WHERE xxxx

I totally agree.  The problem with it in this context was the file being used appears to be a standard mysqldump, not a raw data file.  The original question states the OP has full SQL commands in the file.  In other circumstances, your suggestion would have worked perfectly.
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |   <---- database default
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |   <---- server default
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Open in new window

0
Ioannis AnifantakisSoftware EngineerCommented:
>>I created my database and the tables without specifying anything regarding characters. Are they configured with utf8 by default? how can I check that?

CHARSET should be shown when you ask to see your "create table".

Comment above lets you setup these defaults.
This comment shows you through create table if they are applied
show create table cmp;

// YOU WILL GET THIS AS RESULT

CREATE TABLE `cmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL DEFAULT '',
  `descr` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Open in new window

0
lulonAuthor Commented:
I have exactly the same if I execute this: mysql> show variables like '%character%';
and I'm in Spain ;)

Although this works fine now, I guess I should create my tables with utf-8, shouldn't I ?


About the LOAD_DATA_INFILE, exactly, I dumped the info of the tables to a file using phpmyadmin, and now I have all of the inserts of the tables in a file.
0
lulonAuthor Commented:
This is what I have now...

mysql> show create table <table>;

...
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 |
0
Ioannis AnifantakisSoftware EngineerCommented:
@routinet:

initially I read "insert into table values (.....);" from lulon.
So I assumed that he had the ".........." as raw data available in his file.

After his first reply that DaveBaldwin he said that 'mysql -ppassword -uyouruser dbname < file.sql' was what he wanted I quit my replies.

Later lulon completed this answer
"As for DATA INFILE, this file is in charge of inserting some data in some tables of my tables. It does not affect only one table, so is it possible to use it?"

So I simply got into the procedure of explaining him how it all works and how he can make us of the infile/outfile logic.

:)
0
Steve BinkCommented:
@ioannisa: no problems.  I wasn't calling you out or anything.  I just wanted to make the difference clear for lulon.  Your reply was technically correct...it just didn't apply to this particular scenario.  

@lulon:

You can see the commands to change a table's character set in ioannisa's comment #32344816.  Remember, though, that your result is showing the default character set for the table, not necessarily the column.  If you have already loaded the data in the table with the wrong character set, you'll need to change the column to a blob, then back to a (var)char with the proper character set.  If you have doubts, it may be wise to remove the table entirely and re-import to ensure you get the proper sets everywhere.
0
lulonAuthor Commented:
no problem @routinet, this is not a production database, I can delete the tables and create them again...

so this is the way to set it, right?

ALTER TABLE `myScema`.`myTable` CHARACTER SET utf8 COLLATE utf8_general_ci;
0
Ioannis AnifantakisSoftware EngineerCommented:
ALTER TABLE `myScema`.`myTable` CHARACTER SET utf8 COLLATE utf8_general_ci;
should work for you especially if you can empty your current table data
0
Ioannis AnifantakisSoftware EngineerCommented:
Also as I mentioned in my earlier post, it would be good to setup your database server to give default Character set the UTF8 to any table you build by changing your "my.ini".

That way you won't have to alter every table you make so that it works with UTF8 charset.

Also don't forget that if you change the "my.ini" file you will have to restart your mysql server in order to read the initialization file.
0
lulonAuthor Commented:
Hi ioannisa,

I don't have that file. Is my.cnf the same one for Ubuntu?
0
Ioannis AnifantakisSoftware EngineerCommented:

for windows
C:\mysql\my.ini

for linux
/etc/mysql/my.cnf

0
lulonAuthor Commented:
ok, thanks.

then how do you have it configure? because I don't have anything in that file regarding characters...

Thanks again for the help
0
Ioannis AnifantakisSoftware EngineerCommented:
inside [mysql] section of the my.cfg you have to add "default-character-set=utf8"

[mysql]
default-character-set=utf8

(Restart your mysql server so that it reads the my.cfg)

Finally you have to run the bellow check to see if its turned to utf8.
show variables like "%character%";
show variables like "%collation%";

Open in new window

0
Steve BinkCommented:
You will not see the entry there until you add one.  The server has preconfigured defaults on installation.  To edit the configuration files, it is as simple as adding a line under the correct section of your ini/cnf file, like putting "character-set-server=utf8" under [mysqld].  See here:

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

Here's another handy link that lists all the options available, and where you can set them:

http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html

Notice that some of them are set for [mysqld], some are for [mysql] (or [client]), and some are just system variables that you change during your session or on object creation.  You're looking for the character-set-* variables.  Since you want to set the default to utf8, you use character-set-server under [mysqld], then make sure your client sets -client, -connection, and -results after connecting, as you did with the SET NAMES command.  

As mentioned, be sure to restart your mysqld service after changing the file.
0
lulonAuthor Commented:
I've tried both:

This: default-character-set=utf8
and this: character-set-server=utf8


With default-character-set=utf8, it works but character-set-server does not change:

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0,01 sec)

mysql>



With character-set-server=utf8, after restarting MySql, when I tryto connect:

# mysql -u root -p<password>
mysql: unknown variable 'character-set-server=utf8'
#
0
Ioannis AnifantakisSoftware EngineerCommented:
try this

SET character_set_database = utf8;
SET character_set_server = utf8;

They should fix your values in "database" and "server" to utf8;
Tell me if it fixes your entries.  They should all be utf8 (except of filesystem which is binary)


Finally tell me what results you get when you run
show variables like "%collation%";
0
Ioannis AnifantakisSoftware EngineerCommented:
(run the SET commands I gave you above as if you would run any MySQL statement)
0
lulonAuthor Commented:
mysql> SET character_set_database = utf8;
Query OK, 0 rows affected (0,00 sec)

mysql> SET character_set_server = utf8;
Query OK, 0 rows affected (0,00 sec)

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0,00 sec)

mysql> show variables like "%collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0,00 sec)

mysql>
0
Ioannis AnifantakisSoftware EngineerCommented:
It looks ok! :)

Just to make sure, restart your mysql server and run the
show variables like '%character%';
show variables like "%collation%";

again to see that your change is permanent and ok.

You are done :)
0
lulonAuthor Commented:
no... :(

it's not permanent...

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0,00 sec)

mysql>
mysql>
mysql>
mysql> show variables like "%collation%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0,00 sec)

mysql>
0
lulonAuthor Commented:
thanks to both of you ;)

I created this question  ID: 26108117 in order to continue with the character issue. Have a look if you have the chance please
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.