Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
2
votes
1answer
28 views

MariaDB table is broken after failed dump process

I need to dump MariaDB InnoDB-database consists of several tables. One table that causes problems has nearly 13 Million rows. A fresh install of XAMPP(V.3.2.2) dump process was successful, after that, ...
1
vote
0answers
20 views

mysqldump increases SQL server process memory footprint

i mentioned my initial problem here in this question (config is still the same): mariaDB with a 3 node galera cluster is eating more and more RAM Now I have narrowed the problem down. I found out, ...
0
votes
1answer
28 views

Importing MySQL dumps to multiple MySQL instances in one machine

In case of title is not clear, I would like to explain myself further. I have 6 MySQL instances in my server. Let's call them, M1, M2, .., M6. The reason I have 6 MySQL instances here, I have also ...
6
votes
1answer
186 views

Do I need --skip-lock-tables with --single-transaction?

I am performing regular backups using mysqldump on a schema consisting entirely of InnoDB tables. The mysqldump documentation has this to say about --single-transaction: The --single-transaction ...
-1
votes
2answers
25 views

How can I mysqldump all databases except the mysql schema In Windows

I want to backup all 200+ databases on a MySQL server to all-dbs.sql. I want to exclude the mysql schema. How can I do that IN WINDOWS?
1
vote
1answer
18 views

mysql - import only data from full dump?

I have a full dump from database A. I want to import only the data of this dump into database B. Database B's structure is the same as database A only it misses some tables. How can I do this? The ...
0
votes
1answer
21 views

Will mysql import overwrite my database

So if I take a mysqldump of my production database called production_database, but I've made changes to by test environment database already, called work_database, adding function/stored_procedures/...
0
votes
1answer
39 views

MySQLDump understanding

How come when I write the following line in my CMD: mysqldump -hHOST -uAdmin -p TestDatabase --single-transaction --routines --triggers > "C:\testdump_backup\testdump.sql" Then the file ...
0
votes
0answers
32 views

Backup a large database with MySqlDump / Import useing MySql

So I'm going to make a backup system for my database and wanted to know if there was something specific I have to know before doing too much. I've read that I mught have to change some database ...
0
votes
3answers
53 views

Need Advises on Updating MySQL Database(Both software and hardware)

I have three in production MySQL database (5.7) with about 160GB of Data. The largest table is about 20GB, and the largest schema is about 40GB. I think all tables are innodb table. Currently I don't ...
0
votes
1answer
95 views

How can I create a mysql database (if it doesn't exist) or replace it (if it exists)?

My dump: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql My restore mysql --host=...
1
vote
1answer
17 views

How to restore one mysql database with mysqldump

to make a backup I run: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt mydatabase > out.sql Is it possible to do the ...
0
votes
2answers
159 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
121 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
71 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
49 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
18 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
153 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
20 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
107 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
136 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
534 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
41 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
38 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
191 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
38 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
18 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
66 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
357 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
131 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
11 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
116 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
1k 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
30 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
47 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
47 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
159 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
55 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
80 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
41 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
53 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
40 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
152 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
33 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
3k 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
71 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
79 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 ...

1
2 3 4 5
15