Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

0
votes
0answers
16 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 ...
0
votes
2answers
19 views

mysql - import only schema from full dump?

I have a full database export (single file, schama + data), but I would like to import only the database schema. Is there any magic option or trick to achieve this? I'm NOT able to perform a "...
0
votes
0answers
31 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 ...
0
votes
0answers
39 views

large dump: mysqldump: Got error: 2013: Lost connection to MySQL server

I need to dump to a text file a large database, which comprises one laaaarge table. I'm ding it like this: # mysqldump -p --single-transaction --quick --max-allowed-packet=2147483648 --tab=/mnt/...
1
vote
1answer
35 views

SQL dump loads with Error 1064

Looks like this issue is just the wrong version of MySQL, need to go to 5.5 So I have a backup of an offsite source SQL database. I am not sure how they are preforming the data dump but I am trying ...
0
votes
0answers
89 views

MySQL reporting version mismatch due to picking up previous installation

To solve a mysqldump "Workbench was unable to get mysqldump version. Please verify the log for more information.", I opted for the first solution at https://stackoverflow.com/a/44851129/175241 But ...
0
votes
1answer
25 views

Mysql replication rebuild dump database lock

I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like: FLUSH TABLES WITH READ LOCK; SHOW ...
1
vote
1answer
54 views

How to fix the sys.host_summary table in MariaDB 10.3.13

using mysqldump while doing a backup gives the following error: mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view ...
0
votes
2answers
101 views

Mysqldump multiple tables with where clauses in single transaction

Suppose, we have 2 tables: t1,t2 in database db: Expected the below code to dump tables based on conditions specified in the where clauses: mysqldump --login-path=login_path \ --single-...
0
votes
0answers
11 views

mysqldump and mysqlcheck not returning any output

Because I am unable to start the mysql daemon, I have edited my my.cnf files so that innodb_force_recover=6. Now I am able to start the mysql daemon. I guess the data are corrupt somewhere so I am ...
0
votes
1answer
24 views

Batch query a lot of MySQLdump files

