Mysql:Database migration to a new server

Install and configure the new MYSQL server:

• UPGRADE THE SYSTEM

make sure apt's source.list is up to date

run update

sudo apt-get update

run upgrade

sudo apt-get upgrade

finish the upgrade

sudo apt-get dist-upgrade

this requers reboot

sudo reboot

useful commands to check the update

lsb_release -a

uname -mrs

cat /etc/issue

• INSTALL AND CONFIGURE MYSQL

install mysql package

sudo apt-get install -f <mysql-server-version>

make sure you preserve the password as the old server has

• CONFIGURE FOR REMOTE CONNECTIONS

edit my.cnf

comment out skip-networking line

add line: bind-address=<IP>

restart the server

/etc/init.d/mysql restart

login to database

mysql -uroot -p

show root grants and NOTE the password

show grants;

issue the same grants for root at remote host connection

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'<ip.ip>.%' IDENTIFIED BY PASSWORD <password from previous step> WITH GRANT OPTION;

• FIREWALL

allow all to mysql sockets

sudo /sbin/iptables -A INPUT -i <interface> -s <IP-range> -p tcp --sport 1024:65535 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

• SEMAPHORES: if more than one database is running on the server, the semaphore issues may occur

in this case the shmmax and shmall need to be modified

useful commands:

sysctl -A | grep kernel.sem

ipcs -lm

see loader.conf, shmmax and shmall files

Mysql Database Migration

• DNS

comment out old server entry as a mysql host

• ON OLD SERVER

do all database dump

mysqldump --single-transaction -h<host> -u root -p<psswd> --all-databases > mysql_dump.sql

dump 'mysql' database separately - we will restore this first, and separately from others. carefully follow the instructions!

mysqldump --single-transaction -h<host> -u root -p<psswd> mysql > mysql_temp.sql

compress the files

gunzip mysql_dump.sql.gz mysql_dump.sql

gunzip mysql_temp.sql.gz mysql_temp.sql

copy the files over to the new server

do md5 check

 ATTENTION!
When migrating all databases to the new server it is important to dump 'mysql' separately and partially. If Mysql version was upgraded on the new server, make sure that no tables in ‘mysql’ database will 
get dumped and re-created because the standard and format changed on the new version. All we need to care about is to dump new data in 'mysql' database (like users).
There is a mysql bug which locks mysql out when you restore the full dump because the new server root password stored in 'mysql' database gets overwritten.  Make sure that already existing entries will 
not get modified (especially data entries for 'root' user - they are already locally created). For the rest of the entries - make sure the insert/creation format is unchanged, otherwise you need to add extra 
information to dump file. 

This separate dump file for 'mysql' database already exists as mysql.sql to avoid tedious manual rewriting of mysql.sql 

• ON NEW SERVER

decompress the dumps

gunzip mysql_dump.sql.gz mysql_dump.sql

gunzip mysql_temp.sql.gz mysql_temp.sql

delete all ‘mysql’ database entries in mysq_dump.sql - we will restore 'mysql' database separately

compare insert statements for mysql_temp.sql and already existing 'mysq' dump file mysql.sql

if not the same, add new data entries and / or modify the format mysql.sql

restore the dumps: PRESERVE THE ORDER

mysql -uroot -p < mysql.sql

mysql -uroot -p < mysql_dump.sql

• CREATE NEW DNS ENTRY AND SHUT DOWN OLD DATABASE SERVER