Dedicated Server Notes

General Guidelines

Please be careful to observe the following guidelines when issuing direct commands to the database:

AppSynergy requires that all database connections are SSL encrypted. If you are having trouble connecting to the database server, please be sure that the client software is trying to connect with SSL.
When AppSynergy creates or modifies tables it also maintains a variety of extended meta data that is critical to the proper operation. You will see a set of meta data tables named parasql_* in your database. These should never be modified except by AppSynergy or by special instructions provided by AppSynergy support.
If you issue CREATE TABLE commands directly to the database (instead of via the AppSynergy UI) AppSynergy's extended meta data will not be created; this limits AppSynergy functionality with these tables. You can, however, manually add the extended meta data with special instructions provided by AppSynergy support if needed.
If you issue ALTER TABLE commands directly to the database (instead of via the AppSynergy UI) AppSynergy's extended meta data will not be updated. Further, your application's references to table names, column names, data types, etc. will not be updated. This may result in applications with broken references. Issuing an ALTER TABLE command only for the purpose of adding a column or a unique index is safe so long as no existing column definitions are changed.
You should NEVER change the password for root@localhost nor root@% nor any other AppSynergy maintained user account. All AppSynergy maintained user accounts are named u####@localhost and u####@% with the exception of the special users db#####public@localhost and db#####public@%.
You should never change permissions on any AppSynergy maintained user account except via the AppSynergy UI (AppSynergy uses extended meta data to manage roles); the single exception to this is that you may GRANT EXECUTE permission on stored routines to AppSynergy maintained user accounts (by default only Administrators will have EXECUTE permission).
If you need to create additional database user accounts for other software to access the database directly, you should do so by creating an API Key via Tools > API Keys... so that the permissions for that user can be managed via the AppSynergy UI.

Compatibility

Your private database server is MariaDB 10.4.x compatible, which is an enhanced version of MySQL with further federated data enhancements by AppSynergy. It supports virtually all third party tools, and you can easily make a direct connection to your dedicated database using either an ODBC or JDBC connection or with any application that can communicated with a MySQL database. To see the exact server version you are currently on, type the following into Tools > SQL Console...

SELECT version()

Database Backup via Command Line Tools

AppSynergy automatically performs nightly backups of your entire database server. You can also create a backup at any time on demand to your own local computer.

Note that in the example below the options --master-data --single-transaction acquire a global read lock on all tables at the beginning of the dump for only a short period of time. This allows a dump to run against a live database and still deliver a consistent snapshot without blocking reads and write.

Recommended way to create a local backup of your database:

		mysqldump --host=dbs-myserver.parasql.com --user=root --password=mypass --ssl --triggers --routines --events --master-data --single-transaction db11234 > mybackup.sql 
	

Database Import via Command Line Tools

If you would like to import a MySQL compatible SQL dump file into your AppSynergy cloud database you can do so easily with the command line tools. Note that the name of the database in the dump file must match your AppSynergy database name, which is the same as your AppSynergy account number (e.g. db11234).

Recommended way to import a local backup of your database:

		mysql --host=dbs-myserver.parasql.com --user=root --password=mypass --ssl --comments --one-database db11345 < mybackup.sql
	

NOTE: the dump file to be imported must contain only a single database.

Importing CSV Files Using Command Line Tools

Most importing of CSV files is done simply via File > Import Data... (or at runtime by using the Browser API class parasql.ui.ImportDataPanel if you want end-users to import data without having Administrator permission). However, if you want to load CSV files from the command line (typically as part of a batch job) this can be done using either the mysqlimport or mysql command line utilities.

First, download and install the mysql command line utility and start it as follows:

		mysql --host=dbs-myserver.parasql.com --user=root --password=mypass --ssl --local-infile=1
	

Then you can use a LOAD DATA statement similar to the following to quickly load a CSV on your local machine into an existing table in your AppSynergy cloud database:

		LOAD DATA LOCAL INFILE '\path\to\myfile.csv' 

		INTO TABLE db11234.MyTableName 

 	 	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 

  		LINES TERMINATED BY '\r\n' 

  		IGNORE 1 LINES

The above command assumes the CSV file was created on Windows using Excel and saved in CSV UTF-8 format (important!) with a single header row, and that the columns in the CSV exactly match those in the table.

Connecting To Your Database via MySQL Workbench

NOTE: AppSynergy has an integrated SQL Console which eliminates much of the need for MySQL Workbench. See Tools > SQL Console... for details.

You can connect directly to your AppSynergy database with MySQL Workbench. AppSynergy runs the latest stable version of MariaDB with our exclusive Federated Integration enhancements. Workbench allows you to issue any SQL command as root, which has slightly more permissions than a AppSynergy Administrator. Workbench also has a Migration Wizard for migrating Microsoft Access and other databases to AppSynergy.

The gsutil Command Line Utility

To copy files to or from your bucket you will need gsutil. The gsutil program is a free Google utility. You can download it here. When you configure gsutil be sure you are logged in with the same Google account you used to create your AppSynergy account (we can add additional users to your bucket upon request). When gsutil config asks you for a project id, please specify: parasql-app

Example: copy a local file to your AppSynergy cloud bucket using gsutil:

gsutil cp MyLocalFile.sql gs://db11345.storage.parasql.com/dumps/loadme.sql