Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
1
vote
2answers
74 views

mysqldump file restore while sql is still being dumped

Im using MaridaDB 10, but my question applies to all mysql variants. I have large dataset (70GB uncomressed when dumped to file). The mysqldump process takes 2-3hours. The restore process takes even ...
0
votes
1answer
13 views

meaning code at comment part from mysqldump result

on the results from mysqldump i got this part from create procedure before line BEGIN DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @...
10
votes
2answers
19k views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
0
votes
1answer
314 views

how to dump via mysqldump hugh table into chunks

i have very hugh table , around 100 M records and 100 GB in a dump file , when i try to restore it it to a different DB i get sql query lost connection , i want to try and dump this table into chunks ...
0
votes
0answers
13 views

How to do incremental backup with limited privilege in mysql?

Env:MySql Community v5.7 @AWS RDS User Privs: Select_Priv, Create_View, Show_View, Show_Db, Execute, Create_tmp_table binary_log: ON Currently I can do full backup using mysqldump --single-...
0
votes
1answer
703 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
100 views

ERROR 1064 (42000) You have an error in your SQL syntax during import

New to MySql. I have an export file from server version 5.6.34. I am importing this on a server with the same version. I get an error like: ERROR 1064 (42000) at line 4343: You have an error in your ...
1
vote
2answers
2k views

mysqldump still fails even with mysqldump --column-statistics=0

mysqldump still fails even when using mysqldump --column-statistics=0 # mysqldump --column-statistics=0 Just returns # mysqldump: [ERROR] unknown variable 'column-statistics=0' Am I missing ...
1
vote
1answer
117 views

SQL dump loads with Error 1064

Looks like this issue is just the wrong version of MySQL, need to go to 5.5 So I have a backup of an offsite source SQL database. I am not sure how they are preforming the data dump but I am trying ...
0
votes
1answer
19 views

MySQL - Dumping 1TB database with SED to replace definer

