Professional Web Applications Themes

Separate locations for innodb databases - MySQL

My questions are more about the mechanics of the MySql database and its existence on a Windows file system. I am looking for a MySql 5.0 Community server innodb database administration "GURU" that understands where the actual data is stored and how to move it, without trashing it. I have found very little information on the Internet on this subject where they are using the innodb engine. Most Internet sources for this subject are using the MyIsam engine where what I need to do is simpler. Our requirement is that we must use the innodb engine to preserve our table ...

  1. #1

    Default Separate locations for innodb databases

    My questions are more about the mechanics of the MySql database and
    its existence on a Windows file system. I am looking for a MySql 5.0
    Community server innodb database administration "GURU" that
    understands where the actual data is stored and how to move it,
    without trashing it. I have found very little information on the
    Internet on this subject where they are using the innodb engine. Most
    Internet sources for this subject are using the MyIsam engine where
    what I need to do is simpler. Our requirement is that we must use the
    innodb engine to preserve our table relationships, foreign keys,
    stored procedures, triggers, transactions, row level locks, etc...
    This data is personal health information and must be kept secure and
    stable. Lets suppose we ran the MySql 5.0 install, during the
    beginning of our applications install, and now MySql resides at C:
    \MySql5. Then our application creates the following innodb databases:
    echart_Bill, echart_Bob, and echart_Joe. Each database contains the
    same tables, relationships, and stored procedures, but different data
    because they represent different sets of client data. The database
    forms are located at: C:\MySql5\data\echart_Bill, C:\MySql5\data
    \echart_Bob, and C:\MySql5\data\echart_Joe. But, all the data for all
    three databases is comingled into the same set of interrelated files
    (ibdata1, et al) in the parent directory located at C:\MySql5\data\. I
    understand that this is all done for security purposes and that this
    is a very secure version of MySql. But, we need to store data for
    different databases in different subdirectories, thereby separating
    each database completely. This way we can write code in our
    application that can switch to different sets of data, move data to
    different locations, copy data for backup routines without using
    mysqldump, restore data without running scripts of mysqldumps, copy
    data to other machines for billing companies to utilize, and allow
    students in classroom situations to use different sets of data. These
    are only some of the growing list of reasons why our customers need
    this ability in our application. Some customers are even asking if
    they can store the applications data on SAN's and/or NAS's! Can we
    separate and move this data around like this, just as we did with MS
    Sql Server? Or do we need to similate that it is happening by
    manipulating mysqldump and script commands in the background, while
    only storing the dump files in the subdirectories?

    DaytonaBeach Guest

  2. #2

    Default Re: Separate locations for innodb databases

    == Quote from DaytonaBeach (net)'s article 

    you can use the innodb_file_per_table option in the mysql ini file. this will
    cause mysql to write each innodb table in the same subdir that the server has
    created for its data and not in innodata1.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: Separate locations for innodb databases

    Ahaaaa, that was it. I had already figured it out, and I didn't know
    it. Last week I set the innodb_file_per_table option in my.ini, but it
    never changed anything. Today after you said that the
    innodb_file_per_table is the way to do it, I carefully re-read the
    mysql manual page on innodb_file_per_table and it says it applies to
    "newly created tables", so I recreated the whole database and boom it
    worked.

    DaytonaBeach Guest

Similar Threads

  1. New to MySQL ....InnoDB?
    By Bob in forum MySQL
    Replies: 2
    Last Post: February 5th, 01:10 AM
  2. MyISAM vs. InnoDB
    By jinxidoru in forum MySQL
    Replies: 5
    Last Post: October 22nd, 05:07 PM
  3. InnoDB VS MyISAM
    By howachen@gmail.com in forum MySQL
    Replies: 2
    Last Post: July 10th, 11:12 AM
  4. Is InnoDB dead?
    By howachen@gmail.com in forum MySQL
    Replies: 3
    Last Post: June 15th, 11:14 PM
  5. Federated Databases, joins across databases etc
    By Benjamin Stewart in forum IBM DB2
    Replies: 2
    Last Post: August 1st, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139