Results 1 to 2 of 2

Thread: Does anyone know why MYSQLDUMP would only perform a partial backup of a database !

  1. #1

    Default Does anyone know why MYSQLDUMP would only perform a partial backup of a database !

    when run with the following instruction:

    "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" databaseSchema -u root --password=rootPassword > c:\backups\daily\mySchema.dump
    Sometimes a full backup is performed, at other times the backup will stop after including only a fraction of the database. This fraction is variable.

    The database does have several thousand tables totalling about 11Gb. But most of these tables are quite small with only about 1500 records, many only have 150 - 200 records. The column counts of these tables can be in the hundreds though because of the frequency data stored.

    But I am informed that the number of tables in a schema in MySQL is not an issue. There are also no performance issues during normal operation.

    And the alternative of using a single table is not really viable because all of these tables have different column name signatures.

    I should add that the database is in use during the backup.

    Well after running the backup with instruction set:

    "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" mySchema -u root --password=xxxxxxx -v --debug-check --log-error=c:\backups\daily\mySchema_error.log > c:\backups\daily\mySchema.dump
    I get this:

    mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE '\_dm\_10730\_856956\_30072013\_1375194514706\_key word\_frequencies'': Error on delete of 'C:\Windows\TEMP\#sql67c_10_8c5.MYI' (Errcode: 13) (6)
    Which I think is a permissions problem.

    I doubt any one table in my schema is in the 2GB range.

    I am using MySQL Server 5.5 on a Windows 7 64 bit server with 8 Gb of memory.

    Any ideas?

    I am aware that changing the number of files which MySQL can open, the open_files_limit parameter, may cure this matter. But I have not yet tried this.

    [URL="http://www.memorableindia.com"]India Tour Package[/URL]
    [URL="http://weddingplannersindelhi.in"]Wedding Planners in Delhi[/URL]
    Last edited by andrew1829; August 23rd, 2013 at 03:44 AM.

  2. #2
    Join Date
    Aug 2013
    Posts
    1

    Default

    Twice today a backup of my database schema using MYSQLDUMP has failed. The instruction set for the backup was:

    "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" databaseSchema -u root --password=rootPassword > c:\backups\daily\databaseSchema.dump

    Both backups were logged so I was able to record the failures as follows:

    mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE '\_dm\_10730\_856956\_30072013\_1375194514706\_key word\_frequencies'':
    Error on delete of 'C:\Windows\TEMP\#sql67c_10_8c5.MYI' (Errcode: 13) (6)

    mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE '\_dm\_12411\_715721\_10082013\_1376138981658\_key word\_frequencies'':
    Table 'C:\Windows\TEMP\#sql1044_9_1c69' is read only (1036))

    I can accept that MYSQLDUMP makes use of the temp folder on a server running 64bit Windows 7 PC with 8 Gb of memory. But can anyone tell me why these errors arise?

    Is it a MYSQLDUMP error or is it to do with authorities on the server? Trend AntiVirus is running on the server as well.

    What can be done to correct the matter?

    I notice that some websites suggest increasing the open_files_limit in MYSQL: on my server this is set to 7048, but I have not tried changing this yet.

    My database has several thousand tables totalling about 12Gb. Most of these tables are quite small with only about 1500 records, and others have only have 150 - 200 records. The column counts of these tables can be in the hundreds though because of the frequency data stored. None of this has ever caused a performance issue though.

    I should add that the database is in use during the backup.

    I notice that workbench has an 'Export to dump project folder' where each table is saved as a text file. I am using this manually because of the failures described above. Can this be automated as part of MYSQLDUMP?

    [URL="http://www.memorableindia.com"]India Tour[/URL]
    [URL="http://www.rajasthan-holidays.net/"]Rajasthan Tours[/URL]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •