Mailing List Archive: MythTV: Users

Moving my mysql database to another drive

Index | Next | Previous | View Threaded

mikerice1969 at gmail

Jan 22, 2011, 10:12 PM

Post #1 of 7 (964 views)
Permalink

Moving my mysql database to another drive

I am having some problems that may be related to disk performance.
(See http://www.mythtv.org/pipermail/mythtv-dev/2011-January/070387.html).
I have an extra drive and I am thinking about moving the database to it
to see if it helps the situation.

If anyone has pointers/gotchas on doing this (especially on Fedora)
I'd appreciate hearing about them. Is there any particular way I
should format the drive to help performance?

Thanks.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Jan 23, 2011, 5:33 AM

Post #2 of 7 (947 views)
Permalink

Re: Moving my mysql database to another drive [In reply to]

alex at tomlins

Jan 23, 2011, 6:17 AM

Post #3 of 7 (948 views)
Permalink

Re: Moving my mysql database to another drive [In reply to]

On 23/01/11 06:12, Michael Rice wrote:
> I am having some problems that may be related to disk performance.
> (See http://www.mythtv.org/pipermail/mythtv-dev/2011-January/070387.html).
> I have an extra drive and I am thinking about moving the database to it
> to see if it helps the situation.
>
> If anyone has pointers/gotchas on doing this (especially on Fedora)
> I'd appreciate hearing about them. Is there any particular way I
> should format the drive to help performance?
I've done this in the past, and it's pretty straightforward. What I did
was the following:

1. Stop mysql
2. move the mysql data dit onto the new drive (/var/lib/mysql in my case
(I'm running Ubuntu))
3. create a symlink in the old location pointing to the new location
4. Start mysql again.

One gotcha with this for me was Apparmor. This prevented mysql from
starting. I just uninstalled it instead of reconfiguring it because I
didn't need it. I don't know if Fedora uses apparmor (or similar tools
like selinux) by default, but worth being aware of. It took me quite a
bit of head scratching before I spotted apparmor getting in the way.

An alternative approach that would avoid the apparmor issues would be to
use a bind mount instead of a symlink.

thanks,
Alex

--
Alex Tomlins
Email/Jabber: alex [at] tomlins

There are two kinds of people in the world: those who finish what they started

_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

linux at thehobsons

Jan 23, 2011, 8:09 AM

Post #4 of 7 (942 views)
Permalink

Re: Moving my mysql database to another drive [In reply to]

Alex Tomlins wrote:

>3. create a symlink in the old location pointing to the new location

Or change the setting of "datadir" in /etc/mysql/my.cnf to point to
the new location.

--
Simon Hobson

Visit http://www.magpiesnestpublishing.co.uk/ for books by acclaimed
author Gladys Hobson. Novels - poetry - short stories - ideal as
Christmas stocking fillers. Some available as e-books.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

drescherjm at gmail

Jan 23, 2011, 8:14 AM

Post #5 of 7 (942 views)
Permalink

Re: Moving my mysql database to another drive [In reply to]

> As far as the DVD move goes, the most important thing to remember is that
> you shouldn't use a binary MySQL data file copy. �Instead, use a SQL-based
> backup created with appropriate export options--my recommendation:
> http://www.mythtv.org/wiki/Database_Backup_and_Restore .
>

I would always do this. When moving a database its best to backup
first. And sql dumps are also the recommended way to move databases.

John
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

newbury at mandamus

Jan 24, 2011, 7:01 AM

Post #6 of 7 (923 views)
Permalink

Re: Moving my mysql database to another drive [In reply to]

On 01/23/2011 01:12 AM, Michael Rice wrote:
> I am having some problems that may be related to disk performance.
> (See http://www.mythtv.org/pipermail/mythtv-dev/2011-January/070387.html).
> I have an extra drive and I am thinking about moving the database to it
> to see if it helps the situation.
>
> If anyone has pointers/gotchas on doing this (especially on Fedora)
> I'd appreciate hearing about them. Is there any particular way I
> should format the drive to help performance?

There is a section in the myth documentation which sets out the steps
required to move the database. That section looks more at moving from
one machine to another. In your case, you are only moving the files from
one mount point to another.

I have run myth on fedora for a number of years with the files on a
different partition than the mysql server thinks they are, by making the
usual file tree site a soft link to the new spot, ie:

service mysqld stop
*
mkdir /home/mysql # or wherever...
rsync -avu /var/lib/mysql/* /home/mysql # make the copy
chown -R mysql:mysql /home/mysql # compel ownership
rm -rf /var/lib/mysql # remove old spot
ln -s /home/mysql /var/lib/mysql # trick the mysql server
service mysqld start

The mysql server continues to think that things are at /var/lib/mysql
while the OS slips in a substitution.

In your case, you will want to create a mount point for the new file
tree, on a different drive, first.

You might want to consider moving your entire /home tree to that drive
anyway, so as to keep it entirely separate from the rest of the OS.
(Makes re-installs much easier!)

As to formatting, you want a filesystem optimized for dealing with many
small files (as distinct to one with a few large files). I do not think
that there are disadvantages to just using ext4. Under the
circumstances, I am not sure that having journalling can actually help
much, but I understand that it has no speed disadvantages, so I would go
with that.

HTH

R. Geoffrey Newbury
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

ramin-list at badapple

Jan 24, 2011, 11:01 AM

Post #7 of 7 (920 views)
Permalink

Re: Moving my mysql database to another drive [In reply to]

On 1/22/2011 10:12 PM, Michael Rice wrote:
> I am having some problems that may be related to disk performance.
> (See http://www.mythtv.org/pipermail/mythtv-dev/2011-January/070387.html).
> I have an extra drive and I am thinking about moving the database to it
> to see if it helps the situation.
>
> If anyone has pointers/gotchas on doing this (especially on Fedora)
> I'd appreciate hearing about them. Is there any particular way I
> should format the drive to help performance?

The simplest and most foolproof method would be:

sudo service mysqld stop
sudo rsync -av /var/lib/mysql/ /path/to/new/mysql/

edit /etc/my.cnf and change the datadir to a new path. Add the socket
for your client so it doesn't use the default
/var/lib/mysql/mysql.socket to connect to the server locally.

[client]
socket=/var/lib/mysql1/mysql.sock

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

datadir=/path/to/new/mysql
socket=/path/to/new/mysql/mysql.sock

sudo service mysqld start

Don't delete the old mysql directory until you're sure it works. I
notice in my Fedora14 VM that symlinks are turned off in my.cnf. You'd
need to comment that line out if you decide to symlink the old location
to the new instead of changing the datadir.

Ramin
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads