Migrate MySQL from Slaved Drive

by

Migrate MySQL from Slaved Drive

1. Mount the slave drive. We’ll assume you mounted it at /media/slave

Find the drive:

# fdisk -l
Disk /dev/sda: 1000.2 GB, 1000204886016 bytes
255 heads, 63 sectors/track, 121601 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000374d4

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64         587     4194304   82  Linux swap / Solaris
Partition 2 does not end on cylinder boundary.
/dev/sda3             587      121602   972054528   83  Linux

Disk /dev/sdb: 1000.2 GB, 1000204886016 bytes
255 heads, 63 sectors/track, 121601 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1   *           1          62      497983+  83  Linux
/dev/sdb2              64         584     4184932+  82  Linux swap / Solaris
/dev/sdb3             585      121598   972044955   82  Linux swap / Solaris

sdb is the drive – the slaved drive after the reinstall
Check to see if it is ext3 or ext4

# blkid /dev/sdb3
/dev/sdb3: UUID="52721885-a9af-45e9-89f5-5f26ffca55dd" TYPE="ext3"

Mount according to ext3 or ext4

mount -t ext3 /dev/sdb3 /media/slave

Add to fstab

# nano /etc/fstab
/dev/sdb3   /media/slave   ext3   default 0   1

2. Edit the MySQL config file to point to the slave’s mysql databases

nano /etc/my.cnf


# datadir = /var/lib/mysql
datadir = /media/slave/var/lib/mysql



service mysqld restart

3. Export the required database


mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

for plesk:


mysqldump -u admin -p`cat /etc/psa/.psa.shadow` [database_name] > dumpfilename.sql

4. Reset the Mysql path and import the file


nano /etc/my.cnf



datadir = /var/lib/mysql
# datadir = /media/slave/var/lib/mysql



service mysqld restart



mysql -u root -p[root_password] [database_name] < dumpfilename.sql

&#91;/bash&#93;

Plesk:
&#91;bash&#93;

mysql -u admin -p`cat /etc/psa/.psa.shadow` &#91;database_name&#93; < dumpfilename.sql

&#91;/bash&#93;

Hotkey for Plesk specifically mounted to /olddrive/:

Before migrating, make sure the database you are migrating has already been created in Plesk with the correct username and password.

1. Log onto your server as root using ssh.

2. Edit the mysql config file to use the slave drive
nano /etc/my.cnf

3. Comment out the current path, add your slaved drive’s path and save the file
&#91;bash&#93;

# datadir = /var/lib/mysql
datadir = /olddrive/var/lib/mysql


&#91;/bash&#93;

(save the file using <ctrl><o> then <ctrl><x> to exit.

4. Restart mysql to load the new settings
[bash]

service mysqld restart

5. Create a dump file of the desired database


mysqldump -u admin -p`cat /etc/psa/.psa.shadow` [database_name] > /tmp/database_name.sql

(Repeat this step for all databases that need to be imported)

6. Repeat step 2-4 and reset the original setting


datadir = /var/lib/mysql
# datadir = /olddrive/var/lib/mysql

7. Import the database

mysql -u admin -p`cat /etc/psa/.psa.shadow` [database_name] < /tmp/database_name.sql [/bash] (Repeat this step for all .sql files created in step 5)

Leave a Comment