Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I am using MySQL database on Ubuntu machine.

My MySQL data directory is /var/lib/mysql/ , since I have a database named "db_test" , so, I have a directory named db_test/ under /var/lib/mysql/ . And all the table info are under /var/lib/mysql/db_test/

/var/lib/mysql# ls
db_test

/var/lib/mysql# cd db_test
/var/lib/mysql/db_test# ls
cars.frm
cars.MYD
cars.MYI
customers.frm
customers.MYD
...

What I want to achieve is very simple, just make a new directory named "backup", and move all files under /var/lib/mysql/db_test/ to the /var/lib/mysql/backup/ directory. So, I run the following commands under a sub-directory of my home directory:

~/tmp$ sudo mkdir /var/lib/mysql/backup
~/tmp$ sudo mv /var/lib/mysql/db_test/* /var/lib/mysql/backup

The 1st mkdir command run successfully, I did get a new directory named "backup/" under /var/lib/mysql/

But the 2nd command get failed, with the following error message:

mv: cannot stat `/var/lib/mysql/db_test/*': No such file or directory

Why?? I have db_test/ directory under /var/lib/mysql/ and many table files inside, why it raise the error message?

P.S. I know only root user can access /var/lib/mysql/. Could it be the reason? (but I used sudo), I am not sure though... and how to get rid of it?

share|improve this question
    
what does ls -A /var/lib/mysql/db_test/ output? –  rozcietrzewiacz Nov 28 '11 at 16:23
1  
It output all the table files –  Mellon Nov 28 '11 at 16:24
    
Please check my post, I have updated the thing you ask. –  Mellon Nov 28 '11 at 16:33
    
What do you intend to do after you move the files? –  Rilindo Nov 28 '11 at 16:36

2 Answers 2

up vote 10 down vote accepted
$ sudo mv /var/lib/mysql/db_test/* /var/lib/mysql/backup

When you type that, you user's shell tries do expand the file list for /var/lib/mysql/db_test/*. If your user doesn't have access to that directory, that will fail, and no substitution is done.

So the mv command run by sudo will get /var/lib/mysql/db_test/* as a literal filename. A file with such a name does not exist.

Try with:

$ sudo sh -c 'mv /var/lib/mysql/db_test/* /var/lib/mysql/backup'

But you should really, really consider using database tools to do database backups.

share|improve this answer

This is not how you do MySQL database backups. Instead you need to use mysqldump like so:

mysqldump -u root -p db_test > ~/backup/db_test.sql

Do not create a backup directory in the /var/lib/mysql directory, place it elsewhere. In the above example replace root with the name of the database user and db_test with the name of your database. You'll be prompted for a password which is the database user's password, not your local account's password. When it's all said and done ~/backup/db_test.sql will contain a full backup. To restore a backup you simply need to

mysql -u root -p db_test < ~/backup/db_test.sql

This will import the backup into MySQL. Again replace the aforementioned names with their respective values.

share|improve this answer
    
Yes, I know how to use mysqldump to backup mysql db. But I am not concern on this, I just want to figure out why mv does not work in my case. How to backup database is not the question I wanna ask. Anyhow, thank you for your answer. –  Mellon Nov 28 '11 at 16:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.