Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
680
questions
1
vote
0answers
17 views
is there a way to dump schema tables that don't exist and ignore the ones that exist?
I am using this command to dump schema directly to a different server because the existing server don't have space left to dump the tables/schemas and then transfer to the other server:
mysqldump -h ...
0
votes
0answers
12 views
When setting up an CSV Foreign Data Wrapper for Postgres what's the best way of dealing with \N
I am trying to set up a foreign data wrapper for CSV data dumped from a MySQL database. I have created the FDW server and set up the table but whenever I try and run any queries it raises an exeption ...
1
vote
0answers
24 views
Faster way to restore MySQL innoDB dump
I have a below MySQL configuration.
# The MySQL server
[mysqld]
....
key_buffer = 384M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = ...
0
votes
1answer
37 views
mysqldump: Got error: 1066: Not unique table/alias: 'temp1' when using LOCK TABLES
When I try to dump a whole database using
mysqldump -htest.com -uabc -p --column-statistics=0 abc > abc.sql
The following error occured.
mysqldump: Got error: 1066: Not unique table/alias: 'temp1' ...
0
votes
1answer
13 views
Which folder I use for recovery mysql database
Accidently deleted mysql database and reload again.But ı cant find my old database and no have backup.This is picture that mysql ProgramData folder. Which folder should ı use for recovery? Thanks ...
0
votes
1answer
12 views
Cant find the exe of mysqldump
I'm just learning about MYSQL after 6 years focusing only on sql server(dumb me).
I'm studying about how backups works there, but I can't find the mysqldump file to execute the backup. on DOS it says ...
0
votes
0answers
10 views
Mysql Slave restore from slave server
I have master-slave replication topology in MySql. I have taken the database dump from slave server and restore it on new slave. i just want to know that while pointing it to master what could be ...
1
vote
1answer
33 views
Copy mysql_native_password to new server
I did a mysqldump of my entire database mysqldump --defaults-file="/home/ubuntu/.my.conf" -u api --all-databases > full-dump.sql . I setup a new mysql and phpmyadmin and loaded the ...
1
vote
1answer
55 views
mysqldump with --no-tablespaces on InnoDB with index?
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
0
votes
1answer
19 views
MySQL import silently failing
I'm trying to import a large (30GB) SQL file (generated using mysqldump) into an RDS instance MySQL 5.7 database, from an EC2 instance on the same VPC.
It's importing most without a problem, but ...
0
votes
1answer
28 views
MySql Master Master out of sync
I have 2 MySql nodes with Master-Master Replication set-up.
MySql1 has read and write access and is in current state.
MySql2 has only read access and stopped working due to space issue.
How can I fix ...
0
votes
0answers
13 views
Use mysqldump for replication?
I have a large production database in Amazon RDS MySQL (billions of rows) that I want to set up as a master, replicating to an Aurora Serverless instance. Unfortunately, it looks like the only way to ...
1
vote
1answer
45 views
mysqldump select queries appearing in slow query log after upgrade
We recently upgraded from MariaDB 5.5 to MariaDB 10.5
Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump.
One of the tables is ...
0
votes
1answer
17 views
Powershell script while executing mysqldump script
I'm having an error 1045: Access denied for user 'mysqlsuperuser'@'test.internal.cloudapp.net' (using password: YES) when trying to connect
Here is the mysqldump command on my ps1 file.
$command = [...
0
votes
1answer
20 views
MySQL upgrade via mysqldump
We want to upgrade to MySQL 8.0 from MySQL 5.6 but upgrade manual seems to cumbersome and risky to follow doing an in-place upgrade so we have decided to do it via MySQL Workbench export (mysqldump) ...
0
votes
1answer
14 views
sql backup restore converts createdAt to local TZ
I have a remote MySQL DB in the cloud on JAWSDB.
I have createdAt and updatedAt timestamps for records that are in UTC TZ.
I'll backup these DBs using mysqldump -h HOST -u USER -p > backup.sql. I ...
0
votes
1answer
32 views
Consistent InnoDB dumps with mysqldump
The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use --single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other ...
0
votes
0answers
23 views
Warning in MySql Command line client
I have installed MySQL on my laptop but when I open the command line client it gives the warning, and when I type the password and hit enter, it suddenly crashes. please help me what should I do.
1
vote
1answer
52 views
mysqldump dumps different data with and without --no-create-info
mysqldump dumps different representations of data when called with/without --no-create-info.
Test case
First, create the test table and populate it with interesting data.
CREATE TABLE `test` (
`...
0
votes
1answer
30 views
All recent changes in MySQL have been automatically removed. Recently INSERTed or UPDATEd data is not available
I have been using MySQL for the last two months. I'm regularly INSERTing new records and UPDATEing old ones. But when I opened phpMyAdmin today, all of the changes which I've made in the 10 days have ...
0
votes
1answer
727 views
Migrate MySQL 5.7 to 8.0 via mysqldump
I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p
...
1
vote
1answer
29 views
MySQLdump leads to exceeding max connections. Skip-Quick as a solution?
Every night I do a full mysqldump of a 17G DB in MySQL 5.7.32. This worke for years, now I am doing some heavy load on inserts during the night which caused at exactly the backup time the connections ...
1
vote
0answers
25 views
MySQL Why does mysqldump break/just stop? Improve my mysqldump command
I use the following command to backup my MySQL (with mysqldumpl) two times a day:
/usr/local/mysql/bin/mysqldumpl --lock-tables=false --default-character-set=utf8 -h'host' -u'user' -p'pass' -f -v ...
7
votes
1answer
178 views
How to restore a specific table from a big mysqldump file?
From a very big backup file (created by mysqldump) I need to bring back only one (or a few) specific table(s), without loading all tables.
Are there any fast alternatives?
0
votes
0answers
22 views
mysqldump won't dump tables or data
I'm trying to back up a mediawiki database prior to moving the instance to a new server, but I'm getting stuck on mysqldump, which has never given me trouble like this before. I'm clearly doing ...
4
votes
3answers
66 views
why does mysqldump restore to a smaller size?
I am using MySQL and mysqldump v8.0.19 on Windows 10. I have an InnoDB database, old. I use mysqldump (and the --no-create-db option) to dump its tables. I then create a new database, new, and import ...
0
votes
0answers
17 views
Indexes messed up / slow after running mysqlimport
I'm doing a restore using mysqlimport by iterating over files generated by mysqldump --tab and the import is very fast but performance seems to be lagging after the import.
Does mysqlimport neglect to ...
0
votes
0answers
81 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
15 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
81 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
38 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
166 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
23 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
13 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
17 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
18 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
104 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
406 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
602 views
ERROR 1064 (42000) You have an error in your SQL syntax during import [duplicate]
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
48 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 ...
73
votes
2answers
46k 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
21 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
76 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
58 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
253 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
129 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
36 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
17 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
40 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 ...
