Linked Questions

0
votes
2answers
714 views

MySQL Database Dump To Project Folder [duplicate]

Currently I am dumping my database (running under Linux Mint), from the command line, using the following:- mysqldump -u[user] -p[password] MyDatabase > MyDatabase.sql Now rather than dumping to ...
68
votes
1answer
130k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files increase in size day by day. It has already consumed 290GB of space. The tables in the servers are mostly InnoDB and there are high read and write ...
15
votes
1answer
24k views

What are the optimal mysqldump settings? [closed]

After some search I ended up with the following settings: mysqldump\ --host=localhost\ --port=3306\ --databases ****\ --user=****\ --password=****\ --default-character-set=...
17
votes
1answer
21k views

Does mysqldump -all-databases include all objects

Does mysqldump -all-databases include all objects ? I have to migrate all databases to new server.
11
votes
4answers
36k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
11
votes
2answers
31k views

MySQL replication: 'Duplicated entry for PRIMARY key'

Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync. Basically 'mysqldump' was running almost whole night and then the ...
6
votes
4answers
7k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
5
votes
3answers
3k views

Non-Blocking MySQL Backup

I'm running a MySQL server with a couple of customer databases, some of which are quite large. We are doing a complete mysql dump once a day. It takes about half an hour to complete. During this time,...
8
votes
1answer
6k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
4
votes
2answers
3k views

Is there a Successor or an Alternative to mk-parallel-dump?

Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in ...
3
votes
3answers
4k views

MySQL backup with no downtime [closed]

I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu). My DB is pretty large: ~250GB. I'm looking for a backup tool/service that will do the following: full backup no downtime / locks ...
5
votes
2answers
6k views

Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…? [closed]

I manage a PHP/MySQL website : max 500 simultaneous users / average of 4000 per hour in update/read 1 MySQL Community Server (5.1) - (Xeon / 16 Go RAM) InnoDB Storage engine on 100 tables for 1.4 Go ...
2
votes
1answer
13k views

How to Create Snapshot Backups in MySQL

I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup. This is what the book says: flush tables ...
1
vote
1answer
7k views

MySQL: ERROR 126 (HY000): Incorrect key file for table + slow logical backups

I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error: mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ...
5
votes
2answers
2k views

How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast. The problem is, from research, MyISAM is okay with being ...
3
votes
1answer
4k views

MySQL dump - how to order tables?

It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running: show tables; I would like to reverse that order - is it possible to do without getting ...
5
votes
1answer
2k views

MySQL Server Backup

I was wondering if there was any way of making a backup of an entire MySQL Server at regular intervals to a specific location. (If the version is required I can get it easily). If there is, I must ...
3
votes
2answers
2k views

Quick MySQL Backup

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack. Or mysqldump will work ...
2
votes
1answer
3k views

Moving a large MySql database(Over 15 GB) from server to server mysqldump

I need to move a mysql database that is about 17 GB that is on a Cpanel/Centos based server that I do not have root access, to a Centos server that I have full root access. I do have limited SSH on ...
4
votes
2answers
1k views

Difference between backup methods: Oracle and MySQL

I was reading that Oracle databases have cold, warm, full and redo backups. Questions How is it possible that MySQL doesn't have as many options? I have read that MySQL has cold backups (it puts ...
2
votes
2answers
2k views

How to enable all the possible backup options of MySQL

I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to ...
1
vote
3answers
3k views

“Too many connections” when running MySQLDump

MySQL 5.5.x on Windows, 4GB RAM. I have a Wordpress Multi-Site install. The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now) I am running mysqldump ...
3
votes
1answer
2k views

mysqldump freezing on a specific table

I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376). My dump command: mysqldump -uxx -pxx -...
1
vote
1answer
1k views

what's practical example / use case SELECT SLEEP() for DBA

I don't know how to use SELECT SLEEP(). It looks like a dirty hack. I found an interesting question How and when to use SLEEP() correctly in MySQL?. The answer is developer oriented: If you don't ...
1
vote
1answer
1k views

Can I use a mariadb/mysql dump from older version in newer version?

I'm a mariadb/mysql newbie. I want to take a complete backup. Apparently this is the way (please correct me if I'm wrong): mysqldump \ -h... -u... -p... \ --hex-blob --routines --triggers \ --...
0
votes
3answers
2k 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 ...
2
votes
1answer
1k views

Why does importing a MySQL dump cause so many I/O?

I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many I/...
3
votes
2answers
459 views

Writing transactions to .MYD file to take Backup

I'm trying to backup MySQL DB by archiving the data directory of required databases. I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
0
votes
1answer
2k views

Global Read Lock for Snapshot of MySQL Data

I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: ...
3
votes
1answer
641 views

Best way to backup mysql database?

How do you backup your mysql database so you can be sure that you are 100% protected of data loss whatever problems that may happen? I am thinking to rent one or two servers that i will use only for ...
1
vote
1answer
627 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have a 150GB database and my ibdata1 file size is 240GB so I want to shrink the ibdata1 file because I have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
-1
votes
1answer
793 views

MySQL database dump bombs out Error 1064 when using LOCK TABLES

This specific machine is running MySQL version 5.0.27. I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is ...
1
vote
1answer
441 views

Is it safe to run parallel innodb single-transaction dumps of individual tables?

I can't seem to find the internals for --single-transaction mysqldump option for InnoDB tables, but is it possible to use --single-transaction for single tables in a parallel dump so that the --single-...
3
votes
0answers
536 views

Are `--events` and `--routines` really needed to get a `mysqldump --all-databases` of an entire mysql instance?

Several fine answers on here have already exquisitely argued that mysqldump --hex-blob --events --routines --triggers [...] --all-databases should be all - modulo storage-engine considerations - ...
3
votes
1answer
221 views

Backup and off-site restore setup

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one. Our current situation is ~100 GB of InnoDB and MySAM data in ...
2
votes
1answer
276 views

mysql replication and innodb_file_per_table

Is it possible for the master (5.1) to have innodb_file_per_table = 0 and for the slave (5.1 or 5.5) to have innodb_file_per_table = 1 ?
2
votes
1answer
148 views

Backing up large MySQL DB for DR purposes

Fundamentally I am just trying to come up with the best way to back up my MySQL DB nightly. Complicated by the fact that: its a 400 gb DB and growing its running on windows I am not permitted any ...
0
votes
0answers
75 views

How I optimize data dump in oracle 11g

I've an Oracle 11g Database, now the data is very huge, I've an approximately 858 Object in 1 schema and i have 20 Schema, and it's about approx 20 TB in size. I've tried using Navicat Premium v11 to ...
1
vote
1answer
43 views

How to implement effective MySQL database backup mechanisms?

MySQL database backup is really critical for my company's business, can anyone share some experience on this?
0
votes
1answer
13 views

migrate mysql DB between two servers using replication

I need to perform some steps, I have to migrate our company live DB to a new server, if I use normal way such as lock table, mysqldump, scp, mysql restore, it take around 5-6 hours, so I want to make ...