My Experience on Migrating a Database

I have a server that runs both application and database and all resources are utilized on peak time, so I migrated the database to 2 other new servers with more capacity.

It is about the whole story from OS installation to DB migration and the challenges on the way.

Use raid controller for mirroring 1st 2 disks for / and /boot.
The final layout of the root partitions is as:

sda 557.9G  0 disk
sda1 200M  0 part   /boot/efi
sda2 500M  0 part   /boot
sda3 557.2G  0 part
g_root-lv_root 424G  0 lvm    /
g_root-lv_swap 16G  0 lvm    [SWAP]
vg_root-lv_varlog 19.5G  0 lvm    /var/log
vg_root-lv_home 97.7G  0 lvm    /home

The /boot/efi may appear new that is because of using UEFI mode of BIOS boot agent.

Clearing old partitions of other disks after installing OS (RHEL):

For each disk that has partition
fdisk /dev/sd? -> p ->d -> w
or
dmsetup remove /dev/mapper/ddf???????????????????????


LVM:
pvcreate /dev/sdb /dev/sdc /dev/sdd /dev/sde /dev/sdf /dev/sdg
vgcreate -s 16M vg_data /dev/sdb /dev/sdc /dev/sdd /dev/sde /dev/sdf
lvcreate --type raid6 -i 3 -L 1.5T -n lv_mysql vg_data
mkfs.ext4 /dev/mapper/vg_data-lv_mysql

Mounting Permanently:
/etc/fstab
/dev/mapper/vg_data-lv_mysql /data/mysql        ext4    defaults        1 2

SE Linux:
install semanage
yum install policycoreutils-python-2.0.83-19.47.el6.x86_64

Adding permission for new customized directory of mysql
semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
restorecon -R -v /data/mysql

Firewall:
yum install system-config-firewall-1.2.27-7.1.el6.noarch

iptables -I INPUT 4 -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
service iptables status
service iptables save

Note: -I INPUT 4 is putting this rule before the global rejection rule that is there by default, The status should be checked and ensure about the position of the rule, here 4.


MySQL:
Update /etc/my.cnf
mysql_install_db --defaults-file=/etc/my.cnf --user=mysql
service mysql start

Grant options for defining replication on both servers
mysql  -p -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* TO root@'%' WITH GRANT OPTION;"

Install mysql utilities
yum install mysql-connector-python-2.1.1-1.el6.noarch.rpm
yum install mysql-utilities-commercial.noarch

mysqlreplicate --master=root@10.1.196.4:3306 --slave=root@10.1.196.12:3306 --rpl-user=rpl:sLavep@ss

Note: the --rpl-user values will be created automatically, no need to define that user.

Checking Replication Status:
mysqlrplcheck --master='root@10.1.196.4:3306' --slave='root@10.1.196.12:3306'

mysqlrplshow --master='root@10.1.196.4:3306' --discover-slaves-login='root'

Master Only:
mysql_secure_installation
mysql>
update mysql.user set user = 'dba', password=Password('123456'), host = '%' where user = 'root' and host = '::1';
SET sql_log_bin = 0;
delete from mysql.user where user = 'root';
SET sql_log_bin = 1;
flush privileges;

Note: SET sql_log_bin can stop/start replication of the current session.

SLAVE:
SET sql_log_bin = 0;
delete from mysql.user where user = 'root';
delete from mysql.user where host = 'hostname' and user = '';
SET sql_log_bin = 1;
flush privileges;

Checking replication status:
show slave status \G

Fixing replication failures:
Running empty transaction for failed transaction id (GTID)

SLAVE:
If there is error:
show slave status \G;
SET GTID_NEXT="2e82f8b8-cc26-11e4-bb69-0010e02446b8:3"
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
STOP SLAVE;
START SLAVE;
or
SET GLOBAL sql_slave_skip_counter = 1;

Redirecting old server processes to new DB server:
vi ~/.my.cnf
[client]
user=dba
password=123456
host=10.1.196.4

chmod og-rwx,u-w ~/.my.cnf

DB Migration:
Export:
mysqldump  --tab=./System/ System_db
mysqldump  --tab=./db2/ db2
mysqldump  --tab=./Dimension/ Dimension

Statistics Database:
mysqldump  --tab=./stat/ --no-data CS3_Statistics

Note: --no-data is used because I don't want to migrate all tables data in this phase

mysqldump  --tab=./stat/ --ignore-table=Statistics.ProcessStatistics --ignore-table=Statistics.Log --ignore-table=Statistics.Statistics_System  --ignore-table=Statistics.ServerIf --single-transaction Statistics &> /tmp/dump.log &

Note: --single-transaction is used to  prevent table lock.

Import:
mysqladmin create System_db
mysqladmin create db2
mysqladmin create Dimension
mysqladmin create Statistics

cat *.sql | mysql System_db
repeat for each db export

Note: In some cases that you have a view or foreign key you should ensure the order of sql and txt files to keep consistency. This order is not keeping by use *.sql or *.txt and you may need to list files manually.

mysqlimport --local Dimension Dimension/*.txt

MySQL Proxy:
stop old database server to free the listen port

Run mysql proxy to redirect connections to new server
./bin/mysql-proxy  --defaults-file=my.cnf