Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
0
votes
0answers
12 views

Issues with mydumper

Recently I changed my default backup tool from mysqldump to mydumper because it was FAST! I was quite happy with the results, and a few days ago I decided to import one of my dumps. For my surprise, ...
0
votes
0answers
13 views

mysqldump causing database to get not saved records

I wish there was a safe way to backup mysql database. The database itself is getting not saved records if the records was about to be saved duding the dump. It is happening occasionally, maybe when ...
2
votes
2answers
33 views

Mysql dump as small bulk insert

In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql. I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, ...
0
votes
3answers
33 views

Is there performance loss in out of sequence inserted rows (MySQL InnoDB)

I am trying to migrate from a MySQL AWS RDS instance with a huge SSD and too much excess space down to a small one, and data migration is the only method. There are four tables in the range of 330GB-...
0
votes
0answers
20 views

yet another Tablespace is missing

work0.psg.com:/var/db/mysql/wordpress# mysqldump -p --databases wordpress > dump.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all ...
0
votes
0answers
18 views

How to connect to remote MySql db with user that has '@' symbol in name?

I am having trouble dumping database schema into a self contained file using this command mysqldump -u 'dbadmin@morecharacters' -p -h 'remotehost' -P 31337 --no-data dbname > schema.sql. The error ...
0
votes
0answers
5 views

What files of Mysql lib directory safe to be deleted and to be recreated with innodb_file_per_table settings?

I have brand new mysql 8 setup and installed on a centos 7. Unfortunately I forget to set innodb_file_per_table. I would like to dump the database and recreate with the innodb_file_per_table settings. ...
0
votes
1answer
14 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 = @...
0
votes
0answers
14 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
25 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 ...
2
votes
2answers
50 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 ...
0
votes
1answer
118 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 ...
0
votes
0answers
16 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 ...
28
votes
2answers
12k 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) ...
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
28 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 ...
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
2answers
86 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
118 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
16 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
16 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
37 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
51 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
31 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
676 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
22 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
62 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
62 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
32 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 ...
4
votes
2answers
1k 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
223 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
29 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
72 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
72 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
48 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
112 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
55 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
81 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
vote
2answers
344 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
698 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
154 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
39 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
61 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
63 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
237 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
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 ...
0
votes
1answer
59 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 ...

1
2 3 4 5
14