How to Start, Stop and Restart MySQL

What do you do if your database server is down? Well, you can certainly reboot the server. That might solve the problem, but I rather restart the database first before doing something as drastic as rebooting a server. There are instances were rebooting doesn’t always solve the problem if there’s an issue with the database. Now, most open-source blogs and web applications today use MySQL as their database of choice. So, here are instructions on how to start, stop and restart MySQL on Ubuntu and Debian-based systems.

Stop MySQL database

$ sudo /etc/init.d/mysql stop

Start MySQL database

$ sudo /etc/init.d/mysql start

Restart MySQL database

$ sudo /etc/init.d/mysql restart

MySQL mysql_connect new_link

I want to share a database connection access problem I had last week, while working with a custom PHP script inside WordPress. I had created this WordPress Page Template containing some PHP code that needs access to the database. The problem was that the database connection for my custom script overwrote the WordPress database connection that was previously establish, causing certain parts of WordPress to not display properly.

It took me a while to figure out that it was the newer database connection of my custom PHP script that was causing the previously established WordPress database connection to disappear. Hence, certain parts of the WordPress page were not displayed. Little did I know, that the fix was quite simple. So, here’s a sample of my mysql_connect code. Prior to this line, I’ve already set the variables.

Mysql_connect

$db=mysql_connect($host,$username,$password);

The Fix

Simply add a fourth parameter called new_link and set it to TRUE.

$db=mysql_connect($host,$username,$password,TRUE);

What this does is basically telling mysql_connect to establish a new connection, while keeping the older mysql_connect connection around, in case we need to access it at a later time. It’s amazing how one little switch in a command can make a huge difference to this seemingly simple code. Anyways, adding a fourth parameter and setting it to TRUE was the solution.

Backup MySQL via Bash Script

If you run your own LAMP server, you might want to check out this Bash script which perform backups of the MySQL database. The neat little script can be set to run independently or as part of a cron job. All you have to do is provide the backup directory, username, password, hostname and port number.

The backup script is quite simple. It checks if the backup directory exists and creates one if it’s missing. It will then list all databases and performs a MySQL dump of each database ignoring the mysql, information_schema, and test databases. The file is formatted in gzip and then stored in the backup directory.

The backup script can be set to run every day or once a week using cron. The backup will depend on your backup requirements. I recommend testing the backup and restore process to see if it works.

The Perfect Server Based On Ubuntu 10.04

There is no such thing as a perfect server, but this particular one is as close as you can get to being perfect. This tutorial will walk you through how to install the Ubuntu 10.04 LTS Server with all the services available typically that you get from ISPs and hosters.

It contains the installation of Apache web server (SSL-capable), Postfix mail server with SMTP-AUTH and TLS, BIND DNS server, Proftpd FTP server, MySQL server, Courier POP3/IMAP, Quota, Firewall, etc. It also installs the free web hosting control panel called ISPConfig2. Here’s the link to the tutorial.

MySQL 5.5. Will Be 200 Percent Faster

Oracle promises that MySQL 5.5 will be 200% faster than its predecessor. From H-Online:

Oracle presented a beta of what it called a “much faster” MySQL at the O’Reilly MySQL Conference and insists it will be continuing to invest in the open source database. Oracle’s Chief Corporate Architect, Edward Screven, presented the beta version of MySQL 5.5 which will now use InnoDB as its default storage engine, saying that the switch offers a 200% performance improvement and over ten times faster recovery times. He assured the audience that despite the switch to Oracle’s InnoDB, Oracle will be maintaining the pluggable storage engine architecture and that the company would continue to ship the same code base in the community and enterprise editions.

Screven said Oracle’s plan for MySQL was to keep it as a slim, easy-to-administer database and that it would be investing to make MySQL compete more effectively with Microsoft’s SQL Server. He noted that more customers deploy MySQL on Windows than on any other platform. In an interview with eWeek before the presentation, Screven also confirmed that MySQL’s Falcon storage engine was no longer being developed. “Falcon was Sun’s, or actually really MySQL AB’s…response to Oracle buying InnoDB” he said, “Now that we’ve brought the teams together, Falcon doesn’t have a place in the world”. Oracle’s future plans include making it easier to migrate data between MySQL and Oracle databases and adding Oracle features such as Secure Backup, Enterprise Manager and Audit Vault to MySQL.

Read More

WordPress Blogs Hacked Via Config File

A number of WordPress blogs hosted at Network Solutions were hacked according to ZDNet. A malicious hacker was able to create a script that scanned for WordPress config files which contain MySQL database credentials in plain text.

WordPress config files should only be read by Apache only with permissions of 750. Most users have their permissions set to 755. WordPress users should set their permissions to 750 to avoid from being hacked.

Another way of protecting WordPress config files is to use .htaccess. Add the following code to your .htaccess file.

# protect wpconfig.php
<files wp-config.php>
order allow,deny
deny from all
</files>

A New Webhost

If you noticed some strange behavior in this site the last day or so, I was in the middle of moving from one hosting company to another. What a difference a day makes. Notice how fast and responsive the site is now! It’s like night and day. It’s all good. The move was a lot more involved that I thought it would be. Learned something new about the move.

Moving WordPress was a bit tricky since I had way too many plugins. I had 32 at one point. So, I cut the plugins by 1/3. I’m running 12. Still too many plugins in my opinion. I’ll cut it down some more. I’m trying to limit the number of plugins since they tend to slow the site down.

I installed WordPress using Subversion. I need to update my shell script to get ready for WordPress 2.9 which is just around the corner. Anyway, the site is back faster than ever. Thanks to my new host.