Im going to dump a large database more than 1TB using MySQLDUMP (unfortunately I have to stick with mysqldump, I can't use mydumper or any other backup tools) And I want to replace the definer on the ...
6
votes
4answers
7k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
0
votes
0answers
14 views

mysql < dump.sql or source dump.sql consume all system memory

I am trying to restore a large dump (~300GB MyISAM) into a Mysql server version 5.6.48, but if using: mysql < file.sql or mysql> source file.sql In both cases, after starting the process ...
2
votes
2answers
991 views

Export and Import large database from one rds of amazon to newly created amazon rds

We are facing some issue. We would like to export and import mysql database "xyz" from old rds say xyz-rds to newly created rds say abc-rds (mysql). We are approaching common method taking full ...
3
votes
2answers
2k 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 ...
2
votes
2answers
1k views

Importing a large mysql dump while replication is running

So we have a simple master/slave mysql replication running between two CentOS Servers. The master has multiple databases. eg.. Database1 Database2 Database3 The issue is we have a mysql dumpfile ...
18
votes
2answers
7k views

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for ...
4
votes
1answer
6k views

How can I backup/dump MYSQL database with foreign key constraints?

I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship. I am ...
2
votes
2answers
41 views

How to Skip Existing Data While Restoring mysqldump File?

I use this query to import data using MySQL Dump (Command Prompt / Command Line) mysql -u root -p database_name < database_name.sql I want to import the large database (only new deltas) as of now ...
3
votes
1answer
1k views

MySQL: error 1118 (42000) row size too large

storage_engine = MyISAM table_type = MyISAM version = 5.1.25-rc database_name.sql dump size is 3.3G: mysqldump -u user -p database_name > database_name.sql When I try to restore the database (...
0
votes
1answer
668 views

Dump MySQL table to csv file (utf8mb4_unicode_ci)

I have MySQL table with columns (character-set: utf8, collation: utf8mb4_unicode_ci) to be exported to a .csv file. mysqldump -u *** -p -t -T/tmp/ dbname tablename --default-character-set=utf8mb4 ...
0
votes
2answers
697 views

0kb backup using mysqldump

Same question has been asked here but its not helping me out. In my python utility, I'm trying to take MySQL database backup using mysqldump. It runs successfully but I'm getting 0kb file in my ...
2
votes
2answers
110 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
0answers
12 views

Quick Backup/Restore of Aurora Database

We're getting slow performance (hours for a couple GB database) when trying to do a backup/restore of an aurora database. We essentially want to clone the database so we've got two side-by-side copies ...
0
votes
2answers
93 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 ...
2
votes
1answer
135 views

What dump file options or MariaDB setting is slowing down the load of data?

Context: Loading a copy of a legacy DB for a CRUD app at build by Spring Boot. Primary use case is developers testing the CRUD functionality as it is developed. Problem: The loading operation is ...
4
votes
4answers
15k 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 ...
0
votes
1answer
85 views

Mysqldump backup size issue - 5.6

I'm using MySQL 5.6 with about 5 databases on linux. I have scheduled daily full backups using mysqldump. All databases use InnoDB engine. What I observe is that the backup size seems to vary ...
0
votes
1answer
1k views

Error: ERROR 1047 (08S01) at line 6955: WSREP has not yet prepared node for application use

While restoring a dump from a CloudSQL instance into a node of a Percona xtradb cluster, I am getting this error. The line 6955 in the .sql dump file is in bold below: DROP TABLE IF EXISTS `...
2
votes
1answer
1k views

Passwordless mysqldump via shell script in /etc/cron.daily

I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place. The problem: I ...
8
votes
3answers
15k views

Why is my MySQL dump file and my phpMyadmin export file different sizes?

I am looking to create a cron job to backup my MySQL database. I am using this command: $ mysqldump --user="User" --password="Password" -A --single-transaction > /home/user/Export-`date +"%F"`...
0
votes
0answers
18 views

Why is MySQL dump not restoring all my tables?

I have Mysql 5.5 server on a EC2 instance which have multiple sql users to perform operation , now i want to create a back up db server . For which i have created a mysql dump using root user . ...
2
votes
1answer
135 views

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, I issue or schedule ...
0
votes
1answer
1k views

Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored

I'm uploading data to a db (mysql 5.1.42) from a dump file using mysql command line and get duplicate key error. This is the format I use: /mnt/temp# mysql -u username -p -h url_of_db_master -D ...
2
votes
1answer
27 views

Restore Mysql dump to a mysql container fails with ERROR 1114 (HY000) at line 7238: The table is full

I am trying to restore a mysql dump of size around 18GB to other mysql server which is running inside a container using this command: mysql -h example.com -u user -p matomo < dump.sql But it fails ...
0
votes
1answer
216 views

mysqldump is ignoring option '--databases' due to invalid value

I have this MySQL database named capsule_corporation_database. Then, I have this my.cnf file in my home directory: ~/.my.cnf [client] host=localhost user=trunks password=mai database=...
1
vote
1answer
197 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 ...
2
votes
1answer
1k 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 ...
0
votes
1answer
591 views

mysqldump vs mysqlbackup

I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes ...
0
votes
1answer
103 views

Constant MysQL crashes. How to fix?

MysQL is frequently going down. Error logs contain: [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it | 149 occurrences [ERROR] Native table 'performance_schema'....
0
votes
1answer
219 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
105 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
585 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 > ...
1
vote
0answers
40 views

Getting error while starting slave on MySQL slave./ Backups in MySQL 8.0.20

I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server. I am using below command to take backup of all databases on server. Note: I am ...
0
votes
1answer
182 views

mysqldump waiting for table fush for long time

MySQL version: MySQL Commercial 8.0.16 OS: RHEL 7.6 Following is my mysqldump command mysqldump --defaults-extra-file=/mysql_data/config/extra_options.cnf --single-transaction --flush-logs --master-...
0
votes
2answers
52 views

How to remove references to performance_schema.user_variables_by_thread in mysql 5.6?

I have a Jira 6.4.7 instance running on MySQL 5.6.44. I'm in the progress of updating the Jira instance to 7.0.5, which still supports MySQL 5.6. I accidentally hooked up a newer Jira 7.0.5 to the ...
2
votes
1answer
230 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
300 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 ...
23
votes
9answers
58k views

Any option for mysqldump to ignore databases for backup?

We have 40 databases in our server. We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore ...
88
votes
5answers
118k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
0
votes
1answer
261 views

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MySQL server version: mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL)) MariaDB mysqldump CLI version: mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64) I am ...

1
2 3 4 5
13