mysqldump : MySQL's Standard Dump/Backup Utility
0
votes
1answer
33 views
Database user specified as a definer
I have a view in my database. problem is below
Error
SQL query:
SELECT *
FROM `lumiin_crm_prod`.`v_contact`
LIMIT 1 ;
MySQL said:
1449 - The user specified as a definer ('lumicrm'@'%') ...
1
vote
3answers
67 views
Issue on mysqldump in MySQL5.6
I did upgrade the MySQL server from 5.5 to 5.6
I am trying to take dump for a table like this
mysqldump -uroot -p dba admin > D:\admin.sql
But it gets following error
mysqldump: Couldn't ...
0
votes
1answer
129 views
mysql duplicate entry error 1062 when restoring backup
Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.
Requirement: To create an exact replica 'db4' of an existing DB 'db3'.
Procedure ...
0
votes
2answers
124 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 ...
0
votes
1answer
25 views
Unable to use mysql_upgrade:
I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says-
mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
0
votes
1answer
78 views
mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES
I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command:
mysqldump -uroot -p drupaSite > drupaSite.sql
I get the following error:
mysqldump: Got ...
0
votes
2answers
285 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 ...
0
votes
1answer
26 views
passing --single-transaction in mysqldump command line tool
I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup.
I have however, noticed that if I backup using command line ...
2
votes
1answer
90 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 ...
0
votes
0answers
20 views
'Offline' MySQL backup - server not starting
The MySQL server is no longer starting because the partition is too full. The issue seems to be ibdata (40GB). I wanted to follow these instructions (at 'InnoDB Infrastructure Cleanup')
Now I already ...
2
votes
1answer
53 views
Load different date formats in mysql
I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD.
Is there a way by which I can load DD-MON-YY ...
1
vote
1answer
270 views
How to prevent high memory, CPU and time consumption by MySQL restore?
I have a local test machine with 2 GB RAM and a dual core processor.
I imported a dump on that using
mysql -uuser -psecret < script.sql
script.sql is a 700 MB file which contains a single ...
3
votes
3answers
211 views
How to change data structure in mysql using mysqldump without deleting files
Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, ...
20
votes
5answers
32k views
How do you mysqldump specific table(s)?
How can I dump a specific table or set of tables without including the rest of the db tables?
1
vote
2answers
95 views
Mysqldump tables excluding some fields
Is there a way to mysqldump a table without some fields?
Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of ...
1
vote
2answers
178 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 ...
5
votes
2answers
5k views
What is the recommended way to backup a MySQL/Amazon RDS database to S3?
I have two purposes for this:
To have an offsite backup in case of a region wide problem with Amazon Web Services.
To copy production data from a production billing account to a beta billing ...
0
votes
1answer
141 views
Proper procedure for migrating a MySQL database to another Debian machine?
I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL.
I've created a backup of all databases on the first server like so:
mysqldump -uuser ...
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" ] || [ ...
0
votes
1answer
166 views
MySQL Dump configuration file
I modified th my.cnf file, so that the innodb_data_file_path points somewhere else from the default path. But the mysqldump command seems like trying to dump from the default path.
I found that if i ...
0
votes
1answer
45 views
ERROR 1034 (HY000) : Wrong aligned block, while importing in mysql
I got a SQLdump from my database and now I want to import it in to an other mysql server with same version(5.5.22-0ubuntu1) in ubuntu 12.04 .but at the middle of importing, it said:
ERROR 1034 ...
0
votes
1answer
124 views
importing MySQL dump to SQL Server 2012
I have a database schema from MySQL which I want to import to SQL SERVER, I have tried dumping from MySQL with compatibility for SQL Server, but with no success. Tried dumping as Excel still no dice. ...
0
votes
1answer
145 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
0
votes
2answers
81 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 ...
34
votes
14answers
48k views
How can I move a database from one server to another?
How can I move MySQL tables from one physical server to another?
Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.
I want to move it to a new server, ...
1
vote
1answer
101 views
Compare two MySQL dump files
I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
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?
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
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 ...
0
votes
2answers
210 views
How to restore mysql dump file? [closed]
We have created a dump file (.sql) using the command mysqldump in mysql5.5.
Now we want to restore the same. We are using the command :-
-u[username] -p[pwd] [dbname] < [.sql file]
But we are not ...
0
votes
1answer
162 views
How to copy MySQL Database from Linux to Windows?
I would like to copy a MySQL database form Linux to Windows, from a little research I found this url http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html where the author says we ...
1
vote
2answers
118 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 ...
0
votes
2answers
72 views
How to take a database dump for selective set of values in few tables?
create table foo (id, val1, user_id,...)
create table bar (id, foo_id, val2, ...)
create table baz (id, bar_id, val3, ...)
select * from foo where user_id = 1;
select * from bar where id in (select ...
6
votes
5answers
5k views
slow load speed of data from mysqldump
I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
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
3answers
119 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 ...
0
votes
1answer
58 views
mysqldump including deleted rows [closed]
I'm trying to move a database from a Windows2003Server with mysql 5.0.27-community-nt to a new server with CenTOS. When using mysqldump on the database on the windows server, one table (i haven't ...
0
votes
0answers
38 views
Phpmy admin not showing my table but mysqldump
I have a db running and suddenly when I go into my phpmyadmin under the db it shows no tables and when I tried to do dump it shows me some of my tables are crash. What is best survival in this case?
5
votes
3answers
12k views
How do I change the DEFINER of a VIEW in Mysql?
When I run mysqldump, I get an error:
mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES
This makes sense because foobar is a legacy ...
0
votes
1answer
147 views
Problem with (not) Locking Tables during Restore
First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is:
$string = "mysqldump -u$db_user ...
1
vote
2answers
105 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 ...
2
votes
2answers
81 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 ...
9
votes
3answers
10k views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
2
votes
3answers
3k views
Where does the mysqldump command store the backed up databases?
Am trying to backup my databases through the terminal in Ubuntu using the mysqldump command and it's successful , but where does it place the backed up databases ?
1
vote
1answer
102 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 < ...
0
votes
1answer
54 views
mysqldump not generating USE statement
I've got a small script which allows me to select a database, and a number of tables, then output them via mysqldump.
Currently this does not include the USE 'db_name' statement in the output file, ...
0
votes
0answers
45 views
Best way to merge a Sharded MySQL
I have a MySQL database that is shared across 16 different database servers, and i also have 1 replica for each machine. To save money as product becomes less popular I am wanting to shard down into a ...
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 ...
0
votes
1answer
74 views
Translating backup size to database size
I have a client who gave me a 4 gig datafile from mysqldump that I need to restore somewhere.
Is there a good rule of thumb for translating the size of a backup into the size of the database once it ...
2
votes
1answer
127 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 ...
