Link to home
Start Free TrialLog in
Avatar of lulon
lulonFlag for Spain

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Ioannis Anifantakis
Ioannis Anifantakis
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dave Baldwin
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.
Avatar of lulon

ASKER

yes, that is what I need...

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

But what user do I need to create and with that privileges?
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.
I meant to include this link: https://help.ubuntu.com/  Click on your version to see what's there.
Avatar of lulon

ASKER

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
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.
Avatar of lulon

ASKER

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 ?
Avatar of lulon

ASKER

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?
Avatar of lulon

ASKER

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?
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lulon

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lulon

ASKER

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!
Avatar of lulon

ASKER

Good tips, thanks!
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

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
>>> 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

>>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

Avatar of lulon

ASKER

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.
Avatar of lulon

ASKER

This is what I have now...

mysql> show create table <table>;

...
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 |
@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.

:)
@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.
Avatar of lulon

ASKER

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;
ALTER TABLE `myScema`.`myTable` CHARACTER SET utf8 COLLATE utf8_general_ci;
should work for you especially if you can empty your current table data
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.
Avatar of lulon

ASKER

Hi ioannisa,

I don't have that file. Is my.cnf the same one for Ubuntu?

for windows
C:\mysql\my.ini

for linux
/etc/mysql/my.cnf

Avatar of lulon

ASKER

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
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

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.
Avatar of lulon

ASKER

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'
#
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%";
(run the SET commands I gave you above as if you would run any MySQL statement)
Avatar of lulon

ASKER

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>
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 :)
Avatar of lulon

ASKER

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>
Avatar of lulon

ASKER

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