MYSQL :: Data Kept On The Hard Disk

Moving Mysql To Another Hard Disk
I have a web based intranet application (php) that uses a mysql database. The server has just had a new drive added to it and am looking for a way to move the database from the current c drive to this new drive.I'm looking for a way to either:

move the entire database to the new drive (in which case do i have to do anything within the code - the database name is still the same and i'll presume another drive is still localhost can)

Can i just make Mysql save data into the new drive (and also move just the old data file to the new drive leaving everything else as it was).

Entering Data Into Db With Hard Returns
I have a html textarea where user submit text and it gets stored in a db. For example:

This is a test. Here is my list...
one
two
three

See, the text has hard returns in it, but when i output it back on the page with php, it all goes into a continuous line.

How can i have the hard returns in the output. Like I have the '' included.

How Do I Reclaim Disk Space In Data Folder
I have 5 databases:

- mysql
- test
- product1
- product2
- product3

The hard disk on my server was near full, so I decided to delete databases product2 and product3 as I no longer need them. I dropped the databases product2 and I noticed the disk space was not freeed by MySQL after dropping this database. The tables in databases product2 and product3 are INNODB.

What is the best method to reclaim my disk space when dropping entire databases?

External Hard Drives
I'm using Windows MySQL 5.0 on XP.

I have an extremely large project that I was hoping to use some more disk drive space then the 50 gigs I had available, so I got an External 500G USB 2.0 Drive.
I redirected the databases, and everything starts up fine.

However, I noticed that when executing queries on the external drive compared to the local hard drive, the CPU is cut in half and large queries actually stall the service.

I'm sure it has to do with the reading and writing to the USB device vs the local hard drive. I'm just not sure how and if I can fix it or troubleshoot it.

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?

Mysql Filling Up Hard Drive?
I have a table with about 36 million records that is about 1.8GB in size. I am trying to perform the following query on it:

SELECT query, count(distinct(id)) FROM data group by query;
I am running it via the command line and piping the output to a file to use later. The problem is when it runs mysql fills up my entire ~30GB hard drive (I checked the free space while the query was running) and then spits out the following error:

ERROR 3 (HY000): Error writing file '/tmp/MYQ9Q6ci' (Err 28)

I doesn't seem to be when it is outputing the results that this happens, it is still performing the query (output should be well under a gig). Also, no files are created so it must be all mysql temporary stuff I guess. I have tried the same query using a WHERE clause that restircts it to a few throusand results and it works fine when I do that.

I currently don't have any indexes on the table. I will try adding one to the query field. Anyone have any ideas why it is filling the hard drive and what I could do to fix it?

How To Run A Database Off An External Hard Drive?
i've got mysql 5.0 and I have 50 million records in a text file on an
external USB 2.0 hard drive. I can import the file into a mySQL
database on my laptop - but I don't want the database on my laptop. I
want that database to be physically located on that usb connected hard
drive instead of my laptop. Can anyone tell me how to set up the
database on my laptop, but with a physical location of the data on the
external hard drive? Oh - not that it helps, but I'm using Navicat (my
first day using it).

Restore DataBases To 2nd Hard Drive
Platform FreeBSD 4.9 on a Dell PC. I Ghosted the drive to a secondary disk and put it on the secondary IDE channel. What I want to do is, after the dump of all databases takes place, I want to temporarily mount the secondary drive and restore the databases to it. This would be a nightly proccess run by cron. I know almost nothing about MySQL and am not sure if this is even possible.

I thought about doing the following script I wrote but, isn't there a nicer way to do this?

mysqldump --opt --all-databases > /smx01/profile-mysql.sql
echo "MySQL dump is complete......."
;;

newfs /dev/ad1s1g
mount /dev/ad1s1g /mnt/backups/ad1s1g
cd /mnt/backups/ad1s1d
restore -ruy -f /smx01/usr-local_dump1 /mnt/backups/ad1s1d
cd
umount /dev/ad1s1d
echo Restore to secondary hard drive complete!

