Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
703
questions
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 ...
