Create table if not exists from mysqldump
I'm wondering if there is any way in mysqldump to add the appropriate create table option [IF NOT EXISTS]. Any ideas? ...
mysqldump
Update an external MYSQL database every..2mines from an internal one.
How can I update a MYSQL database ever few minutes to an external database? I was thinking of doing a database dump every 2 minutes to the other server, then the other server could have a cron job to apply the new data to it's database, good idea? The reason I want to do this is because I have 2 servers, one which doesn't pretty much a...
How to selectively dump all innodb tables in a mysql database?
Hi, I have a database called av2web, which contains 130 MyISAM tables and 20 innodb tables. I wanna take mysqldump of these 20 innodb tables, and export it to another database as MyISAM tables. Can you tell me a quicker way to achieve this? Thanks Pedro Alvarez Espinoza. ...
How to use mysqlimport to read in result of mysqldump --databases
I have successfully dumped an entire MySQL database using mysqldump --databases generating a nice .txt file. However, I can't see how to read the whole file back into MySQL in one go; mysqlimport seems to want just one table at a time. ...
GoDaddy's MySQL dump isn't compatible with server (error 1251)
I've a problem when trying to run mysqldump using the cron manager in GoDaddy's hosting interface. It spew out: Got error: 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client when trying to connect Anyone ever have the same problem? Is there a solution that doesn't involve updatin...
Loading MySQL dump file with bin extension
I know how to restore MySQL DB with *.sql files. But i don't know how to import *.bin files. Content in this file is: 123456789|www.site-name.com 123789456|www.go.com 123987456|www.g1.net 987865432|www.site.org Please help! ...
How to dump a single table in MySQL without locking?
When I run the following command, the output only consists of the create syntax for 'mytable', but none of the data: mysqldump --single-transaction -umylogin -p mydatabase mytable > dump.sql If I drop --single-transaction, I get an error as I can't lock the tables. If I drop 'mytable' (and do the DB), it looks like it's creating the ...
Import single database from --all-databases dump
Is it possible to import a single database from an --all-databases mysqldump? I guess I can modify the file manually but wondering if there are any command line options to do this. I am moving servers and have a lot of databases, most of which I don't currently need or want at the moment but would like to have the option to restore a si...
Unable to restore from mysql database dump
Hello All, I have made a dump of my database in which one table is pretty huge (about 4 million records) and has good number of indices(One full text index along with a primary key and 3 unique indices). When i try to restore this dump the restore hangs(its been running for five days now) when it tries to execute the statement which ena...
MySQL will connect on the command line with u/p but not mysqldump
It's driving me mad! I can connect to my server's MySQL via Terminal using: mysql -u admin -p and then password fine. Trying the same with: mysqldump --user admin --password=mypassword test123 > /backups/test.sql just gets me mysqldump: Got error: 1045: Access denied for user Any words of wisdom for me, do I need to grant more...
Compare structures of two databases?
Hello, I wanted to ask whether it is possible to compare the complete database structure of two huge databases. We have two databases, the one is a development database, the other a production database. I've sometimes forgotten to make changes in to the production database, before we released some parts of our code, which results that t...
Skip all databases and run only specific one
I have a sql file generated by "mysqldump --all-databases" . There are many databases in it. What I want to do is to update my local database but only a specific one, not all. I tried to use "mysql -database=db_name < file.sql" but it updated all databases. Is there a way to skip all databases except the one that I want. ...
Can you automatically create a mysqldump file that doesn't enforce foreign key constraints?
When I run a mysqldump command on my database and then try to import it, it fails as it attempts to create the tables alphabetically, even though they may have a foreign key that references a table later in the file. There doesn't appear to be anything in the documentation and I've found answers like this that say to update the file aft...
mysqldump from a query
How do we take a mysql dump for table from a query I need something like this.. `mysqldump -uroot -pxxxx mydb "select * from table where name='1';" > /tmp/a Thanks ...
Determine which table takes most disk space in MySQL
What is the easiest way to determine which table takes most disk space ? A handicap: I have no MySQL server, only the file with all data (dump.sql) ...
Dumping views with mysqldump in the right order.
I have a script that backs up our database, which contains multiple tables and views constructed from tables. The command used is: mysqldump -u UserName -ppassword -h hostname DatabaseName > dump.sql; I have noticed however that some view definitions are backed up before the definitions of the tables. This causes an issue when restor...
Is there any free tool for profiler of MYSQL database?
Is there any free tool for profiler of MYSQL database? I want to capture the SQL statement from application. and want to tune it for performenencae. Can any one suggest? Thanks, Paresh ...
something about sql-dump
hi, guys, i know that sql dump is a series of insert sql statement which reflects all the records inside the database. but may i know what is it used for? why should we dump the database records? is every database supports dumping function? Any additional background information is welcomed! Thanks in advance! ...
MySQL - mysqldump --routines to only export 1 stored procedure (by name) and not every routine
So we have a lot of routines that come out from exporting. We often need to get these out in CLI, make changes and bring them back in. Yes some of these are managed by different folks and a better change control is required but for now this is the situation. If I do mysqldump --routines --no-create-info --no-data --no-create-db then g...
Faster way to dump mysql
This may be a dumb question, but I was just watching a screencast on MySQL replication, and I learned that a master database doesn't send SQL over to a slave for replication, it actually sends data over in binary, which makes importing extremely fast. I started wondering, "if a database can export and import binary, why do mysqldumps / ...