All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
1answer
45 views

Suggestions for large DB maintenance

i have one large database (more than 1,500 tables and total db size of 56GB) and i would like to ask for tips and guidance regarding the maintenance of such large database! Will the size be a problem ...
0
votes
0answers
137 views

MySQL8 Restore from Physical Files - Innodb

I recently had one of my VM's go down (MS Server 2016) and the only version I am able to get up and running is two weeks old. However, I do have a backup of my C:/ drive from last night that has my C:/...
0
votes
2answers
259 views

Is it possible to backup and restore InnoDB tables on Oracle's MySQL 5.7.19 Server with Percona Xtrabackup?

I have the next configuration: OS: Ubuntu 16.04.2 x86_64 MySQL Server: Ver 5.7.19-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) installed from Ubuntu's repos Percona Tools: innobackupex version 2....
-1
votes
2answers
263 views

page size Issue in mysqlbackup command

I am facing issue while taking MySQL remote backup using enterprise utility mysqlbackup, the output and the command used to take backup is is as following 170717 09:30:02 MAIN INFO: A thread ...
1
vote
0answers
400 views

Missing tables from MySQL restore

I'm not a DBA but a JIRA admin. I run a cron backup of my mysql InnoDB JIRA database which runs a script that executes the command below: mysqldump -v --single-transaction --user=DATABASEUSER --...
0
votes
1answer
162 views

MySQL/MariaDB data directory backup

I am using a solution of backblaze B2 / duplicity / duply to create backups of my databases. I upload full backups and incremental backups to B2 using duplicity via duply. The incrementals are done ...
3
votes
2answers
1k views

Fast backup & restore of MySQL database to revert recent changes

I have a decently big MySQL DB (InnoDB) with size about 400 MB. Thus dumping the whole DB with mysqldump and restoring it back takes forever (more than 15 minutes, which is unacceptable). For ...
2
votes
1answer
307 views

Restoring MySQL database from small ibdata1

I've got a database from a dead Rasperry Pi, which I want to restore. As it doesn't boot anymore because of a damaged SD card, I only could recover the data files, but not do a normal dump. The server ...
0
votes
2answers
369 views

How to hot-backup mysql slave database with read operations

I have a slave database of about 120GB in size on which application is performing select statements. I also have a backup system (Acronis backup) available to me from a hosting company. Unfortunately ...
3
votes
1answer
690 views

MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles

Using the backup procedure described below, it gives warnings. These warnings start to appear from the 2nd till 6th (last) incremental backup that is being applied to the base backup. How to solve ...
0
votes
0answers
60 views

MySQL Storage Engine and Memory

I recently found out that, the databases does not have standard architecture(data model), some of them are, No relationship between tables(entities). No indexes defined. Only integer and varchar data ...
1
vote
0answers
110 views

How to restore InnoDB from files

here is the situation I fu*d up my mysql setup, before I did that I copied entire /var/lib/mysql directory to another location. Now fresh installation of mysql is back and I cannot restore my files. ...
1
vote
1answer
350 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-...
1
vote
1answer
600 views

How do flush innodb table, copy files, then unlock tables from Windows bat file?

I can successfully execute "FLUSH TABLES myTable FOR EXPORT;" in MySql Workbench. Then I can copy the cfg and ibd files I need, and then I can go back to workbench and run "UNLOCK TABLES" and all is ...
0
votes
1answer
904 views

Restore MySQL Database with only .frm files

One of our servers (running Ubuntu 12.04) recently crashed during a power outage, and MySQL refused to start back up. After a lot of trial and error, I decided to completely reinstall it. Since I wasn'...
4
votes
2answers
13k views

Is possible to backup database online using rsync?

Some people tell me that the backup database on-the-fly using rsync is possible. I think it is possible but not safe because some log, buffer... is not flushed. Database is being recovered from the ...
4
votes
4answers
6k views

How to backup live mysql database?

I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website. How do I make backup of my live ...
3
votes
1answer
879 views

Can I use xtrabackup to import a database without altering others?

Context: Each time a demo branch is built, I want to download a copy of the prod database and setup it onto the demo server. This way under the MySQL I have X database corresponding to X demos. I ...
70
votes
5answers
94k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
4
votes
1answer
4k views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
10
votes
1answer
17k views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
7
votes
2answers
3k views

Copying datadir directory enough for backing up a database with InnoDB tables?

I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer. I used to copy database with only MyISAM ...
1
vote
2answers
2k views

Is it safe to copy DB files directly from an LVM snapshot?

I saw two different approaches for backing up database with LVM snapshot. This article suggests spawning another MySQL instance to do an SQL dump. MYSQL Performance Blog suggests that it is safe to ...
9
votes
3answers
41k views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
2
votes
1answer
583 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
3
votes
1answer
2k views

Why didn't MySQL keep the innodb_file_format after loading a mysqldump

I was loading a mysql dump today when I ran into ERROR 1118 (42000) at line 279: Row size too large (> 8126) Which I thought was weird because I know I had turned on row compression and was ...
1
vote
1answer
760 views

Consistent backup of large DB --single-transaction

I need to mysqldump a large DB (330 GB) in order to set up a slave for replication. Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the ...
7
votes
4answers
17k views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
1
vote
2answers
3k views

ib_logfile0 and ib_logfile1 got deleted

After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed: ...
3
votes
1answer
1k views

Deadlock from mysqldump --single-transaction, is it possible?

Is it possible to get a deadlock if you ran mysqldump with the argument --single-transaction ? or any other complication ? If it is used to backup a live site with many other querys running at the ...
3
votes
1answer
9k views

What happens if the mysql database's innodb log files are lost?

What I did was /etc/init.d/mysql stop then removed the files: ib_logfile0, ib_logfile1 then modified my.cnf file, variable: innodb_log_file_size and then: /etc/init.d/mysql start and allowed the ...
2
votes
3answers
3k views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
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 -...
2
votes
1answer
924 views

MySQL backup InnoDB

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
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 ...
3
votes
2answers
1k views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with 8GB ...
10
votes
4answers
31k 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 ...
2
votes
4answers
3k views

MySQL Enterprise Backup error: Log scan was only able to reach xxxx

I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup. I get this error: mysqlbackup: ERROR: Log scan was only able to reach ...
0
votes
2answers
2k views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
7
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 ...
5
votes
3answers
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
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 ...
0
votes
1answer
187 views

Backup and trimming data idea

Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows. We would like to maintain ...
1
vote
2answers
604 views

Copying a database for testing in a safe way

For testing purposes I have to create a copy of a production MySQL database that cannot be touched. These are the steps I would follow: Create a reduced size database backup: mysqldump prodDB --...
1
vote
1answer
901 views

Restore the data from Snapshot Backup when the data spans over multiple directories?

Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF. We have done some testing work on one of the slave say server A.and it takes a huge amount of ...
3
votes
1answer
365 views

Xen snapshots and live Innodb tables?

I am wondering if I can trust my host doing it's nightly snapshots with live Innodb tables. From what I have read: "Innodb is ACID-compliant, so you can "safely" use fs snapshot with Innodb tables." ...
11
votes
5answers
14k views

Incomplete mysqldump

I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB. ...
5
votes
2answers
7k views

Different MySQL Datafile Sizes After Restoration

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86. Some weeks ago, we upgraded the company's ...
175
votes
7answers
209k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...