We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
-1
votes
1answer
91 views

Incomplete dumps using mysqldump

I am running Mysql server with master and slave replication. From past few days i found that backups on my master server are incomplete. When i ran manually on master it's returning the below error ...
14
votes
4answers
50k 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 ...
3
votes
1answer
39 views

Migrate large (200GB) mysql databases to new hosts with as little downtime as possible

My question is about migrating large amounts of MySQL data with as little downtime as possible. I have 3 MySQL databases that need to be moved from one RDS instance inside a VPC into a brand new AWS ...
2
votes
1answer
385 views

Database size twice as large after mysql dump and re-import

After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be? This is occurring when importing the sql dump right ...
1
vote
1answer
153 views

Grabbing SQL Dump of Master DB Without Downtime

I'm curious whether downtime will be necessary to grab a SQL dump of my master database. Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that ...
0
votes
1answer
90 views

Batch query a lot of MySQLdump files

I have a website for an event. I set up a cron job to back up the database every hour. The backup command is mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(...
1
vote
2answers
88 views

mysql dump best practices for development

I have an application that has a large database that includes, among many other tables, a table called content and a table called users. I would like to set up a development environment for this ...
0
votes
1answer
29 views

Intentionally slow down importing of (Percona) MySQL 5.7 dump

I need to move a very large MySQL table (250M rows, 120 columns. 120GB of data, 310GB of index) from one database instance to another (on AWS). For reasons beyond the scope of this question, this is ...
0
votes
1answer
179 views

Why do I have two collations in mysqldump, under stored procs?

When I run mysqldump --routines I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now). /*...
0
votes
1answer
420 views

mysqldump from AWS RDS slowing down

Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using - mysqldump -hxx -u xx -p --extended-insert --single-transaction --max_allowed_packet=1G db > ...
0
votes
1answer
144 views

MySql Moving from development to production into a different user

I have a project that need to be moved to production (for the first time). The database includes few tens of tables and procedures/functions. They were all created under the ROOT user (yes, bad ...
1
vote
1answer
31 views

Can I use a mariadb/mysql dump from older version in newer version?

I'm a mariadb/mysql newbie. I want to take a complete backup. Apparently this is the way (please correct me if I'm wrong): mysqldump \ -h... -u... -p... \ --hex-blob --routines --triggers \ --...
0
votes
0answers
24 views

mysqldump large innodb tables - only importing small number of records

I've inherited a website I didn't originally build. The database includes a users and a bookings table both of which are relatively large (innodb) tables. I'm trying to download a copy of the ...
-1
votes
1answer
470 views

MySQL database dump bombs out Error 1064 when using LOCK TABLES

This specific machine is running MySQL version 5.0.27. I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is ...
0
votes
2answers
145 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) ...
2
votes
1answer
122 views

Proper Way to Remove Mysql Tables

I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?. Removing the .frm files ...
0
votes
1answer
261 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 ...
0
votes
1answer
14 views

mysql - cancel source call

I want to cancel calling a source file on a mysql server which includes an enormous amount of data. It appears to be a sqldump that is broken into many small updates for many tables. First I tried ...
0
votes
1answer
31 views

Using mysqldump with WHERE clause but WHERE clause only applying to specific table? [closed]

This question is related to a question I posted on another StackExchange site. Question: How to apply the --where argument only to a specific table when mysqldump-ing a whole database? So I have a ...
3
votes
2answers
2k views

fix `mysqldump: Error 2013: Lost connection` on “cloud” server

I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form. If I try to dump more than a day worth of data I get ...
0
votes
1answer
38 views

Import a large MySQL dump 68GB on RDS instance

I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM). The import takes more than six hours: More than 500 tables I have 2 large tables: the first ...
0
votes
1answer
191 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 ...
1
vote
3answers
459 views

Significantly Slower DB Dumps on AWS RDS After Turning on Encryption?

Turning on encryption on an AWS RDS MySQL instance results in significantly slower database dumps (using mysqldump). mysqldump -u test -p -h xxx.aws.com > dump.sql Is there a reason for ...
41
votes
6answers
82k views

Restore mysql database with different name

How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?
1
vote
2answers
299 views

Determine size of mysqldump prior to dumping

This question is a followup on this previous question. Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump? I have tried using the most upvoted ...
0
votes
1answer
239 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 (...
0
votes
2answers
22 views

MariaDB mysqldump lacks all data - how do I force including it?

for years I've been using mysqldump without much trouble. Recently I joined a project that is using "MySQL dump 10.14 Distrib 5.5.52-MariaDB, for Linux (x86_64)" and when I attempt to do a dump it ...
2
votes
1answer
6k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
1
vote
2answers
388 views

Large MYSQL DB on single file

I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server. I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size. Im ...
0
votes
1answer
947 views

Unable to migrate local database to RDS instance

I recently created an MySQL instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance from the MySQL command line. I will brief you the details. 1) I want to move/migrate my ...
0
votes
0answers
78 views

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get: Error: ...
1
vote
1answer
120 views

Import Windows MySQL dump on a Debian Linux server

I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records. Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, ...
0
votes
1answer
2k views

Automysqlbackup fix error “Skipping the data of table mysql.event. Specify the --events option explicitly”

I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.". I found that its because of not specifying whether or not to add ...
0
votes
1answer
252 views

Unable to export 8.0.1 MySQL database using MySQL Workbench 8.0

I'm unable to export my 8.0.1 MySQL database using MySQL Workbench 8.0. When I go to perform the export via the "Administration" table, with all the default settings (I didn't change anything ...
0
votes
1answer
117 views

Mysql dump restore doing some funny stuff

I am using linux platform. I also dump the data into a file and I restore into a server and I ran this command [root@localhost /]# mysqldump -u root -p fms < /device.sql The funny part it keep ...
1
vote
1answer
21k views

Database user specified as a definer

I have a view in my database. problem is below Error SQL query: SELECT * FROM `lumiin_crm_prod`.`v_contact` LIMIT 1 ; MySQL said: 1449 - The user specified as a definer ('lumicrm'@'%') does ...
3
votes
1answer
4k views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?
2
votes
1answer
5k 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 option,...
2
votes
2answers
994 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
1answer
1k views

How we take dump with batch of records option?

we have 30 GB database.we want to take backup of this database. here the question is we want to take the backup batch of every 10k records. How we can take batch of records backup using mysqldump
6
votes
3answers
2k views

Mysqldump treats some table as View

When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables. So for some tables it looks like -- Retrieving table ...
2
votes
1answer
90 views

Get list of excluded databases from command line by asking user

Based on this I'm working on this bash script for export all databases from MySQL but exlude some ones and also system ones. This is how it looks like: read -e -p "Enter MySQL root user: " -i "root" ...
2
votes
2answers
281 views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
5
votes
2answers
4k views

MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form: SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments). I can't seem to find much ...
0
votes
1answer
51 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 running....
4
votes
1answer
706 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
2
votes
1answer
3k views

Moving a large MySql database(Over 15 GB) from server to server mysqldump

I need to move a mysql database that is about 17 GB that is on a Cpanel/Centos based server that I do not have root access, to a Centos server that I have full root access. I do have limited SSH on ...
3
votes
2answers
1k views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with 8GB ...
1
vote
1answer
1k views

Make MySQL dump from parts of each table

I need to export some data from different tables. I tried this query select * into outfile 'C:\Users\\User\\Desktop\\ololo.sql' from db.users where id % 2 = 1; After this query, the SQL file was ...
1
vote
3answers
662 views

Is any tool for taking MySQL Dump Automatically with a particular time? [closed]

I am using VS2010 and MySQL for my project. My client wantw to take mysqldump every day like a daily backup. I want to give my client a tool for taking mysqldumps on a particular time schedule. Do ...