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
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
