mysqldump : MySQL's Standard Dump/Backup Utility
2
votes
1answer
34 views
How can I determine the size of a dump file prior to dumping?
How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
2
votes
2answers
87 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 ...
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> ...
2
votes
2answers
590 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 ...
2
votes
1answer
1k 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 ...
2
votes
2answers
1k 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 ...
2
votes
1answer
128 views
MySQL Dump/Import progress on Windows
I'm going to dump a considerable big datatabase (7gb) from a MySQL 5.1 instance running on Windows into a .sql script and import it into a MySQL 5.5 instance, also on Windows (another machine). I'd ...
2
votes
1answer
373 views
Can I mysqldump an entire database while using where='condition' on individual tables?
I have used mysqldump to dump an entire database,
However, when I want to use --where=condition", on individual tables, I have dumped individual tables
mysqldump --where='condition' mydb table ...
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 ...
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 ...
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
415 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 ...
2
votes
2answers
115 views
mysqldump seems to copy excessively
I have an old_server with 50 databases. I want to create new_server with just 3 databases.
When I try using mysqldump, it seems like all the database directories (although they are empty on ...
2
votes
1answer
1k 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 ...
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 ...
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 ...
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
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
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 ...
2
votes
1answer
381 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 ...
2
votes
1answer
1k views
When are we supposed to set default-character-set for the client?
I'm trying to understand when I'm supposed to set/force default-character-set for the client.
The documentation states:
You can force client programs to use specific character set as
follows:
...
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 ...
2
votes
1answer
92 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 ...
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
2answers
120 views
mysqldump: flush-privileges option
I'd like to get some clarification about the --flush-privileges option for mysqldump.
Here's the description of the option from the MySQL docs:
--flush-privileges
Send a FLUSH PRIVILEGES ...
1
vote
3answers
123 views
There is any option for mysqldump to ignore databases for backup in mysql
we have 40 databases in our server.
we want to take 36 databases backup using mysqldump.
how can i ignore remainig 4 databases in mysqldump command.
there is any option for mysqldump to ignore ...
1
vote
2answers
457 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
2answers
121 views
How does mysqldump take place when there are more than one database?
I'm writing a shell script wherein I'm doing a mysqldump of all databases.
My questions are:
Are the tables of all databases in the server locked during the mysqldump?
If I have to do an ...
1
vote
1answer
542 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 ...
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 ...
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 ...
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
2answers
5k views
How to log verbose output from mysqldump?
You typically save the verbose output of a Linux command like this:
# command > output.txt
But when you use a command like mysqldump, the > option outputs the dump of the database tables to ...
1
vote
1answer
79 views
mysql server password change while restoring
I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to ...
1
vote
2answers
2k views
Set up a MySQL slave using mysqldump to get the initial data
I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
1
vote
1answer
789 views
mysql db backup excluding specific tables
Are there any mysql scripts for taking a database backup, while excluding specified tables?
Also, what is the best way to take backup of a running mysql system?
Regards,
Praveen
1
vote
1answer
2k 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 ...
1
vote
2answers
2k views
How do you encrypt data while performing a mysqldump?
My OS is windows 7.
How can I encrypt data while performing a mysqldump?
From the command prompt, can I take a mysqldump with encrypted varchar and texts values?
Please help me.
Thanks
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 ...
1
vote
1answer
34 views
mysqldump error on bash script
I'm trying to run a bash script to backup my database with Cronjob.
# Backup and compress each database
for database in $mysql_databases
do
if [ "${database}" == "information_schema" ] || [ ...
1
vote
2answers
184 views
how to import mysql system “mysql” database [closed]
This question might be already answered but it's almost impossible to google it.
When I perform full backup of mysql server using
mysqldump --all-databases
then reinstall it from scratch I want to ...
1
vote
1answer
141 views
MySQL Dump all rountines except one
We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
1
vote
2answers
577 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 ...
1
vote
2answers
509 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
118 views
Access denied Error in MySQL and Drupal
We are trying to deploy a Drupal application on our production server.
We see this strange error Lost connection to MySQL server at ''Access denied root@ipaddres password(yes) after deploying.
We ...
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 ...
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
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 ...
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 ...
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 ...
