ownership problem if i restore the backup

[Gurjit Singh Dhillon]

Hi,

i have found the ownership problem if i restore the backup,

following are the steps i have done.
1) created a database office1
2) created a table employee_data under database office1
3) taken backup of database office1

mysql-zrm-scheduler --now --backup-set office1 --backup-level 0

The result was successfull

4) verified the same backup,

5) droped the database office1

6) restore the database office1 from backup.

mysql-zrm --action restore --backup-set office1 --source-directory /var/lib/mysql-zrm/office1/20070129101608/

which was also successfull.

7) check the database in mysql again, which was correct.
8) check the table under the database office1, where i have found error.
the error is below,

[root@dev0 mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use office1
Database changed
mysql> show tables;
ERROR 1018: Can't read dir of './office1/' (errno: 13)
mysql> check table employee_data;
+-----------------------+-------+----------+------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-------+----------+------------------------------------------------------------+
| office1.employee_data | check | error | Can't find file: './office1/employee_data.frm' (errno: 13) |
+-----------------------+-------+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from employee_data;
ERROR 1017: Can't find file: './office1/employee_data.frm' (errno: 13)


i have found here, there is ownership problem whenever i restore the database,

[root@dev0 mysql]# ls -ld /var/lib/mysql/office1
drwxr-x--- 2 root root 4096 Jan 29 10:18 office1

the ownership of the database comes to root, if i change the ownership from root to mysql manually, and found it works, now i was able to see the tables also under mysql console.

can anyone tell me how i can preserve or keep the ownership after taking the restore backup of mysql.

[shailen]

Gurjit,

Can you run this command:

chmod g+s /var/lib/mysql/
this will make sure that all new files/directories created under the /var/lib/mysql directory are set to the same owner/group as the parent directory, no matter which user creates them.

Also, are you running ZRM locally on the same server as where MySQL is installed???

If you are using the ssh-copy plugin to remotely backup/restore your mysql database then you will need to make sure the mysql user is created on the remote server. If you are running the mysql server and ZRM on the same server then you should be fine.

Also, can you verify that your mysql server is running as the "mysql" user.
You can do this by first looking in your global 'my.cnf' file you should see an entry similar to this:

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

next type
ps -ef | grep mysql
and you should see at least one entry similar to this.

mysql 6313 6277 0 11:36 pts/1 00:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

the first part of that entry shows the user thats running the mysql daemon which in this case it is the user "mysql"

Thanks
-shailen

[Gurjit Singh Dhillon]

HI,

As you said, i tried the below command, but didn't help still if i drop the database and restore again, i am not able to access table under that database, and even the right are getting changed to root,

chmod g+s /var/lib/mysql/

i am running mysql locally , i my content of my.cfg is as above

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

[mysql.server]
user=mysql
basedir=/usr/local/mysql

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


here my basedir is /usr/local/mysql as i have am using Mysql version 5.

My ps -ef | grep -i mysql output is below

[root@dev0 root]# ps -ef | grep -i mysql
root 31888 1 0 06:17 pts/42 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/dev0.pid
mysql 31926 31888 0 06:17 pts/42 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/dev0.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

[shailen]

Gurjit,

Can you answer a few questions for me.

1) Version of ZRM for MySQL? (cat /etc/mysql-zrm/mysql-zrm-release)
2) How was ZRM installed? from source or rpm?
2) What OS are you running on? Including kernel level.

Thanks!
-Shailen

[Gurjit Singh Dhillon]

following are the answer of your query.

1)
[root@dev0 root]# cat /etc/mysql-zrm/mysql-zrm-release
ZRM for MySQL Community Edition - version 1.1.3

2)
ZRM was installed from rpm.

3)
root@dev0 root]# cat /etc/issue
Red Hat Enterprise Linux AS release 3 (Taroon Update 4)
Kernel \r on an \m

4)
[root@dev0 root]# uname -a
Linux dev0 2.4.21-27.ELsmp #1 SMP Wed Dec 1 21:59:02 EST 2004 i686 i686 i386 GNU/Linux

and i am using mysql version 5 , which is install under /usr/local/mysql


Thanks
Gurjit Dhillon