Mysql:Database migration to a new server

Install and configure the new Postgres 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