{"id":6509,"date":"2017-02-27T09:13:25","date_gmt":"2017-02-27T01:13:25","guid":{"rendered":"http:\/\/rmohan.com\/?p=6509"},"modified":"2017-02-27T09:13:25","modified_gmt":"2017-02-27T01:13:25","slug":"mysql-databases-to-separate-disks","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6509","title":{"rendered":"MySQL Databases to Separate Disks"},"content":{"rendered":"

You may have a database server which started out small, with all its databases stored on the same disks, that is now experiencing severe storage I\/O bottlenecks. With so many heavily accessed databases on the same storage device your queries are timing out while waiting for response from disk. And despite all your efforts in optimizing the databases and queries, there has come a time where the disks just can\u2019t keep up. For this type of scenario, you need to spread your load across more storage devices.<\/p>\n

Sadly, MySQL doesn\u2019t have an option to configure separate storage paths for each database like more enterprise database servers do. The solution is to symbolicly link your databases from the new storage device to the MySQL data home directory<\/p>\n

 <\/p>\n

As long as the new location has the proper ownership and SELINUX context, this fools MySQL into believing your migrated databases still exist in the data home directory.<\/p>\n

Objectives<\/h3>\n
    \n
  1. Prepare new storage for databases.<\/li>\n
  2. Moving I\/O heavy databases to separate storage.<\/li>\n<\/ol>\n

    Scenario<\/h3>\n

    We have a MySQL 5.1 server hosting five databases on a single disk. One of the five databases is flooding the disk with I\/O due to its work profile and needs to be moved to separate storage. The databases information is shown below.<\/p>\n\n\n\n\n
    Database<\/th>\nOld Data Location<\/th>\nNew Data Location<\/th>\n<\/tr>\n
    webapp02<\/td>\n\/var\/lib\/mysql\/webapp02<\/td>\n\/Databases\/webapp02<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    The storage for the the database.<\/p>\n\n\n\n\n
    Device Name<\/th>\nType<\/th>\nConfiguration<\/th>\n<\/tr>\n
    sdb<\/td>\nSCSI<\/td>\n4 physical disks in RAID 10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    <\/a><\/p>\n

    Preparing Your new Storage<\/h2>\n
      \n
    1. Attach the new storage devices to the server.<\/li>\n
    2. Create a single partition and format it with a filesystem.Create a root directory which will be used to contain mount points for your new storage.\n
      mkdir \/Databases<\/pre>\n<\/li>\n
    3. For each database being migrated, create a folder for its storage device to mount to.\n
      mkdir \/Databases\/webapp02<\/pre>\n<\/li>\n
    4. Set the SELINUX context type of the new directories to\u00a0mysqld_db_t<\/strong>\u00a0to allow MySQL access to them.\n
      chcon -r -t mysqld_db_t \/Database<\/pre>\n<\/li>\n
    5. Modify\u00a0fstab<\/strong>\u00a0so that<\/li>\n<\/ol>\n
      <\/a><\/p>\n
      Copying Databases to New Storage<\/h2>\n
        \n
      1. Copy your database\u2019s files to the new location, using\u00a0cp<\/strong>\u00a0and\u00a0-preserve=all<\/strong>\u00a0to maintain ownership and SELINUX contexts.\n
        cp -r -preserve=all \/var\/lib\/mysql\/mydb1 \/new-mydb1-location<\/pre>\n<\/li>\n
      2. Verify the SELINUX context is applied correctly to the directories and files.\n
        ls -lZ \/Databases && ls -lZ \/Databases\/*<\/pre>\n<\/li>\n<\/ol>\n
        <\/a><\/p>\n
        Point MySQL to New Database Locations<\/h2>\n
          \n
        1. Stop the MySQL daemon.\n
          service mysqld stop<\/pre>\n<\/li>\n
        2. Navigate to the MySQL data home directory, which is\u00a0\/var\/lib\/mysql<\/strong>\u00a0by default.<\/li>\n
        3. Delete the databases directories for the databases being migrated, making note of the directory names. They\u2019ll be needed in the next step.<\/li>\n
        4. Create soft links to the storage of each database being migrated. The link file names must must the name of the database\u2019s directory name.\n
          ln -s \/Databases\/myappdb1 myappdb1<\/pre>\n<\/li>\n
        5. Repeat the process for every databases being moved.<\/li>\n
        6. After all databases have been migrated, restart the MySQL daemon.<\/li>\n
        7. If all goes well, MySQL should start correctly. If it does not, check the system logs for Selinux context errors.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
          You may have a database server which started out small, with all its databases stored on the same disks, that is now experiencing severe storage I\/O bottlenecks. With so many heavily accessed databases on the same storage device your queries are timing out while waiting for response from disk. And despite all your efforts in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72,16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6509"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6509"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6509\/revisions"}],"predecessor-version":[{"id":6510,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6509\/revisions\/6510"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}