Category: MySQL

MySQL Dump is probably one of the best tool to take copies of databases, and it comes with MySQL, so you don’t need to install more stuff.

Example 1: Backup all databases (Use with caution, see below)
IMPORTANT: if you dump this to another server, you will lose all users on the target server, this is because the database named mysql (not the database engine but the actual database that has the users) on the target server is overwritten by the one from the source

Added note: If you want to monitor how large the uncompressed dump file has gone, or in other words, how much data mysqldump has brought so far, you can use PV, in this example, i expect the data to be 123GBs so i put that in so that PV can tell me what percentage of that i have finished, it will tell me the exact number of bytes anyways, but this is visually easier.

mysqldump --opt -u root --password="yourpass" databasename | pv -s 123g | pigz -c > dumpfile.sql.gz

 

1- Dump all databases

mysqldump -u root --password="thispassword" --all-databases > thisdatabasedump.sql

2- Dump all databases and gzip compress the output file, gzcompress is like compression used in zip files, this command compresses on the fly (make sure gzip is installed)

mysqldump --opt -u root --password="thispassword" --all-databases | gzip -9 > thisdatabasedump.sql.gz

3- Dump all databases and BZIP compress the output file, BZIP compression is better than gzip compression, but takes significantly more time, like the one before this command compresses on the fly (make sure you have bzip2 installed)

mysqldump --opt -u root --password="thispassword" --all-databases | bzip2 > thisdatabasedump.sql.bz2

4- If you have a server with multiple processors, you can overcome the slowness of bzip2 by simply making all the CPUs (real or virtual or hyper threaded) work on compressing at the same time, the application is called parallel bzip2 (make sure pbzip2 is installed)

mysqldump --opt -u root --password="thispassword" --all-databases | pbzip2 > thisdatabasedump.sql.bz2

4.5- If your server has 8CPUs and you only want 7 of them to do zipping so that one of them can be dedicated to mysqldump

mysqldump --opt -u root --password="thispassword" --all-databases | pbzip2 -p7 > thisdatabasedump.sql.bz2

I will not give any more examples about compression, obviously, as you can see from the examples above, to compress on the fly all you need to do is replace the section ( > thisdumpfile.sql ) with ( | pbzip2 > thisdumpfile.sql.bz2 )

5- Dump a certain database to the file

mysqldump --opt -u root --password="thispassword" thisparticulardbsname > thisdumpfile.sql

6- Dump certain databaseS

mysqldump --opt -u root --password="thispassword" --databases db1name db2name db3name db4name > thisdumpfile.sql

7- Dump certain tables from within a database

mysqldump --opt -u root --password="thispassword" databasename table1name table2name table3name > thisdumpfile.sql

8- Exclude certain tables from the mysqldump

 mysqldump --opt -u username --password="thispassword" databasename --ignore-table=databasename.table1 --ignore-table=databasename.table2 > database.sql

They say the number of rows you see in PHPMyADMIN is approximate and not very precise for innodb tables, i can tell you that depending on how the table has been used, the results can be very very inaccurate and sometimes irrelevant.

phpmyadmin wants to display a row count with the list of tables, in myisam, a value is maintained within the database saying how many rows are in the database, INNODB does not maintain such a value so it has to either respond accurately without regard to the delay and scans required or give an approximate reading, it will depend on how you ask, read on for more details.

Q: why are innodb row counts innacurate in PHPMYADMIN ?

Well, innodb would return an accurate count if it were asked formally with a

SELECT COUNT(*) FROM tablename"

But phpmyadmin can not execute such a statement with every listing of a database, because it means the database engine will have to read the whole database and scan all rows.

Q: in that case how come it works for MyISAM ?

A: MyISAM maintains a number stating how many rows are in there that is incremented and decremented with every insert and delete, so the database engine does not need to scan the whole table to give us a row count.

Q: Then where does PHPMYADMIN get those approximate numbers ?

For speed, PHP MY ADMIN would execute something similar (If not exactly)

SHOW TABLE STATUS WHERE 1;

This is when MyISAM would read it’s internal row count value, and Innodb would return an estimate because it does not maintain such a value.

You can also use the command to see a subset of the tables like this

SHOW TABLE STATUS LIKE 'wp_%';

to see status of all tables starting with ‘wp_’

Q: Why does innodb not maintain such a number

A: probably for performance reasons, to avoid the need to update such a number with every insert and every delete.

Please note: most of you are probably visiting this post for the answer to whether you can use multiple disks with innoDB, the answer is, even though you can not move idb files around like when you move MYI MYD files, you can move an IDB file while the DB engine is not running, then create a symlink where the Innodb expected to find the original file, then start mysql again, so the short answer is that innodb does work with symlinks just like myisam.

The second answer is, you can not use more than one disk on the same table (even though you can for the same database by putting every table on a separate disk).

As you would probably know, innodb, by default stores all data into its central, single file data dictionary (on debian, /var/lib/mysql/ibdata1), you can ask innoDB to put every table’s data in a separate file (tablename.ibd) by simply adding the directive innodb_file_per_table anywhere under [mysqld] section.

Databases already in single file will stay there, the directive only affects the creation of new databases, InnoDB does not mind mixed mode regarding ibd files, dome tables in a single database can be in the shared file, and some can have their own files. files created while the directive is in effect will get separate files, you can then remove the directive from your my.cnf and the engine will start putting new databases back into the shared file.

Mind you, unlike ISAM tables, you can not move these files around, in MyISAM you can simply copy the 3 files that are any table, the .frm, .MYI and .MYD and you have your table elsewhere, in INNODB, this is not the case as much is still stored in INNODB’s ibdata1 , also, innodb files are connected to a certain database with internal strings, Yet, you can copy the whole data directory and have it work, remember, log files are also needed and must be kept.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

The main disadvantage of splitting data files per table is disk space, files in INNODB never get smaller, when in 1 file, other tables can use that space of deleted rows in another table, when split apart, you can only use the occupied space again if you add rows to the same table that had deleted rows.

The advantages are

1- You can utilize multiple disks for both space and performance (By using symlinks)
2- You can backup certain tables and leave others to add flexibility to your backup plan