MYSQL :: Database Restore Space

How Much Space Is The MySQL Database Using On IIS?
I'm running IIS on Windows Server 2003 with full access to my server.

How do I find out how much space the MySQL database is using on my server? Is there a way to break it down by Schema and/or table?

We do not have PHP MyAdmin installed.

Extend The Space Of Database
I`m testing some queries about loading tables from files with a large amount of data, millions of records.
It proceeded until the half of the file, interting in the table 10 million records approximately, and then failed showing an error in MySQL query browser telling me that I have to remove some things in databse in order to free the space. All I want to do is to extend the actual database capacity but I dont know how and from where to do it.

Release Disk Space By Drop Database ?
OS. linux 2.4.22
MYsql : 4.0.18-standard

I created a database and populate 2 big InnoDB tables, the total size
is > 3G. After all testing, I droped the database .

But, the harddisk space is not release to the filesystem, Why, How
could I recover that ?

Release Disk Space By Drop Database
OS. linux 2.4.22
MYsql : 4.0.18-standard

I created a database and populate 2 big InnoDB tables, the total size
is > 3G. After all testing, I droped the database .

But, the harddisk space is not release to the filesystem, Why, How
could I recover that.

Can I Upload A MYSQL Database To My Free Web Hosting Space?
I have a domain with awardspace.com which has php and MYSQL enabled.

I am a newbie and not quite sure how to set it all up or if it's even possible with the Database I've created on my desktop.

When I go to the Database Manager in the administrator section it has a spot to create a database,and confirm a password. From there I take it you would use the PHPMyAdmin to alter the database.

Can I just upload a database using an FTP manager like Dreamweaver, or do I need to start from scratch and create the database on this server. If so is there a way to eventually download it so I don't lose any of the information in it.

How To Restore Database
I think i will have to da a full resortation of a database.

lets assume old database is deleted.

So I go to my hosting control panel and create a new mysql database
Use same username and password as used in original

open phpmyadmin and do to the new database and select import-then import the file that I have as a backup--i have a file that I exported as a text file-a full databse export. it is about 4 mb so I hope can do it at one time

Is that basically how I can do it??

Database Restore Help
im trying to construct a database using a backup file i made aout a year ago when i use phpmyadmin to upload the file i get an error on the first table it trys to create

CREATE TABLE `cal` (

`id` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
`author` varchar( 32 ) NOT NULL default '',
`subj` varchar( 64 ) NOT NULL default '',
`txt` text NOT NULL ,
`typ` tinyint( 4 ) NOT NULL default Ɔ',
`dmy` varchar( 8 ) NOT NULL default '',
`hm` varchar( 4 ) NOT NULL default '',
`wday` tinyint( 4 ) NOT NULL default Ɔ',
`location` varchar( 64 ) NOT NULL default '',
`approved` char( 1 ) NOT NULL default '',
`tz` int( 11 ) NOT NULL default Ɔ',
`dat` bigint( 20 ) NOT NULL default Ɔ',
`mkt` bigint( 20 ) NOT NULL default Ɔ',
PRIMARY KEY ( `id` ) ,
KEY `typ` ( `typ` ) ,
KEY `wday` ( `wday` ) ,
KEY `dmy` ( `dmy` ) ,
KEY `mkt` ( `mkt` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

With this msg

MySQL said:

#1064 - You have an error in your SQL syntax near ') ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 20

this was a backup straight from phpmyadmin some time ago, can someone tell me why it wont restore?

How To Restore A Database (.bak)
Recently I downloaded a ASP script, so I can use on my website, however in the instructions, it tells me the following : -

Restore the bbdf.bak SQL database with SQL 2005 (any edition) Management Tools

Now I have the file bbdf.bak, but I am not sure what I am suppose to do.

I have downloaded and installed Microsofts SQL 2005, but am totally lost as to what I am suppose to open and type .... ? ?

I would of thought it would be easier for them to just give me a .mdb file or something which I could use...

Restore Of Database
How do I go about restoring multiple databases at the same time. I know if I needed to restore 1 database I can do something like.

mysql db_name < backup-file.sql

but what if backup-file.sql consists of multiple databases (ie it was created using mysqldump-all-databases) , which database would I specify in the db_name section?

SQL Database Restore
I upgraded to Tiger and it damaged my SQL database.

I have a backup file that is about 80 mb. The backup file was created
with PHPmyAdmin and is in .SQL format.

When I try to use PHPmyAdmin to load this .SQL file and restore my database, I get an error message that says NO SQL QUERY.

The file stops executing and NO data is restored.

Is this because the file is too big ??

Is there an application that will break the .SQL file down into smaller
parts ??

Is there a way to restore the data using the .SQL file and a direct SQL command line entry ??

Database Restore
How may I restore *.sql databases from linux shell?
I've read the use of mysql command I don't have installed.
This is a remote server with preinstalled Apache-php-mysql.

Cannot Restore A Database
I have a text file that contains the output from mysqldump. I try to restore the database with: Quote: mysql -u root -p <password>1472 database-name < name_01_06_2007.sql and mysql produces a message as if the command is incorrect.
The message is: Quote: mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]

How do I restore a database after mysqldump? I read an article published in devshed and I followed their steps but...

Backup And Restore Database
i did a backup of mysql database using the command line utility

mysqldump. this is the command i used "mysqldump -u root -h <hostname> --databases <dbname> > <backupfile>

when i try to restore this using mysql command line client it gives a error after some time the message says that a error has happened on a line number and it states that the table columns does not match the values in the insert statmenet.

i havent done any cahnges to the backup file i just dumped and restored thats all

but when i tried this with mysql administrator gui tool it worked fine.

Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working.

Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.

Cannot Restore Mysql Database
My host suddenly went out of business, and deleted all my files without a notice. The only thing I have now is a week old back up
Now everytime I'm with a new host, and created a new database, to restore the old one over it, but everytime I try to restore, using ssh, I get that dreadful error message:
" ERROR 1062 at line 238892: Duplicate entry '189864' for key 1"

Get Trouble With Restore Database
I truly get trouble with my database. I transfer from a shared hosting to another one. Thus, I make a backup file called svduhoc.sql. My database is so large over 160MB. Of course we can not restore database by phpMyAdmin. Therefore, I use another way. Although I have tried many times, I couldnt restore. I even asked for the help from techincal support of my new shared hosting. However they told me that there could be a problem and recommended me to come over here to get your help.

Both of us: technical support and me got the same error at line 11923. Whenever we restor, it quits at the same the line 11923.

So what should I have to do now? I really get mad and stress because I couldn not lose this database. It is very important to me.

If anyone can fix this problem and successfully restore my database, I will pay $50 bucks even though I am poor student now. Because it is very important then I need all your help. You guys can contact me here or pm or email "tony_hai@hotmail.com"

How To Restore Big Database On Server With WHM/cPanel?
I have 175 Mb MySQL database. I've tried to resotre it with MySQL Administrator and phpMyAdmin, but I have low speed (outbound) internet connection.

InnoDB Constraints And Database Restore
We use InnoDB tables and foreign key constraints extensively. The mysqldump
backs up the database tables in alphabetical order with foreign key
constraints defined in the create statement of each table. These foreign key
constraints are violated at the time of restore. We have tried the following
two solutions...

(1) We have tried to backup the database tables in the order of their
dependencies. This works but the backup scripts need to be maintained
carefully as new tables are added/removed from the database.

(2) phpMyAdmin export does the database dump and puts the table constraints
as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin
dumps cannot be automated and I have not been able to push constraints to
the end of the backup using mysqldump.

MySQL Database Restore Error
I decided to download a db and upload it back to the server just out of curiosity. My site is now missing the top menu and components menu in the backend of Mambo. This site is for a client and I'm in big trouble here. Code:

Error 1044 On Database Restore
I'm trying to move a database from one machine to another. I dumped the
database and successfully loaded it on one machine, but I can't do it on
another machine.

I have the database set up. I created the user to access the database,
and gave all privs with grant option for that database. But when I go to
run the command to restore the database, I get the following error:

$ mysql -u user -p database < database.sql
Enter password:

ERROR 1044 at line 26: Access denied for user: 'user@localhost' to
database 'database'

I don't understand. Looking at the sql file, the line is:

/*!40000 ALTER TABLE accidents DISABLE KEYS */;
LOCK TABLES accidents WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE accidents ENABLE KEYS */;

Why can I do this on one machine but get an error on the other? The
users are set up the same. Actually, using mysqlcc, I can't even get the
user to have the lock tables priv, even though I'm root granting the
priv, it just refuses to take. Yet, the machine it worked on seems to be
set up the same way and I didn't have a problem.

Can someone point me in the right direction to a solution?

Error 1044 On Database Restore
I'm trying to move a database from one machine to another. I dumped the
database and successfully loaded it on one machine, but I can't do it on
another machine.

I have the database set up. I created the user to access the database,
and gave all privs with grant option for that database. But when I go to
run the command to restore the database, I get the following error:

$ mysql -u user -p database < database.sql
Enter password:
ERROR 1044 at line 26: Access denied for user: 'user@localhost' to
database 'database'

I don't understand. Looking at the sql file, the line is:

/*!40000 ALTER TABLE accidents DISABLE KEYS */;
LOCK TABLES accidents WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE accidents ENABLE KEYS */;

Why can I do this on one machine but get an error on the other? The
users are set up the same. Actually, using mysqlcc, I can't even get the
user to have the lock tables priv, even though I'm root granting the
priv, it just refuses to take. Yet, the machine it worked on seems to be
set up the same way and I didn't have a problem. Code:

How To Restore A Mysql Database From A .bck File
i am trying to move my site to a new host. the mysql database was backed up to a .bck file nightly and i would be most appreciative if one of you could inform me of how to restore it?

InnoDB Constraints And Database Restore
We use InnoDB tables and foreign key constraints extensively. The mysqldump
backs up the database tables in alphabetical order with foreign key
constraints defined in the create statement of each table. These foreign key
constraints are violated at the time of restore. We have tried the following
two solutions.

(1) We have tried to backup the database tables in the order of their
dependencies. This works but the backup scripts need to be maintained
carefully as new tables are added/removed from the database.

(2) phpMyAdmin export does the database dump and puts the table constraints
as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin
dumps cannot be automated and I have not been able to push constraints to
the end of the backup using mysqldump.

Database Restore / MySQL 4.1 -- InnoDB
I have got a MySQL 4.1 that we use wit vBulletin.... We had a crash
last week and I've been able to restore the data up to September 2005
as it stands.

Now, I'm under the impression I could rebuild the data from the logs
but I cannot seem to figure out how to do that based on the tools and
instructions posted.

I've got my ib_log files (ib_log0 and ib_log1) also my ibdata file,
which is full of all the data i'm trying to get restored. Opening it up
in hex editor, I can see posts and information right up to the moment
the database crashed.

What steps should I take to restore the data from the log/data files?

I've tried several different steps, but I'm goofing it up somewhere I
guess. Are there some other tools available to help with this?

This site was a 'non-revenue' site, so I didn't exactly make all of the
redundancy arrangements that I would normally do for an Oracle/MSSQL or
any other live business database. I'm ateast going to enable mirroring
this time around and move the backups to another location.

Font Error After Restore Database
I have a forum which is use Vietnamese language and input is utf-8. It
work normall in mysql 4.0 but after i upgrade to mysql 4.1 it has some
strange error in font. Some character like á à é broken and become
ỿ . :( . In the end i found a stupid solutions, that was i copy a
/usr/local/mysql/mysql/databasename/ ( a folder which is contain
database in mysql ) from mysql 4.0 and put to mysql4.1 folder.
Everything work well again. And now a real big problem happen. I
backuped my database with : mysqldump data data.sql and drop
everything to fix my server, after fixing my server i restore database
use : mysql database < database.sql and a old problem appear again :( .
Some character broken and apear as ỿ . I dont know what to do now
becauase that was my only backup at this time. I'v google a week
already and could not find any solutions.

Backup/Restore Mysql Database From Remote Server To Local Pc
Im trying to download mysql database "dbproducts1" which is residing in my webhost's server (Linux). From telnet i backed up using "mysqldump -u -p dbproducts1 > dbproducts1.sql"

Then i downloaded this file via ftp to my local pc(WinXP). Now on my local pc to restore database i went to command prompt and i did following:

mysql -u -p dbproducts1 < dbproducts1.sql

I got following error:

ERROR 1064 (42000) at line 380: 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 '-workshop (
title varchar(250) NOT NULL default '',
decription longtext NOT ' at line 1

I opened the phpmyadmin and found that dbproducts1 database was restored but with only few tables i.e. not all tables in the db was restored.

Can somebody please help me to backup/restore this database. I checked the mysql server version and found following:

server version: 5.0.24a-community in my local winxp pc
server version: 3.23.36 in my webhost's server (Linux)

MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same).

My server specifications:
Red Hat 9.0
Pentium 4 3.0 GHz
2 GB Ram, Burst RAM 5 GB
Running webserver, mail server as well.

Is there a way I can view the minute process details, as what table record is being updated ?

How Do I Get The Space Being Used?
I have a client table, and then I have data table with a foreign key back to client. Is there a way to determine how much space a client is using is using in the DB? I would like to know if there is a way to cap a client from storing more then 1MB of data in the DB. Can MySQL support anything like this?

How To Do A Select When The Db Name Has A Space In It
I have a database named Property Tax in MySQl I need to create a query in c# where
the use database command is not given.
I need to do something like
Select * from Property Tax.tablename;

I have tried using: Select * from 'Property Tax'.tablename;
but that does not work; What is the method of specifying this.

Space Efficiency
I had a question about the most efficient way to arrange some data. I'm conducting a survey and I would like to store the responses in a MySQL database. I'm expecting a large number of responses, so I would like to avoid wasting space. However, each respondant is only asked some small subset of the possible questions.

I was wondering if it would be advisable to include a column for each response value, even if many or most are unused for each row, or if I could save space by making a single varchar field that stored a string indicating their responses (e.g. "1:a,2:b,3:c").

Table Space
I'm using InnoDB table, and when my data reaches for about 4GB, i can no longer insert new data.

Zero = Blank = Space
how I can change it if possible:

mysql> select count(*) from property where number = 0 and number = "" and number = " " and number = " ";
+----------+
| count(*) |
+----------+
| 43 |
+----------+

Set A Space Limit
I have any users who have hosting on my server and they want a MySQL User
Account for their websites.How can I do to limit the space of their database following the plan of their hosting?If I can't set a limit they can put much more datas into database.

Calculating Innodb Space ?
I have just started with some innodb. So first I say there was a file called ibdata1 of size 10Mb. So as I add on data it became 18Mb now. So I want to find out is how to exactly know what is size of my innodb database.

Space For MYSQL Corrupted
I have found that if I declare:

MYSQL mysql; MYSQL_RES *result;

that after a call to mysql_real_connect(), the result variable is clobbered causing subsequent queries to produce a crash. If I insert a dummy variable:

MYSQL mysql;
int dummy;
MYSQL_RES *result;

the variable dummy gets clobbered but this doesn't hurt anything and there are no more crashes. It appears that MYSQL is actually writing into space beyond the declared size of the structure.

Space Delimited Files
I have a space delimited file that I need to import into a database. I've done this before with other space delimited files, but before each row of data was contained on a single line.

In this file, there are multiple lines per entery and I need to know what to do to import those into my database. What would be the recommended way of doing this? Code:

Removing Space In Varchar
i have a problem to make a transtype

from field XX varchar(20), example :
19 200 340,56

to field YY integer, required :
19200340

I make two replace
- first for replacing the , by a .
- second for replacing the space by '' (nothing)

and a cast :

insert into table2
select cast(replace(replace(XX,',','.'),' ','') as unsigned) from table
1 ;

The problem is the first replace. The space is not recognized. Even for
example with others commands related to string. If I put any other
caracter, it works. Does anyone have any solution ?

How To Set A Default Colum Value To <space> ?
I am trying to set the default column value to a space, but when I use...
ALTER TABLE myTable MODIFY COLUMN myColumn varchar(255) DEFAULT ' ';

it behaves as if I'd entered....
ALTER TABLE myTable MODIFY COLUMN myColumn varchar(255) DEFAULT '';

Is there any way to force the default to be a space, rather than an empty string?

Hard Drive Space?
I have recently installed mysql to be the backend of a websystem. The system tracks and bills about 500 customers. I have it installed on a hard drive with nearly 4 gigs of free space. It was recomended to me that i move the mySQl datafiles to a different hard drive with more space, but 4gigs seems like plunty to me. Does anyone know if i really should move it or if 4gigs will be acceptable? If i should move it, anyone have any tips on moving it without uninstalling, reinstalling and rebuilding my tables?

Losing Storage Space
I have a slackware 10 box with mysql 4.0.20. I have just started learning to
use mysql.

I have used the medium file for my my.cnf file and have uncommented the
innodb stuff as that is what i wanted to play with.

I imported 3 million recs from my postgresql table into mysql as an myisam
table and ran some speed tests... I then converted the table to innodb
format and ran the same tests. When i was finished i dropped the table and
database i was using and my disk space is almst 2 gigs less than it was
before i started the test.

I am assuming the space went into a transaction log somewhere. Is there some
way to reclaim this space?

Table Space Full
I have a problem in MySQL 4.0. When my data reaches 4GB, I can no longer insert new record. I've tried to use "ALTER TABLE tbl_name MAX_ROWS=5000000000;" to expand the limit
but when I check it's status it only return "max_rows=4294967295" as its max_rows.

Isn't that "max_rows=4294967295" only allocates 4GB?

How To Create A Table Space?
how to create a tablespace in mysql?

Administer Free Space
I started administering a MySql database this week. I am looking at putting in space reporting scripts as I have done for my other DB's.

Is there a stored proc? A script of equivalent?

Escape Space On INSERT
<?php

$object_name = mysql_real_escape_string($array[$i]);
$query = "INSERT INTO $table (`object_name` , `type` , `parent`, `size`) VALUES ('$object_name', '$type', '$parent', '$size')";
$result = mysql_query($query);
$parent = mysql_insert_id();
$query = "SELECT * FROM `$table` WHERE `object_id` =$parent";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
echo "inserted name = '$row[object_name]'
";
}

?>

When the object_name is, for example "my file with a space ", the trailing space is not entered into the database.

MySQL, Partition Out Of Space
I admin a small server for students of my major and recently we've been getting a lot more popular. We are already in the process of upgrading to better hardware by January but a problem has arisen that we need a temporary fix until then.

our root partition, because the guy before me made it 100meg insize, is at 100% usage. This is causing mySQL to refuse to run for our clients because it has no room. Since I'm assuming we can't repartition it without starting over, the best thing I can think of is to move mySQL to another partition. My question is two fold:

1) where the bloody hell IS mySQL? I can't find it, or I don't know what I'm looking for either. I've been told /databases is not it.

2) How, exactly, do I move mySQL safely and have it work properly afterwards?

I appreciate any help you can give me. We are running Debian Potato with MySQL 3.23.54. Also have phpMyAdmin if that means anything. Here's a paste of the df:

Filesystem 1k-blocks Used Available Use% Mounted on
/dev/sda2 120979 114733 0 100% /
/dev/sda5 2402848 2110956 169832 93% /usr
/dev/sda6 483930 301713 157233 66% /var
/dev/sda7 5227932 3867832 1094528 78% /home

Recovering Space In Tables
How does MySQL deal with space in tables when records are deleted? If we have 100 records auto numbered 1-100 and we deleted records 30-35 + 51 + 76 (for example) what happens to that space in the table (and on the disk). Does it write record 101 into the space used by 30 automatically? Does it leave "holes" in the space on the disk and not reuse that space? is there a function or command to reclaim that space by "packing" or "compressing" the table?

Remove Text Space
Trying to format a string to remove text space characters that have been stored in the db, those nasty little narrow rectangular fellas, think its code U+2060.

There the last two characters in the field for export to a .csv file where they cause havoc.

Remove Leading Space
Some data in my table came from a different source and some of the name fields have a leading space, which is hindering the effectiveness of my ORDER BYs.
I came up with this but it didn't work:
Code:

UPDATE table
SET name = SUBSTRING(name, 2)
WHERE SUBSTRING(name, 1) = ' '

Database Restore Space

i want to increase space of the database.. while restoring.. wen i click import it was '(Max: 2,048KiB)' i want to increase this space to.. higher