MySQL Forums - Backup

Can't restore databases, Error 1214 (1 reply)

Nils Stec — Sat, 18 May 2013 05:42:08 +0000

Hi,

i have to restore some databases from an old mysql-server (5.1.46, i386) to a new server (5.5.30, amd64).

I have dumps created for each table with "mysqldump -u -p --skip-opt --result-file=" and for all databases in one file with option "--all-databases".

On the new system I am trying to import those tables, but it does not work like excpeted...

$ mysql -u root < all.sql
and
$ mysql -u root < database.sql
throws me an error:

ERROR 1214 (HY000) at line 7770: The used table type doesn't support FULLTEXT indexes.

The databases are from an old joomla-installation which has to be kept and some databases from a few web-designers, which need their dbs, so i can't easily say "use a new one...."

Hope you can help me, give a hint what to do.

Nils

Enterprise Backup Enhancement Request (1 reply)

Charlie Berg — Sun, 28 Apr 2013 03:21:56 +0000

Hello;

We have Enterprise Backup 3.8.1 installed, and don't see this as a parameter option. The option I'd like to see... the opposite of the --databases parameter for mysqlbackup. Something like -- excl-database to list database that are to be excluded from a backup.

Thanks,
Charlie

Back-up of live databases (2 replies)

dieter nog iet — Thu, 25 Apr 2013 06:51:10 +0000

Hello

In our company we have a lot of MySQL databases. Some of these are critical and we want to take a back-up of all databases. These databases can't be locked because the application can change these database at every moment. I want to write a script that makes a dump of the database an store it somewhere on a back-updisk. What is the best way to achieve this without locking databases and get a consistent dump off all databases from a remote server.

Thanks

MySQL Backup Strategy for Master-Master Replication (4 replies)

Varun Datta — Sun, 12 May 2013 18:37:25 +0000

Hi,

We are developing backup Strategy for MYSQL Master-Master Replication FT Setup.

Application always makes connection to only one Master MySQL Database and there are minimal chances of update conflict.

Full backup can be taken on any one or both MySQL Master Databases but i am not sure from which Master MySQL database we should take binlog backup.

If application makes connection from Node 1 , then binlogs will be created on Node 1 and Node 2 will only recieved updates from Node 1 . Node 2 will not create any binlog for changes recoieved via replication and same is applicable for vice-versa.

One thing can be done is to see from which Node application makes connection and from that we can take binlog backup , that too is not reliable if application switchover just happened before backup was started.

If we take binlog backup on both databases, then to do Point-in-time recovery becomes tougher as it will not be sure which Node's binlog backup to use.

Most Likely Startegy
---------------------
If we use log_slave_updates=ON binlogs will be created on both Master MySQL database. We can take fullbackup and binlog backup on both Master MySQL database and we can use any Node's backup for Restore & Recovery.

Please let us know if my backup startegy is ok or provide your views on any other backup strategy.

Thanks,
Varun Datta

MySQL Scaleout Database Backup using SAN Storage Based Replication (Innodb Engine) (2 replies)

Sanjoy Dasgupta — Wed, 17 Apr 2013 08:22:09 +0000

Hi!

I plan to setup a MySQL Scale-out Multi-Node MySQL Database (using Galera - Multi-Master Replication);

Database for each node will use disks from a Common SAN Storage that supports storage based replication - Example EMC SRDF or 3PAR Virtual COPY, Hitachi Shadowimage.

I want to backup the databases using these "Storage based replication".

For a Point-in-Time consistent Replication of Production Disk volumes to Backup Disk volumes. If I am using Following Engines, how do I put database into a "Read-only" mode for the period the Storage replication is on-going ?

1. Innodb
2. MyISAM
3. NDB

Thanks in advance for any advice.

mysqlbackup backup one of databases and restore issue (2 replies)

Justin Xu — Fri, 19 Apr 2013 04:52:44 +0000

