Tag Info

New answers tagged

0

If you do a dump using the following two rules: Do not use options such as --databases, --database, and simply just put the database name at the end of the command without these options. Include the option --no-create-db If you do the following then mysqldump will create the SQL without referencing the database, then you can use the new database name ...


1

Google searching turns up a few good posts on Python script wrappers for mysqldump Oct 08, 2014 : StackOverflow : How to copy a database with mysqldump and mysql in Python? May 05, 2014 : GitHub Gist : A configurable python script that wraps around mysqldump with some additional configuration. Dec 03, 2013 : TecAdmin.net : Python Script for MySQL Database ...


0

What you are asking for can be done by getting the list of all tables from the database. However, you should use the INFORMATION_SCHEMA database. In your particular case, you can use the INFORMATION_SCHEMA.TABLES table. TABLES IN DESCENDING TABLE_NAME ORDER (Database mydb) MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" ...


0

As a solution for someone who can't get pv to work or for whom pv tells lies. You can monitor the size of ibdata1 file in /var/lib/mysql which contains the data. This will end up the same size (or thereabouts) of the filesize in your source server. If there are many tables you can also watch them appear one by one in /var/lib/mysql/< database name>.


2

mysqldump is a command i invoke at the shell prompt, not within the mysql client environment. mysql> exit C:\wamp\bin\mysql\mysql5.5.24\bin>mysqldump -d -h localhost -u root -p databasename > dumpfile.sql this works for me. useful link


1

Try creating a directory under your C: drive. mkdir dumps Then issue the mysqldump command and specify the new location. mysql> mysqldump -d -h localhost -u root -p databasename > C:\dumps\dumpfile.sql You can navigate with the GUI and check if the file was created in that directory.


2

if you are using MySQLdump and specify location will be inside the directory you specified for example: mysqldump -u root -d -P port -p password databasename > c:\dumpfile.sql the dump location will be inside your machine under C drive but if you did not specify the directory the dump location will by inside MySQL\bin mysqldump -u root -d -P port ...


0

I have about 10 clients with same database configuration (but different versions of mysql/mariadb), and one has the same issue (mysqldump: Couldn't execute 'ROLLBACK TO SAVEPOINT sp': SAVEPOINT sp does not exist (1305)) while performing mysqldump. I am using : mysql Ver 15.1 Distrib 10.0.13-MariaDB, for Linux (x86_64) using readline 5.1 Looks like a MySQL ...


2

since the database size is large you should make the dump command like the following: mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz --quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than ...


0

You should modify the mysqldump command and use --single-transaction. All new changes to the database are stored in a transaction and you have valid point-in-time-database backup. I have two steps: I export the database as a dump and the next step is to zip the dump, but i never tested if this make any differences. You could use other backup software like ...


1

You should use Percona Toolkit to backup database to prevent from blocking database: sudo innobackupex --user=root --password=rootPASSWORD --host localhost /tmp/ sudo innobackupex --apply-log --use-memory=2G /tmp/$TIMESTAMP/ You need enough disk space in /tmp. Once you finish it, you can copy entire directory to another server. In xtrabackup_binlog_info, ...


1

You need to use Percona XtraBackup tool. It works like a charm for huge datasets and doesn't interrupt MySQL operations. http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/creating_a_backup_ibk.html There are some tricks but it's worth it.


1

There is a bug report on this: mysqldump: Couldn't execute 'show table status': SELECT command denied to user. Surprisingly, it's not a bug at all. here is why: customer_cohort_paid might be a view. Whatever user created the view is not listed anymore in mysql.user. What this does is make such a view incapable of being dumped. You could do one of two(2) ...


1

try the following command this one since the database size is not small: mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz


1

You must use --single-transaction option mysqldump -uroot -p --single-transaction databasename > dump.sql It allows for point-in-time snapshot of data. Once mysqldump starts, all the InnoDB tables will be frozen in time. Suppose you start the mysqldump at 2:30 PM and it finishes at 3:00 PM. All the InnoDB tables dumped will be from 2:30 PM. All other ...


0

Found it, using this flag: --skip-lock-tables -f The schema still can't be exported correctly, except by using phpmyadmin



Top 50 recent answers are included