MYSQL :: Running MySQL Using A Dos Batch File

Running Mysql Commands In A Batch File
I am trying to automate a mysql command to upload data everymorning on a windows server. I have created a .bat file and entered the commands to login to mysql but when it gets to the mysql prompt it fails to complete the rest of the commands.

cd
c:
cdmysqlin
mysql
TRUNCATE TABLE blah
LOAD DATA INFILE 'blah.txt' INTO blah
QUIT
exit

Is this possible?

Running A Sql Batch Script From The Linux Command
s this possible?

I want to write a command script which i want to put into a cron job which will download a db backup from cpanel. then i want to unzip it, and run the sql file, so keeping the database on the mirror machine up to date.

So what i have is:

#get the database
wget -v --http-user user --http-pass pass https://www.site.com/path/to/cpanel/backup
#move to backups folder
date=`date -I`
mv -fv ./mysqldump.gz /home/user/dbbackup/$date.gz
now what i also want to do is:

mysql -uuser -ppassword
u database
set foreign_key_checks = 0;
. mysqldumpfile.sql
set foreign_key_checks =1;
exit
is it possible to do that last bit from the command-line? as far as i know, a shell script can't run commands to mysql?

Batch File
I am new to running batch files. Is there a way to loop through a results set in a batch file? I want to be able to create tables using table names that are stored in a table. I had hoped that I could query the table to retrieve all tablenames, then loop through this result set, creating a table with each tablename returned. (Table definitions will be the same for all tables created. Only the tablenames are different.)

Cant Execute Batch File
Hi new to this but pulled out nearly all my hair with this.
I got the test database open and I want to automate creating the menagerie table ( as in the tutorial ) so I download the textfile save it to c:/ and execute the command source c:/cr_pets_tbl.txt all I get is unable to open file error 22.
I have tried creating my own text file ( windows, notepad ) just containing the following
use test;
create table pets(name varchar(10),species varchar(8),born date);

simple as that then type in the consoe source c:/pets.txt;
unable to open file error 22 or sometimes error 2
tried enclosing filename in " and ' tried source= filename
tried . instead of source but nothing works.
using manual insert command or load data local infile works but not this, HELP.
Malcolm K.

Batch File For Backup
Can I make complete Backup of a database (same as from MySQL Administrator) using batch file?

Batch FIle To Create Database
I am trying to create a batch file to create a database and populate it with create table statements. I can't figure out why it will not create the database and load the tables. I have the following in my batch file

/mysql/bin/mysql.exe -vvv -u root -pcs3911 < C:mySql est.txt

and the following is my test.txt file

CREATE DATABASE 'lovettsite';

