Tagged Questions
1
vote
3answers
49 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
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
23 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 ...
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 ...
0
votes
1answer
30 views
Mysql 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
1answer
33 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
77 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
1answer
44 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
2answers
78 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 ...
1
vote
1answer
96 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
33 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?
0
votes
2answers
123 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 ...
2
votes
1answer
88 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
1answer
159 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
116 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
71 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 ...
1
vote
2answers
170 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
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
110 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
0answers
36 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?
0
votes
1answer
56 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
1answer
140 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
94 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 ...
0
votes
1answer
141 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
2
votes
2answers
76 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 ...
1
vote
2answers
104 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 ...
1
vote
1answer
93 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
1answer
137 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 ...
0
votes
1answer
155 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
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
138 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
2answers
263 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
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 ...
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 ...
2
votes
1answer
125 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 ...
0
votes
1answer
96 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
541 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 ...
0
votes
1answer
591 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 :
...
2
votes
2answers
223 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
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
...
3
votes
1answer
115 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
3answers
203 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, ...
1
vote
1answer
522 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 ...
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 ...
5
votes
1answer
975 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:
...
3
votes
3answers
943 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). ...
1
vote
1answer
126 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
494 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 ...
