MySQL hotcopy limitation

Post by **cah** » Thu Jul 12, 2012 6:47 pm

I was adding the new schema Chia-Wei to MySQL's backup script and creating a first copy of it.
From the log, I found the following error message:

Code: Select all

Cannot open dir '/usr/local/var/Chia-Wei': No such file or directory at /usr/local/bin/mysqlhotcopy line 289.

Out of curiosity, I went to /usr/local/var to find out why there's no such a directory.
I found there's a directory named "Chi@002Wei" instead of "Chia-Wei".
Same applies to chung-shan (chang@002shan).
There's one more interesting directory that is "Computer@0020Consulting" and it should be "Computer Consulting". This one is less important because I did not back it up anyway.

Apparently, MySQL doesn't handle dash/hyphen "-" nicely. It works for mysqldump but not mysqlhotcopy.

A simple fix for mysqlhotcopy is to create a symbolic link for both Chia-Wei and chung-shan like:

Code: Select all

ln -s "Chi@002Wei" "Chia-Wei"
ln -s "chung@002shan" "chung-shan"

When they are in place, the hotcopy worked fine.

Code: Select all

lrwxrwxrwx   1 mysql    mysql         12 Jul 12 18:29 Chia-Wei -> Chia@002dWei
drwx------   2 mysql    mysql         13 Jul 12 18:08 Chia@002dWei
...
lrwxrwxrwx   1 mysql    mysql         14 Jul 12 18:30 chung-shan -> chung@002dshan
drwx------   2 mysql    mysql         17 Oct  3  2011 chung@002dshan

All files reside in /usr/local/var/Chia-Wei and /usr/local/var/chung-shan are now copied to /export/home/mysql/backup/Chia-Wei and /export/home/mysql/backup/chung-shan:

Code: Select all

$ ls -l /export/home/mysql/backup/Chia-Wei               
total 122
-rw-rw----   1 mysql    mysql        260 Jul  6 15:14 2012_Birthday.MYD
-rw-rw----   1 mysql    mysql       2048 Jul  6 15:14 2012_Birthday.MYI
-rw-rw----   1 mysql    mysql       8738 Jul  6 15:14 2012_Birthday.frm
-rw-rw----   1 mysql    mysql       1071 Jul  3 14:11 2012_Birthday_Participation_View.frm
-rw-r--r--   1 mysql    mysql      12967 Jul 12 18:24 Chia-Wei-backup-20120712182426.sql
-rw-r--r--   1 mysql    mysql      12967 Jul 12 18:30 Chia-Wei-backup-20120712183031.sql
-rw-rw----   1 mysql    mysql       1108 Jul 12 18:08 Expense.MYD
-rw-rw----   1 mysql    mysql       2048 Jul 12 18:08 Expense.MYI
-rw-rw----   1 mysql    mysql       8764 Jul 12 18:08 Expense.frm
-rw-rw----   1 mysql    mysql        687 Jul 12 16:58 Expense_By_Store.frm
-rw-rw----   1 mysql    mysql        714 Jul 12 16:59 Expense_By_Year.frm
-rw-rw----   1 mysql    mysql        499 Jul 11 19:41 Total_Expense.frm
-rw-rw----   1 mysql    mysql         61 Jul  3 13:46 db.opt

$ ls -l /export/home/mysql/backup/chung-shan             
total 1304
-rw-rw----   1 mysql    mysql       2872 Oct  9  2010 2010reunion.MYD
-rw-rw----   1 mysql    mysql       2048 Oct 10  2010 2010reunion.MYI
-rw-rw----   1 mysql    mysql       8770 Sep  1  2010 2010reunion.frm
-rw-rw----   1 mysql    mysql       3264 Sep  9  2011 2011reunion.MYD
-rw-rw----   1 mysql    mysql       2048 Sep 10  2011 2011reunion.MYI
-rw-rw----   1 mysql    mysql       8770 Jul 22  2011 2011reunion.frm
-rw-rw----   1 mysql    mysql       1869 Jul 28  2011 2011reunion_Participation_View.frm
-rw-rw----   1 mysql    mysql       1275 Aug 22  2010 Class_View.frm
-rw-r--r--   1 mysql    mysql      62198 Jul  8 05:35 chung-shan-backup-20120708053501.sql
-rw-r--r--   1 mysql    mysql      62198 Jul  9 05:35 chung-shan-backup-20120709053501.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 10 05:35 chung-shan-backup-20120710053501.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 11 05:35 chung-shan-backup-20120711053502.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 12 05:35 chung-shan-backup-20120712053501.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 12 18:21 chung-shan-backup-20120712182126.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 12 18:22 chung-shan-backup-20120712182235.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 12 18:24 chung-shan-backup-20120712182426.sql
-rw-r--r--   1 mysql    mysql      62198 Jul 12 18:30 chung-shan-backup-20120712183032.sql
-rw-rw----   1 mysql    mysql         61 Nov  2  2009 db.opt
-rw-rw----   1 mysql    mysql          0 Oct 21  2008 sessions.MYD
-rw-rw----   1 mysql    mysql       1024 Oct 21  2008 sessions.MYI
-rw-rw----   1 mysql    mysql       8596 Oct 21  2008 sessions.frm
-rw-rw----   1 mysql    mysql      30028 Jun 28 23:33 user_profile.MYD
-rw-rw----   1 mysql    mysql      10240 Jun 29 05:35 user_profile.MYI
-rw-rw----   1 mysql    mysql       9366 Oct  3  2011 user_profile.frm

I verified the backup of chung-shan on dept02 and it did not have the hotcopy files, only dump files by mysqldump:

Code: Select all

%date
Thu Jul 12 15:45:41 PDT 2012
% ls -l /home/hsiaoc1/backup/export/home/mysql/backup/chung-shan
total 1296
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 24 02:35 chung-shan-backup-20120624053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 25 02:35 chung-shan-backup-20120625053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 26 02:35 chung-shan-backup-20120626053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 27 02:35 chung-shan-backup-20120627053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 28 02:35 chung-shan-backup-20120628053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 29 02:35 chung-shan-backup-20120629053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jun 30 02:35 chung-shan-backup-20120630053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  1 02:35 chung-shan-backup-20120701053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  2 02:35 chung-shan-backup-20120702053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  3 02:35 chung-shan-backup-20120703053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  4 02:35 chung-shan-backup-20120704053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  5 02:35 chung-shan-backup-20120705053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  6 02:35 chung-shan-backup-20120706053502.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  7 02:35 chung-shan-backup-20120707053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  8 02:35 chung-shan-backup-20120708053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul  9 02:35 chung-shan-backup-20120709053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul 10 02:35 chung-shan-backup-20120710053501.sql
-rw-r--r-- 1 hsiaoc1 adm 62198 Jul 11 02:35 chung-shan-backup-20120711053502.sql

Starting tomorrow, the backup should have both dump and hotcopy files.