multi-threaded mysqldump

What a great feature! isn't it? let's see how we can use it.

Installation:
------------

1. We need to install latest version of perl ( version v5.8.8 or higher)
2. Download mk-parallel-dump script
i.e. wget http://www.maatkit.org/get/mk-parallel-dump
3. Test if it works!
shell# chmod +x mk-parallel-dump
shell# ./mk-parallel-dump --help

We have now successfully installed multi-threaded mysqldump tool! now its the time to learn it.

let's backup mysql database

[root@localhost maatkit]# ./mk-parallel-dump --user root --databases mysql
CHUNK TIME EXIT SKIPPED DATABASE.TABLE
db 0.49 0 0 mysql
all 0.49 0 0 -

[root@localhost maatkit]# ls -lh mysql/
total 476K
-rw-r--r-- 1 root root 654 Jan 20 14:39 00_columns_priv.sql
-rw-r--r-- 1 root root 1.6K Jan 20 14:39 00_db.sql
-rw-r--r-- 1 root root 351 Jan 20 14:39 00_func.sql
-rw-r--r-- 1 root root 325 Jan 20 14:39 00_help_category.sql
-rw-r--r-- 1 root root 230 Jan 20 14:39 00_help_keyword.sql
-rw-r--r-- 1 root root 243 Jan 20 14:39 00_help_relation.sql
-rw-r--r-- 1 root root 360 Jan 20 14:39 00_help_topic.sql
-rw-r--r-- 1 root root 1.5K Jan 20 14:39 00_host.sql
-rw-r--r-- 1 root root 750 Jan 20 14:39 00_procs_priv.sql
-rw-r--r-- 1 root root 1.6K Jan 20 14:39 00_proc.sql
-rw-r--r-- 1 root root 838 Jan 20 14:39 00_tables_priv.sql
-rw-r--r-- 1 root root 234 Jan 20 14:39 00_time_zone_leap_second.sql
-rw-r--r-- 1 root root 190 Jan 20 14:39 00_time_zone_name.sql
-rw-r--r-- 1 root root 232 Jan 20 14:39 00_time_zone.sql
-rw-r--r-- 1 root root 291 Jan 20 14:39 00_time_zone_transition.sql
-rw-r--r-- 1 root root 403 Jan 20 14:39 00_time_zone_transition_type.sql
-rw-r--r-- 1 root root 2.6K Jan 20 14:39 00_user.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 columns_priv.000000.sql
-rw-r--r-- 1 root root 197 Jan 20 14:39 db.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 func.000000.sql
-rw-r--r-- 1 root root 1.2K Jan 20 14:39 help_category.000000.sql
-rw-r--r-- 1 root root 6.4K Jan 20 14:39 help_keyword.000000.sql
-rw-r--r-- 1 root root 7.9K Jan 20 14:39 help_relation.000000.sql
-rw-r--r-- 1 root root 376K Jan 20 14:39 help_topic.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 host.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 proc.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 procs_priv.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 tables_priv.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_leap_second.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_name.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_transition.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_transition_type.000000.sql
-rw-r--r-- 1 root root 783 Jan 20 14:39 user.000000.sql

innodb_file_per_table- move specific tables to separate physical disks

Use of innodb_file_per_table can be useful if you want to move specific database or table(s) to a separate disk.
To enable multiple table spaces, add a line to the [mysqld] section of my.cnf (or my.ini) file and restart mysql server.
[mysqld]
innodb_file_per_table

The new innodb tables will have their own .frm and .ibd files, this is similar to what Myisam storage engine does but it divides the tables into .myi and .myd.

In this post, I'll show you how to move specific database onto separate disk.

Assumptions:

- datadir= /var/lib/mysql
- you have basic linux skills

Steps:

log on to mysql

shell# mysql -uroot -p
# create database
mysql> create database my_db;
mysql> quit;

# stop mysql
shell# /etc/init.d/mysql stop

# create new directory onto separate disk
shell# mkdir /disk2/innodb

# move database onto separate disk
shell# cd /var/lib/mysql
shell# mv my_db /disk2/innodb

# create symlink
shell# ln -s /disk2/innodb/my_db my_db

# correct permissions
shell# chown -R mysql:mysql /disk2/innodb

# start mysql server
shell# /etc/init.d/mysql start

you have now successfully moved my_db database onto separate disk.