mv on a glob pattern does not work without sudo

Question

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?

Answer 1

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

Answer 2

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.