Moving MySQL Database to Another Server

How do you move a MySQL database?

The steps for moving a MySQL database are provided as a courtesy and are not officially supported by GWAVA Tech Support; however, GWAVA does provide consulting services that can perform this for you.  Contact your local sales representative if interested.

Linux / Windows to Windows

1.  Disable all archive and deletion jobs and - for good measure, stop Tomcat on the Retain server.

2.  Perform a dump of the database.

3. Download MySQL 5.5.x. onto the new server.

4.  Install MySQL and configure the retain database on the new server.

Refer to the appendix of the Windows Install Guide for instructions. At this point, you may want to configure MySQL memory allocation in the [drive]:\Program Files\MySQL\MySQL Server 5.5\my.ini file.  Your retain database uses the innoDB storage engine.  The following should be researched and considered (reference our KB,  "MySQL Database maintenance for Best Performance"):

• innodb_buffer_pool_size
• innodb_innodb_additional_mem_pool_size
• innodb_log_file_size
• innodb_log_buffer_size

This can enhance the performance of MySQL and Retain.   The MySQL for Windows installer configures settings for you based on your system's available memory and the answers to the questions you provide its configuration wizard, so there may not be anything you need to do; however, you might want to research this and play with those settings to find what works best for your system.  GWAVA support cannot answer questions about or help customer's with their databases.  That is the customer's responsibility. If you decide to change the innodb_log_file_size setting, you will need to take the following preparatory steps before doing so:

a)  Open the MySQL 5.5 Command Line Client (Start | Programs | MySQL)
b)  Log into MySQL:  mysql -u root -p
c)  From the MySQL prompt, type:  SET GLOBAL innodb_fast_shutdown=0;

Then type "quit" to log out of MySQL.

d)  Stop the MySQL service.
e)  Rename the ib_logfile0 and ib_logfile1 files.
f)  Edit the C:\Program Files\MySQL\MySQL Server 5.5\my.ini and change the innodb_log_file_size setting and save the file.
g)  Start the MySQL service.

If you do not take these steps when changing the logfile size, MySQL will not load.  You can re-enable the innodb_fast_shutdown (...=1) afterwards if there is a need, but average size systems do not seem to experience any significant delay in MySQL shutdowns by leaving it disabled.

5.  Import the dump into the new retain database.

a)  Open the MySQL 5.5 Command Line Client (Start | Programs | MySQL)
b) Type:  mysql -u root -p retain < [path to dump file]\[dump filename]

EX:  mysql -u root -p retain < d:\dump\2013-12-11.sql NOTE: This assumes that the ..\MySQL\bin directory is in the path.

6.  (Optional) Reboot the server to clear memory.

7.  Start Tomcat and re-enable archive jobs; and, if used, re-enable deletion jobs.

8.   If only moving MySQL and not Retain; and, if using IP addresses and not DNS names (or using a new DNS address), change Retain's URL to the MySQL server in the ASConfig.cfg:

a)  Edit the ASConfig.cfg (/opt/beginfinite/retain/RetainServer/WEB-INF/cfg)
b)  Change the  <DBURL> and <confDBURL> settings to reflect the the new IP/DNS address
c)  Save the changes
d)  Restart Tomcat on the Retain server.

Linux to Linux

1.  Disable all archive and deletion jobs and - for good measure - stop Tomcat on the Retain server.

2.  Perform a dump of the database.

3. Download MySQL 5.5.x. onto the new server and un-tar it.

Retain only needs the client and the server RPM's, but it is simpler to download one bundle file then two separate files.

4.  Install MySQL 5.5 and configure the retain database on the new server, see the appendix of the Linux Install Guide for instructions; then, come back and perform step #5 in this article.

a)  At this point, you may want to configure MySQL memory allocation in the /etc/my.cnf file.  Your retain database uses the innoDB storage engine.  The following should be researched and considered:

• innodb_buffer_pool_size
• innodb_innodb_additional_mem_pool_size
• innodb_log_file_size
• innodb_log_buffer_size

If done correctly, this can enhance the performance of MySQL and Retain.  You might want to research this and experiment with those settings to find what works best for your system.  GWAVA support cannot answer questions about or help customer's with their databases.  That is the customer's responsibility. If you decide to change the innodb_log_file_size setting, you will need to take the following steps first:

a)  Log into MySQL:  mysql -u root -pb)  From the MySQL prompt, type:  SET GLOBAL innodb_fast_shutdown=0;

Then type "quit" to log out of MySQL.

c)  Shut down MySQL:  rcmysql stop
d)  Rename the ib_logfile0 and ib_logfile1 files.
e)  Edit the /etc/my.cnf and change the logfile size.
f)  Start up MySQL: rcmysql start

If you do not take these steps when changing the logfile size, MySQL will not load.  You can re-enable the innodb_fast_shutdown (...=1) afterwards if there is a need, but average size systems do not seem to experience any significant delay in MySQL shutdowns by leaving fast shutdowns disabled.

5.  Import the dump into the new retain database.

mysql -u root -p retain < /[path to dump file]/[dump filename] To watch the progress of the import, use this command: watch "mysqladmin -u root -p[mysql root user pw] processlist"

6.  (Optional) Reboot the server to clear the memory.

6.  Start up Tomcat and re-enable archive jobs; and if used, re-enable deletion jobs.

This article was originally published in the GWAVA knowledgebase as article ID 2017.