Tag Info

Hot answers tagged

105

If you are dumping tables t1, t2, and t3 from mydb mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql If you have a ton of tables in mydb and you want to dump everything except t1, t2, and t3, do this: DBTODUMP=mydb SQL="SET group_concat_max_len = 10240;" SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')" SQL="${SQL} FROM ...


44

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways: Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly ...


37

INSIGHT INTO DOING BACKUPS WITH mysqldump IMHO Doing backups has become more of an art form if you know just how to approach it You have options Option 1 : mysqldump an entire mysql instance This is the easiest one, the no-brainer !!! mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz Everything ...


37

If you're just importing from a dump file from the CLI on *nix, e.g. mysql -uxxx -pxxx dbname < /sqlfile.sql then first install pipe viewer on your OS then try something like this: pv sqlfile.sql | mysql -uxxx -pxxxx dbname which will show a progress bar as the program runs. It's very useful and you can also use it to get an estimate for ...


30

My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance, mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserver You can do all databases with mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver The only ...


27

A note to expand on the answer by RolandoMySQLDBA. The script he included is a great approach for including (and table_name in) or excluding (and table_name NOT in) a list of tables. If you just need to exclude one or two tables, you can exclude them individually with the --ignore-table option: mysqldump -u -p etc. --ignore-table=Table1 ...


26

The architecture of InnoDB demands the use of four basic types of info pages Table Data Pages Table Index Pages Table MetaData MVCC Data (to support Transaction Isolation and ACID Compliance) Rollback Segments Undo Space Double Write Buffer (background writing to prevent reliance on OS caching) Insert Buffer (managing changes to non-unique secondary ...


24

I recently moved a 30GB database with the following stragegy: Old Server Stop mysql server Copy contents of datadir to another location on disk (~/mysqldata/*) Start mysql server again (downtime was 10-15 minutes) compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata) copy the compressed file to new server New Server install mysql (don't start) ...


21

You don't even need mysqldump if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered) Stop the database (or lock it) Go to the directory where the mysql data files are. Transfer over the folder (and its contents) over to the new server's mysql data directory Start back up the ...


15

To backup: mysqldump -u user -p database > backup.sql To import: mysql -u user -p database < backup.sql


14

mysqldump has the --where option to execute a WHERE clause for a given table. Although it is not possible to mysqldump a join query, you can export specific rows from each table so that every row fetched from each table will be involved in the join later on. For your given query, you would need to mysqldump three times: First, mysqldump all table3 rows ...


12

According to the MySQL 5.0 Certification Study Guide, Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following: Binary portability is important if you want to take a binary backup that was made on one machine and use it on another machine that has a different architecture. For example, ...


12

All Data is InnoDB This is what will give you an exact point-in-time snapshot of the data: mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql --single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming ...


12

What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'. Now the solution is that you have to replace the definer's for that procedures/methods then you can generate the dump without the error. you can do this like .. UPDATE `mysql`.`proc` p SET definer = ...


12

Easier to use the --single-transaction switch: mysqldump --single-transaction -u username -p db > db.sql


11

You can toggle innodb to store tables per file by adding innodb-file-per-table to your cnf. Innodb really just cares about pages of data at a basic level. In fact you can setup innodb to use just a raw block device with no filesystem what so ever! http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html There are conveniences to storing tables for ...


10

Have a look at MySQL replication master to slave. It allows you to clone the database of master to another database server with same database. That includes the master and slave identities. Slave makes itself the exact copy of the master database server and or its databases. There may be one-one, one-many, many-one relation among master(s) and slave(s). ...


10

There are options to help you in this: --insert-ignore Insert rows with INSERT IGNORE. --replace Use REPLACE INTO instead of INSERT INTO. -t, --no-create-info Don't write table creation info. Keep this paradigm in mind mysqldump everything from DB1 into DUMP1 load DUMP1 into DB3 mysqldump everything from DB2 ...


10

You can let mysqldump create the dump in such a way that it does not create or select the database. EXAMPLE : You are dumping the database db1 and loading it into database db2 This will put in the CREATE DATABASE and the USE commands in the dump mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql This will not put in the ...


10

There is current open bug report on this one. The bug report has a suggested work around at the bottom entry [22 Jan 2010 6:46]: replace 1.79769313486232e+308 \'1.79769313486232e+308\' -- filename Give it a Try !!!


9

The recommended way to back up RDS is with automatic backups and DB snapshots. DB snapshots are basically the same as EBS snapshots, which are stored in S3 behind the scenes, but are only available within the same region. If you need cross-region fault tolerance (good plan!), there is no way to restore your data in another region without doing it "the ...


9

Take these some points in your consideration they may help you in case of generating the dump and restoring it. Use Extended inserts in dumps. Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile. Import with multiple threads, one for each table. Use a different database engine if possible. importing into a heavily ...


9

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs: DROP TABLE IF EXISTS `tblAccountLinks`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE ...


8

The quickest solution would just be to re-create the definer so it does exist, as long as it doesn't create any conflicts with existing users. CREATE USER 'root'@'foobar';


7

When you execute a mysqldump of a single database, all tables are dumped in alphabetical order. Naturally, the reload of the mysqldump into a database would also be in alphabetical order. You could just do a SHOW PROCESSLIST; and find out the DB Connection running the mysqldump. When the dump is reloaded, the DB Connection will vanish. If you want to know ...


7

A few admin points first: Are you connecting to do an ftp or are you ssh'ed in and it's dying? If ssh, then be sure to use screen so that you can resume after the comcast crash. If ftp, then make sure you're compressing it/tar before the send. Also try the --opt parameter or --quick --opt This option turns on a set of additional options to make the ...


7

This is the default behaviour but not mandatory. From MySQL docs, Using Per-Table Tablespaces: By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting ...


7

Although there are a number of tools out there, I prefer the command line for tasks such as this. Better performance, and just simpler. Make sure you're logged into the mysql client: mysql -u <username> -p The first step is to create a database in mysql that you want the data in: CREATE DATABASE <database name>; Then you need to open--or ...



Only top voted, non community-wiki answers of a minimum length are eligible