MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

0
votes
1answer
116 views

Consistent backup of large DB --single-transaction

I need to mysqldump a large DB (330 GB) in order to set up a slave for replication. Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the ...
1
vote
1answer
74 views

mysqldump not exporting all tables

I have a Drupal site with a fair amount of content, about 12G in size, with 500+ distinct fields making for almost 1400 tables. We're migrating to a new server environment so I need to be able to ...
0
votes
1answer
36 views

mysql diff and add diff records into new database

I have two database on two different hosts: host A has database A and table A and host B has database B and table B and now "host A/database A/table A" table A contains records with primary key ...
0
votes
0answers
78 views

Database as it is raw content

recently I asked my hosting company to give the backup of my database . I asked them to create a new database and import backup to that database. But they reply cannot dump database ...
0
votes
1answer
307 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 ...
1
vote
1answer
46 views

Incorrect backup size

We are storing our everyday full backup in remote storage. Everything seems to be working fine, but every seven day interval the archived folder size is much more than the everyday archived backup ...
0
votes
1answer
39 views

How speed up mysql dump without data?

There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in ...
190
votes
4answers
361k views

How do you mysqldump specific table(s)?

How can I dump a specific table or set of tables without including the rest of the db tables?
0
votes
1answer
213 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 &...
0
votes
0answers
26 views

Import MYSQL DB scripts to MSSQL

Please suggest how to restore Mysql DB to SQL server 2008. I have backup script file taken from MYSQL (.sql file) 50gb in size and needs to be restored to SQL server. I have tried using sqlcmd ...
0
votes
1answer
17 views

Copying mysql data from one db to another with a where clause for a subset of tables

I'd like to be able to move mysql data from one db to another. I only want to move records where account_id=1234 except for tables like account which does not have an account_id column. Is there any ...
0
votes
0answers
11 views

MySQL disk usage due to failure on dump import

I've just SCPed a 400 MB database from a live server. I dumped it with mysqldump and gziped it as following: mysqldump --single-transaction=TRUE -u dbuser -p dbname | gzip> /home/user/database.gz ...
1
vote
0answers
27 views

MySQL master to slave dump fails when done over SSH pipe

When we need to [re]build a MySQL-5.5 slave, our usual approach is this (CHANGE MASTER TO / START SLAVE omitted for clarity): master$ mysqldump -uroot -p --all-databases --master-data > dump.sql ...
1
vote
1answer
69 views

MySQL Database import into 5.7 hangs with CLI or with Workbench 6.3 CE

I have a SQL dump file that I exported using Workbench from a server that is being decommissioned. The host and target OS is Microsoft Windows Server 2008 R2 (therefore is x64 natively). There seems ...
0
votes
1answer
133 views

MySQL dump with low disk space

I have an asterisk PBX that has call recordings stored in a SQL database table. This table has 9199 rows and is consuming 21GB on my server. I want to dump the data in this table to restore space on ...
1
vote
2answers
39 views

mysqldump not consistent after stopping slave

We have a Mysql master (server A) and slave (server B) setup. I am trying to create one more slave(server C) for master server(A). I have stopped the slave in server B and noted master server(A) ...
27
votes
4answers
59k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
4
votes
2answers
115 views

Is there a way to create a MySQL/Percona Slave without using a dump?

I haven't been able to find anything so I'm sure the answer is no, what are you, a noob? but I feel the need to ask anyway :) Is there a simple utility that would allow a new slave server to be ...
0
votes
0answers
40 views

How to dump few columns of a mysql table using mysqldump?

I want to dump few columns of a table. How can I achieve this? Options with me are, (as per my thinking): I can use mysql -hXX -pXX -uXX -pXX db_name -e "select query with particular fields" > ...
5
votes
1answer
411 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
2answers
42 views

Securing MySQL backup process

I'm running my MySQL backups through cron scheduled every night, but the thing is in cron the username and password is given for it run, is there any other way I can securely run the backups? where I ...
1
vote
2answers
295 views

Migrating MySQL database structure with foreign keys fails

I am trying to set up a new testing server with a somewhat complex database structure by dumping the old database and importing it. But even though I use SET FOREIGN_KEY_CHECKS=0;, the import fails ...
0
votes
1answer
163 views

How do you dump specific table(s)?

Hi i have a shell script for doing a selected databases backup. Now my requirement is i wanted to take a particular tables in a database to be backed up. Each database backup need to be created in ...
4
votes
4answers
5k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
2
votes
1answer
281 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: ...
1
vote
1answer
85 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
15k 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 ...
93
votes
7answers
58k 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 5.0....
0
votes
1answer
131 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
66 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
3answers
53 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
42 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
100 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
64 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
24 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- ...
0
votes
1answer
40 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 ...
6
votes
4answers
17k 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 -...
0
votes
0answers
63 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?
3
votes
0answers
66 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
36 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 ...
3
votes
2answers
10k 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
277 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 ...
1
vote
0answers
18 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
93 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
23 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
22 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
133 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 ...
6
votes
4answers
18k 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 ...