Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
624
questions
1
vote
3answers
25 views
mysqldump interrupts always with same file size
mysqldump -h127.0.0.1 -u user -p'password' -t db mail --where 'status=3' > /var/www/project/storage/web/dump2.sql
I try it on ubuntu 18.04, mysql 5.7.29.
And the file is always 141.8MB.
Data just ...
2
votes
0answers
70 views
+250
What kind of complications would I face downgrading from MySQL 8 to MySQL 5?
Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter.
The docs say,
The only supported alternative ...
-1
votes
1answer
19 views
-bash: PURGE: command not found
I want to delete binary log files
following are my binary log files
binary2.000001
binary2.000002
i want to delete binary2.000002
mysql> PURGE BINARY LOGS TO 'binary2.000002';
It shows me:...
0
votes
2answers
40 views
How to dump a large database?
I try to take a backup of a huge MySQL database via mysqldump. The database has millions of rows with a total size of 1.3TB.
CREATE TABLE PageContents
(
ID int(11) unsigned,
Added date,
Text ...
-1
votes
0answers
26 views
mysql reduce size of ibdata but information_schema not exist
my client has an old version of MySql (4.1.15) and his server is getting full. I looked at it and saw the ibdata file of his mysql is getting to big.
I looked at this article:
What is the best way to ...
0
votes
2answers
35 views
How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;
I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line:
mysqldump --add-drop-table --add-locks --allow-keywords --...
0
votes
1answer
17 views
mismatch between server schema content and exported sql rows [duplicate]
I am using MySQL 5.6.44 within phpMyAdmin on Go Daddy shared hosting and experimenting with some duplicate table data.
I am trying to figure out, when I export my table from phpMyAdmin the exported ...
0
votes
1answer
70 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
65 views
Best way to backup/restore a very large DB (~2TB)
The ibdata1 file has increased to ~2TB and leaves only a few GBs free space in the same disk (this is because I forgot to turn on innodb_file_per_table, before it was too late)
From what I have read ...
-2
votes
1answer
23 views
mysqldump very slow [closed]
We have an application running on mysql 5.1.73. We recently converted the server from physical to virtual and from that date the mysqldump is running for a very long time.
We have a big database of ...
0
votes
1answer
37 views
mysqldump generating inconsistent dumps
I cannot get mysqldump to generate a dump that will properly restore. The dump is being generated to setup a new slave, but whenever the slave starts up, it gets foreign key constraint failures.
I'm ...
0
votes
0answers
30 views
Why does PhpMyAdmin put the idexes at the end of a dump?
When I export a MySQL database with Adminer or with mysqldump, it produces a .sql file with each table definition followed by (if the table is non-empty) an INSERT statement (or multiple statements ...
0
votes
1answer
39 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
393 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 ...
0
votes
1answer
18 views
Incomplete MysqlDump Files - Mysqldump Inside For Loop
I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below.
for DB in $(mysql -uroot ...
3
votes
1answer
62 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 ...
0
votes
1answer
37 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
0answers
30 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
vote
1answer
106 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
1answer
18 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
51 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 ...
0
votes
1answer
53 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 ...
1
vote
2answers
27 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 ...
0
votes
0answers
663 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: ...
0
votes
1answer
79 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=...
0
votes
0answers
25 views
Database synchronization 2 times a day -MariaDB
I have 2 different servers. The first one is server1 and the other is server2. On servers, one works for websites (server1) and the other for mobile applications (server2) for security purposes. I ...
1
vote
2answers
25 views
Using MySQL binary logs to keep backups between database dumps?
I currently take a full database backup using mysqldump once a day. I'd like to make this more granular (e.g. every 15 minutes), but can't just take more full dumps due to size constraints. Can I use ...
1
vote
0answers
66 views
Are `--events` and `--routines` really needed to get a `mysqldump --all-databases` of an entire mysql instance?
Several fine answers on here have already exquisitely argued that
mysqldump --hex-blob --events --routines --triggers [...] --all-databases
should be all - modulo storage-engine considerations - ...
1
vote
1answer
62 views
Does a MySQL dump that contains triggers double data on import?
I have a quick and simple question to clarify. I have medium sized database (400 Gigs) running in MariaDB 10.2 with lots of tables and lots of triggers as well. I am planning to take a mysqldump and ...
0
votes
0answers
18 views
Possible issues dumping a legacy MySQL 5.0 database to a local MariaDB 10.2 for develpment?
I've inherited a site that uses MySQL 5.0 and want to get a development version up and running with the existing data on my local (OS X) machine where I have MariaDB 10.2 but the compatibility tables ...
0
votes
1answer
103 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
258 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
...
0
votes
1answer
382 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
57 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
42 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
0answers
44 views
Mysqldump is not exporting the full table (phpmyadmin and sqlyog neither)
When I export the table from PhpMyAdmin it downloads a .sql file with just a small part of the database (like 2 or 3% my table is 84M rows)
I have tried with SQLYog and mysqldump with a similar result....
0
votes
0answers
540 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
1answer
124 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
116 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=...
0
votes
1answer
98 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 ...
0
votes
1answer
60 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
45 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
...
0
votes
0answers
23 views
All rows not being exported in MySQL dump
I have a MySQL instance (5.7) which is just a single instance. It is not setup to have slaves/replicate. I've updated my stack to have slaves. In the process of moving to this newer stack, I needed to ...
0
votes
0answers
373 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 ...
0
votes
1answer
98 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 ...
0
votes
0answers
56 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
19 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:
...
2
votes
1answer
662 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
33 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 ...
0
votes
0answers
480 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 ...
