How to use MySQL dump from a remote machine
Try it with Mysqldump #mysqldump –host=the.remotedatabase.com -u yourusername -p yourdatabasename > /User/backups/adump.sql
mysqldump
mysqldump error: Got packet bigger than max_allowed_packet’
You can add –max_allowed_packet=512M to your mysqldump command. Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf (thanks @Varun) Note: it will not work if it is not under the [mysqldump] section…
How to use mysqldump for a portion of a table?
mysqldump -uroot -p db_name table_name –where=”id<1000000″ or you can use SELECT * INTO OUTFILE ‘data_path.sql’ from table where id<100000
How can I slow down a MySQL dump as to not affect current load on the server?
I have very large databases with tens of thousands of tables some of which have up to 5GB of data in 10’s of millions of entries. (I run a popular service)… I’ve always had headaches when backing up these databases. Using default mysqldump it quickly spirals the server load out of control and locks up … Read more
mysqldump with create database line
By default mysqldump always creates the CREATE DATABASE IF NOT EXISTS db_name; statement at the beginning of the dump file. [EDIT] Few things about the mysqldump file and it’s options: –all-databases, -A Dump all tables in all databases. This is the same as using the –databases option and naming all the databases on the command … Read more
How to Export & Import Existing User (with its Privileges!)
One of the easiest ways I’ve found to export users is using Percona’s tool pt-show-grants. The Percona tool kit is free, easy to install, and easy to use, with lots of documentation. It’s an easy way to show all users, or specific users. It lists all of their grants and outputs in SQL format. I’ll … Read more
Mysqldump: create column names for inserts when backing up
Use –complete-insert in the mysqldump command params
Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)
Assuming by full dump you also mean the VIEWs and the EVENTs, you would need: GRANT USAGE ON *.* TO ‘dump’@’%’ IDENTIFIED BY …; GRANT SELECT, LOCK TABLES ON `mysql`.* TO ‘dump’@’%’; GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `myschema`.* TO ‘dump’@’%’; and if you have VIEWs that execute a function, then unfortunately … Read more
Error importing SQL dump into MySQL: Unknown database / Can’t create database
This is a known bug at MySQL. bug 42996 bug 40477 As you can see this has been a known issue since 2008 and they have not fixed it yet!!! WORK AROUND You first need to create the database to import. It doesn’t need any tables. Then you can import your database. first start your … Read more
How can I get rid of these comments in a MySQL dump?
WHOA! These aren’t really comments even though they look that way. They are conditional-execution tokens. Take this line: /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; If the version of mySQL is 4.00.14 or higher, then the MySQL server will run this statement. This magic comment syntax is documented in the Comment Syntax section of the manual. You … Read more