Mysql:Database migration to a new server - Revision history

Elena at 00:52, 16 April 2014

2014-04-16T00:52:01Z

← Older revision		Revision as of 00:52, 16 April 2014
Line 86:		Line 86:
	:: <tt> mysql -uroot -p < mysql_dump.sql </tt>		:: <tt> mysql -uroot -p < mysql_dump.sql </tt>

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

Elena at 00:51, 16 April 2014

2014-04-16T00:51:41Z

← Older revision		Revision as of 00:51, 16 April 2014
Line 86:		Line 86:
	:: <tt> mysql -uroot -p < mysql_dump.sql </tt>		:: <tt> mysql -uroot -p < mysql_dump.sql </tt>

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

Elena at 00:50, 16 April 2014

2014-04-16T00:50:44Z

@@ Line 1: / Line 1: @@
-=== Install and configure the new Postgres server:===
+=== Install and configure the new MYSQL server:===
 * UPGRADE THE SYSTEM
 :make sure apt's source.list is up to date
@@ Line 61: / Line 61: @@
 :: <tt> mysqldump --single-transaction -h<host> -u root -p<psswd> mysql > mysql_temp.sql </tt>
 : compress the files
-:: gunzip mysql_dump.sql.gz mysql_dump.sql
+:: <tt> gunzip mysql_dump.sql.gz mysql_dump.sql </tt>
-:: gunzip mysql_temp.sql.gz mysql_temp.sql
+:: <tt> gunzip mysql_temp.sql.gz mysql_temp.sql </tt>

Elena: Created page with "=== 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 :..."

2014-04-16T00:27:15Z

Created page with "=== Install and configure the new Postgres server:=== * UPGRADE THE SYSTEM :make sure apt's source.list is up to date :run update ::<tt> sudo apt-get update </tt> :run upgrade :..."

New page

=== Install and configure the new Postgres 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
:: gunzip mysql_dump.sql.gz mysql_dump.sql
:: gunzip mysql_temp.sql.gz mysql_temp.sql

Mysql:Database migration to a new server - Revision history

Elena at 00:52, 16 April 2014

Elena at 00:51, 16 April 2014

Elena at 00:50, 16 April 2014

Elena: Created page with "=== 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 :..."

Elena: Created page with "=== 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 :..."