MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

0
votes
0answers
5 views

How to set mysqldump using /*!40101 SET character_set_client = utf8mf4 */;?

When I use mysqldump to export mysql database, it always produce a dump.sql containing ...some other things... /*!40101 SET character_set_client = utf8*/; ...some other things... This is the ...
0
votes
0answers
6 views

Table data is not getting dumped

This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is ...
0
votes
1answer
17 views

error 1146 when importing mysql DB

I'm trying tu dump and re-import a database, I'm using the following bash script to export (I have reasons to export each table separately) for x in `mysql --skip-column-names -h [host] -u [user] ...
0
votes
0answers
30 views

mysqldbcompare SELECT command denied to user

I am trying to run mysqldbcompare to see the difference between two tables: mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests ...
1
vote
1answer
26 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
0answers
14 views

MySQL quick database transfer between two servers

I am migrating a reasonably large (18GB) database used for a website from one server to another. There are two tables which make up 95% of that size and they do not have to be fully transferred for ...
0
votes
0answers
20 views

Restoring a mysql database from raw files found in a mysql folder

My webhost screwed me. The dedicated server I had had a failed hdd and I lost the drive. I backed up all files but not the databases. I was able to save some MYI and MYD files but no able to do a ...
3
votes
1answer
46 views

mysqldump inconsistencies

I'm planning to take a dump of a table with the --single-transaction option enabled. I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the ...
0
votes
0answers
53 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 ...
1
vote
1answer
37 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 ...
2
votes
1answer
65 views

ERROR 1031 (HY000) at line X: Table storage engine for 'TBL' doesn't have this option

I am trying to import mysqldump into following system: # cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) # rpm -qa | grep ^mysql mysql-community-libs-compat-5.7.11-1.el7.x86_64 ...
1
vote
1answer
44 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
0answers
15 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
28 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 ...
0
votes
1answer
14 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
23 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 ...
0
votes
1answer
46 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
121 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
36 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) ...
4
votes
2answers
93 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
30 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" > ...
0
votes
1answer
34 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 ...
1
vote
1answer
74 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 ...
0
votes
1answer
70 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
64 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
50 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 ...
1
vote
1answer
153 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
0answers
21 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
39 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
23 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
77 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
37 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 ...
3
votes
0answers
60 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 ...
0
votes
0answers
42 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
1answer
35 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
93 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 ...
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 ...
3
votes
2answers
177 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
0answers
80 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
21 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 ...
0
votes
1answer
103 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 ...
1
vote
1answer
17 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 ...
0
votes
1answer
38 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 ...
3
votes
2answers
72 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
43 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 ...
0
votes
1answer
190 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 ...
0
votes
1answer
26 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
31 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 ...