Mysql

VirtualBox Manager – Whats Next?

There is a small bug on the delete hosts page that will be fixed. I’m going to keep the max number of hosts, at this time, to 7 to support smaller screens without having to rewrite the code to support multiple pages of hosts. I’m going to add, at mininum, an additional class where I want to allow users to connect to the virtual machine itself to run commands. I will configure the virtual machine menu the same way as the host control menu. VirtualBox Manager will scan the virtual machine and display buttons with relevant servers that are running. For example if the machine has apache, sendmail, and mysql it will detect them and display a button for each service. The user can then start, stop, or reset the service.

I should have this update implemented in the next couple of days. I will also set a toggle in the main menu to enable a main application password required before connecting to any host. Although I may reserve this feature for the enterprise level application.

Lighttpd – A lighter Web Server

The first thing to do it get lighttpd installed with php and mysql working smoothly. Rather than reiterating the same content here is a convenient post:

http://www.howtoforge.com/lighttpd_mysql_php_debian_etch

Interesting options and various configuration for lighttpd can be found here:

http://redmine.lighttpd.net/projects/lighttpd/wiki/Docs:ConfigurationOptions

One insteresting, built in, option is throttling. Use connection.kbytes-per-second and you can effectivly limit users to particular data transmission rates. There is also a compression module, and a caching module. Much like apache these modules can be installed with apt-get. Issue:

sudo apt-cache search lighttpd

This will list various packages that can be installed. I know the apache modules are packaged as libapache2-mod-*.

With built in round robin and proxy modules this is an interesting package to investigate using.

Slave Server – Backup the Backup – More Uptime

I’m almost at the point of having a completely duplicated slave server. Currently mysql database replication is working. I have apache php5 and mysql fully working. The server synchronizes the apache root directory, with the server, every minute. Therefore for all practical purposes any file changes on the master are propogated to the slave. The mysql database is propogated in real time. I installed dovecot, sendmail, spamassassin, and clamav therefore the email environment is setup. I’m going to sync the entire /etc directory.

I’m stuck, for a little, on the users inboxes. I’m trying to rsync /var/mail but I think some permissions are missing. After I get the /var/mail directory to synchronize, as per crontab, the systems will be fully synchronized.

Now when server2 is backed up using vboxtool the main server will remain completely online! This will save approximately 5 minutes per day, therefore I will be saving 30.42 hours of downtime per year. By backing up the server as such I will be adding 30.42 hours of uptime; a rather big number.

Get Data from Master – Not Working As Expected

While doing mysql database replication, to a slave server, I gave up trying to get the data directly from the master. I just copied the master database manually and things worked out smoothly. First dump the database from the master:

mysqldump -u root -p --opt exampledb > exampledb.sql

Clearly replace exampledb with the database that you want to dump. The command will also primpt you for the root mysql password. Then scp the sql file to the slave server:

scp exampledb.sql server2:exampledb.sql

Notice that the hostname of my slave server is conveniently server2. The command will also assume that the user is the same username that you are logged in with on the master.

On the slave server create a database with the same name as the master. Login to mysql:

mysql -u root -p

Issue the command to create the database:

CREATE DATABASE exampledb;

Then dump the sql file to the slave database:

mysql -u root -p exampledb < exampledb.sql

The command will dump exampledb.sql to the newly create exampledb database. Then login to mysql on the slave:

mysql -u root -p

Issue:

STOP SLAVE;
RESET SLAVE;
QUIT;

As per notes in my previous post issue:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='replicate', MASTER_PASSWORD='replicate', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

As states in the previous post the log file and the master log pos are determined by running on the master mysql server in a mysql shell:

SHOW MASTER STATUS;

Now log back into the slave mysql shell. Issue:

START SLAVE;

Thats should do it.

Hostnames – Strategic and Secure Naming

I have 4 system in my house. The primary I label serverhost. On the serverhost the /etc/hostnames file is the same as the hostnames file on the other machines. The hostnames file is formatted accordingly:

##/etc/hostnames
192.168.1.100 serverhost.bgevolution.com serverhost
192.168.1.101 server.bgevolution.com server
192.168.0.102 tv.bgevolution.com tv
192.168.0.103 server2.bgevolution.com server2

Server 2 is new. I can further cleanup the schema by changing the static address of tv to 103 and server2 to 102. But for now its ok. Now I can reference hosts, with the respective ip address, simply by hostname. This is convenient for setting permissions. Permissions can be a pain in the neck, and if you are referencing a host by ip, localhost, and or the hostname you will have to create permissions for each. For example your mysql server will need permissions for each hostname that you want to connect to it with. Connecting to the server from the serverhost I need permissions for username@serverhost.bgevolution.com. But if I am connecting to mysql through phpmyadmin I need permissions for username@server.bgevolution.com.

I have replicated the mysql server on server to server2, which I connect to with the phpmyadmin installation on server. On server2 I must give permissions to username@server.bgevolution.com to be able to connect from the server. Ultimately with no experience dealing with permissions it is a pain, but after getting everything running you will realize that it makes sense, and it does increase security.

I want the slave server to also behave as the master, and ultimately I want to experiment with setting up a round robin mysql server, meaning as http requests come in to the website, I want it to randomly access database information from either server or server2. This should prove fun.