Remove Apache, Install Lighttpd

I was asked in my previous post as to why I installed the popular Apache web server instead of lighttpd, a fast and highly optimized web server with a small footprint. If you need enough convincing, Youtube, Wikipedia and Meebo all use the lighttpd web server. Well, if you asked for it, you’ll going to get it. This article is a how-to in removing Apache and replacing it with the lighttpd web server.

Remove Apache

sudo apt-get remove apache2

Remove Apache from startup

sudo update-rc.d -f apache2 remove

Install lighttpd

sudo apt-get install lighttpd

Install PHP-5 and modules

sudo apt-get install php5-cgi php5-mysql php5-curl php5-gd php5-idn php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-mhash php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl

Edit php.ini and set cgi.fix_pathinfo to 1

sudo vi /etc/php5/cgi/php.ini

Set cgi.fix_pathinfo = 1

Add lighttpd user

sudo useradd -d /home/lighttpd -m -s /bin/bash lighttpd

Add lighttpd group

sudo groupadd lighttpd

Set mod_fastcgi and mod_rewrite

sudo vi /etc/lighttpd/lighttpd.conf

Uncomment the following lines:

"mod_fastcgi",
"mod_rewrite",
Add FastCGI</strong>
fastcgi.server = ( ".php" => ((
"bin-path" => "/usr/bin/php5-cgi",
"socket" => "/tmp/php.socket",
"max-procs" => 1,
"bin-environment" => (
"PHP_FCGI_CHILDREN" => "4",
"PHP_FCGI_MAX_REQUESTS" => "1000"
),
)))

Set file permissions to log files

sudo chown -R lighttpd:lighttpd /var/log/lighttpd/error.log
sudo chown -R lighttpd:lighttpd /var/log/lighttpd/access.log

Set WordPress Permalinks

sudo vi /etc/lighttpd/lighttpd.conf 
server.error-handler-404 = "/index.php"

Restart lighttpd web server

sudo /etc/init.d/lighttpd force-reload
or 
sudo /etc/init.d/lightttpd restart

Install LAMP on Ubuntu Desktop

My development server is old. You’ll get a chuckle when you see the specs. It runs on a 400Mhz Pentium II CPU with a 128MB RAM and a 40GB drive. This old relic still manages to run Apache, MySQL, PHP and a local DNS. The server has also gone through 4 Ubuntu upgrades from version 7.04 to 9.04. After each upgrade, pages that require MySQL and PHP have slowed down considerably.

You guessed it. It’s about time to move to another machine. So, I’ve decided to install AMP minus the L (Linux) since we are already installing it on the Ubuntu Desktop. The following tutorial will show you how to install Apache, MySQL, PHP as well as the MySQL admin tool called PhpMyAdmin. Let’s get started:

Install Apache

sudo apt-get install apache2

Install PHP. Restart Apache

sudo apt-get install php5
sudo /etc/init.d/apache2 restart

Install MySQL

sudo apt-get install mysql-server
sudo apt-get install libapache2-mod-auth-mysql
sudo apt-get install php5-mysql

Finally PhpMyAdmin
sudo apt-get install phpmyadmin

You’ll be asked to provide passwords on the MySQL and PhpMyAdmin installations. There are a couple of minor tweaks you have to do to make sure the applications are working properly. First, make sure the MySQL extension is set in PHP. Restart Apache again after you make your changes.

MySQL Extension. Restart Apache

sudo vi /etc/php5/apache2/php.ini
extension=mysql.so
sudo /etc/init.d/apache2 restart

Now, open your Firefox browser, and type `localhost` in the address bar. If you see “It Works!,” that means the installation was successful. One final thing before you go, I installed WordPress and the installation was a success, except for the mod_rewrite which wasn’t working if you try to use the permalinks feature. To make the permalinks feature work, first you’ll need to create a .htaccess file and make it writable. Next, turn on mod_rewrite module.

Mod Rewrite

cd /var/www/
touch .htaccess
chmod 777 .htaccess
sudo a2enmod rewrite

Lastly, make sure AllowOverride is set to All. Edit the file:

sudo vi /etc/apache2/sites-available/default

Allow Override

<Directory /var/www/>
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>

Restart Apache

sudo /etc/init.d/apache2 restart

So What Happens to MySQL

mysqlWe all woke up Monday morning and heard the news. Oracle just bought Sun Microsystems for 7.4 billion. Oracle produces business software and databases. By acquiring Sun, Oracle becomes a hardware vendor with Sun’s family of servers and workstations. Oracle also inherits Java and its assortment of programs. It also gains an operating system called Solaris. And most importantly, Oracle is now the proud owner of the widely popular open-source database called MySQL.

At first, the acquisition makes a lot of sense. But, some think that this buyout is a mistake. Does Oracle really want to become a hardware vendor? Can it sell servers and workstations better than Sun ever did? Can it make Solaris more popular than Linux? Or was Oracle only after MySQL? We will never know the answers to these questions. At least, not in the immediate future. So, what happens to MySQL?

No one knows for sure. Will Oracle make money from it? No one has done it before. Not even MySQL. So, will Oracle simply kill it or charge existing and future users? One thing is for sure, some people will bail out. There are other options out there. One thing about open-source, you can’t really kill it. You can buy it, but you can’t stop it from forking.

Michael “Monty” Widenius, the founder of MySQL and Monty Program Ab has already left Sun a few months back and has forked MySQL with a database called MariaDB. Another option is to go with PostgreSQL, another open-source database that has played second fiddle to MySQL all these years.

It will be interesting to see which direction will MySQL go to under the direction of Oracle.