Moving Databases From One Hard Drive To Another ?
I have a hard drive that fails to boot with some mysql 4.1 data I would like
to keep.

Have slaved that drive to another PC (with mysql 4.1.12 loaded) and have
saved other data and am able to access its MYSQL folder ...

I tried to copy the data folder into corresponding directory on new drive
but when I try ot look at any of the databases I get errors like:

#1017 - Can't find file: 'master_name.MYI' (errno: 2)

... I look for corresponding .MYI files but cant find any on the old drive.

Have I lost my database files or is there another way of porting the
database to the new drive.

Receiving Beep From Hard Drive
I am working with MySql 5.0

if the command is wrong for it can't connect I receive a beep from my hard drive, is that a normal thing?

Another Easy(for You, Hard For Me) Select/join
Table 1 indexed on ID.
Table 2 is a chat log. Colums: From, To

I'd like to find out who is NOT chatting.
So, I'd like to select all ID in Table1 that are NOT found in either column From or To in Table2

So far I'm using two selects, but this doesn't seem right.

Remove Hard Returns In DB Field
I have a simple PHP page that is adding data to a mysql database (longtext as the type).
Now as it stands - when a user presses "enter" then add more text, the results (when displayed) have a hard line return. How do I modify this behavior? I just want it to be captured exactly as the user enters it....
IE - press enter and the text starts on hte next line, and hit it twice, and it shows up 2 lines below with a space in between.

Disk Performance
We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries.  Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration:

Raid5 or Raid10?  Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially.

The hardware looks like this:

Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5
Powervault 220S, 14x73gb 15K drives, dual-connected to the server.  Current configuration is Raid5.

MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver.

There is probably room for optimization in the application, but that's currently out of our control.  We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.

Running MtSQL On A RAM Disk
I am trying to set up the same thing but have been stopped right from the
get go as I can not get a larger than 2G RAM disk going (using a 64 bit
Ubuntu installation).

I am hoping someone who has gone through the basic set up might get me
squared away to start. An offline conversation would be fine and
perhaps save the rest of y'all wading through said basics.

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.

Disk Quota In MySQL
Is there a way to assign a limit on the size of a specific database? For example database A can take up no more than 500 MB.

One of my databases can potentially grow without any bounds and possibly stop other critical databases from operating. We are using MySQL 4.0.21 on Linux. We use innodb tables.

Showing Disk Usage
How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.

Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.

Load Apache/mySql On External Hard Drive
How can I load Apache,mySql,PHP on an external usb hard drive then move the unit to a pc with winxp and firefox and use the setup.

Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap
table to disk?

For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

Copying Table To Disk During Execution
I seem to be having an issue with a query's results being paged out to
disk during runtime. I notice in processlist it saying "Copying to tmp
table on disk".

select col1, col2, count(ip) as 'COUNT' from db.table group by 2 order
by 1, 3;

None of the columns are a primary/foreign or an index. This query
returns 92K rows and has taken anywhere from 50 seconds to run to over
1.5 minutes. The table size on disk is around 33MB.

After doing some research i found that i should be changing the
tmp_table_size system variable. Originally it was set to the MySQL
default, 33554432. I set the varaible to 90,000,000 and it still
started paging out to the disk (this was the only query running at the
time). I would think that 90MB in space would have been enough to
handle the query since that would assume over 600K row size.

I actually had some trouble setting the tmp_table_size for the global
variable but was able to change it as a session variable.

I have also tried altering the query_cache variable (which has only
slowed the query down - currently it sits at a size of 50MB) and also
the sort_buffer_size but neither have made a difference.

What is the magic variable that i should be messing with?

Free Disk Space On DELETE
I'm building a huge DB. One particular table can contain as much as 125 million records. I need to DELETE records older than a year. That part works fine but it doesn't free any space on my hard drive (C:/). I tried OPTIMIZE TABLE and it still won't free hard disk space.

Ibdata1 Is Close To Disk Size
I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL?

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
query_cache_size = 16M
query_cache_limit = 8M

log-bin
server-id = 1

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

( I had to comment the innodb to stop crashing )

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is:

