MySQL processes are created via the
MySQL panel of our member site. For a description of our MySQL pricing, see this page.
Within a MySQL process, you will have full administrative control and you may create as many databases as you wish. One process is more than adequate for most uses, including supporting web sites, each with multiple unrelated applications, such as forum software and blogging tools, but you may add more if you wish.
We do reserve the right to limit excessive MySQL usage that disrupts other members. MySQL is very efficient, so this is rarely an issue, although it can come up if a process is using large tables that are not properly indexed.
(For information about the difference between a MySQL process and MySQL database, please see this related FAQ entry.)
There are two easy ways to create a database inside your MySQL process. For either method, you will need the database administrator username and password. The username is the same as your member login name, and a temporary password was created and emailed to you when you created the process.
The fastest way to create a database in your MySQL process is to click the "Create a Database" action on the MySQL Process Information page for your MySQL process on the MySQL panel, and just follow the steps.
However, the phpMyAdmin tool that we provide can also be used: Choose "Open phpMyAdmin" from the "Actions" box on the MySQL tab Then log in to your MySQL process. Then choose the "Databases" tab. Next, look for the text box under Create database. Pick a name for your database and enter it in that box. Then select the Create button. That's all there is to it!
You can use this ability to set up a whole bunch of applications to use different databases in the same MySQL process, even if the applications are running on completely different sites and don't share any information.
Example: Suppose you have two sites, and you wish to run the phpBB forum software on both of them without having them smack into each other. You could create two databases, one called sitea and one called siteb, in your single MySQL process, then set the $dbname value in each site's phpBB config.php file to sitea or siteb.
(You can also use phpBB's $table_prefix feature for this purpose, but three out of four nerds agree that the two-database way is "better.")
The DSN, or hostname, for connecting to MySQL is the name you gave when you created it, followed by the extension .db. The full connection information can be found in the email sent to you when you created your database. Never use localhost as part of your database connection information.
If you need to find your DSN and no longer have this email, visit the MySQL panel. The DSN for your process appears in the first column. Clicking on the DSN name will take you to a page with more information about your process, including your default admin username.
DSN stands for Database Server Name and is sometimes also referred to as hostname or MySQL Server Name or even just server name. This is different from Database Name, which is something you choose separately for each program that uses your database (and usually create from our interface, the command line, or a tool like phpMyAdmin).
A MySQL process or MySQL server (or even MySQL server process) refers to the actual running MySQL software.
A MySQL database, on the other hand, is a collection of related tables within a MySQL process that share a common purpose, like running a forum or blog application.
A MySQL process may contain more than one MySQL database. (If it doesn't have at least one, it isn't very useful.)
MySQL processes are identified by their DSN or hostname, which is of the form example.db. You chose this when you created the MySQL process, and you can see it on your MySQL panel in the user interface.
Sometimes the term MySQL database gets misused to mean the MySQL process. This usually happens when the process only has one database. We try to avoid that usage, because it is ambiguous and confusing, and we encourage you to do the same. But it happens, so if someone says "MySQL database" and you aren't 100% sure which one they mean, ask them. "Do you mean the MySQL process itself or the database within the process?" The confusion you avoid may be your own, because you have to know which name goes where when you configure your application.
Possibly. When we created your MySQL process, we assigned a password to the 'root' MySQL user, which only works from the physical machine running the process (which no users have access to). If you have not removed that, we can reset your database password with no problem. In cases where this is all that is needed, the cost is $1 (waived for support subscribers). If you want to do this, open an assistance request and indicate you are aware of the charge. If the reset is successful, a temporary password will be emailed to you.
If you have removed our access, which you are welcome to do, then we will have to use a different approach that is very time-consuming. A professional services fee will apply. (Typically, $10-30 depending upon the amount of DBA time required.) This approach will cause downtime for your database and any sites that depend on it. The procedure is fairly safe, but there is a small risk that it might cause you to lose some or all of your data.
We strongly recommend that you use your MySQL administrator username and password only for the purpose of creating other MySQL users.
It is much too powerful to be used directly from your website or plugged into any web pages or config files! To create a new user with only the necessary privileges, see: this entry.
Please keep careful track of your MySQL passwords.
To connect successfully, use a command similar to the following:
YourPrompt$ mysql --host=example.db --user=username -p Database
Where the "username" is the database username (usually the same as your NearlyFreeSpeech.NET login, unless you have created another) and "example.db" is the database DSN you chose when creating your database and "Database" is the name of a database in your MySQL process that you have created. Using the above command line, you will be prompted for your MySQL password. The initial password was emailed to you at the time you created your process, but hopefully you've changed it since then.
To create a database in your MySQL process from the command line, use a command similar to the following:
Using the same values as the first example, this will prompt you for your MySQL password and then create a database called Database in your MySQL process. (We'll leave the task of coming up with a database name more creative than "Database" as an exercise for the reader.)
If you need to redirect input (for example to load a MySQL dump file), you will additionally have to specify the MySQL password on the command line, like so:
YourPrompt$ mysql --host=example.db --user=username --password=password Database <database.sql
You cannot directly access MySQL processes running on our network; they use private non-routable IP addresses.
However, you can use SSH port forwarding to access them remotely.
This is allowed for two very specific purposes:
Initial set up (or tear down, should it be necessary) of your MySQL database.
Routine database administration tasks.
The best guideline for whether what you are doing is allowed is whether you are doing it. In other words, no automated port-forwarded access to MySQL is allowed; it must be you personally doing whatever you're doing. If, for example, you want to back up your process, you should use another method.
Because of the large variety of ssh clients and ssh-protocol tunneling applications that exist, and the innate complexity of ssh tunneling, we cannot provide any technical support related to remote access to your MySQL database.
To use ssh port forwarding in conjunction with the industry-standard OpenSSH client, one would use a command similar to the following on one's local system (not our ssh server):
Please note that the command above needs significant specialization for use in your specific circumstances. If you are uncomfortable making the needed changes, this is not a feature you should attempt to use.
On our system, whatever method you choose needs to boil down to two SQL commands:
CREATE USER 'username' IDENTIFIED BY 'password';
GRANT ALL ON dbname.* TO 'username';
Do not specify a hostname when creating users; since we use a clustered hosting network, you cannot predict from what host a connection will originate. Also, attempts to "GRANT ALL ON *.*" will typically fail due to the lack of the FILE permission by default on admin users, and it is bad practice to grant administrative privileges to a website user. Finally, try to avoid granting ALL if you can; security is always enhanced by minimizing the privileges of website users.
The "mysql" database contains vital information about the other databases, usernames and passwords in your MySQL process used when it starts up. It is a very important part of MySQL, and your process won't start if it's removed or corrupted. Unfortunately, MySQL will let you alter or remove it without any warning or special confirmation. Don't ever do that. Because people sometimes do, our system checks on this database periodically and if it detects a problem it will report your MySQL process as "Damaged" in our member interface.
MySQL 5.0 and later versions also contain a synthetic database called "information_schema" which can be used with SQL commands to obtain metadata about your other databases. Since this database is created entirely from information about other databases, it doesn't really exist, and attempts to alter or remove it will fail.
MySQL processes may also be created with a database called "test." This database serves no purpose and can be removed if you don't want to use it.
Because we still have to retain the associated MySQL data and reserve enough resources for you to start it at any time.
To avoid being charged for having a MySQL process you are not using, you can remove it by visiting the "mysql" page, selecting the name of the process you want to delete, and then selecting "Permanently Remove Process" from the "Actions" box.
There are several causes of this error. To figure out what's going on, look for a hash sign and a four-digit error code in front of this message. E.g. "#1045 Cannot log in to the MySQL server." This code is a MySQL error code, which comes in two varieties, 1XXX server codes and 2XXX client codes.
The two most common error codes are #1045 and #2002.
Error code #1045
This typically indicates a problem with the username/password combination provided. That's definitely the first thing to check. It could also happen if you got your username and password right, but mistyped the DSN and happened to get another valid one that doesn't accept your info.
Also, if your member login (which is also your default MySQL username) is too long for MySQL, our system truncates it automatically, and you must use that instead. You can verify the correct username information by visiting the "mysql" tab and clicking on the name of the process (ending in ".db"), which is listed under the "DSN" column. Your MySQL default username is listed under "How to Access MySQL."
Error code #2002
This indicates that the MySQL process didn't answer. In other words, it didn't even get as far as checking if your username and password were valid. This usually indicates a typo in the DSN, or that the MySQL process is not running. In rare cases, it may mean that the process is hung or deadlocked.
In some rare cases, if your MySQL process has existed for a long time without any updates to your password, it may be storing your password in a format phpMyAdmin can't authenticate against. (phpMyAdmin uses very recent PHP versions and extensions that are not compatible with archaic setups.) This will cause you to be unable to log in via phpMyAdmin, even with the correct username and password. In these cases, it is usually sufficient to change your MySQL password by another means (e.g. the command line), even if you "change" the password to the same thing.
You receive this message if you attempt to connect to MySQL without telling our system which actual MySQL process you wish to connect to.
When you created the MySQL process, you selected a DSN (also called its hostname), which is the name you must use to access your process. This name is of the form something.db and is also identified in your MySQL setup email and on your MySQL panel.
Most software packages that use MySQL offer a configuration variable of some kind to specify the DSN. You must enter the something.db name for this purpose. You cannot use localhost or leave it blank, or your program will not work, and you'll get this error message.
When setting up your site's MySQL connection information, it's tempting to skip the step of creating an additional user and just use the "admin" username (your member login name) and password. Never do that!
The first and most important reason not to do this is security. Storing your MySQL administrator password in plain text as part of your publicly-accessible web site is a really bad idea. The slightest vulnerability in your application, the web server software, or the operating system could result in a complete takeover of your MySQL process, rendering it unrecoverable to you.
Your web site doesn't need administrator privileges against your database. It needs some subset of that, and creating a dedicated MySQL user for your site with only the necessary privileges is a good way to limit exposure to SQL injection attacks and other security issues.
Second, your admin username has the SUPER privilege. MySQL only allows a certain number of connections at a time. If it runs out, either due to a problem with your application or a glitch in the MySQL process itself, your site will start getting "too many connections" errors. However, in order to make sure you can get in and fix it, MySQL always holds a single connection aside for a user with the SUPER privilege. If your site is using your admin username, you'll be unable to resolve the issue yourself because that "rescue" connection will already have been wasted by the site. If you find yourself in that situation, you'll have to open a support issue and ask us to manually restart the process.
Note: If you have a subscription membership, you can request that we log in and manually restart your process. The protective measures described above are designed to ensure that it is never necessary. If you bypass all the protections, you can still wind up in this situation.
So remember, using your MySQL admin username from your site is a terrible idea. It seems easy and harmless up front, but sooner or later you will wind up regretting it.
The MySQL "user" table that controls who can access your MySQL process contains a "Host" field that controls what hosts can connect as that user. No matter how you create MySQL users, when using our service, you should always make sure the "Host" field contains only the % character.
Since we use a clustered network with specialized nodes for hosting and specialized nodes for MySQL, you will never connect to your MySQL process from the server it's actually running on. Since things move around, you won't even know in advance which server will originate the connection. Using the % character allows connections from any of the servers on our network, which is the only way you will be able to connect. Using "localhost" or any other value will lead to connection problems.
Ordinarily, we prohibit unattended access to our systems. However, there's an exception for backups, and this definitely qualifies.
There are basically three parts:
Something on your local computer to schedule/run the backups. On a Unix system or Mac, this would be "cron." Windows has a way to do the same thing, but we are not familiar with it. So you set this up to run a task every time you want the backup to happen. (E.g. once a week.)
This command spews the backup directly to standard output, so you generally want to send it somewhere after that. If you happened to be using a Unix machine on your side and the OpenSSH client, you could do something like:
That would create a compressed SQL file on your system containing a full backup of your MySQL process on our system. To do this in an unattended way, you would need an ssh public key installed on our system that has no passphrase on the private key on your local machine.
Since the MySQL password you're using will be in the above command, we recommend creating a separate MySQL user with the minimum privileges necessary (i.e. SELECT) to do backups. You could do that from phpMyAdmin or with SQL similar to:
MySQL> CREATE USER backup IDENTIFIED BY 'correct-horse-battery-staple'; MySQL> GRANT SELECT ON *.* TO backup;
Then use backup and correct-horse-battery-staple (being sure to use a fresh random password and not this thoroughly-popularized example) in lieu of your normal MySQL username and password in the mysqldump commands above.
This will take some (or a lot of) tweaking depending on what you've got on your end, but that's the gist of how it can work.
InnoDB is a standard part of all MariaDB 10.x processes.
InnoDB can be added to older processes (i.e. MariaDB 5.3) that do not already have it. To do so, enable it from the Actions box on the MySQL info panel for that process. (A small additional fee applies.)
InnoDB cannot be completely removed. If you do not want to use it and you have a MariaDB 10.x process, you can attempt to disable it to save money by using a custom configuration similar to:
default-storage-engine = MyISAM
innodb = OFF
This setting may cause your process to use less memory (and therefore cost less), but please make very, very sure you don't have any important data in InnoDB tables before disabling it.
We do strongly recommend using InnoDB whenever possible. We use it ourselves, exclusively. The power and reliability of InnoDB are well worth the small additional cost.
To remove your MySQL process, visit the MySQL panel, click on the name of the relevant MySQL process, and select the "Permanently Remove Process" action from the MySQL Process Information panel.
When you created your MySQL process, a temporary password was set up automatically. The name of your process, your admin username, and the temporary password were all sent to you via email at that time. The temporary password is not stored and no one at NearlyFreeSpeech.NET knows what it is. Also, we recommend that you change the admin password and create additional users for your application.
Your MySQL admin username is typically the same as your member login. However, we allow longer usernames than MySQL supports, so if your username is very long, it may be truncated for MySQL. To check your MySQL admin username, view the MySQL Process Information panel in our member interface for your MySQL process. The username will be shown on the "Default Admin Username" line of the "How to Access MySQL" box.
There is no in-place upgrade between MariaDB major versions; it is too risky and too irreversible. To upgrade, create a new process and migrate your data from the old process to the new process. This approach removes the risk of an upgrade failure, and ensures you can "roll back" if the upgrade fails.
Follow these steps:
Create a new MySQL process from the MySQL tab of our member interface.
Create the necessary MySQL users to support your applications on your new process using its admin user. (What a great time to change/upgrade those passwords!)
Backup the relevant database(s) containing your data. Do not back up the mysql database or any synthetic databases like information_schema or performance_schema.
Restore the backups to the new process, using its admin login and password.
Update your application(s) to use the right DSN and password for the new process. (Save the old values in case you need to roll back.)
Test and confirm that absolutely everything you care about is working.
Disable auto-restart on the old process.
Stop the old process.
Test and confirm that absolutely everything you care about is working again.
Wait a week just in case. (Optional, but highly recommended.)
Delete the old process once you're adequately convinced the upgrade is a success.
The recommended way to backup and restore MySQL data is to use the ssh command line, but it can also be done via phpMyAdmin if you prefer.
To do step 3 from the command line, first complete steps 1 and 2, then make the backup with a command like this for MariaDB 10.x or newer:
In either case, the command is all one line, starting with mariadb-dump. Replace exampleuser with your MySQL administrator username. Replace exampleold.db with the name of your original MySQL process. Replace exampledb1 through exampledb3 with the names of all of your relevant databases in the process, as described in step 3 above.
Running this command will prompt you for the password. On success, it will produce no other output.
For step 4, load the backed-up data into the new process with a command like this:
YourPrompt$ mysql --user=exampleuser --host=examplenew.db -p <migrate.sql
Replace exampleuser with your MySQL administrator username for the new process. Replace examplenew.db with the name of the new process you created in step 1.
This command will also prompt you for the password when you run it, and should produce no other output.
When a MySQL process is first created (or started) it can be very resource intensive. After things settle down, our system can very carefully start to prune away at bits that aren't being used to get the resource usage down. This process is very conservative so as not to impact performance, and it can take a couple of weeks.
The effect of this is that the initial billing estimates of MySQL are based on a usage level that isn't (usually) sustained, so it's not unusual for it to report that a new process may cost several dollars a month because based on that limited sample, it would. But as things settle down, the estimate will typically go down to reflect your real usage. As of September 2016, the median cost for MariaDB 10 processes is about $0.60 for resources and storage combined and over 72% of MariaDB 10 processes cost less than $1.00/month.
If your usage records and estimates remain high for more than a week or two, you may wish to review your usage to see if something is more intensive than you expected. Missing indexes, for example, can really increase the resource usage of a MySQL process.
This indicates that you have altered or removed the contents of the "mysql" database of your MySQL process in a way that will prevent your process restarting, or render it unusable after restarting. MySQL processes don't restart very often, so it could be weeks or months before the effects of these actions surface, by which time you will probably have forgotten all about them.
If you catch the problem before your process restarts, you should still be able to connect to it and make a backup of your data.
If you don't catch the problem in time, there is essentially no way to repair it; your MySQL process either won't start anymore or will start but won't be accessible. You'll end up having to delete the process and start over with a new one. If you contact us before you delete the old MySQL process, we may be able to recover some or all of your data and restore it to the new one, but if this is possible and you wish it done, a non-trivial service fee will apply to cover MySQL administrator time.
Keep in mind that while our system does check periodically for this type of damage, it doesn't detect all cases and may not pick it up right away. To keep your MySQL process safe and happy, make sure you always leave the "mysql" database alone.
The "FLUSH PRIVILEGES" SQL command will also cause a running process to immediately fail if it has this type of damage.
For MariaDB 10.x and later, set the following variables in a MySQL admin session, typically either via phpMyAdmin or from the MySQL command line client:
SET GLOBAL log_output = 'TABLE'; (to make sure logs go where you can see them)
SET GLOBAL general_log = 'ON'; (to enable the general log)
SET GLOBAL slow_query_log = 'ON'; (to enable the slow query log)
You can enable either or both of the log files, depending on your needs.
After taking these steps, log contents will be in the mysql.general_log and mysql.slow_log tables; no restart is required. Please see the MariaDB documentation for full details.
When you are finished, make sure to disable logging again, and truncate the log files to save space and cost:
SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';
TRUNCATE TABLE mysql.general_log;
TRUNCATE TABLE mysql.slow_log;
For MariaDB 5.x, logging to tables is not supported. If you have an older process, consider upgrading, but in the mean time:
