Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
0
votes
1answer
501 views

mysqldump can write to /tmp and /var/tmp but nowhere else

I have various mysql databases on different servers and at different versions. I'm using the same mysqldump command across Linux versions without issue except for this new Red Hat 7.6 server. Command:...
-1
votes
1answer
1k 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 ...
2
votes
1answer
3k views

Error Unknown command '\U' when restoring MySQL dump on Windows

I am updating software that used MySQL 5.4.3 in its old version, and now uses MariaDB 10.2.11. So, I dumped the old data with that command: mysqldump -uroot -p --no-create-info --skip-triggers --...
1
vote
1answer
206 views

Restoring a mysqldump loses user privileges for routines?

When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql ...
0
votes
0answers
98 views

mariadb import - all rows missing except those in final INSERT of InnoDB tables

This is a very strange bug. I'm using this on a mac: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64) If I import an SQL dump of a WordPress DB (so not very complicated structure, but ...
0
votes
1answer
2k 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 `...
0
votes
2answers
640 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 ...
1
vote
0answers
237 views

mysqlpump --set-gtid-purged=OFF still adds @SESSION.SQL_LOG_BIN=0;

Whenever I add --set-gtid-purged=OFF, it still adds @@SESSION.SQL_LOG_BIN=0; to the output. According to the documentation, shouldn't it get removed? --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=...
1
vote
1answer
261 views

How to MySQLDump with For Channel to set up multi-source replication?

I'm trying to set up multi-source replication. But, when I try and import the second database I get the error: ERROR 3079 (HY000) at line 28: Multiple channels exist on the slave. Please provide ...
1
vote
1answer
183 views

Does MySQLDump Verify the backup as restorable

I have written a PowerShell script to use MySQL dump to perform full backups of a specific database at a specific time. I am wondering if I need to confirm the backup is restorable via my script or ...
0
votes
1answer
528 views

Backing up and restoring all databases in MySQL on linux

I plan to dump all databases on one computer and restore them on another using the following 2 commands:- mysqldump -u username -p --all-databases > dump.sql mysql -u username -p < dump.sql ...
2
votes
1answer
1k views

How to take mysqldump with Generated column?

I am getting below error while importing the dump, ERROR 3105 (HY000) at line 82: The value specified for generated column 'column_name' in table 'table_name' is not allowed. When I describe the ...
1
vote
1answer
573 views

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

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 ...
0
votes
0answers
129 views

error while restoring database from mysql: ERROR 1006 (HY000)

I made a mysqldump from all my databases prior to fully deinstall and reinstall MariaDB. while restoring the databases with the following command, logged in as root: MySQL -u root -p < /mnt/...
1
vote
1answer
67 views

mysqldump changes primary key to unique key?

While attempting to dump and restore a WordPress database in MySQL, I noticed that mysqldump seems to be changing the PRIMARY KEY on several tables to a UNIQUE KEY instead. For example, this tables: ...
3
votes
1answer
2k 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
0answers
52 views

mysqldump writes dumped data to output

I'm trying to bring incremental backups to one old setup. The script I create worked perfectly when run directly. However when I run it from crontab or when redirecting shell output all data is ...
1
vote
0answers
1k views

how to dump mysql schema without keys and without constraint ? and keep all of them to alter afterwards

i am using ubuntu 16.04 and want to dump large schema with hounded of tables from AWS rds, remove all keys and foreign key , load data and then add back the keys , is there any automatic tool that ...
1
vote
1answer
1k views

How to restore mysqldump of one database in different databaseName with content of data in the same host machine

Let's start from the beginning there is a MySQL database with name DB1. As I want to restore the mysqldump backup of DB1 database in DB2. The DB2 database is another database in same Host Server ...
0
votes
2answers
620 views

mysql - import only schema from full dump?

I have a full database export (single file, schama + data), but I would like to import only the database schema. Is there any magic option or trick to achieve this? I'm NOT able to perform a "...
0
votes
0answers
80 views

How to speed up MySQLl loading from dump file?

I'm using mysqld Ver 5.7.25-0 in Ubuntu 16.04. I dumped from AWS Relational Database Service (RDS) with mysqldump --single-transaction --quick --max_allowed_packet 1G ... and now when I tried to load ...
1
vote
1answer
131 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
0answers
634 views

MySQL reporting version mismatch due to picking up previous installation

To solve a mysqldump "Workbench was unable to get mysqldump version. Please verify the log for more information.", I opted for the first solution at https://stackoverflow.com/a/44851129/175241 But ...
0
votes
1answer
231 views

Mysql replication rebuild dump database lock

I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like: FLUSH TABLES WITH READ LOCK; SHOW ...
2
votes
1answer
2k views

How to fix the sys.host_summary table in MariaDB 10.3.13

using mysqldump while doing a backup gives the following error: mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view ...
0
votes
2answers
3k views

Mysqldump multiple tables with where clauses in single transaction

Suppose, we have 2 tables: t1,t2 in database db: Expected the below code to dump tables based on conditions specified in the where clauses: mysqldump --login-path=login_path \ --single-...
0
votes
1answer
133 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}.$(...
0
votes
1answer
110 views

Safe online dumping of an InnoDB database in the presence of DDL changes

When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
0
votes
2answers
132 views

(Partially) clone a huge DB for DEV purposes

I want to setup a dev and test environment for website that has a huge MySQL database. Since this DB is so big, it is quite inconvenient to clone it 1-on-1, because it simply takes too long and uses ...
0
votes
1answer
980 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
936 views

How to restore dump from MariaDB 10.1 to MySQL 5.5

I generate backups of my databases by using mysqldump.exe -u root --all-databases > mariaDbBackup.sql. When I try to restore my dump by doing mysql -u root -p < mariaDbBackup.sql, I get an ...
0
votes
0answers
111 views

How to ignore errors in mysqldbcopy

Is there an option to ignore errors and continue copy in mysqldbcopy
2
votes
1answer
393 views

Slow mysqldump on big table

I am running MySql 5.6 on Google cloud as VM instance, one of the tables contains 1500,000K rows with 676G .ibd file. using mysqldump --opt --add-drop-table --quick DATABASE BIG-TABLE >DATABASE....
1
vote
1answer
148 views

Error while restoring the table dump

ERROR 1067 (42000) at line 25: Invalid default value for 'created_on' I'm getting this error while restoring a table after taking the dump(remotely). I have used mysqldump -h (hostname) -u -p (...
1
vote
1answer
539 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
1k views

How to specify “partition (partx)” in “mysqldump” command?

I have to export some data from a table which is partitioned, and with some extra condition So I use mysqldump command,and use --where"xxxxxx" options as below mysqldump -hxxx -Pxxx -uxxx xxx rtm --...
1
vote
3answers
303 views

mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

I'm struggeling the last two days with the following issue: Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to ...
1
vote
2answers
2k views

Mysql: set variable in mysqldump

I recently added a global variable in mmy.cnf max_statement_time=120 ; to stop long running queries and to throw an exception. Its working fine. Then I am unable to run mysqldump for backing up all my ...
0
votes
1answer
714 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
36 views

How to fully dump mySQL database via workbench?

I am exporting database in SQL file using workbench. Export is done successfully, but when I import data and compare record counts with older database, some of the records are missing. Not all records ...
0
votes
1answer
771 views

mysqldump insert data to exists table

hellow Dumped x.sql file from db and then trying to find way to insert data from x.sql file to existent tbl in another server. But mysql execute all the concept of the x.sql ( that include "drop ...
3
votes
1answer
2k views

Backup via mysqldump. Is this safe to do on production or will it crash the server?

I want to run this command during the time in which the production server is running the application: mysqldump -u sbp -p databasename_production > databasename_development_copy.sql Is this safe ...
1
vote
1answer
1k views

MySQL Enterprise Backup download site

I was given a sever to use for tests that has MySQL commercial version installed. I would like to test backups using MySQL MEB, but cannot find the download site. I have seen many blogs mentioning ...
2
votes
2answers
136 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 ...
1
vote
1answer
402 views

Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks,...
1
vote
2answers
111 views

Importing huge dumps into a mysql server 5.5

I want to import a couple of huge .sql dumps (~600G each) into a mysql server 5.5 running on an Ubuntu 14.04. The machine has about 8G of RAM allocated to it; The previous machine had 128G; Storage ...
1
vote
1answer
968 views

Getting Master Current Binlog and Binlog Position in Replication Backup

I want to take the current binlog file and binlog position of the master server in a mysqldump on the slave server. I cannot use master-data=2 on slave - how can I do this nevertheless?
0
votes
1answer
3k views

Mysql Restore - ERROR 1227 (42000) at line 30908: Access denied DEFINER issue

Im trying to restore the database dump which has the DEFINER=test@%. Backup file is 50GB. OS - CentOS 7.5 While restoring this, I got the below error. ERROR 1227 (42000) at line 30908: Access denied;...
-1
votes
1answer
111 views

Web interface or tool for developers to run select query with out access database directly [closed]

I want a tool or web interface that can allow inside users (developers,..) to run select query with out access the database directly. I don't want to give sql user to all the developers for the ...
0
votes
2answers
870 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 ...

1 2
3
4 5
14