| MySQL Database |
  
|
| MySQL Database |
|
MailCOPA comes with a built-in database, which is what is used initially after installation. Upgrades from earlier versions will also use the built-in database initially.
For most people, this will be quite sufficient, but in some circumstances, the greater speed of a MySQL database may prove an advantage. It is only appropriate on network versions of MailCOPA - it is superfluous on single-machine installs.
The installation, linking-to and maintenance of a MySQL database is technically complex and should only be attempted by those with the skills and experience required. If you do not know if you have the skills and experience required, then you probably DON'T !
As too many variables are outside our control, Intervations do not provide support for databases other than the built-in one.
Development and testing of this feature has been done by personnel competent in this field, and the information provided here is for other such people.
1. Install the latest version of MySQL onto your server machine and ensure that it is working properly. You do not need a web server or PHP installed on this machine, but it can be helpful as you can use phpMyAdmin to check the MySQL installation. Otherwise, the MySQL command-line client that comes with MySQL can be used.
2. Create a suitable MySQL database (no tables are required), and a suitable username and strong password. Initially this user must have ALL privileges and be a SUPER user - this is dangerous, so once the data transfer is complete, this SUPER privilege MUST be removed.
3. Edit the my.ini (Windows) or my.cnf (Linux) file and add the following to the line to the [mysqld] section:
max_allowed_packet=1G
net_write_timeout=1200
Please see also the comments about the Windows Virtual Store, below.
4. Ensure that the firewall on the server will allow incoming connections to port 3306.
5. ONLY when the above have been successfully accomplished, contact MailCOPA Support for a link to the utility that will move back and forth between the two types of database.
6. Close all MailCOPA clients and run the utility on the a Windows machine that has access to both the old built-in database and the new MySQL database. On the MailCOPA machine might be quickest. It will create all the tables and copy all of your data from the built-in database. This may take a considerable time if you have lots of data.
6. At the end of the process, you will be offered the chance to run the Multi-Machine Data Sharing Wizard, to connect to the MySQL database.
We have found that with large datasets, there is a considerable increase in speed, such that, over good internet connections, it can be slow but practicable - ie a MailCOPA client in a remote connection can access the data on the server, just as if it were in the local network. NOTE however that there must be an uninterrupted connection to the MySQL database server - 3G connections have not been found to be very reliable in this respect.
For this to work you must make sure that your MySQL server can be accessed across the internet. If using a router this must forward traffic from the internet to port 3306 on your MySQL server machine. Some ISPs block traffic to this port for security reasons, in which case the you will need to run MySQL on an alternative (accessible) port, or to configure the router to translate this alternative port to port 3306.
NOTE that exposing a database server in this way is dangerous, and it is up to you to ensure that you have adequate security to prevent intrusion. We accept no responsibility for such problems.
The ideal way is to backup to a replicating MySQL database on another server.
A simple alternative is to create a batch file with the following line in it:
[Path To]mysqldump --routines --max_allowed_packet=1G --lock-tables --user=mailcopa --password=****** mailcopa > [Path To]mailcopa.sql
This will lock all of the tables in the database (which will cause any running MailCOPA clients to freeze until the backup process finishes) and export the whole database to the file mailcopa.sql. In the event of a disaster this file can be easily used to restore the database.
Notifications
If you are accessing the MySQL database using clients on different subnets, then these will not work. For more information, please see here.
Windows 7 & Windows 8 Virtual Store
NOTE that if editing any file in the \Program Files or \Program Files (x86) hierarchies, Windows will prevent changes. You may appear to be editing a file, and the results being saved, but in fact the file is saved to a Virtual Store created by Windows, not to its original location: the file you think you are editing is not being modified.
The solution is to start a text editor (eg Notepad) as Administrator, then use it to open the file you want to edit. Changes will now be correctly saved.