Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
695
questions
0
votes
2answers
26 views
MySQL Dump only views, triggers, events and procedures
how are you?
I need to generate a file with all triggers, events, procedures from a server's databases and tried the two commands below
mysqldump -u root -p --all-databases --host=127.0.0.1 --no-data
...
3
votes
2answers
106 views
Importing a 16 GB MySQL file silently fails
I've looked at a lot answers here MySQL any way to import a huge (32 GB) sql dump faster? and on Server Fault but haven't found a solution to not being able to import a 16 GB MySQL file completely; it ...
0
votes
1answer
58 views
Wait state: Statistics
We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics.
According ...
0
votes
2answers
22 views
How do I get a list of all tables in a mysql db, ordered by foreign key dependencies?
I want a list of tables, ordered by foreign key. So if table "Book" has a FK dependency on table "Author", Author would be listed first and Book second. I have no cyclic dependency ...
0
votes
0answers
17 views
MariaDB export only business data
I'm looking for a switch for mysqldump that will make it export only the business data.
What is business data? Everything except "mysql system data" such as the entire mysql database and ...
1
vote
1answer
44 views
Mysql Innodb Error Number 24 Means 'too Many Open Files'?
I was in the middle of restoring my database using mysqldump and it errored out this
ERROR 1030 (HY000) at line 637885: Got error 168 from storage engine
And when i check the logs, i am seeing this :-...
0
votes
0answers
16 views
MySQL Table structure and data export - Script sequence is not correct
When I export the data and structure of my MySQL database to a file, I notice that the table sequences in the output file are not correct. For example the script of a child table is put before the ...
0
votes
0answers
21 views
The user privileges I have assigned don't work when someone imports my .sql database file on mySQL
I'm new to SQLs and mySQL, I and my colleagues who are also new to SQLs are working on a database project and I have successfully queried its structures and inserted data into them.
I then created ...
1
vote
2answers
38 views
How to check the last time a table has been modified?
I regularly backup tables individually via mysqldump. To avoid unnecessary backup, I would like to check if a table has been modified since the last backup. By "modified", I mean any change (...
0
votes
0answers
45 views
Data truncated after importing a MySQL 5.7 dump into MySQL 8
I am trying to import a 12G MySQL 5.7 dump (on ubuntu 16.04) into a MySQL 8.0 database (on ubuntu 20.04).
Half way through the import exits and returns the following error:
ERROR 1265 (01000) at line ...
0
votes
1answer
190 views
Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x?
I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance.
mysql -u username -p test < DB-210508.sql
The import starts but fails with the ...
0
votes
0answers
29 views
MySQL 8 can not enable Mast-Master Replication after rollback from GTID Replication
can you help me with this issue please?
I have rolled back from GTID replication to traditional (master-master replication with binlog), and at the same time I’m adding new node to the cluster as ...
0
votes
0answers
29 views
mysqldump has structure but no data, only when run from cron
I have a script with a mysqldump command that looks like this:
mysqldump ${mysql_auth_args} \
--force \
--single-transaction \
--quick \
--compact \
--extended-...
0
votes
1answer
88 views
“Percona-XtraDB-Cluster prohibits use of ALTER command on a table (<example>) that resides in non-transactional storage engine”
I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via ...
0
votes
0answers
36 views
Very slow loading of MySQL DB-backup
I'm working on a transition of a web-site, which uses MySQL database as a backend.
The transition involves copying the production database -- into another on the same server. We do this by taking a ...
1
vote
0answers
20 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
14 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
38 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
242 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
15 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
44 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
56 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
503 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
25 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
34 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
33 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
89 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
33 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
49 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
28 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
49 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
36 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
101 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
31 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 ...
1
vote
1answer
2k 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
49 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
49 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 ...
6
votes
1answer
360 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
28 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
147 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
18 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
118 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
160 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
40 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
273 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
27 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
28 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 = @...