CREATE TABLE `big` (
`keyval` int(10) unsigned zerofill NOT NULL,
`name` varchar(20) default NULL,
`avail` enum('y','n') default NULL,
PRIMARY KEY (`keyval`),
KEY `keyval` (`keyval`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.

MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index
--sort-records=1 against an MYI file. Is there something I can use to
confirm what this command did? I would like to see what the sort order
on the disk for each table in my database.

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.

Disk Usage Increases After Dropping A Large Index?
I have a linux server which is hosting several very large (~20GB)
databases. In order to save some disk space I dropped an index on one
of the larger (InnoDB) tables because it is no longer needed. It took
about 30 minutes for MySQL to drop the index, during which time I saw
the free disk space decrease. I didn't think anything of it while the
drop operation was running because I figured it was some kind of temp
file being used by the drop (even though the temp directory for mysql
is supposed to be on another partition). To my surprise, after the
index was dropped, not only did I not have more free space as I
expected, but in fact I had about 2 GB less than I did before I
dropped the index. Huh?

So now I'm down 1 index and 2 GB of space. How does dropping a db
object actually increases the amount of disk used by mysql? Is there
anyway I can reclaim this space? MySQL version is 4.0.15

Disk Size Of Tables[or Database] In Mysql Or Oracle
how can i know disk memory occupied by database[tables] in mysql or oracle.

Table "hard Links" Under Windows
I am attempting to create "Symbolic" or as windows calls it "hard links" for some tables in a database.
I have 2 databases, db1 has a users table that I created a 'hard link' to in db2.
I created this using this program which ads shell intiration for hard linking: http://www.elsdoerfer.info/=ntfslink

The hard-link appears to work, I tested with a simple .txt file and i can make changes to one file and open the other and see them. The 2 databases were working fine for awile after I created these hard-links, then things started to go wrong.

It seems whenever I try to add a new row to the users table in db2 (with the hard-link) the change doens't show up in the same table in db1. It also corrupts the table. And same the other way, I added a bunch of users into the table in db1, then did a CHECK on the users table in db2 and it gives me this "Size of datafile is: 6872396 Should be: 6874300". So it seems part of the data is being written, since it knows what it should be.

I read that one can do this type of setup in linux with symbolic links, maybe it doens't work with windows hard-links? It seems it should... maybe i'm missing something?

"hard" Sort Table Order
Is it possible to resort the rows in a table like in a query where using
ORDER BY, but have to new sort order stored in the table structure?

Does Mysql Store Max Size For Data Type Each Time, Or Only What Is Needed For Specific Data?
If I use MEDIUMTEXT, which (if I understand correctly) has a max size of 65k, does mysql use the entire size each time data is written to that column? I have a text field on my site that users fill in, but more often that not, they don't. However, when they do, it can be 1000 bytes or more. Am I wasting space using MEDIUMTEXT or does mysql only set aside 1000 bytes for that column if the data is only 1000 bytes?

How To Export Data From Phpmyadmin Into LOAD DATA INFILE Syntax
I have a table with a lot of data (several millions of records).
I would like to export the data using phpmyadmin into a SQL file. I know that phpmyadmin has an export feature.

I need the exported data to be in the LOAD DATA INFILE format after the export. Is there a way to achieve this?
Regular export uses the INSERT INTO ... syntax for the data.

LOAD DATA INFILE To Import Data Exported From A MS Access Xp
I am trying to use LOAD DATA INFILE to import data exported from a MS Access Xp table. But all i get in the date field is 00:00:00, as Access exports DATE and TIME and MySQL seems to use just the TIME portion of this.

Is there any way to ahve MySQL import just the DATE portion or is there any way to have Access export only the DATE portion?

What MYSQL Data Type Can Hold Multiline Text Data?
say a file:

name#1 (start)
line2
line3
...
line14 (end)
name#2
....
name299
....
<eof>

It's really too messy to split into fields, file process and put it in tabular format. Every 14 lines there's a new "record" - I use the term record very loosely because by record I mean one data item eg. The new "records are separated by name# - so when I see a new name# I know it's a new "record". So in mysql I might have one primary key and just one other filed per a record that would be multiline text.

What I an specifically asking is:

What MYSQL data type can hold multiline text data?

Split Innodb Data File Into Smaller Data Files
we configured innodb configuration with default my.ini settings. now data file is increased more than 40 GB.After that we come to know that we need keep the settings like below

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

so that it will keep on creating 2GB data files.
Now how we can change the settings and splitting the data file of 40 gb please.

Help CHECKING FORM DATA AGAINST MYSQL TABLE DATA
PHP

// See if relevant fields have been set.

$submitted = IsSet($_REQUEST['Submit'])   ? TRUE : FALSE;
$user      = IsSet($_REQUEST['username']) ?
                   cleanValue($_REQUEST['username']) : ''
$pswd      = IsSet($_REQUEST['password']) ?
                   cleanValue($_REQUEST['password']) : ''

if($submitted) {                                        // Form submitted yet?

    if (empty($user) || empty($pswd)) {                 // Are Form fields empty?

        // Display error message when fields are not filled in.

$message = 'Make sure you enter both a username and password.'

    }

    else {

/* THIS IS MY HUMBLE EFFORT BELOW  AND IT DONT WORK HELP */

//CONVERT PASSWORD FROM FORM INTO MD5

$pswd = md5($pswd);

//CONNECT TO DATABASE

$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd")
    or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
    or die("Couldn't select database.");

    //SELCT TABLE AND DATA

$sql = @mysql_query("SELECT * FROM users WHERE username='$user' AND password='$pswd' AND activated=Ƈ' ");
if (!$sql) {
  exit('<br /><br /><h3>Error retrieving Art members from database!<br />'.
      'Error: ' . mysql_error() . '</h3><br /><hr /><br />');
}

//MAKE VARIABLES FOR CHECKING FORM DATA AGAINST  IS THIS RIGHT!!

while ($login_check = mysql_fetch_array($sql)) {
  $user = htmlspecialchars($login_check['username']);
  $pswd = $login_check['password'];

  //NOT SURE FROM HERE

  // ORIGINAL SCRIPT BELOW

// Authenticate username and password (i.e. match against a database).
// Start a session only after username and password have been verified.

THIS IS THE EXAMPLE LOGIN CHECK THAT CAME WITH THE CLASS

if ((!strcmp($user, 'empl')) &&         // Does username match 'empl'?
  (!strcmp($pswd,'demo'))) {        // Does password match 'demo'?

            $sess_param['new_sid'] = TRUE;      // Create new session ID to be safe.
            $sess_param['security_level'] = 100;    // Security level for Employees area
            $sess_param['gc_maxlifetime'] = 300;   // Inactivity timeout of 300 seconds (5 minutes)
            $sess = new DB_eSession(&$sess_param);   // session_start() done.
            $sess->setSessVar('authenticated', TRUE);  // Set that authentication occurred.

// Save the username for displaying and encrypt
            $sess->setSessVar('username', $user, ENCRYPT_VALUE, EXTRA_FIELD);

            $URI = $sess->setSessURI('employees.php');  // Optional - pass session ID in URI. Not as safe.

            header("Location: $URI");                   // Go to members area.
            exit;
        }

REGARDS Bill007

Add Additional Data To Existing Data In A Blob Field
I have a Blob field called "category" that we use to store different categories of job types that our users set up so they receive emails based off of the ones that they have picked. What I need to do is to add 4 new values to the beginning of each users "category" field.

I am not really sure how to do this except that I might need to use UPDATE to get it done. This is where it will get tricky, I need to check for the 4 values to see if they already have them and just add the oines that they don't have.

Here are the 4 values that I need to check for and add. Allied Health Care, Nursing, Rehab/Therapy, and Other/Pharmacy/Physicians. Here is what a current list of values would look like in the users table and the category column: Code:

Data Kept On The Hard Disk

my mysql database went down... had to reinstall it... where is all the data for the tables kept - i got the /var/lib/mysql/ bit but thats only the tables