MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
1answer
23 views

Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?

Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space. Have option to either optimize the table, or dump the table and restore ...
8
votes
2answers
11k views

Taking MySQL dump from another machine

I have a MySQL db on Machine A and I want to access the mysqldump from machine B. Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What ...
1
vote
1answer
132 views

MySQL Workbench can either Dump or Restore - How Do I Configure To Do Both?

I have previously posted this question on Stack Overflow, but have not solved the problem. Hopefully it is not incorrect to post it here in the hope that it will be seen by a some different ...
76
votes
7answers
47k views

How can I monitor the progress of an import of a large .sql file?

I am importing a 7 GB foobar.sql to restore a table in a local database. $ mysql -h localhost -u root 'my_data' < foobar.sql $ mysql --version /usr/local/mysql/bin/mysql Ver 14.12 Distrib ...
0
votes
1answer
25 views

incompatibility of max_allowed_packet in mysql

I have a client and I want to dump a row such as : $ mysqldump -uroot -p pahlevan_azad mp_posts --where="id='655'" --max-allowed-packet=1024 > dump.sql Warning: option 'max_allowed_packet': ...
1
vote
2answers
57 views

Why database size is smaller in RDS

I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer. I use this query: SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + ...
1
vote
1answer
3k views

Extremely slow mySql restores

Please bear with me as I ask this question, which looks similar to questions already on DBA@SE. In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database ...
1
vote
1answer
56 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
3answers
38 views

Should a MySQL slave used for backup also be used to service read-only requests in a production environment?

Based on best practices, I'm wondering if we should have at least one slave that's used solely as a backup server, without servicing any forward-facing requests from users. For example, if we have a ...
0
votes
0answers
18 views

MySQL replication Got fatal error 1236

I am getting this error when starting a new slave replication from master. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded ...
0
votes
1answer
59 views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql ...
0
votes
1answer
20 views

Lost connection to mysql while restoring dump. Is it safe to resume?

I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS. The dump was created with the following command: mysqldump --all-databases \ --single-transaction \ ...
0
votes
1answer
15 views

Mysql Replication backup and restoration failed multipal time

I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason. I have 2 host Master & Slave with below details. Server1_Master os-centos6.7 mysql- ...
2
votes
1answer
521 views

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES I am able to loging properly and able to take full backup from SQLyog Tool ...
0
votes
1answer
354 views

Operation failed with exitcode 1 - while importing from a dump folder

I'm new to dbs and I'm trying to learn how a certain db works. They gave me a dump folder to play with it but, after setting up a connection (works), while trying to import, I get this annoying ...
0
votes
1answer
177 views

mysqldump is not dumping data by default

