We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
6
votes
1answer
9k views

MySQL - MySQLDump problems with large table

I have a MySQL database that is 10.6GB - that has both MyISAM & InnoDB tables. One particular table (called 'responses') is 8Gb - this table is InnoDB. The live database is replicated to a ...
0
votes
2answers
830 views

Dump single rows (with blobs) to single files

I am creating MySQLDumps for the sake of versioning of a database. The problem currently is a table with blobs. My first approach would be to dump single tables to single files. But next, I want on ...
0
votes
2answers
99 views

Creating Production -> staging flow, how to skip email addresses when doing mysqldump?

I want to run mysqldump or basically, anything that lets me easily export / import mysql db from one server into another. Thing is: I want to export production data as staging / integration / demo ...
0
votes
1answer
377 views

Disable Binary Logging During MySQL Dump

I do apologize if this has been asked before but my search skills were not able to turn up a definitive answer. SITUATION: I have two Windows Server 2012 R2 servers running WAMP 3.06 (which contains ...
0
votes
1answer
118 views

mysqldump to a hostname which is proxy/router (not MySQL server)

I do a mysqldump with those options --single-transaction --routines --events --max_allowed_packet=1G --flush-logs --master-data=2. The desination hostname is not a MySQL server, it's a proxy/router. I ...
0
votes
1answer
258 views

Does mysqldump use in memory pages during backup?

I mean when backup command is issued to database, does backup process read all data pages from disk or use in memory pages?
1
vote
1answer
517 views

MySQL Error: “Table storage engine for `proc` doesn't have this option (ERROR 1031)”

I'm running into a strange problem with MySQL. Searched everywhere for a fix but cannot find anything. Running Ubuntu 14.04. I have a database running on Percona MySQL 5.7.18-16. I want to update ...
2
votes
0answers
557 views

Prevent mysqldump from writing column-level collate and charset?

Using mariadb 10.x, it looks like the mysqldump utility adds column-level collate even if the table was created without them. Is there a way to suppress this behavior? Table created: CREATE TABLE `...
4
votes
2answers
724 views

Backup all privileges related to specific user of MySQL

I want to backup all privileges related to specific user (for example u_1) from a MySQL database and restore that in another server. As described here, The 'mysql' database contains users/privileges/...
1
vote
1answer
377 views

Transfer data from standard mysql server to Percona XtraDB Cluster

My old mysql 5.5 server is having some hardware issue, so now I'm going to setup a new Percona 5.7, two node cluster in my environment and connect my app into it. Now the problem is, How should I ...
2
votes
1answer
774 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 ...
0
votes
1answer
2k 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
votes
1answer
112 views

What dump file options or MariaDB setting is slowing down the load of data?

Context: Loading a copy of a legacy DB for a CRUD app at build by Spring Boot. Primary use case is developers testing the CRUD functionality as it is developed. Problem: The loading operation is ...
2
votes
1answer
788 views

Passwordless mysqldump via shell script in /etc/cron.daily

I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place. The problem: I ...
4
votes
3answers
2k 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. ...
6
votes
2answers
207 views

Issues after schemas migration from mysql 4 to mysql 5.6

Recently I've migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully. ...
0
votes
2answers
44 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 ...
1
vote
0answers
43 views

mysqldump dies on the same row. Analyze is fine. Timeouts are long

I have an innodb table with about 70M rows. Recently a cron job to back it up with mysqldump started failing at the same row. Mysqlcheck also fails. Mysqlanalyze runs fine and prints OK. Alter ...
5
votes
2answers
7k views

mysqldump Not Exporting All Rows or mysql Not Importing

I have a table that contains almost 4 million rows. I exported it with mysqldump and transferred it via scp to another server and imported it with the mysql command but now it's missing several ...
2
votes
1answer
432 views

Mysqldump subset of database with relational data using a master table

I have found a lot of articles describing the concept of exporting and importing relational data, but I have not been able to find any article describing this more generally for a MySQL database, ...
2
votes
1answer
2k views

How to Import all the files in a directory with a single command using mysqlimport?

I'm trying to dump and import some data from a MySQL (5.7) database. Running the command mysqldump --tab=/mysqldump mydatabase I'm able to get a .sql and .txt file for each table. How can I import ...
1
vote
1answer
1k views

How to disable duplicate entry checks in MySQL workbench?

I am trying to import a large database dump (~30 GB) with millions of rows. There are some duplicate rows in there for sure, and I'd like MySQL to just skip those rows and move on without giving me ...
1
vote
1answer
558 views

How can I take a full backup from MySQL without any System tables?

We are downgrading our version of MariaDB, and are looking to take a full backup of all of the databases. I do not want to include system tables since they would be incompatible between the two ...
2
votes
1answer
90 views

Can't restore MySQL DB because of a function

I'm trying to restore a database dump that I took via mysqldump onto a new server. Here's how I made the dump: mysqldump --routines --triggers database_name | pbzip2 > database_name.sql.bz2 ...
0
votes
1answer
485 views

Mysqldump, exclude data from tables by query

It's possible to ignore tables in mysqldump using: mysqldump -u username -p database --ignore-table=database.table1 > database.sql Is it possible to ignore certain records in tables while dumping?...
1
vote
2answers
699 views

MySQL Still purging binary logs and ignoring expire_logs_days

We are running mysql v5.6.17 and I cannot find if there is something else that is causing the binary logs to be purged other than the following: Adding expire_logs_days to the my.ini (and ...
1
vote
1answer
2k 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
0answers
162 views

Bash Script gives error in a loop passing thru multiple Mysqldump

The script I'm experiencing an issue with this bash script: dump_all_dbs(){ $DOMAIN="mysql.domain.com" $DBS_FOLDER_BK="$HOME/_backup/" $DATA=("'1' 'dbname' 'dbuser' 'dbpassword' 'Yes'" "'...
0
votes
1answer
468 views

Any program / command to see a preview of .sql dump file?

If I have [multiple] .sql dump files, is there a command or tool that would allow me to see a preview of the file ? e.g: db-preview backup.sql tables (40) ---------- - users (1200) -...
0
votes
1answer
61 views

Need help in mysql database backup on Windows except mysqldump?

Can anyone tell me what is the easy way to backup 500Gb datasize on windows environment ? My physical memory is 672 GB RAM on server? Which tools will be fast to back up and restore? Note : mysqldump ...
1
vote
3answers
4k views

how to safely stop mysqldump?

I (well, my cron script) tried killall mysqldump and that didn't end very well - mysql server stopped accepting connections after a while. It was Debian Jessie machine with mysql 5.5.55-0+deb8u1. ...
2
votes
2answers
1k views

Importing a large mysql dump while replication is running

So we have a simple master/slave mysql replication running between two CentOS Servers. The master has multiple databases. eg.. Database1 Database2 Database3 The issue is we have a mysql dumpfile ...
0
votes
1answer
451 views

mysqldump stops at 4GB

I'm trying to dump a MySQL (5.7) database of a about 150 GB, out of which most data (about 90 GB) is in a single table. I tried using MySQL Workbench (6.3) to do this through the GUI. The process ...
1
vote
1answer
601 views

mysqldump error

I'm using mysql version 5.0.5 and trying to dump my data, in order to prepare for version upgrading, and get an error. I run: ./mysqldump -u root -p --add-drop-table --all-databases > /data/...
0
votes
0answers
75 views

Copy un-replicated Bin logs from Master to Slave , if Master goes down MySQL 5.7 Enterprise

I have async-Classic file based replication with 2 Nodes as Active - Active. R/W is happening on Primary Node (PN) only and Secondary Node (SN) has been setup as HA only. SN has been configured as ...
0
votes
2answers
606 views

Galera / InnoDB: how can “mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded” happen?

Galera 3 node cluster with MariaDB 5.5 error mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Lock wait timeout exceeded; try restarting transaction (1205) backup command mysqldump --...
2
votes
1answer
1k views

MySQL: error 1118 (42000) row size too large

storage_engine = MyISAM table_type = MyISAM version = 5.1.25-rc database_name.sql dump size is 3.3G: mysqldump -u user -p database_name > database_name.sql When I try to restore the database (...
0
votes
1answer
1k views

Credentials for mysql fail for mysqldump in bash script [closed]

I am having a very strange problem in a script to back up MySQL schemas. The script creates a list of schemas and then runs the mysqldump command for each of them. The credentials are stored in a ...
0
votes
1answer
180 views

Why do I have two collations in mysqldump, under stored procs?

When I run mysqldump --routines I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now). /*...
1
vote
1answer
430 views

MySQLDump issue with a special characters

Code: use test; CREATE TABLE Test_user(Username VARCHAR(15),path VARCHAR(100)); INSERT INTO Test_user VALUES('new1','C:\newfolder\one.xlsx'); INSERT INTO Test_user VALUES('new1','C:/newfolder/one....
0
votes
3answers
1k views

optimize mysqldump for approx. 20GB databases for 8GB RAM

MySQL server having following specifications: 8GB RAM Data & Index of size 49.64GB Dump file size is 17.24GB(18512936320 Bytes) CPU: Processor: Intel(R) Core(TM) i5-3330 CPU @ 3.00GHz ...
0
votes
3answers
93 views

MySQL migration with table verification

I'm in the process of migrating from one MySQL server (5.0.95) to out new MySQL server (5.1.73). I have a script that is transferring the schema's over and doing a table checksum using CHECKSUM TABLE ...
1
vote
0answers
170 views

Attempt to use mysqldump results in usage suggestions only

I attempted the following: mysqldump -u root -p sa -n -d -t --routines --skip-triggers --all-databases > C:\backups\MySQLStoredProc.sql but only received back; Usage: mysqldump [OPTIONS] ...
2
votes
1answer
400 views

Why does mysqldump stop the slave SQL thread for the entire backup?

I realized recently that I was using --master-data to dump a backup from a replication slave when what I really wanted was --dump-slave. After switching to --dump-slave, I see that mysqldump will stop ...
1
vote
1answer
383 views

Rebuild a mysql slave adding innodb_file_per_table without locking the master

I would like to rebuild a mysql slave, adding innodb_file_per_table to the configuration in order to reduce the disk space used (idbdata1 has grown a lot). I want to do it without locking the master's ...
5
votes
2answers
3k views

Disabling binary logging when restoring a compressed MySQL dump

I am busy build a slave of an existing database. I don't want it to build bin logs for the data I import before bringing the slave into the same state as master. This is mostly to save space on ...
1
vote
0answers
305 views

Loading MySQL Dump From one Character Set to Another

As you can see from these configuration differences, the MySQL server is configured with latin1, while the MariaDB server is using utf8mb4: Variable MySQL Server MariaDB Server ...
2
votes
0answers
106 views

Unable to create backup with mysqlbackup.exe (MEB)

I am not able to create a backup with the mysqlbackup.exe The command: Z:\path\mysql-5.7.11-winx64\bin>mysqlbackup.exe -u dba --host=127.0.0.1 --backup-image=C:\test\backup --backup-dir=%temp%\...
1
vote
1answer
851 views

mysqldump takes forever to dump tables from a remote server

I'm trying to pull the database schema from a remote server, but mysqldump is too slow to output (125+) tables' structures. If I enter the said server and run mysqldump --compact --compress --no-data ...
0
votes
1answer
582 views

Dump MySQL table to csv file (utf8mb4_unicode_ci)

I have MySQL table with columns (character-set: utf8, collation: utf8mb4_unicode_ci) to be exported to a .csv file. mysqldump -u *** -p -t -T/tmp/ dbname tablename --default-character-set=utf8mb4 ...