6

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?

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

2 Answers 2

12
$ 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.

3

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.

1
  • 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, 2011 at 16:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .