Mysql:Database migration to a new server

From Earlham CS Department
Jump to navigation Jump to search

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 DATABASE ON OLD SERVER