MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

2
votes
1answer
216 views

How to make sure that mysqldump command is working in command prompt

I'm getting this error: C:\Users\IT-Admin>mysqldump 'mysqldump' is not recognized as an internal or external command, operable program or batch file.
2
votes
1answer
67 views

Strategy for setting up a 60gb MySQL database from a dump?

I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been ...
2
votes
2answers
236 views

An easy way to select one db dump from multiple db MySQL dump

In an urgent situation another person made a backup of all the databases from my webserver to one *.sql file (MySQL dump). Now I have a ~700 Mb file with about 20 different databases. As I only need ...
2
votes
1answer
411 views

Is it possible to gain the precise position of a live mysql database when performing full backup

I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups. The production server is on the west coast while business hours are ...
2
votes
1answer
295 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: ...
2
votes
1answer
467 views

Consolidated realtime mysql backup with master/slave setup or binlog

I have 20+ mysql servers and their have low write requirements. So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose. If I ...
2
votes
1answer
511 views

mysqldump―output details

I am about to write a little program to process database dump files created by mysqldump and I would like to make sure that my expectations about the »anatomy« of mysqldump's output are right. So ...
2
votes
4answers
571 views

Moving large databases

I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space). Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need ...
2
votes
1answer
1k views

Table optimize command crashed MySQL server

I have a large 5GB table on one of my MySQL databases that I wanted to optimize. I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a ...
2
votes
1answer
330 views

MySQL Dump all rountines except one

We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
2
votes
1answer
453 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
2
votes
1answer
558 views

How to prevent high memory, CPU and time consumption by MySQL restore?

I have a local test machine with 2 GB RAM and a dual core processor. I imported a dump on that using mysql -uuser -psecret < script.sql script.sql is a 700 MB file which contains a single ...
2
votes
2answers
127 views

Database Files-Related Issue

I am using MySQL database and using Ubuntu linux machine. I noticed that my database, db_test, is under the path /var/lib/mysql/db_test There are files with the suffix .frm, .MYD, .MYI like the ...
2
votes
1answer
5k views

When are we supposed to set default-character-set for the client?

I'm trying to understand when I'm supposed to set/force default-character-set for the client. The documentation states: You can force client programs to use specific character set as follows: ...
2
votes
1answer
40 views

Trying to restore SQL file from mysqldump, results in syntax error

I ran the following sqldump call in a MySQL 5.7 instance (Windows OS): mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --routines -A > ~/dump.sql I ...
2
votes
2answers
39 views

How can I generate the code-containing comments like mysqldump?

I'm needing to duplicate the mysqldump command in Python, and I am trying to figure out how automatically generate the code comments like mysqldump generates. If you're wondering why I am doing such ...
2
votes
0answers
186 views

mysqldump restore error

I am getting the error below, while restoring a dump to RDS MySQL 5.6. Dump is taken from MySQL 5.5 originally. I tried restoring twice but same error. Can anyone help ERROR 1064 (42000) at line ...
2
votes
1answer
390 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
2
votes
0answers
836 views

How do I safely import MySQL data in a directory to a running MariaDB server?

We're integrating another office into ours. That includes some of their data. I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
2
votes
1answer
637 views

How can I restore a single record from a MySQL backup file?

Occasionally we have an issue where a record's previous data might get wiped while editing. We backup our database every night for issues like this, however sometimes I am not available to do a ...
2
votes
1answer
3k views

importing myisam 5.0 database into a 5.5 innodb server

I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
2
votes
4answers
2k views

Getting 0KB of size file while backup mysql database using mysql dump [closed]

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions? $dbhost = 'localhost'; $dbuser = '...
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
1
vote
4answers
403 views

How to make a remote mysqldump?

I know that's it's possible to do a remote mysqldump if I specify --host but is it possible to create the file in the remote server as well?
1
vote
3answers
81 views

MySQL backup breakes a table every time