CREATE TABLE `applications` (
`username` varchar(255) NOT NULL default '',
`firstname` varchar(255) NOT NULL default '',
`middlename` varchar(255) NOT NULL default '',
`lastname` varchar(255) NOT NULL default '',
`current_address_line_1` varchar(255) NOT NULL default '',
`current_address_line_2` varchar(255) NOT NULL default '',
`current_city_town` varchar(255) NOT NULL default '',
`current_state_province` varchar(255) NOT NULL default '',
`current_zip_code` varchar(16) NOT NULL default '',
`p.........................................................................

Using Batch-/cmd-file For Dumping Data
i want to build in data in my database that are written in single table dumps like [tablename].sql.

Each time it is nececery to use several files. And it is also nececary to do this using the command line because of the files are bigger than 1,5 gb
But i didnt find a way to transfere the needed commands into the mysql command line.

How To Execute A Batch Sql File Under Windows?
I have installed MySQL 5 in c:Program FilesMysql. I have a sql file named "books.sql" which is put in the subfolder in,it contains many statements that like "INSERT ... value ...".

my question is how to execute it? I try it, but failed.

One more question is how can I return from "->" to "sql>" promopt?

Ever Thought About Running Mysql Without My.cnf File ?
I installed from source mysql on Fedora3 and started mysql demon without my.cnf by chance and it was running . I m wondering did it happen with somebody else too?

Running Mysql Code Within A Php File?
I have been given a some mysql code that I have to run within a website - I am familiar with mysql in terms of insert info and querying a database but I at a loss as to how to make this work.

the mysql code works in mysqladmin, but how do I call it into my php code?

BTW the mysql code creates a new record in the database .......

Also, I need to tweak the code so it runs in a loop for different variables - in php I would run a FOREACH query, how do I do it in mysql?

Mysqld-nt Crashes When Executing Batch File In 5.03 Beta
I'm quite new to mysql but I had installed the 5.0.1 and 5.0.0a alpha under windows XP, and designed a little database for managing cards. To make some backup of this database I dumped it in a batch file. This file was working perfectly with mysql 5.0.1 and 5.0.0a.

When I try and execute my batch file, having previously created the database concerned, it crashes the mysqld-nt. On top of that the first table created in the database is listed but when a DROP DATABASE is issued it says that this table does not exist and then the command fails.

The database uses UTF8 and SET FOREIGN_KEY_CHECKS=0; is set in the begining of the file.

So I have two questions :

1- Can I force the database to be dropped or is it definitely corrupted?

2- Any clue on why the batch file crashes the server? (I'd be glad to send it is is a mere 250 ko when zipped)

Running Existing .sql File In Mysql Command Prompt?
I've seen "examples" of this in my book on MySQL, but it just shows the file on the prompt. It says that I can run an existing sql file - for creating a table - right from the prompt.

However, it doesn't really explain how to do this in detail. I've tried :

-h host - u user -D database -p < myfile.sql

but it gives an error. How do I point it to the right file on my system?

Running An Existing SQL File
I hope this question is not too 'newbie' to answer.. I'm reading a book (PHP and MySQL web development by Luke Welling and Laura Thomson), and am getting an error on an example script.

I'm trying to create tables using an existing SQL file through MySQL. According to the book, it should be done like this:

> mysql -h hostname -u username -D database -p < file.sql
(I've changed the hostname, username, etc.)

However, I get the same error over and over again: ERROR 1064 (42000): ... at line 1. The book didn't even mention where to put the file, I've tried all sorts of things, but can't get it to work..

Running Script File
I want to write mysql code like create table ... and insert into ...
in text file and run the codes in mysql.
I have installed mysql in my machine windows xp. and the help says the mysql> source (filename) will work to run the code file. but it does no work. says could not open the file.
I am not sure where should i put my code file and the extension of the file required.

Running Sql File From Shell Script
When i run the command "mysql -h host - u user - p password -D database <file.sql" (the file.sql contains an Update Scrpit) from command prompt

/usr/test> mysql -h host - u user - p password -D database <file.sql

i am able to run the file and i am getting expected results.

When i copy the same string in a shell script (suppose in update.sh) and try running it from the command prompt

/usr/test> . update.sh

i get an error as "No file or directory found"

i tried giving in the full path of file.sql and tried running hte shell script but it gave me the same results "No file or directory found".

Running External .sql Text File
I have a database on a sever that is running some version of MySQL 4. I wan't to put the same database on my local computer.

I ran a MySQL dump from the server and tried to run the resulting text file from the UNIX command line on my computer (Running MySQL 5 on Mac OS 10.4). I always get error 1064:

CODEERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
CREATE TABLE 'art_type' (
'art_type_id' tinyint(11) unsigned NOT NULL auto' at line 1

Mysql Batch Question
I'm writing this here because it's related to mysql. I'm using Mysql with PHP and because Mysql 4 doesn't support stored procedures, I would like to run several scripts from one database connection. In running a multi-line sql.txt file externally I can do this by delimiting the statements with a semi-colon so I tried to do the same:

$strsql = "";

$strsql = "delete from tbl1 where tbl1ID = '1';";
$strsql = $strsql . "delete from tbl1 where tbl1ID = '2';";
$strsql = $strsql . "delete from tbl1 where tbl1ID = '3';";

$result = @mysql_query($strsql, $db);

I'm not getting an error; the sql scripts simply won't run.

Batch Mode Feedback Of "mysql" Command Line Tool
while writing a bash shell script to backup some databases and I am having slight problems with the mysql command line tool's feedback.

Here goes command line:

#!/usr/bin/env bash
mysql -ubackup -e "UPDATE `mydb`.`sysvar` SET `val`='backup' WHERE `var`='updateState' AND `val`='0';"

The backspaces are nescessary for the shell, otherwise it'd interpret whatever is inclosed in --> ` <--- as command.

The problem is, I need to know wether a line has changed or not. The command always returns 0 (that is a success in Unix/Linux) if it was successfully executed, no matter wether or not a line had been changed, and produces no output at all.

Could anyone tell me how I get that info?

Batch Mode
I understand that to use batch mode it is necessary to put the commands you want to run into a file before telling MySQL to read its input from the file, but how do I create the file and what do I create it in? Also, I'm not really sure how you would then tell MySQL to read it.

Using Batch Files
I have a small problem with using batch files (platform: Windows XP). The problem I have is that I do not want to place and call all my db batch files from within BIN folder, but rather from, say c:a1a2a3. Calling a batch file in Windows like this is not an issue:

mysql>source c:/a1/a2/a3/test.sql;

Then, you can call test.sql from test2.sql, for example, by the same line of code:

source c:/a1/a2/a3/test.sql;

Is there any way to keep the base direcory, i.e. c:a1a2a3 in some sort of environment or whatever variable and then use it in the parameter passed to the source command.

I have experimented with user-defined variables to no avail:

SET @BASEDIR = 'c:/a1/a2/a3/test.sql';
SOURCE @BASEDIR;

Batch Mode
I want to execute the queries wriiten in a .sql file in a single attempt using the batch process of mysql.Since I don't have a good grip over the database systems, I am confused how should I go for it..

Using Batch Mode
can someone show me the way of using batch mode?
what is the syntax of it?
what should i do for the first step?
can someone tell me about that?

Getting Error In Batch Update
I am using Mysql 4.1.13 with Tomcat5 and Connector mysql-connector-java-3.1.10. When I try to update the table in batch mode sometimes( When server is idle for a long time) get following error. Code:

Consecutive Ids In A Batch Insert?
I am attempting to batch insert 100 rows into a table, and I need to know the 100 id's of all the inserted rows.

I know that I can find the id of the last inserted row despite my app
being multi-threaded, since the select last inserted command is
connection-specific.

Given the id of the last inserted row, can I back out all the ids of
the inserted rows? Are the inserts consecutive in an auto-increment
table?

If not, I will be looking to lock the table during inserts, but that
seems very sub-optimal.

Running mysql 5.0.24

Consecutive Ids In A Batch Insert
I am new to mysql and would appreciate any advice that you have to
offer.
I am attempting to batch insert 100 rows into a table, and I need to
know the 100 id's of all the inserted rows.

I know that I can find the id of the last inserted row despite my app
being multi-threaded, since the select last inserted command is
connection-specific.

Given the id of the last inserted row, can I back out all the ids of
the inserted rows? Are the inserts consecutive in an auto-increment
table?

If not, I will be looking to lock the table during inserts, but that
seems very sub-optimal.

Batch Mode Problem
I am having a problem with 2 sql statements in 1 batch file. Only the last
statement executes. If I separate the statements into individual files they
work fine.

Are you only allowed 1 statement per file?

Here is the contents of the batch file:
insert into monlog (monlog_locno, monlog_custno, monlog_time,
monlog_inputdate, monlog_errno) select loc_no, loc_custno,
(hour(now())*60)+minute(now()), now(), 200 from loc where
loc_next+29<(hour(now())*60)+minute(now())+30 and loc_errno>=200 and
loc_errno<=249;
update loc set loc_errno='250' where
loc_next+29<(hour(now())*60)+minute(now())+30;

Help Please Running Mysql On Os X
I am very new to mysql. I am using the sitepoint book 'Build your own Database Driven Website using PHP and MYSQL. I have installed mysql mysql-5.0.41-osx10.3-powerpc. Under preferences/mysql it indicates that the mysql server is running. When I try to access mysql through the terminal window I get a message - command not found. Or no permissions. I can go to the bin directory and list the contents - they seem to be there. I can't get past page 21/22 Post-installation setup tasks. I am also new to using the terminal window. I suspect I have missed something really basic - any suggestions on how I can get up and running would be much appreciated!

Need Some Help In Getting MySQL Set Up And Running.
I wasn't sure if someone would be able and willing to help me, but I am new to installing MySQL on a Windows 2003 server (actually .. any server), but I am installing it on a Windows 2003 server right now. I also installed a new version of PHP on the same server. Everything looks fine, but when I go to phpinfo.php I don't see anything saying MySQL. (I saw somewhere that if MySQL is installed properly, it should be displayed on the phpinfo.php page.)

I am currently trying to set up and run Cerberus Help Desk, but it isn't finding the MySQL database that I installed.

So, I would really appreciate it if someone could not just help me via email or postings in here, but actually by using Instant Messenger (AOL, Yahoo! or MSN) to walk me through the set up, testing, etc. so I can get things up and running properly.

I was hoping that someone with more experience than I might be willing to help me in this manner.

Could Not Get MySQL Running
I went to MS-DOS to start the MySQL application by typing mysqld into the bin directory of where I installed MySQL but once I did typed the command and pressed enter this is all I got :

A moment passed before I am shown the path of bin with no indication of MySQL server started. And also I have installed this as a window service.

Running MySQL 5.0
I've just downloaded and installed 5.0 on my Pc/ win 98se.
When I run the program MySQL Command Line Client, it appears to run for a second then disappears. I see the program flash open for a second and then it is gone.Complete newb needs some help.

MySQL 4.1 Running
I've been running mysql 4.1 for sometime and using phpMyAdmin (v2.6.2) to administer the databases.

Using php scripts I can also update/insert etc the databases (maintained on my laptop Mac OS X Tiger).

Suddenly, I can't get phpMyAdmin to load in the browser. I've also tried other MySQL GUI tools like CocoaMySQL and Navicat....they won't run either. I downloaded and installed phpMyAdmin 2.6.3-pl1 as well but no improvement.

Why would mysql run but the admin apps not work?

Running MySQL From A CD-ROM?
I have used PHP and MySQL for a few years now. Just today I can across an application that had a MySQL database running locally off a CD ROM. I did not know this was posible. When the application runs it starts up the MySQL database without the user even knowing they are running it. It is pretty amazing.

So I want to do the same thing. Is there any documentation on how to run MySQL locally. I would like to know how to build the database and create tables under these curcumstances.

Running MySQL
I have installed mysql-3.23.54-win and it all seems to have gone OK .... now i want to run it and setup a database. I dont have an icon on the desktop and there is nothing in Start - Programs ..... start it from DOS prompt via C:> C:mysqlinmysql.exe and i get error 2003 cant connect to MySQL server on localhost <10061>

How Do I Upload A File To MySQL Database And View That File
I do not know how to upload a file to MySQL database and view the posting file on the webpage. I followed the code on-line that show me how to upload, but when I click "browse" to open my file in local drive and click "upload". Nothing happen ! How can I view it on the web page to make sure it was saved in data base.

Prepared Statements (for Batch Inserts)
I am looking into using prepared statements as a possible performance optimisation in my application. I am currently using a batch insert statement:

insert into mytable values (d11, d12, d13), (d21, d22, d23),...,(dn1, dn2, dn3)

The database insert/select logic is multi-threaded. The number of rows that are to be inserted in the batch is variable. I am using Connector/NET to access MySQL from the application.

MySqlCommand as far as I can tell is not thread safe which means creating a new command instance for each statement the application needs to execute for each thread.

How does this affect prepared statements ? Are they automatically cached under the hood so if a new MySqlCommand is created using a previosuly prepared statement, is this handled so as to prevent "preparing" the statement again ? Do I need to cache each MySqlCommand instance for each unique prepared statement ?

What performance gains if any will I get if I use prepared statements instead of non-prepared statements for batch inserts as shown above?

How would I define the prepared statement parameters for a batch insert with a variable number of rows ?

See Feedback Message On Batch Mode
I wrote a perl program to update/insert mysql
database. It worked fine but I could not see the feedback message to
show how many rows updated or inserted. I can see that if I issue SQL
commands interactively.

BTW, what's the easiest way to check return code from SQL commands in
perl. I use korn shell script to invoke perl script that does SQL
stuff. I need to pass the return code back to shell script.

See Feedback Message On Batch Mode
I am new on mysql. I wrote a perl program to update/insert mysql
database. It worked fine but I could not see the feedback message to
show how many rows updated or inserted. I can see that if I issue SQL
commands interactively.

BTW, what's the easiest way to check return code from SQL commands in
perl. I use korn shell script to invoke perl script that does SQL
stuff. I need to pass the return code back to shell script.

Tabs Missing In Batch Mode
in a shell script of mine I'm executing a mysql SELECT in batch mode. I
want to use the return value for other sql statements.
The problem I'm facing is that the tabs, which seperate the columns
from each other, are missing in the output.
What am I doing wrong?

#!/bin/sh
QUERY="select id, i.handle from idmap i, person p where
i.handle=p.handle and email="$1""
mysql mydatabase -pmypassword -N -B -e "$QUERY" | while read line ; do
personid=$(echo $line | cut -f1) ; handle=$(echo $line | cut -f2 ) ;
echo $handle ; done ;

MySQL Is Running, But I Can't Connect
I really need some help with this. MySQL will not start on boot despite
everything I've done to make sure that it is set to do so.

When I start it as root from a terminal with "/usr/bin/mysqld_safe
--skip-grant-tables &" I show several mysqld procs. and one mysqld_safe,
but I can't connect:

[root@adamsmdk madams]# ps -aux| grep mysql
Warning: bad syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html
root 3650 0.0 0.1 2276 664 ? S 11:09 0:00
/usr/lib/courier/authlib/authdaemond.mysql start
root 3654 0.0 0.1 2316 792 ? S 11:09 0:00
/usr/lib/courier/authlib/authdaemond.mysql start
root 3655 0.0 0.1 2276 664 ? S 11:09 0:00...............

Gdb Bus Error When Running App With Mysql 5.0
When linking with mysql 5.0 library, I can't no longer use gdb to debug
my app anymore. It cores immediately with a bus error. I've also
noticed that the size of the executable is more than doubled compared
with one linked with mysql 4.0

Running MySQL In RAMDisk
Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences?

Running Two MySQL Versions
Actually what I want to do is remove 4.1 because I downloaded 5.0 and installed w/o removing the previous version. I now have port 3307 for 5.0. Is this a problem and how can I change it back to 3306 so e.g. when I use Apache/PHP evrything works?

MySQL Running Very Slow
I have the following INSERT SQL, which runs very slow (For 2 full days it had not done anything) on a 2 processor Compaq machine running Suse Linux. Code:

On What Port Is MySQL Running?
I'm running MySQL on my Mac, and I'm trying to figure out on what port MySQL is running. What can I type into Unix terminal to figure this out? Or can I find out some other way?

Slow Running MySQL?
I'm hoping someone may have an idea what could be causing the slow loading pages of my web-site. I'm not positive, but I'm relatively sure that it's related to a problem with MySQL database.

I purchased a program called Sam3 for broadcasting our radio signal over the internet. The software requires the use of MySql in order to set up web pages for our web site.

The problem is, that while everything works GREAT (requests can be made from the site, songs can be searched, the history of what we've played shows up) the pages load PAINFULLY slow. Other users of this software have had no such problems so I'm at a loss.

I know from testing that the PHP script for the web pages is fine, I have plenty of bandwith both upload and download available on my DSL line, and while the computer running MySQL is behind a router I have tried plugging the DSL line DIRECTLY into the host computer and it makes no difference.

For all appearances it seems the problem is that it takes MySQL forever to gather and provide the information needed for these pages. Does anyone have any idea what I should be looking at to solve this problem?

I do not know the MySQL program at all, so I get somewhat lost when people start throwing out technical terms, but this has been really frustrating because no one else seems to have the problem and everyone so far has just told me to check the things I've already checked.

MySQL Config Not Running
i'm trying to set up MySQL with IIS 7.0 on Windows Vista Home Premium. After you install MySQL, there's a check box to launch the config app.

if i leave it checked, the app does not load. if i try to run the app by double-clicking its icon it gives me an error saying, "The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log for more detail." I cannot find the application event log, though.

Running Mysql From Shell
I need to run this sort of querys from shell:

delete from 3dinformatica where preco = " " ;

CREATE TABLE tudotest AS SELECT DISTINCT * FROM 3dinformatica;

DELETE FROM 3dinformatica;

INSERT INTO 3dinformatica (cr_datetime,produto,vendedor,preco,link) SELECT DISTINCT cr_datetime,produto,vendedor,preco,link FROM tudotest;

DROP TABLE tudotest;

i wanted to put the mysql cmd in a file like mysql.sh and run it from crontab.
i have about 100 querys like that i need to run at the same time.