RMLinux

[Log in to get rid of this advertisement]

Hi Keen Fellowman!

Currently mysql data is in /var/lib/mysql/[database] i want to transfer the data to my username coz currently it was owned by mysql in /var what am I going to do?

edit /etc/my.cnf? and put the database location inside?

how about ownership and permission?
thanks

rylan76

Not sure what you mean, but you can change ownership for files in a directory by logging in as root and doing

chown -R username.username directoryname

in a terminal

where

username = your username
directoryname = the name of the directory.

This should change the directory itself as well as the files it contains to the username you specify.

RMLinux

If I change ownership supposed it is in /var/lib/mysql directory the increasing bytes consumed on my HDD are owned by /var/lib/mysql or by my [username]? /usr/home/nnnnname

what I want is to avoid /var to become full? I am running out of space in /var but my /usr/ is 100+G.

Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup_ID_17452-LogVol1
1008M 206M 752M 22% /
/dev/sda3 190M 15M 166M 8% /boot
none 2.0G 0 2.0G 0% /dev/shm
/dev/mapper/VolGroup_ID_17452-LogVol2
496M 15M 457M 4% /tmp
/dev/mapper/VolGroup_ID_17452-LogVol5
154G 3.1G 143G 3% /usr
/dev/mapper/VolGroup_ID_17452-LogVol4
496M 359M 112M 77% /var
/dev/mapper/VolGroup_ID_17452-LogVolHome
123G 5.0G 112G 5% /home

because of a lots of log files in apache, php, mysql, oracle, 3rd party...etc..etc...running simultanously my /var is easy to become out of space.

what am I going to do?

paul.fisher

If limited space in /var is the only consideration, then you should resize your partitions. You DON'T want to start messing with the ownership of MySQL files. You will create more problems than you solve by doing so. Further, simply changing ownership will not solve your space issue, even if you "own" the files they will continue exist and take up space in /var

RMLinux

can you help me how to resize my partition?

http://www.linuxquestions.org/questi...780/page2.html

jstephens84

Why not just edit my.cnf and change the location of your datafiles. You may have to do a mysqldump in order to move the database. However simply changing the permissions will not fix anything but most likely break the database.

RMLinux

can you give me the step by step procedure buddy.

jstephens84

all this does is make a backup of the database. so then you would edit the my.cnf file, change the database location, then recreate the mysql database using mysqldump again. However before you do this please check the syntax of this command and make sure that everything looks ok. especially if this is on a live server.

paul.fisher

I've used the GParted live CD to modify partitions. See the tutorial at: http://www.howtoforge.com/partitioning_with_gparted

jstephens84

Here is a high level step by step procedure. However again do your research and I nor LQ are responsible for lost data. Now without further adieu

1. do a mysql dump on your databases
2. stop mysql
3. move database files to new location
3. edit my.cnf to point to where new database files are located.
4. start mysql.
5. Connect to mysql and check each database for data integrity.

Again exercise extreme caution. And do not change any permissions on the database files. If you do mysql will surely throw a fit. If this is a live server I recommend that you do an image of the server for a full backup. If it is a test server than I would be kind of concerend. But I am guessing that this is a production server.

RMLinux

Thanks for you reply dud.

here's my etc/my.cnf

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

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




can you edit for me supposed I
want to save my database in username [foo].

/home/foo -- this is the username

how about the permission, ownership, socket, basedir?

anything that are possible to change kindly fill it up. thanks

jstephens84

you would change the datadir to /home/foo/. However you don't really need to change the permissions on the datafile as that will not restrict grow. Again just migrate the databases to a new partition with more space. Another suggestions could be to try and compact your databases.

RMLinux

I change datadir=/home/foo/mysql on my.cnf.
I restart my mysql and it works fine still I can create transaction.

The thing is it seems it is using the same old database the database in /var/lib/mysql because I add transaction on the foo/ settings and I shutdown my database I edited my.cnf and return to old settings the transaction that I created still appeared on the old database?

Why?

jstephens84

Not really sure. Try changing your basedir also just to see if that would work. But I don't think that would matter. Try adding a new database and see where the location for the database file goes.

Wim Sturkenboom

There is an argument that you can pass on the command line to change the data directory. That will override the setting in my.cnf.

Check how mysql is started.