Chapter 7: Migrating a SiteWorx account to a different MySQL server Up Part II: Advanced MySQL Administration and Management

8 Odds and Ends

8.1 Command-Line Reference

service mysqld [start|stop|restart|status] or /etc/init.d/mysqld [start|stop|restart|status]
Start/Stop/Restart/Check status of the MySQL server daemon
chkconfig --levels [12345] mysqld [on|off]
Enable/disable mysqld on boot based on linux init run level. Runlevels 3,4,5 are typically considered the “live server” run levels and thus you typically want MySQL to be enabled on those levels.
~iworx/bin/backup.pex
This is the script which allows you to generate SiteWorx account backups (including MySQL-only partial backups) from command-line. See section 6.2.1↑ for more details.
~iworx/bin/iworxdb-transfer.pex
This script allows you to migrate a SiteWorx account’s mysql data from one MySQL server to another, as well as perform a remapping of the current assigned MySQL server to a new one. See chapter 7↑.
grep rootdsn ~iworx/iworx.ini
This command is used to extract the iworx MySQL user password from the InterWorx configuration file. You should typically strive to use the root password yourself, but this can be handy when InterWorx has error messages about not being able to connect to the MySQL database. You can check to see if perhaps there’s an issue with the root password.
mysql
The command-line mysql client.
mysqldump
Will dump a database or databases of your choice to screen in the form of SQL queries. These queries can be issued to the server to “recreate” the state of the database on demand. Used for generating database backups.
mysqlcheck
Used to check the integrity of the database files on the filesystem. Can also be used to repair the databases automatically if any corruption is found.
/etc/my.cnf
The configuration file for the MySQL server
/var/lib/mysql
Where the actual data for all your MySQL databases “lives” on the file system.

8.2 How MySQL users and databases are mapped to SiteWorx accounts

When creating a MySQL database or user in SiteWorx, InterWorx automatically appends the unix user name of the SiteWorx account to every database/user in order to know which database/user belongs to whatever SiteWorx account. This is why it’s critical to not modify the phrase or text in front of the underscore ’_’ character in any username or database from outside InterWorx. You can mistakenly un-map a user or database from the SiteWorx account and deny the user the ability to control that user or database from inside their SiteWorx panel.
This is also how InterWorx is able to create temporary users on the fly when a SiteWorx user visits phpMyAdmin and is able to see all the databases across all their users. The MySQL user is created with a long, random string password and is only permitted to login from localhost (127.0.0.1) such that this is exclusively a phpMyAdmin-only MySQL user. The user is GRANTed all privileges on the databases that are pre-pended with the SiteWorx unix user name.

8.3 Clustering Considerations

When using clustering, you have to keep in mind that you web application is going to be running and served from multiple servers, not just one. That is why it is critical that you pay attention to how you have your MySQL user permissions setup when creating users for the databases backing the web applications. The easiest thing to do is simply allow the MySQL user to log in from ’%’, which is the wildcard character that means a user can log in from any host. Alternatively, you can set it up so that you have one user/host combination per server (with identical passwords, usernames, and permissions) that allows each server in the cluster access to that database. This is often not recommended if the SiteWorx user is not aware of the cluster’s infrastructure. An alternative is to setup a remote MySQL server on a private network, have every server on the cluster have access to that private network, and create the user with the ’%’ wildcard host name. This ensures that people from the outside would not be able to get access to your MySQL server directly while at the same time ensuring that every server in the cluster is able to log in as the user for the web app.

Footnotes

[A]This is done on the back-end by setting the service to “on” via chkconfig
[C]This is because on the back-end, the primary key is fed into a hashing algorithm to find the correct row in the file that backs your database.
[D]index blocks which more than likely contain hash tables for rapid look-ups of specific rows based on keys
[G]Debugging mode can be activated in NodeWorx under Server ▷ Settings
[J]Backslashes ’\’ at the end of the line are used to allow the command to span multiple lines in the BASH shell

(C) 2017 by InterWorx LLC