Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
640
questions
0
votes
0answers
27 views
Can't import MySQL database from mysqldump
I am interested in backing up a Wordpress (multisite) website.
Because this Wordpress lives on a shared hosting environment, my options (for daily automated backups not managed by most host) are a ...
1
vote
0answers
35 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
2answers
37 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 ...
3
votes
2answers
110 views
How can I list database names within a mysqldump file?
I have a large mysqldump and I need to create the databases before importing them one-by-one. How can I search and list only the database names using the linux or mysql terminal?
0
votes
0answers
11 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 ...
0
votes
2answers
14 views
Possibility on huge MySQL database replication
I have a mysql database with size 2.4TB which has lots of reads and writes happening continuously. I want to replicate this database as a backup.
But it's almost impossible to get a dump from this ...
1
vote
1answer
15 views
mysqldump locks all tables even when specifically grabbing a few?
I'm using a mysqldump command pulling data from 3 tables of 7. Are the other 4 locked as well?
0
votes
1answer
32 views
SQL Select query is taking so long time on new Server
I am migrating our live website server from rackspace to AWS. We have already done all parts but stuck at below point:
One of query which is running within a second on live server, it's taking around ...
1
vote
1answer
37 views
Transferring innodb table
I have a 30GB innoDB-only database that keeps having Lost connection to MySQL server during query when dumping when running mysqldump -u root -p --compress --max_allowed-packet=1G --net-buffer-...
0
votes
1answer
11 views
restore table and populate new data from transaction log
I use mysqldump nightly to backup a database. I'm trying to find a way to do a partial restore on the data from one table. I use the --master-data flag so I know where in the transactions log my data ...
1
vote
0answers
24 views
How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?
I am having the exact same problem as this person here: How can I prevent mysqldump from prepending the database name in CREATE VIEW?
I am using MariaDB 10.4.7 x64 on Windows. For just ONE of my ...
0
votes
1answer
320 views
Error Code: 1416. Cannot get geometry object from data you send to the GEOMETRY field
I am trying to run .sql file/import into MySQL database, however, I am running into Error Code: 1416. Cannot get geometry object from data you send to the GEOMETRY field .
Here's my code:
CREATE ...
0
votes
1answer
17 views
Migrating a Mariadb/wordpress site with only .frm files
My server crashed. My /home /www and lib/mysql were on a different hard disk so I have all the data. The original server was Debian 9 with Mariadb and an apache webserver. I planned to move to mysql ...
1
vote
2answers
48 views
MySQL restore dump takes too long to complete, how DBAs deal/replicate large databases?
Due to coronavirus my company is forced to scale down and cut costs. Long story short, I went the route of least disruption I set the original database server (that's going to be turned off for good ...
0
votes
1answer
36 views
How to dump / restore mysql.user [closed]
I'm able to dump the table of users from one server with
mysqldump -u root -p -h [host address] mysql user > user_table_dump.sql
but how can I restore these users into my localhost enviroment? I ...
0
votes
0answers
29 views
MySql uncomplated Upgrade Error
I faced showed bellow error every apt upgrade. An mysql server frequently down. I try backup my database with mysqlpump, this backup not contain tables values. Only table construct. How can I fix this ...
2
votes
2answers
437 views
How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8
I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump.
Later, I realized that I needed to downgrade back to MySQL ...
0
votes
1answer
92 views
MySQL: Importing databases from older version
I'm in the process of moving everything from a very old production server to a new one. I'm now trying to copy the MySQL database over to the new system. I have very limited knowledge of MySQL, BTW. ...
0
votes
0answers
25 views
Convert SQL database to a newer version
My new hosting has been having issues importing my wordpress database and are getting errors.
The current export is..
-- MySQL dump 10.16 Distrib 10.1.31-MariaDB, for debian-linux-gnu (x86_64)
And ...
0
votes
1answer
62 views
MariaDB/MySQL: Import from dump (.sql) file remotely via network
After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a ...
0
votes
1answer
39 views
MySQLdump Exclude master-data
Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static ...
1
vote
3answers
36 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
107 views
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
31 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
63 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 ...
0
votes
2answers
209 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
414 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
122 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
32 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
49 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
45 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
102 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-...
1
vote
2answers
1k 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
41 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 ...
4
votes
1answer
79 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
38 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
42 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
377 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
22 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
164 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
85 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
30 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
0answers
2k 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
180 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
35 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
104 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
77 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
107 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
19 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 ...