Hello,

In my MySQL server 5.5 there are 5 databases, I need to backup one of them and restore it in another new Mysql server.
$./mysqlbackup --defaults-file=/usr/local/mysql/my.cnf --user=root --password='p123456' --host=10.10.10.102 --include='db1\.*' --backup-dir=/data3/mysqldata_3/mysqlbackup1410 --only-innodb-with-frm backup-and-apply-log

Then transfer backup data files to new server ,run
-bash-3.2$ ./mysqlbackup --defaults-file=/usr/local/mysql/my.cnf --innodb_log_files_in_group=2 --backup-dir=/export/home/mysql/mysqlbackup04091122 copy-back

130409 14:02:11 mysqlbackup: INFO: Copy-back operation completed successfully.
130409 14:02:11 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql/data'

But when I start the new MysQL server, the error happened.
130409 14:02:54 InnoDB: Error: table 'db2012/tab1'
InnoDB: in InnoDB data dictionary has tablespace id 49947,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

there are lots of this kind of error for each table in original databases and tables , I tried to resolve it by the method in document ,but did not work. please give me an idea to resolve it.
Thanks a lot.

Help on backing up parititions (5 replies)

Frwa Onto — Wed, 17 Apr 2013 07:39:18 +0000

We have manage to come out with the script to both create and delete partitions as below. We need help on how to backup up those partition before we launch the delete script?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = 
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN  
    SET @stmt := CONCAT(
        'ALTER TABLE testPart1 DROP PARTITION '
    ,   ' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ) 
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;  
  END IF; 

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS 
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME = 
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN   
  SET @stmt := CONCAT(
        'ALTER TABLE testPart1 ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 2 
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 
  END IF;

Does MySQL Enterprise Backup support multi-streaming via SBT Interface?? (1 reply)

Sreekar Anugu — Wed, 03 Apr 2013 02:59:08 +0000

MySql Enterprise Backup 3.8.0 supports multiple threads. It has command-line options
--read-threads,--process-threads,--write-threads
But Does it support multiple threads via SBT interface?
When I tried to stream the backup , I got the info as:

mysqlbackup: INFO: Target image is not seekable and so resetting the number of write threads to 1.

How can I get multiple write threads. I couldn't find any information regarding this.

Backup Plan / flush-logs by database (5 replies)

steve ungar — Thu, 14 Mar 2013 03:48:36 +0000

I'm in the process of figuring out how to transition from daily full backups to weekly full backups and daily incremental backups. My plan is:

- weekly: mysqldump for each database (not using --all-databases)
- daily: mysqladmin flush-logs

Is that the proper procedure? If so, I could use some advice/feedback on the following:

* We have several databases that get backed up, and most of the time we only need to restore 1 (ie - screwed up data in 1 database as opposed to a disaster recover situation). It looks like the logs from flush-logs contain the changes for ALL databases. Is it possible to start from a full backup of 1 database and apply just the changes that affect that database?

* My plan is to archive off site both the weekly full backups as well as all of the daily logs. Am I being thorough or is it overkill to keep the daily logs once I have a complete full backup?

Any other advice?

Thanks for your help,
-Steve

MySQL Scheduled Backup Not Working (2 replies)

Cyl R — Thu, 14 Mar 2013 03:39:00 +0000

Hi,

We just migrated our servers from a physical server to a cloud platform.
Before migrating, the scheduled backup works perfectly.
When it has been migrated to cloud, the backup is not running.
I can actually create a schedule but the only problem is, it's not running.
OS: Windows Server 2008.
The manual backup works.

I believe that when you schedule a backup, a task is also created.
I am just thinking that the reason why the backup doesn't work in cloud is because no task has been created automatically.

Hope someone can help me with this kind of issue.

how can I prevent the password from being listed in mysqlbackup output (1 reply)

John Sebastian — Sun, 24 Feb 2013 02:12:55 +0000

I issue the following mysqlbackup command:

mysqlbackup --user=$USERNAME --password=$PASSWORD --backup-dir=$BACKUPDIR --with-timestamp backup-and-apply-log>$LOGFILE 2>&1

The output log file has an INFO section and it shows the password used in clear text. See clip from output of the above command below:

MySQL Enterprise Backup version 3.8.0 [2012/07/27]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.

mysqlbackup: INFO: Starting with following command line ...
mysqlbackup --user=eiadba --password=realpasswordshown
--backup-dir=/u02/mysql_backups --with-timestamp backup-and-apply-log

I'm not seeing a switch to turn off this behavior. Maybe I've not looked hard enough.

Can anyone comment?

Help for restore (2 replies)

John Sebastian — Fri, 15 Mar 2013 08:26:29 +0000

Hello all,

I'm a newbie to MySQLEnterprise Backup.

I have a test database that I have backed up using the following command:

mysqlbackup --backup-dir=/u01/mysql_backups --with-timestamp --user=uname --password=pwd backup-and-apply-log

The backup ran successfully.

I want to test a restore. So I deleted the data files for the database that were backed up. There are datafiles and innodb files that I deleted with an OS command after shutting down the mysql instance.

From /etc/my.cnf:
datadir=/u01/mysql_data_files
innodb_data_home_dir = /u01/mysql_innodb_data_files

I deleted all files in /u01/mysql_data_files and /u01/mysql_innodb_data_files

Now, I'm trying to restore the database from the backup.

The files were backed up to a directory named:
/u01/mysql_backups/2013-02-11_08-53-47 becuase I used the --with-timestamp parameter

Here is the restore command I am attempting and the errors I am receiving:

mysqlbackup --defaults-file=/u01/mysql_backups/2013-02-11_08-53-47/backup-my.cnf --backup-dir=/u01/mysql_backups/2013-02-11_08-53-47 copy-back
MySQL Enterprise Backup version 3.8.0 [2012/07/27]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.

mysqlbackup: INFO: Starting with following command line ...
mysqlbackup
--defaults-file=/u01/mysql_backups/2013-02-11_08-53-47/backup-my.cnf
--backup-dir=/u01/mysql_backups/2013-02-11_08-53-47 copy-back

IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back' run mysqlbackup
prints "mysqlbackup completed OK!".

mysqlbackup: ERROR: Missing datadir parameter for server repository.
mysqlbackup: ERROR: Server repository configuration problem found.

I'm curious about the --defaults-file parameter, should that be the backup copy of the my.cnf file which the backup made? It currently resides in the backup directory /u01/mysql_backups/2013-02-11_08-53-47 and is named backup-my.cnf

Second, the --backup-dir parameter, should that include the entire name of the backup directory right down to the /u01/mysql_backups/2013-02-11_08-53-47 part as shown above or just the main directory that I used when issuing the bacup command /u01/mysql_bacukps?

Can somone give me an idea what the two errors are above and how to get around them to restore the database?

My /etc/my.cnf file has the following

[mysql@eiawwwdb01 mysql_backups]$ cat /etc/my.cnf
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir=/u01/mysql_data_files

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=/u01/mysql_binlog_files/mysql-binlog

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /u01/mysql_innodb_data_files
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /u01/mysql_innodb_data_files
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_support_xa = 1

FREE! MySQL Health Check (no replies)

Edwin Desouza — Fri, 08 Feb 2013 04:17:44 +0000

FREE! MySQL Health Check
https://www.mysql.com/news-and-events/health-check/

Error 1062 while restoring backup (5 replies)

Subin S — Thu, 14 Mar 2013 05:38:55 +0000

Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.

Requirement: To create an exact replica 'db4' of an existing DB 'db3'. This is a Request Tracker (RT) database that I'm trying to replicate on the same server.
MySQL version- 5.0.51a-24+lenny3 on Debian Lenny. Basically this is a test environment and but meets all the requirements for successful RT installation and functioning.

Procedure followed:

1. Shutdown webserver to prevent any access to database.
2. mysqldump -uuser -ppass db3 > db3.sql (size is 6G)
3. mysql -uuser -ppass db4 < db3.sql (db4 was a newly created blank database)

The 3rd step throws in the error:

ERROR 1062 (23000) at line 5524: Duplicate entry '600806' for key 1"

I ran the 2nd step again with --force. The restore completed but with 2 additional similar errors:

ERROR 1062 (23000) at line 6309: Duplicate entry '187694' for key 1
ERROR 1062 (23000) at line 6572: Duplicate entry '1567400' for key 1

Obviously the restored database reported missing records.

A "sed -n 5524p db3.sql" showed the "INSERT INTO `Attachments` VALUES ...' query.
Similarly for line numbers 6309 and 6572 "INSERT INTO ..." was reported for
tables 'Tickets' and 'Transactions'. The three tables are Innodb with all the
three having an 'id' column set as PRIMARY KEY with AUTO INCREMENT.

Question:

Does this indicate a corrupted/problematic db3 database?

How to proceed to create a 'consistent/working' replica (db4) of db3?

Thanks,

MYSQL Enterpise Backup- Identifying Binlog position (1 reply)

Varun Datta — Thu, 10 Jan 2013 10:51:36 +0000

Hi,

We are exploring MYSQL Enterprise Backup. We do not have support right now.

I have one query if any one can answer.

If we are taking full backup using below command:-

mysqlbackup --port=3306 --protocol=tcp --user=root --password=mysql123 --backup-dir=/MYSQL/backup/master_backup backup-and-apply-log

Then how do we identify till which binary log file and position the above backup contains data.

I need this information to be extracted from backup so that i can save this information which will help in Point-in-Time Recovery.

If i do flush tables with read lock before i take backup, my backup fails, so i do not know how should i get the binlog file and position till which i have data restored from backup.

Quick reply will really help!!

Thanks,
Varun Datta

Recover Data from iPhone 4& iTunes backup (5 replies)

thanker show — Thu, 25 Apr 2013 16:29:42 +0000

World’s 1st iPhone, iPad & iPod Touch data recovery software for personal users,Full recovery of contacts, SMS, photos, etc. under various situations
iPhone Data Recovery is an easy-to-use iPhone data recovery tool that can help you easily recover iphone photos, videos, contacts, SMS, call logs, memos and calendars and more! You can use this iPhone data recovery software to recover data directly from iPhone 4 or iphone 3GS and recover data from iTunes backup.this is an easy-to-use iPhone data recovery tool that can help you easily recover photos, videos, contacts, SMS, call logs, memos and calendars and more! You can use this iPhone data recovery software to recover data directly from iPhone 3GS and recover data from iTunes backup.

Key features:
1.Recover deleted data without backup directly from iPhone 4/3GS, iPad 1 & iPod Touch 4
2.Regain previous data from iTunes backup for all iOS devices, including iPhone 5/4S, etc.
3.Save data after your device is crashed, smashed, broken or lost;
4.Retrieve data after Jailbreak, iOS upgrade, factory settings restore.

Point-In-Time Recovery using mysqlbinlog (3 replies)

Varun Datta — Fri, 04 Jan 2013 01:46:45 +0000

FOR POINT-IN-TIME Recovery , After we have restored the backup, we know the Binlog and position till which data exists in backup.

Doing Recovery using mysqlbinlog, what should be the position to be supplied mysqlbinlog command ?

mysqlbinlog binlogfile --start-position= ??

Backup using mysqldump and restore dump in ndbcluster environment shows errors while restoring system database 'mysql'. (3 replies)

piyush nagar — Sat, 29 Dec 2012 19:54:06 +0000

Hi ,

I have very urgent questions related to backup/restores of databases in a node of ndbcluster environment.

I used mysqldump to backup all the databases. it got successful. Now when i try to restores those db dumps, i see the error in restoring 'mysql' system database.

here are the results:

[root@IfxLinux mysql-cluster]# mysql < /tmp/my.sql
ERROR 1036 (HY000) at line 386: Table 'ndb_apply_status' is read only

error says 'ndb_apply_status' table is read only and restore halts.

even dropping 'mysql' database shows error:
system command = /usr/bin/mysql -u backup --socket=/var/lib/mysql-cluster/mysql.sock -e 'FLUSH PRIVILEGES;drop database `mysql`;' out err = ERROR 1051 (42S02) at line 1: Unknown table 'ndb_apply_status'.

Please help and advise how to resolve this. We are able to restore all other databases apart from 'mysql' database from a dump. Is there any way to drop 'ndb_apply_status' table and recreate it manually later after restore completes?

#Note: we don't want to use start backup and ndb_restore programs.

Since we are in urgency.
Any kind of help is appreciated.

Reading binlog to Identify Start time and End time of Binlog (1 reply)

Varun Datta — Fri, 21 Dec 2012 23:06:38 +0000

Hi,

How can i read the start time and End time of from binlog file, preferrably using mysqlbinlog utility.

Thanks,
Varun Datta

Running backup in cron without clear text pw (1 reply)

Lee Murray — Sun, 23 Dec 2012 16:21:27 +0000

I am a long time Oracle DBA just now getting involved in supporting MySQL. I am use to in the Oracle RDBMS world on Unix of being able to run RMAN backups via script in cron where connection to the database can be made via "/ as sysdba" (or more correctly "rman target /") when running under the "oracle" Unix account not requiring an embedded clear text password. With MySQL and using the standard mysqldump it appears the best one can do is put username/pw of the user used for backup in the my.cnf file under [mysqldump] and set permissions to 600 for that file. Am I missing anything? Are there other backup tool options like MEB or XtraBackup that would provide a means to not reveal a password in clear text for the backup user? What would the gurus on this forum suggest for a production system?

My first system will be fairly small single MySQL database using the MyISAM engine. It does not get used (or will not) after business hours. Thanks in advance for your help.

Re-directing Logs of mysqlbackup (2 replies)

Varun Datta — Tue, 11 Dec 2012 11:42:48 +0000

Hi,

How can we redirect logs generated by mysqlbackup to specific Logfile.

I tried it using below mentioned method but with no luck.

mysqlbackup --port=3306 --protocol=tcp --user=root --password=mysql123 --backup-dir=/MYSQL/backup/master_backup backup-and-apply-log | tee -a backup.log

mysqlbackup --port=3306 --protocol=tcp --user=root --password=mysql123 --backup-dir=/MYSQL/backup/master_backup backup-and-apply-log > backup.log

Please assist.

Thanks,
Varun Datta

MYSQL Backup on SLAVE (4 replies)

Varun Datta — Sun, 23 Dec 2012 16:35:01 +0000

Hi,

We have a Master-Slave Replication Setup.

We want to take daily full backup on Slave and binlogs backup from Master.

In case of failure of Slave, Master will Starting taking full backup and backup of binlogs.

Now, in order to take consistent backup at Slave we are stopping Slave to process any data from Master and then take backup using MYSQL Enterprise Backup 3.8 and then Start Slave process again.

Command used:-

mysqlbackup --port=3306 --protocol=tcp --user=root --password=mysql123 --backup-dir=$BACKUP_DESTINATION

Please confirm whether we should use backup or backup-and-apply-log for

Our requirement is that Backup should be consistent and should be restored easily using copy-backup option.

Please confirm which type of backup to be used.

Thanks,
Varun Datta

how to create .sql file in mysql (1 reply)

chetak patil — Sat, 08 Dec 2012 22:57:29 +0000

how to create .sql file in mysql

Advise required on backup plan (4 replies)

Jim Buckley Barrett — Mon, 26 Nov 2012 09:23:08 +0000

Hi,

Currently our database is 9GB and growing around 1GB a month. All the tables are MyISAM and our largest two tables have over 16 million records. We do a lot of INSERTS and UPDATES and very few deletes.

We’re currently using MySQLDump to create a midnight backup of the database but we are having issues with that recently which is causing our application to slow down considerably during the back up.

What I would like to do is create a full daily back up with differential backups as well as weekly and monthly backups - your normal backup schema, 6 days, 5 weeks, 12 months.

I've been considering replication but I don’t know for sure if I really need that.

So I’m looking for advice on what backup software to use?

Should I look at replication?

Should I be saving my backups to a separate drive to help with READ/WRITES?

Should the database be on its own drive for READ/WRITES?

Thanks,
Jim

Industrial-Strength MySQL Applications Using Percona and Continuent (no replies)

Petri Virsunen — Tue, 06 Nov 2012 23:14:29 +0000

Webinar, Wednesday 11/28
http://www.percona.com/webinars/industrial-strength-mysql-applications-using-percona-and-continuent

MySQL is the first choice for a large majority of web applications thanks to its ease of use and low cost of operation. However, running big apps on MySQL is still a challenge even for experts. In this webinar we will show you how to combine Percona Server and Percona XtraBackup with Continuent Tungsten to build business-critical systems capable of processing hundreds of millions of transactions per day with no downtime. We'll cover best practices from hardware configurations and MySQL tuning to HA and integration with non-MySQL systems. We will also survey technologies from Percona and Continuent that work together to help you go big on MySQL. Come learn from the foremost experts in the field of building high-performance MySQL applications!

Presenters:
- Peter Zaitsev, Founder, CEO
- Robert Hodges, CEO of Continuent

Register at http://www.percona.com/webinars/industrial-strength-mysql-applications-using-percona-and-continuent

LOCK TABL errors with mysqldump (explicit socket file) (no replies)

Tim Johnson — Tue, 06 Nov 2012 00:15:37 +0000

Using mysql 5.5.21 on Mac OSX 10.7
I'm attempting to backup a drupal database using mysqldump.
In my case drupal uses a separate datadir, socket file and option file,
and it's own mysql listening on port 3307.
I can access the drupal database from the command line using

mysql -hlocalhost -uroot -psecret -P3307 -S/Applications/drupal-7.15-0/mysql/tmp/mysql.sock

However, when I attempt to dump the drupal databases, I get errors. The
following console session attempts to dump each database separately :

linus:prj tim$ mysqldump -hlocalhost -uroot -psecret -P3307 -S/Applications/drupal-7.15-0/mysql/tmp/mysql.sock test >drupal-test.sql
linus:prj tim$ mysqldump -hlocalhost -uroot -psecret -P3307 -S/Applications/drupal-7.15-0/mysql/tmp/mysql.sock information_schema >drupal-information_schema.sql
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
linus:prj tim$ mysqldump -hlocalhost -uroot -psecret -P3307 -S/Applications/drupal-7.15-0/mysql/tmp/mysql.sock bitnami_drupal7 >drupal-bitnami_drupal7.sql
linus:prj tim$ mysqldump -hlocalhost -uroot -psecret -P3307 -S/Applications/drupal-7.15-0/mysql/tmp/mysql.sock mysql >drupal-mysql.sql
linus:prj tim$ mysqldump -hlocalhost -uroot -psecret -P3307 -S/Applications/drupal-7.15-0/mysql/tmp/mysql.sock performance_schema >drupal-performance_schema.sql
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

So, I don't understand the error messages. What do I need to do to
effect this dump successfully?
thanks
tim

Need help with a backup plan (3 replies)

Dave S — Wed, 07 Nov 2012 06:32:08 +0000

Hi all. I need some advice on a backup plan for my business, as it's growing rapidly and my data storage needs are getting kinda hairy. The particulars are:

- hosted with a fairly reputable host, on a VPS plan
- the mySQL db currently has about 2.2 million rows in the largest table
- the db is basically write only. No rows are ever deleted. The largest table is only added to, and others get updated often, but added to slowly. We're getting to the point where 1000 inserts a day might be possible.
- the last backup I did through cPanel's Backup Wizard was about 98MB as a .gz file
- we're using myISAM tables

One problem I'm noticing is that using the Backup Wizard in cPanel seems to adversely affect the site's operation, and some transactions are timing out while the file is downloading. I'm guessing there's some locking going on, or something.

If it affects a good backup plan, I would be very happy to consider including a culling/archiving strategy, where I only allow my customers (say) 6 months of transaction history (i.e. the data stays in the live tables) and anything older gets automatically archived and would have to be requested specially. (I bring this up because 2.2 million rows on a live table, added to daily, makes me a bit nervous! Should it?)

So I'm looking for some ideas on how to handle this amount of data. I'm sure lots of people must have more, but with the biz growing, and more/larger customers coming online, the rate at which data is getting added is increasing, so I need to start making plans :)

Any links, advice, anecdotes, or other info would be very much appreciated.

Thanks in advance,
Dave

mysql dump failure (1 reply)

jim longo — Wed, 24 Oct 2012 04:38:01 +0000

after upgrading to mysql 5.5.27 my cron jobs are failing with an error

CRON:
$command = "mysqldump --add-drop-table -u $dbuser --password=$dbpwd $dbname | gzip > $filename";

ERROR:
mysqldump: unknown variable 'default-storage-engine=MyISAM'

here is the top section of /etc/my.cnf
[mysqldump]
quick
max_allowed_packet=16M
default-storage-engine=MyISAM
innodb_file_per_table=1

Thanks for any help.
jim

percona innobackup failed (2 replies)

Mannoj Kumar — Sun, 21 Oct 2012 23:02:32 +0000

I'm trying to get percona innobackup but I'm struggling with this since 4hrs. Tried all possible ways from many forums. interms of changing my.cnf, parsing arguments but still not able to get this fixed. Have any one fixed this before.? Please help!

sh: xtrabackup_51: command not found
innobackupex: fatal error: no 'mysqld' group in MySQL options

mysqldump failing when dump routines if database name contains special characters (no replies)

Ravi Thati — Tue, 16 Oct 2012 12:49:55 +0000

I have a database with name ';havesinglequotes' (single quote at begin and end + a semicolon in it).

when I dump the database with below command it fails with error 1049 while dumping routines.

mysqldump --single-transaction --flush-logs --master-data=2 --create-options -B --default-character-set=utf8 --routines --max_allowed_packet=41943040 -u root --socket=/tmp/mysql.sock "';havesinglequotes'" -p
Enter password:
-- MySQL dump 10.13 Distrib 5.5.27, for linux2.6 (x86_64)
--
-- Host: localhost Database: ';havesinglequotes'
-- ------------------------------------------------------
-- Server version 5.5.27-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000093', MASTER_LOG_POS=107;

--
-- Current Database: `';havesinglequotes'`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `';havesinglequotes'` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `';havesinglequotes'`;

--
-- Table structure for table `tb1`
--

DROP TABLE IF EXISTS `tb1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb1` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tb1`
--

LOCK TABLES `tb1` WRITE;
/*!40000 ALTER TABLE `tb1` DISABLE KEYS */;
INSERT INTO `tb1` VALUES (2);
/*!40000 ALTER TABLE `tb1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database '';havesinglequotes''
--
mysqldump: Couldn't execute 'use `\';havesinglequotes\'`': Unknown database '\';havesinglequotes\'' (1049)

Could anyone advice how to resolve this?

Thanks in advance.
Ravi.