All Questions
59 questions
1
vote
1
answer
63
views
how to verify mysql backup file using BlackHole
I'm interested in trying this solution: https://dba.stackexchange.com/a/44893/71052, also referenced here: https://dba.stackexchange.com/a/206151/71052
The problem I face is the following:
mysqldump ...
- 131
0
votes
0
answers
516
views
FIX: mysqldump: Couldn't execute '/*!80000 SET SESSION information_schema_stats_expiry=0 */
I've already asked this question in the "Stack overflow" website.
I don't understand why they closed my question even if I didn't get a working solution for my setup.
Why did I submit it?
I'...
0
votes
1
answer
230
views
MariaDB restore error - ERROR 1556 (HY000) at line 2562: You can't use locks with log tables
MariaDB Restore fails
mysql --host mydbhost --port 5500 --user root --password < /var/tmp/dump.sql
Enter password:
ERROR 1556 (HY000) at line 2562: You can't use locks with log tables
Dump ...
- 11
0
votes
1
answer
371
views
While restoring from mysqldump file, how do I write the table names to a log file?
Sometimes I've run into situations where developers have changed structures, added/removed columns on different DB servers, environments, etc for the same table and this causes errors such as ERROR ...
- 119
1
vote
0
answers
518
views
Mariadb large live database dump/restore fails when using recommended options
I have a 58Gb database that is live, using InnoDB engine and I want to dump restore it to a development machine for test purposes. I'm using mariadb 10.4 client and servers on Debian 11 Buster.
I've ...
- 111
1
vote
1
answer
1k
views
Function does not exist then dumping mariadb database to remote host
Im trying to do an initial transfer of my data to a replica server by running this command:
sudo mysqldump -f -u root -pPassword --opt mydb | mysql --host=replica01 -C mydb -u restore -pPassword
It ...
- 123
1
vote
1
answer
1k
views
How to take mysqldump of specific tables across all databases and restore them to a a single database?
I need to take a mysqldump of specific tables across all databases in a cluster.
In this case the cluster has more than 300+ databases. And I need to take the dump of table1 and table2 from each of ...
- 111
2
votes
0
answers
386
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 = ...
- 49
0
votes
1
answer
151
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 ...
- 239
7
votes
1
answer
5k
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?
user218202
6
votes
2
answers
11k
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 ...
- 61
0
votes
1
answer
23
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 ...
- 149
1
vote
2
answers
2k
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 ...
- 137
3
votes
1
answer
13k
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 --...
- 135
0
votes
0
answers
379
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
0
answers
3k
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 ...
- 163
1
vote
1
answer
2k
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 ...
- 6,497
0
votes
0
answers
136
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 ...
- 163
0
votes
1
answer
5k
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,960
1
vote
1
answer
3k
views
MariaDB: point in time backup recovery with binary log using position
Following the below sequence of commands i expected the recovery of backup and subsequent point in time recovery using binary log. However, the database was restored to backup only:
1)
CREATE TABLE ...
- 183
0
votes
1
answer
4k
views
delete tables on import of dump created by mysqldump
There are 2 copies of the same database on 2 different storages which need to be in sync.
For example, database named test_db contains tables T1,T2,T3 tables.
T2,T3 are deleted.
Dump of the 1st copy ...
- 183
2
votes
1
answer
2k
views
MySQL - Fastest restore of 1TB database
I have MySQL Database and its 1.5TB. I need to setup replication for this. I have 32core CPU and 250GB Memory. The problem restore is taking more time even I used mydumper/myloader to backup and ...
- 1,960
3
votes
1
answer
5k
views
When to use mysqldump with '--no-autocommit'? My benchmark shows slower restore
I tested quick and dirty --no-autocommit vs. without this option on MacBook (installed with Homebrew latest MariaDB). I restored to an empty MariaDB a 4.6 GB *.sql file. The dump made without --no-...
- 2,558
4
votes
3
answers
8k
views
Migrating 300 GB mysql database from one server to another cleanly with minimum downtime
I have a 300 GB mysql database which i want to migrate to another server in order to setup Master-Master replication between the two and my main aim is to achieve this with least possible downtime.
...
- 151
1
vote
2
answers
175
views
mysqldump file restore while sql is still being dumped
Im using MaridaDB 10, but my question applies to all mysql variants.
I have large dataset (70GB uncomressed when dumped to file).
The mysqldump process takes 2-3hours. The restore process takes even ...
- 11
1
vote
1
answer
3k
views
MySQLfrm - Using multiple frm files
I am busy rebuilding an entire MySQL DB. There are multiple schemas and multiple tables in each schema.
I am restoring them using *.frm files as all other methods used have either failed or corrupted ...
1
vote
1
answer
5k
views
Fastest way to do a mysqldump restore on Amazon RDS?
We need to restore a database from a 7-Gb mysqldump file to a RDS Amazon instance.
The full restore takes between 3 and 4 hours (probably because of network latency since the dump has to reside on a ...
- 1,334
3
votes
1
answer
7k
views
Restoring a mysql database using --single-transaction
I learned that specifying "--single-transaction" (yes we do have InnoDB) option into the mysqldump tool produces a dump utilizing transactions. However, I am failing to find a way to restore the ...
- 133
2
votes
1
answer
1k
views
Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?
Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space.
Have option to either optimize the table, or dump the table and restore ...
- 279
1
vote
1
answer
2k
views
Lost connection to mysql while restoring dump. Is it safe to resume?
I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS.
The dump was created with the following command:
mysqldump
--all-databases \
--single-transaction \
--...
- 243
1
vote
0
answers
56
views
How to merge MySQL backup files (*.sql) [duplicate]
We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
- 1
5
votes
2
answers
8k
views
How to merge MySQL backup files (*.sql)
We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
- 1
0
votes
1
answer
46
views
mysqldump - does it have non-repeatable DMLs?
We are migrating databases using mysqldump, and since this is the first time I'm doing it, I have a couple of questions.
If in the middle of mysqldump the utility crashes(for some reason- latest was ...
- 11
0
votes
1
answer
1k
views
Restore .dmp file into csv
We have asked our clients for certain data in a csv file format and we just got a .dmp file. I have been trying to open it using MySQL as if it were .dump but it doesn't work.
I have seen other ...
- 101
1
vote
1
answer
2k
views
Migrating all MySQL databases from from one server another. Getting error during import
I create a mysqldump on all the databases on the source server with this command:
mysqldump -h mysql-01 -u root -p --all-databases > newdump.sql
This takes about an hour, but runs fine.
Now, to ...
- 11
2
votes
2
answers
996
views
ALTER DATABASE script preventing restore of MySQL database in another server
While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines.
/*!50003 DROP FUNCTION IF EXISTS `f_my_function` */;
ALTER ...
- 147
4
votes
2
answers
1k
views
An easy way to select one db dump from multiple db MySQL dump
In an urgent situation another person made a backup of all the databases from my webserver to one *.sql file (MySQL dump). Now I have a ~700 Mb file with about 20 different databases. As I only need ...
1
vote
1
answer
190
views
Moving two databases from MySQL 5.1 [Prod] to MySQL 5.6 [Stage], not working [Windows]
I'm in the process of upgrading our production MySQL from 5.1 to 5.6, on a Windows Server 2008 R2 server. What I'm trying to achieve is to move 2 databases [say abc and xyz] from Prod server to a ...
- 11
15
votes
4
answers
36k
views
mysqldump doesn't restore database. why not?
Why doesn't my restore operation work?
I created a dumpfile for a database using:
mysqldump -u root -p databasename > /home/databasename_bkup.sql
I then opened the dumfile and confirmed ...
- 487
0
votes
1
answer
459
views
Recovering Mysql - how to get a dumpfile from wampserver and none running db
I had the following:
- Wampserver/mysql/drupal + batch file with auto backup by use of mysqldump.
- Backup was distributed off and online.
then : my laptop was stolen :-( and I need to recover but.....
- 103
0
votes
1
answer
86
views
I'm building up a large MyISAM table from a few dumps. The first 21GB took less than 10 hrs total. The next 5GB is forecasted to take two months
I had a couple machines building up identical MyISAM tables. I've dumped all of those tables to a few files, and am now trying to restore them all into a single table on my own machine.
The first ...
- 215
1
vote
1
answer
1k
views
Restore mysql from database snapshot
I have a folder containing the snapshot taken from a database.
They are all in the format organisation_product_tablename.sql.
How can I restore them back into the database?
EDIT 1:
I managed to ...
- 113
1
vote
0
answers
171
views
SQL Error while restoring big database in Mysql
I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB.
When I try to restore it in an UBUNTU machine I got this error.
11
votes
4
answers
28k
views
Verify mysqldump backup is corruption free
We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
- 221
3
votes
2
answers
9k
views
Extremely slow mySql restores
Please bear with me as I ask this question, which looks similar to questions already on DBA@SE.
In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database ...
- 81
1
vote
0
answers
2k
views
restore using mysqlbinlog failing
While doing restore from a MySQL binary log file, it is failing with the following error .
mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb
ERROR 1032 (HY000) at line 48: Can't find record in '...
- 173
1
vote
1
answer
6k
views
mysqlbinlog failing with " ERROR 1032 (HY000) at line 48: Can't find record in `` " Error
I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error .
# mysqlbinlog mysql-bin.000035 | mysql -u root -p db
Enter password:
ERROR 1032 (HY000) at ...
- 173
1
vote
2
answers
2k
views
Can't restore mysqldump database backup in Ubuntu 12.04
I'm trying to restore a database backup created using mysqldump. If I do:
$ mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL
the database remains empty, even though I get some screen output ...
- 11
0
votes
2
answers
127
views
synchronizing local and server database
I am developing a billing system software. For this I have created a database it contains many tables database and this is in local system
all transactions data will be stored in local systems ...
- 103
0
votes
1
answer
167
views
MySQL data retained after OS reinstall
Something strange happened today.
I reinstalled my OS and then when I reinstalled MySQL workbench some of my old data was still there. I think the actual local db is empty, but all the connections, ...
- 101