mysqldump is dumping only the table structures, without data, by default. mysqldump -u user -d database -p Which is the option to dump is with data? How can I configure it, to include the data? (I ...
0
votes
1answer
33 views

How to import a single table in Windows environment

I am running Windows 7 with xampp. I can easily import a database with the following command: mysql -u user -p database < backup.sql The problem is that I have a database around 8.5 GB, which ...
0
votes
1answer
163 views

Shell script for partial table backup

I have multiple database in my MYSQL server and i want to take some particular tables backup from each database. can anyone help me in writting a shell script for this scenario.
5
votes
4answers
13k views

Error while restoring a Database from an SQL dump

I am extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error: $ >mysql -u root -p -h localhost -D database ...
3
votes
1answer
301 views

Dump with special characters in table column name

I'm taking over an old database, and while doing recovery tests from my dump I found out a table is wrongly backed up, the table definition is truncated. Looking at the live db I've found a column ...
1
vote
3answers
222 views

Using mysqldump on RDS to export a large file in TSV format

I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica. However, RDS doesn't provide a way to use ...
0
votes
1answer
397 views

Setting MySQLDump to keep running in the background after session times out

I'm tryng to set up a new Slave DB by copying another Slave DB. Normally I would simply use MySQLDump to dump the Slave and create a .sql file, then import that into the new database. However, as our ...
0
votes
1answer
159 views

Are certain environment variables required for mysql login-path?

I created a bash script that backs up my database, db_backup.sh It basically just runs these commands: DB=/home/linuxuser/backups/backupfilename.sql.gz mysqldump --login-path=local db_name | gzip ...
28
votes
8answers
105k views

MySQL DB import/export command line in Windows

How to export / import a database in MySQL through the command line? MySQL system configuration, WampServer server installed. OS: Windows The advice for my local host, not an live hosting...
0
votes
0answers
29 views

mysqldump resulting in empty file

I keep getting empty files generated from running $command = 'mysqldump -uroot -pmysql pakaranak tamp2 > mydb_tables.sql'; exec($command); I'm using phpMyAdmin Version 2.10.3. So, any ideas?
0
votes
1answer
114 views

mysql database migration of schemas with updated data and structures

I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema. Assuming the source schema is defined by ...
3
votes
0answers
41 views

mysqldump with --skip-grant-tables

It appears the files in my /var/lib/mysql/mysql are borked, and I can only start mysqld with --skip-grant-tables. Now I want to export all tables with mysqldump --all-databases, wipe and reinstall ...
3
votes
1answer
25 views

missing frm files for restore

We backup a database daily with mysqldump using the following options: --quick --hex-blob --lock-tables --skip-extended-insert --flush-logs --max_allowed_packet=500M Each database gets its own ...
0
votes
1answer
198 views

Mysqldump without slow query

Situation: I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query. Question: ...
3
votes
2answers
7k views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
3
votes
2answers
72 views

How to merge MySQL backup files (*.sql)

We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
0
votes
1answer
31 views

error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

I'm trying to load mysqldump and I keep getting following error: ERROR 2013 (HY000) at line X: Lost connection to MySQL server during query /etc/my.cnf: [mysqld] max_allowed_packet = 16M ...
0
votes
0answers
17 views

How to merge MySQL backup files (*.sql) [duplicate]

We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
0
votes
0answers
42 views

mysql optimize table and mysqldump

I have a dbserver with about 1.000 databases. This year (2015) we didn't do anything about table optimizing at all. Also our had an uptime for almost a year (338 days). Our backup script with ...
0
votes
0answers
20 views

Dumping data to a CSV file

I am trying to export mysql data to a csv file and before I execute the command, I'd like to make sure the outcome as intended. If I run, mysqldump -u user -ppassword --no-create-info --tab=c:/temp ...
0
votes
0answers
20 views

Dreadful encoding issue

I've got several Drupal sites, and all their tables are latin1 and collations are latin1_swedish_ci. Recently, I wanted to shrink my ibdata1 so I proceeded to read this answer and I dropped my tables ...
1
vote
3answers
5k views

How do you encrypt data while performing a mysqldump?

My OS is windows 7. How can I encrypt data while performing a mysqldump? From the command prompt, can I take a mysqldump with encrypted varchar and texts values? Please help me. Thanks
0
votes
1answer
47 views

Mysqldump.exe Unknown option --no-beep

When I try to run the command : "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqldump.exe" -u root -p******* my_database > backups/my_database_backup_20151221121320.sql it says ...
5
votes
5answers
15k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
4
votes
2answers
2k views

ERROR 1166 (42000) at line 65203: Incorrect column name ''

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message: $ mysql -u root < mydata.sql ERROR 1166 (42000) at line 65203: Incorrect ...
1
vote
1answer
13 views

Move data from mysql 4.0 to 5.6 without corrupting encoding

I have a old mysql database that I would like to move to a recent version of mysql and optimize for SELECT ... WHERE ... statements. It is going to be a SELECT only database. Original database uses ...
1
vote
1answer
32 views

How to reset slave without over write of master dump in mysql

My master having position is File Position mysql-bin.000046 430674598 I lost my data dir of mysql on master after that I have backup of slave which restored in master.Now Both ...
4
votes
2answers
56 views

MySQL for Oracle DBA

I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform. 1) In Oracle we have to ...
0
votes
1answer
35 views

Point in time restore MySQL

I want to perform a point in time restore on MySQL. I want to change log_bin value and ensure that log files are created. I am following this article and am working on a Windows platform. Log ...
1
vote
2answers
296 views

Recommendation for the mysqldump?

I have approx 90 GB of database, I need to schedule a mysqldump backup , thats why I Required a recommendation how much time it takes for the full hot backup? Because I didn't taken that much size of ...
0
votes
1answer
39 views

Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs). I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those ...
4
votes
2answers
12k views

Problem with mysqldump and view

When using mysqldump to backup MySQL, I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
0
votes
1answer
21 views

Continuously backup/restore selected tables

I'm backing up production data locally to perform experiments and investigate production bugs. I need only a handful of tables from product DB. I'm using generic mysqldump and now facing a problem ...
0
votes
1answer
23 views

Restore of database using Mysql DUmp

How would i verify that whatever the backup i have taken is correctly restored or not. EG: I am taking backup on live system where inserts are going on.backup size is around 50 GB.I have started the ...
0
votes
0answers
36 views

My DB restarts again and again

MySQL keeps restarting and logs keep giving this errors InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd ...