See Advanced Topics.
The Open Dental database is MySQL which is a very robust and reliable database with extensive features and millions of installations worldwide.
- We recommend using MySQL version 5.5 because it is in our installer.
- We do support MySQL version 5.6 but you should have a dedicated IT team and understand the risks before updating.
- If you would like to update to MySQL version 5.7 or Maria, you may do so, but we do not support it. We do have customers using them though.
- A database is simply a collection of tables (about 211 in this case). The database server runs on one computer and every copy of Open Dental that connects to it is referred to as a client. The MySQL connection from the client is created after clicking OK on the Choose Database window. The User and Password boxes must match a MySQL username and password (MySQL Security).
- Overall configuration of MySQL is controlled by the my.ini file on the database server. Inside of the my.ini file is a data path Variable which is set when MySQL is installed or upgraded. The database files themselves (mysql\data) will always be located on the same computer where MySQL is installed, not to another computer or NAS (too slow).
- In MySQL, certain words are reserved and cannot be used as identifiers for the database, tables, columns, etc. See https://dev.mysql.com/doc/refman/5.5/en/keywords.html. Also, databases should not be named only numbers (e.g. 2014). Do not use databases named mysql.
- There are many ways to backup the tables.
- The simplest is to copy the files when the database is not being used.
- Hot backups are preferred when using MySQL InnoDB. There are also tools and techniques available for hot backups.
To perform a backup, you don't have to shut down Open Dental, although depending on the backup method, users may be locked out for a few moments. MySQL also keeps logs of changes to the database and can maintain a slave database which is always an exact copy of the master (Replication).
- There are also tools for improving performance, defragmenting, and repairing tables. They all require a degree of skill. They are not essential, but might come in useful.
- MySQL Server Variables: There are some users who do not use the Open Dental Installer (Trial Version) to install MySQL. We only support MySQL 5.5 in our installer. Any other versions of MySQL are not supported by Open Dental and may cause problems with your database. In addition, MySQL must be installed using the myISAM default engine, not InnoDb. If you do not use the installer provided by Open Dental, you may end up with extra server variables as part of the installation.
MySQL server variables are stored in the my.ini file that is part of the installation process. If you do not use our installer, you will tend to get a variety of errors. Errors will also tend to happen during an update process, corrupting the database. If you are not sure if MySQL server variables are the problem, look at this file: C:\Program Files\MySQL\MySQL Server 5.5\my.ini. It should look like the examples in my.ini.
How to Determine if MySQL is Installed
MySQL should be installed on the server. There are two ways to ensure MySQL is installed:
- On the server, go to Services. See if the MySQL service is listed.
- On the server, go to Add or Remove programs. See if an existing installation of MySQL is listed.
How to Determine if MySQL is Running
The MySQL server will run as a service on one computer. To easily determine if MySQL is running, use the management console.
- Right click on My Computer and select Manage.
- Open Services and Applications
- Open Services. These are all the programs (services) that are running in the background without a user interface. MySQL would show as started.
How to Start/Stop the MySQL Service from Windows CMD
- Open CMD.
- CMD may need to be run as administrator depending on Windows permissions settings. Right click the CMD icon, select Run as Administrator.
- To start the MySQL service, type net start mysql.
- To stop the MySQL service, type net stop mysql.
To access the data in your MySQL database, use the table viewer in User Query.