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

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

Import single database from –all-databases dump

You can use the following command: mysql -u root -p –one-database destdbname < alldatabases.sql Where destdbname is your desired database which you want to restore. Another option which is IMHO much safer, is to extract the DB from an –all-databases dump. Example: sed -n ‘/^– Current Database: `dbname`/,/^– Current Database: `/p’ alldatabases.sql > output.sql Replace …

Read more

mysqldump – Export structure only without autoincrement

You can do this : mysqldump -u root -p -h <db-host> –opt <db-name> -d –single-transaction | sed ‘s/ AUTO_INCREMENT=[0-9]*\b//’ > <filename>.sql As mentioned by others, If you want sed to works properly, add the g (for global replacement) parameter like this : mysqldump -u root -p -h <db-host> –opt <db-name> -d –single-transaction | sed ‘s/ …

Read more

Mysqldump only tables with certain prefix / Mysqldump wildcards?

You can specify table names on the command line one after the other, but without wildcards. mysqldump databasename table1 table2 table3 You can also use –ignore-table if that would be shorter. Another idea is to get the tables into a file with something like mysql -N information_schema -e “select table_name from tables where table_schema=”databasename” and …

Read more

mysqldump exports only one table

try this. There are in general three ways to use mysqldump— in order to dump a set of one or more tables, shell> mysqldump [options] db_name [tbl_name …] a set of one or more complete databases shell> mysqldump [options] –databases db_name … or an entire MySQL server—as shown here: shell> mysqldump [options] –all-databases