I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS. Every night we do a backup of all the databases with this script: rm -rf /var/backup/mysql/ * for i in $(echo 'SHOW DATABASES;' |...
1
vote
2answers
68 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
48 views

How can I do an incremental `mysqldump` without dropping table?

I'd like to fetch the last 100 rows of a table via a mysqldump, but I already have the rest of the table. How can I only fetch the last 100 rows for a mysqldump and then replace (or overwrite) any ...
1
vote
1answer
4k views

What are the optimal mysqldump settings?

After some search I ended up with the following settings: mysqldump\ --host=localhost\ --port=3306\ --databases ****\ --user=****\ --password=****\ --default-character-set=...
1
vote
1answer
445 views

Why does importing a MySQL dump cause so many I/O?

I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many I/...
1
vote
1answer
80 views

Where Mysqldump gets data from

How mysqldump is processing backup? From which all files is it reads data for creating dump? Is the process differs if storage engines are different?
1
vote
1answer
649 views

Unable to use mysql_upgrade:

I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says- mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
1
vote
1answer
3k views

passing --single-transaction in mysqldump command line tool

I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup. I have however, noticed that if I backup using command line ...
1
vote
3answers
17k views

importing MySQL dump to SQL Server 2012

I have a database schema from MySQL which I want to import to SQL SERVER, I have tried dumping from MySQL with compatibility for SQL Server, but with no success. Tried dumping as Excel still no dice. ...
1
vote
3answers
4k views

mysql duplicate entry error 1062 when restoring backup

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'. Procedure ...
1
vote
4answers
4k views

How to do a mysqldump with a use database in the dump

So the command I normally use to get a database sent to another server is: mysqldump -u user -p --add-drop-tables database *file* But I always have to go into the file and add a line at the top: use ...
1
vote
2answers
3k views

Restore data from remote server using “.sql” file

I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file (...
1
vote
1answer
36 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
1
vote
1answer
86 views

InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0. We decided to change ...
1
vote
1answer
100 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 ...
1
vote
1answer
58 views

How can i make dump of big mysql table? [closed]

I have server with db(36Gb data), only mysql access. When i'm trying to use mysqldump utility, i get connection timeout(or something like this). I doesn't have ssh access for copy /var/mysql/data. It'...
1
vote
1answer
1k views

WARNING Error checking mysqldump version Error retrieving version from mysqldump.exe: mysqldump: unknown option '--no-beep' (exit 2)

When i go to do a manual export from mysql database to workbench, this error occurs, i dont know solve
1
vote
1answer
87 views

mysqldump - select table in database only if it contains a specific string

(I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right ...
1
vote
1answer
63 views

Mysql dump with View dependent on user

I use simple mysqldump with only parameters are login and db details. No option of anykind is set. Now, when I recieve the dump file, it export the view in steps (I am sure you all know it, but just ...
1
vote
2answers
471 views

mysqldump with automatically generated export name

When I do database dumps through MySQL WorkBench it picks a filename for me which is the current date (YYYMMDD). Now, I want to do this in a batch file since I want to make weekly dumps automatically ...
1
vote
1answer
814 views

MySQLdump on specific db date

I'm new in here and i don't really know where to start but here is my concern. I need to backyp a MySQL DB running under windows once a week. For e.g every friday i need to backup and transfer to ...
1
vote
1answer
2k views

mysqldump: replace drop table in output with delete?

I am using mysqldump to dump some records in a table: mysqldump mydatabase mytable --where="field=value" this emits script that includes drop table mytable statement. Since I use the --where ...
1
vote
1answer
4k views

How to copy MySQL Database from Linux to Windows?

I would like to copy a MySQL database form Linux to Windows, from a little research I found this url http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html where the author says we ...
1
vote
1answer
184 views

Creating dump file with use command

I am taking backup of 2 tables from different schema. I need to create the "use dbname" statement at the beginning of each table. mysqldump --databases company --tables loadme --databases new_company ...
1
vote
2answers
233 views

Copying a database for testing in a safe way

For testing purposes I have to create a copy of a production MySQL database that cannot be touched. These are the steps I would follow: Create a reduced size database backup: mysqldump prodDB --...
1
vote
1answer
95 views

mysql server password change while restoring

I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to ...