Linked Questions

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 ...
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 ...
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 \ --...
-1
votes
1answer
790 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 ...
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 ...
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 -...
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
0answers
508 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
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 ...
1
vote
1answer
440 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-...
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 ...
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 ...
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: ...
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=...
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?

15 30 50 per page