MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

4
votes
2answers
90 views

Is there a way to create a MySQL/Percona Slave without using a dump?

I haven't been able to find anything so I'm sure the answer is no, what are you, a noob? but I feel the need to ask anyway :) Is there a simple utility that would allow a new slave server to be ...
4
votes
1answer
2k views

Which are the minimum privileges required to get a backup of a MySQL database schema?

Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data. Are SELECT privileges required?
4
votes
4answers
5k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
4
votes
1answer
5k views

how to import mysql database structure?

I have a database on Machine A. What I want is to dump my database in such a way that when I import it on another machine (Machine B) only the structure of the tables and Database are created. I want ...
4
votes
2answers
9k views

DROP PROCEDURE IF EXISTS not included in mysqldump

I'm dumping my stored procedures only using the following command: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql but the ...
4
votes
2answers
13k 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 ...
4
votes
1answer
670 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
4
votes
1answer
11k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like ...
4
votes
2answers
4k 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 ...
4
votes
1answer
7k views

Dump of a mysql table on import replaced existing records

I took a dump using mysqldump.. mysqldump -u... -p... mydb t1 > mydb_table.sql Then I imported the dump in another database having same table, but different records.. mysql -u...-p... mydb < ...
4
votes
2answers
7k views

Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find.. [mysqld] max_allowed_packet = 1M [mysqldump] max_allowed_packet = 16M What is the point? Because when you ...
4
votes
2answers
1k 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 ...
4
votes
2answers
4k views

Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script

I have a database that I am trying to recreate at my web host. I used mysqldump to create a sql script. My web hosting company, IPage, has a hosted mysqladmin site where I am supposed to be able to ...
4
votes
1answer
1k views

How does max_allowed_packet affect the backup and restore of a database?

I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
4
votes
4answers
8k views

Is MySql locking table during backup and how to avoid?

I have a scheduled backup that runs daily in MySQL and I have noticed that during the backup a lot of queries that query a frequently used table seem to take forever to complete. This causes the ...
4
votes
1answer
8k views

MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump

while trying to restore a MySql Dump, i get the following error: ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes any ideas?
4
votes
1answer
1k views

What's the fastest way to replicate a MySQL database from one machine to another?

What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN. How to do it without mysqldump? If using mysqldump, is the fastest way $ ...
4
votes
2answers
2k views

ERROR 1166 (42000) at line 65203: Incorrect column name ''

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message: $ mysql -u root < mydata.sql ERROR 1166 (42000) at line 65203: Incorrect ...
3
votes
2answers
420 views

When does creating a .sql backup with mysqldump become too large?

Is there a limit to how large a database can be when executing a .sql backup using mysqldump, or will you run into server problems before that - will the server not be able to take such a large backup ...
3
votes
1answer
5k views

mysqldump not escaping single quotes properly

I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly. Here is an example of the mysqldump generated sql script : ...
3
votes
2answers
3k views

Why is mysqldump file so large?

We have a 2GB MySQL DB. We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here? Questions Why is this file so ...
3
votes
1answer
21k views

How do I open a MySQL Database Dump?

I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
3
votes
2answers
978 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
1answer
5k views

mysql works, mysqldump doesn't

I have mysql 5.5 on my ubuntu 12.04 server. This command: mysql -u root -p works perfectly, but this gives me error: mysqldump -u root -p mydb_name > a.sql mysqldump: Got error: 2002: Can't ...
3
votes
2answers
11k views

How do I dump only the data for all tables using mysqldump

I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the ...
3
votes
1answer
2k views

Restore dropped mysql database

I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years. This year I did not do a backup and accidentally dropped the ...
3
votes
2answers
166 views

How to merge MySQL backup files (*.sql)

We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
3
votes
2answers
69 views

MySQL for Oracle DBA

I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform. 1) In Oracle we have to ...
3
votes
1answer
5k views

How do I show warnings when loading data created from mysqldump?

I've got a large .sql file with large insert into ... values ... statements. Many of these statements are generating warnings during execution. How can I get mysql to print the warnings? If it hit ...
3
votes
2answers
614 views

Mysqldump from production database, every morning

Our Db is about 2GB in size. We run a scheduled job at 3am every morning, when traffic is pretty low, where we export the whole db and sftp it off site. Is it bad practice to do this on a production ...
3
votes
2answers
3k views

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

I'm trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the ...
3
votes
2answers
4k views

mysqldump vs LOAD DATA INFILE

Why would you ever use mysqldump to repopulate a table over load data infile? Here's the back story to my question. We had a table that was incorrectly partitioned. It was supposed to be ...
3
votes
1answer
45 views

mysqldump inconsistencies

I'm planning to take a dump of a table with the --single-transaction option enabled. I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the ...
3
votes
1answer
55 views

mysqldump: How to include index type?

When I dump the schema of my database mysqldump --no-data my_db > schema.sql I get lines like this KEY `index_tbl_on_col` (`col`) but I want lines like this KEY `index_tbl_on_col` (`col`) ...
3
votes
1answer
112 views

Which databases are backed up by “mysqldump --all-databases”?

Which databases are included in the dump generated by mysqldump --all-databases? By my experience, that should be all the user-created databases, plus the mysql database. The database ...
3
votes
1answer
2k views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
3
votes
1answer
676 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 ...
3
votes
3answers
5k 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 ...
3
votes
2answers
991 views

mysqldump fails to dump database

I m using following sql query for creating db backup mysqldump -u[username] -p[password] -h[hostname] [database] | gzip > [backup file path] This generates zipped .sql file in path specified but ...
3
votes
2answers
1k views

php/mysql database redesign and migration: changing databases to tables

I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old ...
3
votes
2answers
1k 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 ...
3
votes
1answer
34 views

missing frm files for restore

We backup a database daily with mysqldump using the following options: --quick --hex-blob --lock-tables --skip-extended-insert --flush-logs --max_allowed_packet=500M Each database gets its own ...
3
votes
2answers
2k views

Tool to export data with all relational data? [closed]

Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design? The purpose is to ease migrations of bits of data ...
3
votes
2answers
10k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
3
votes
1answer
354 views

Backing up a 400-500 GB database on MySQL/Amazon RDS?

We have an Amazon RDS small instance for a MySQL datawarehousing database. Database size will be around 400-500 GB very shortly. What is the best way to backup our database? Can it be done ...
3
votes
1answer
613 views

adding another table to existing MySQL replication scenario

I'm running a simple MySQL replication scenario MASTER ➙ BLACKHOLE ➙ SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE. Now I would like to add several more ...
3
votes
1answer
8k views

Problem with View When Restoring a MySQL Backup

I have a database (InnoDB) with some tables, views and routines... Periodically I do a backup using the command line: mysqldump -u user -ppassword --routines db_name > backup.sql But I have a ...
3
votes
1answer
2k views

How to speed up a loading data into MySQL from a zipped archive

I dumped a large database to an archive with: mysqldump --compress --max_allowed_packet=500M -h myhost mydb | gzip > /tmp/mydb.sql.gz This took about 10 minutes to run, and generated a file ...
3
votes
2answers
807 views

MySQL corruption even after full dump and re-import

We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over. The error message seem to relate to indexes so I assume the data itself is ok and when I ...
3
votes
1answer
3k views

MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database. The bash command looks something like this: mysql -e ...