I have a website for an event. I set up a cron job to back up the database every hour. The backup command is mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(...
0
votes
1answer
30 views

Safe online dumping of an InnoDB database in the presence of DDL changes

When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
0
votes
2answers
30 views

(Partially) clone a huge DB for DEV purposes

I want to setup a dev and test environment for website that has a huge MySQL database. Since this DB is so big, it is quite inconvenient to clone it 1-on-1, because it simply takes too long and uses ...
0
votes
1answer
76 views

Unable to export 8.0.1 MySQL database using MySQL Workbench 8.0

I'm unable to export my 8.0.1 MySQL database using MySQL Workbench 8.0. When I go to perform the export via the "Administration" table, with all the default settings (I didn't change anything ...
0
votes
0answers
22 views

How to effectively, and efficiently and the simplest way to partition and move data from one db to another?

We have a large database with client data, with many entities, but we dont allow connections to the production db (mysql) due to security. Only option remaining is to move some data to development db (...
0
votes
1answer
81 views

How to restore dump from MariaDB 10.1 to MySQL 5.5

I generate backups of my databases by using mysqldump.exe -u root --all-databases > mariaDbBackup.sql. When I try to restore my dump by doing mysql -u root -p < mariaDbBackup.sql, I get an ...
0
votes
0answers
9 views

MyISAM Mysqldump failing with a 1.5GB database on a VPS

It's basically because of a memory issue. I just downgraded to 2GB ram on this openvz vps. And the swap can't be changed from 512mb. Another database at 350MB has no issue finishing. But the 1.5GB db ...
0
votes
0answers
10 views

How to know if a sql dump has been already imported?

I am writting a Dockerfile where the entrypoint script checks if any *.sql file present inside initdb.d dir then it import that *.sql file. However as this import mechanism is a part of entrypoint ...
0
votes
0answers
37 views

How to ignore errors in mysqldbcopy

Is there an option to ignore errors and continue copy in mysqldbcopy
1
vote
1answer
120 views

Slow mysqldump on big table

I am running MySql 5.6 on Google cloud as VM instance, one of the tables contains 1500,000K rows with 676G .ibd file. using mysqldump --opt --add-drop-table --quick DATABASE BIG-TABLE >DATABASE....
1
vote
1answer
27 views

Error while restoring the table dump

ERROR 1067 (42000) at line 25: Invalid default value for 'created_on' I'm getting this error while restoring a table after taking the dump(remotely). I have used mysqldump -h (hostname) -u -p (...
1
vote
1answer
40 views

Import Windows MySQL dump on a Debian Linux server

I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records. Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, ...
0
votes
1answer
90 views

How to specify “partition (partx)” in “mysqldump” command?

I have to export some data from a table which is partitioned, and with some extra condition So I use mysqldump command,and use --where"xxxxxx" options as below mysqldump -hxxx -Pxxx -uxxx xxx rtm --...
1
vote
2answers
77 views

mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

I'm struggeling the last two days with the following issue: Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to ...
1
vote
2answers
186 views

Mysql: set variable in mysqldump

I recently added a global variable in mmy.cnf max_statement_time=120 ; to stop long running queries and to throw an exception. Its working fine. Then I am unable to run mysqldump for backing up all my ...
0
votes
1answer
194 views

mysqldump from AWS RDS slowing down

Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using - mysqldump -hxx -u xx -p --extended-insert --single-transaction --max_allowed_packet=1G db > ...
1
vote
0answers
18 views

How to fully dump mySQL database via workbench?

I am exporting database in SQL file using workbench. Export is done successfully, but when I import data and compare record counts with older database, some of the records are missing. Not all records ...
0
votes
1answer
64 views

mysqldump insert data to exists table

hellow Dumped x.sql file from db and then trying to find way to insert data from x.sql file to existent tbl in another server. But mysql execute all the concept of the x.sql ( that include "drop ...
1
vote
1answer
155 views

Backup via mysqldump. Is this safe to do on production or will it crash the server?

I want to run this command during the time in which the production server is running the application: mysqldump -u sbp -p databasename_production > databasename_development_copy.sql Is this safe ...
1
vote
1answer
162 views

MySQL Enterprise Backup download site

I was given a sever to use for tests that has MySQL commercial version installed. I would like to test backups using MySQL MEB, but cannot find the download site. I have seen many blogs mentioning ...
2
votes
1answer
63 views

mysql dump best practices for development

I have an application that has a large database that includes, among many other tables, a table called content and a table called users. I would like to set up a development environment for this ...
1
vote
1answer
61 views

Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks,...
1
vote
2answers
35 views

Importing huge dumps into a mysql server 5.5

I want to import a couple of huge .sql dumps (~600G each) into a mysql server 5.5 running on an Ubuntu 14.04. The machine has about 8G of RAM allocated to it; The previous machine had 128G; Storage ...
1
vote
1answer
243 views

Getting Master Current Binlog and Binlog Position in Replication Backup

I want to take the current binlog file and binlog position of the master server in a mysqldump on the slave server. I cannot use master-data=2 on slave - how can I do this nevertheless?
0
votes
1answer
654 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: ...
-1
votes
1answer
49 views

Web interface or tool for developers to run select query with out access database directly [closed]

I want a tool or web interface that can allow inside users (developers,..) to run select query with out access the database directly. I don't want to give sql user to all the developers for the ...
0
votes
1answer
243 views

0kb backup using mysqldump

Same question has been asked here but its not helping me out. In my python utility, I'm trying to take MySQL database backup using mysqldump. It runs successfully but I'm getting 0kb file in my ...
-1
votes
2answers
109 views

check syntax errors on mysql dump file

I have an issue to check syntax error on MySQL dump file. Please inform me a tool or command to check syntax on MySQL dump file. Thank You.
0
votes
0answers
5k views

mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

I am having some difficulty with my MySQL database. It appears the server suffered from a power outage during the night. As a result, MySQL wouldn't start when it reboots. I viewed the error log ...
1
vote
1answer
1k views

Mysqldump with ansible for database backup

I am trying to backup remote database using ansible and mysqldump. I have installed ansible in my local server.Generated the ssh keys. I also sent the public key to the remote server. Everything is ...
1
vote
1answer
32 views

Restore MySQL tables with data folder only?

I have many MySQL databases with many tables and I’m trying to figure out if I can restore all of it only using the content of my MySQL data folder. Or should I just not bother and use the following ...
1
vote
1answer
234 views

mysqldump generates incorrect create view queries

I use mysqlpump to backup database: mysqlpump --single-transaction --add-drop-database --skip-definer \ --databases mydatabase --result-file /opt/myservice/backup/export.sql My DB contains several ...
1
vote
0answers
55 views

Mysql dump is not full

I have installed fresh ubuntu 16.04 with apache2.4 and php7.0 on it and then installed mariadb. Here is version: mysql Ver 15.1 Distrib 10.0.35-MariaDB, for debian-linux-gnu (x86_64) using readline 5....
1
vote
2answers
312 views

Significantly Slower DB Dumps on AWS RDS After Turning on Encryption?

Turning on encryption on an AWS RDS MySQL instance results in significantly slower database dumps (using mysqldump). mysqldump -u test -p -h xxx.aws.com > dump.sql Is there a reason for ...
0
votes
1answer
502 views

MySQL Workbench is adding --enable-cleartext-plugin option on mysqldump command

For some reason my copy of MySQL Workbench on one of my servers has started adding this --enable-cleartext-plugin option on every mysqldump command, which results in this error every time. 20:13:02 ...
-1
votes
0answers
33 views

Import MySQL Dump into Database Table with Another Table Name [duplicate]

I have exported the selected records from a table using mysqldump using --no-create-info and --where options. But the dump contains a statement like INSERT INTO `table_name` VALUES I cannot ...
-1
votes
0answers
43 views

How to take mysql backup more than 300 GB?

For Syncing mysql replication we need to take backup of master data more than 300 GB.Which is the best method for backup in this scenario ?
0
votes
1answer
662 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 ...
0
votes
1answer
88 views

Mysql Dump Taking longer time

Mysql Dump Taking longer time I have 188 databases, which I am individually dumping in a loop using a shell script as below echo "$(date +%b-%d-%y) Dumping Started at $(date +%r)" | tee -a $DEST/...
0
votes
1answer
389 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 ...