View source for 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
::<tt> sudo apt-get update </tt>

:run upgrade
::<tt> sudo apt-get upgrade </tt>

:finish the upgrade
::<tt> sudo apt-get dist-upgrade </tt>

:this requers reboot
:: <tt>sudo reboot</tt>

:useful commands to check the update
::<tt> lsb_release -a </tt>

::<tt> uname -mrs </tt>

::<tt> cat /etc/issue </tt>

* INSTALL AND CONFIGURE MYSQL
: install mysql package
:: <tt> sudo apt-get install -f <mysql-server-version> </tt>
:: make sure you preserve the password as the old server has

*CONFIGURE FOR REMOTE CONNECTIONS
: edit <tt> my.cnf </tt>
:: comment out <tt>skip-networking</tt> line
:: add line: <tt>bind-address=<IP></tt>
:restart the server
:: /etc/init.d/mysql restart
:login to database
:: <tt> mysql -uroot -p</tt>
: show root grants and NOTE the password
:: <tt> show grants; </tt>
: issue the same grants for root at remote host connection
:: <tt>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;</tt>

*FIREWALL
: allow all to mysql sockets
::<tt> sudo /sbin/iptables -A INPUT -i <interface> -s <IP-range> -p tcp --sport 1024:65535 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT </tt>

*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:
:: <tt> sysctl -A | grep kernel.sem </tt>
:: <tt> ipcs -lm </tt>
:: see <tt>loader.conf</tt>, <tt>shmmax</tt> and <tt>shmall </tt> files

=== Mysql Database Migration ===


* DNS
:comment out old server entry as a mysql host
* ON OLD SERVER
: do all database dump
:: <tt> mysqldump --single-transaction -h<host> -u root -p<psswd> --all-databases > mysql_dump.sql </tt>
:dump 'mysql' database separately - we will restore this first, and separately from others. carefully follow the instructions!
:: <tt> mysqldump --single-transaction -h<host> -u root -p<psswd> mysql > mysql_temp.sql </tt>
: compress the files
:: <tt> gunzip mysql_dump.sql.gz mysql_dump.sql </tt>
:: <tt> gunzip mysql_temp.sql.gz mysql_temp.sql </tt>
:copy the files over to the new server
:: do <tt> md5 </tt> check

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

* ON NEW SERVER
:decompress the dumps
:: <tt> gunzip mysql_dump.sql.gz mysql_dump.sql </tt>
:: <tt> gunzip mysql_temp.sql.gz mysql_temp.sql </tt>
: delete all ‘mysql’ database entries in mysq_dump.sql - we will restore 'mysql' database separately
: compare insert statements for <tt>mysql_temp.sql </tt> and already existing 'mysq' dump file <tt>mysql.sql</tt>
:: if not the same, add new data entries and / or modify the format <tt>mysql.sql</tt>
: restore the dumps: PRESERVE THE ORDER
:: <tt> mysql -uroot  -p < mysql.sql </tt>
:: <tt> mysql -uroot  -p < mysql_dump.sql </tt>

* CREATE NEW DNS ENTRY AND SHUT DOWN DATABASE ON OLD SERVER