mysqldump : MySQL's Standard Dump/Backup Utility
0
votes
1answer
98 views
mysqldump, preserve update_time and modify_time attributes as listed by show table status from
Does mysqldump preserve the create_time and update_time attributes that are output by show table status from?
If not, is there an option that does this?
From the manual it looks like mysqldump ...
1
vote
2answers
575 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 ...
7
votes
2answers
12k views
Safest way to perform mysqldump on a live system with active reads and writes?
I'm not sure if this is true but I remember reading if you run the following command in linux
mysqldump -u username -p database_name > backup_db.sql
while reads and writes are being made to a ...
0
votes
1answer
71 views
Incomplete MYSQL Dumps
I am using MySQL dump to back up my MySQL Databases and I'm having trouble with a couple dumps on one box.
Server Specs
Windows 2008
MySQL 5.1.48-community
I've tried various ...
5
votes
5answers
7k views
How to import a .sql file in MySQL?
I am trying to import a .sql file using MySQL Workbench and I get this error:
ERROR 1046 (3D000) at line 28: No database selected
I have first created an empty database called with the same name as ...
0
votes
1answer
611 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
1answer
117 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 ...
2
votes
1answer
61 views
Is there any risk associated with stopping mysqldump incomplete?
I started to backup 30GB table at wrong time and now I want to stop it because it is estimated to take 7.5hours.
I used following command and options:
mysqldump -u db -p user table --hex-blob | gzip> ...
7
votes
2answers
3k views
How can I monitor the progress of an import of a large .sql file?
I am importing a 7 GB foobar.sql to restore a table in a local database.
$ mysql -h localhost -u root 'my_data' < foobar.sql
$ mysql --version
/usr/local/mysql/bin/mysql Ver 14.12 Distrib ...
2
votes
2answers
229 views
MySQL, import SQL dump with another database
I have an SQL dump of db1 that contains this line:
"USE db1"
If I import that file into db2 with:
mysql -D db2 -uroot -p < /var/backups/db1.sql.gz
Is there the risk that db1 will be overriden?
...
1
vote
1answer
2k 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 ...
2
votes
2answers
411 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 ...
1
vote
2answers
423 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 ...
3
votes
3answers
1k 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 ...
4
votes
1answer
558 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
$ ...
1
vote
3answers
1k views
How to convert a MySQL database to PostgreSQL?
Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.
1
vote
1answer
294 views
How mysqldump replaces special characters during dumping records?
i am trying to dump a particular row using java.
So that i retrieve the data by using record sets and i manually written sql script code by appending the dynamic records.
But my problem is i could ...
2
votes
2answers
237 views
mysqldump reimport isnt working correct
I am trying to restore this database with my sql file I have...yet just get this output with it not importing anything into my db. Thoughts?
[root@domU-12-31-39-0B-06-AA data]# mysqldump ...
1
vote
1answer
1k views
mysqldump: Error: 'Can't create/write to file
Can't create temporary files on /var/tmp.
mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces
On my OS.
id mysql
...
16
votes
4answers
21k views
MySQL DB import/export command line in Windows
How to export / import a database in MySQL through the command line?
MySQL system configuration, WampServer server installed.
OS: Windows
The advice for my local host, not an live hosting...
3
votes
2answers
185 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 ...
5
votes
1answer
1k views
How should I migrate a large MySQL database to RDS?
I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here:
...
1
vote
1answer
540 views
mysql.proc keeps crashing, cannot do a mysqldump?
Due to some problems with InnoDB, I'm going to dump all databases to a new server:
mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server
The dump process stopped with ...
0
votes
4answers
520 views
How to do a mysqldump with a use database in the dump
So the command I normally use to get a database sent to another server is:
mysqldump -u user -p --add-drop-tables database *file*
But I always have to go into the file and add a line at the top:
use ...
3
votes
1answer
544 views
MySQL dump restore - how to retain comments
So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers.
Some of our evil triggers had a comment lines starting with the # character.
When I ...
2
votes
4answers
1k 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 ...
2
votes
1answer
81 views
MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
I'm trying to estimate my db traffic not including backups (mysql 5.1.41).
I use show global status; to get the parameters bytes_sent and bytes_received.
Questions
Do these numbers include ...
3
votes
2answers
1k views
Slow MySQL Database Import with mysqldump and USE .. SOURCE
I have about 12 tables in MySQL innoDB, one of them has 11 million records in it.
I used this command to back things up:
mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz
...
2
votes
3answers
546 views
How to detect a running mysqldump?
Here are the things I can't change:
We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
2
votes
2answers
2k views
Why is my MySQL dump file and my phpMyadmin export file different sizes?
I am looking to create a cron job to backup my MySQL database. I am using this command:
$ mysqldump --user="User" --password="Password" -A
--single-transaction > /home/user/Export-`date ...
2
votes
1answer
222 views
How can I restore a single record from a MySQL backup file?
Occasionally we have an issue where a record's previous data might get wiped while editing.
We backup our database every night for issues like this, however sometimes I am not available to do a ...
1
vote
0answers
73 views
MySQL replication backup
How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.
What happens if,
I issue or schedule ...
1
vote
1answer
82 views
How export a database based in a primary key of a table?
So, I'm trying to export all the DB structure of my MySQL db.
Attached is my DB structure, I have more then this, but I want these tables only.
Basically Tools relate to Factsheets and Factsheets ...
2
votes
2answers
589 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
3answers
969 views
how to use a different tmp dir for INTO OUTFILE select with MySQL?
I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough). ...
2
votes
2answers
478 views
Taking mysql dump from another machine
I have MySql db on Machine A , I have Mysql credentials. And accessing this MySQL from machine B which I have crdential's. But I couldn't take mysqldump from Machine B. Please help me. Both ae Linux ...
1
vote
2answers
508 views
Database table data transfer between different servers using bcp.exe and mysqldump
I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY.
Using MSSQL on two different servers I am using the following command ...
1
vote
1answer
127 views
Creating dump file with use command
I am taking backup of 2 tables from different schema.
I need to create the "use dbname" statement at the beginning of each table.
mysqldump --databases company --tables loadme --databases new_company ...
2
votes
2answers
398 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
2k views
No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00
I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
2
votes
1answer
380 views
MySQLDump wrong dump
i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ?
My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for ...
1
vote
2answers
113 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 ...
2
votes
2answers
114 views
Database Files-Related Issue
I am using MySQL database and using Ubuntu linux machine.
I noticed that my database, db_test, is under the path /var/lib/mysql/db_test
There are files with the suffix .frm, .MYD, .MYI like the ...
3
votes
1answer
545 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
563 views
Exporting\Importing a large MySQL data without mysqldump
I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy.
After running a simple --all-databases dump succeeds, but the import ...
1
vote
1answer
388 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 ...
1
vote
1answer
181 views
Mysql 5.1.X How to upload data from dump file without the partitioning commands?
I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning.
How can I tell the mysql command line to ignore the partitioning ...
1
vote
2answers
456 views
Restore data from remote server using “.sql” file
I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file ...
1
vote
1answer
357 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 ...
0
votes
1answer
71 views
Mysql dump restore doing some funny stuff
I am using linux platform. I also dump the data into a file and I restore into a server and I ran this command
[root@localhost /]# mysqldump -u root -p fms < /device.sql
The funny part it keep ...
