:::: MENU ::::
Posts tagged with: mysql

How-To: Insert Binary Data into MySQL

Binary Data is AWESOME!

Working with binary data can be extremely beneficial, especially when it’s very detailed data. Example, it is easier to & and | two integers than to create a crazy function to do math. In one of our previous posts, we created a class and a few functions to handle IP addresses. Inserting a converted IP into the database allows you to & and | a user supplied IP to return matches. This is a great way to work with IP’s within databases.

One issue you may run into is, when you echo a binary string, it could result in a ©'. This doesn’t look like anything readable in this format, but if you convert this with inet_ntop, it would return 97.32.169.39.

If you tried to insert that specific string into a database, it would fail under the most simplest of INSERT‘s. Note the ' (single quote). We will provide some examples on how to insert this string using queries and PHP.

Our Sample Database

For this example, we will use the following database and table:

CREATE DATABASE BinaryTest;

CREATE TABLE `BinaryTest`.`Testing` (
  `binary_field` varbinary(39) NOT NULL
);

Inserting the Data

We have included some examples on how you could insert binary data. There are 2 simple ways to do this, and one that is just plain cool.

1st Method: Prepared Statements

Our first method will use a prepared statement. This will sanitize your input and gracefully insert the row.

try{
    echo "Trying Prepared...\n";
    $stmt = $pdo->prepare("INSERT INTO Test.Testing SET binary_field=?");
    $stmt->execute(array(inet_pton("97.32.169.39")));
}
catch(\Exception $e){
    echo $e->getMessage()."\n";
}

2nd Method: Escaping Statements

Those who are old-timers with MySQL know about the need to quote your insert strings. This would turn ' and " into \' and \", preventing the breakage of quoted values. This will work, but not the most secure and desirable method.

try{
    echo "Trying Prepared...\n";        
    mysql_query("INSERT INTO Test.Testing SET binary_field=". mysql_real_escape_strings(inet_pton("97.32.169.39"));
}
catch(\Exception $e){
    echo $e->getMessage()."\n";
}

3rd Method: Converting to Hex

This is the cool method. If you want to insert Binary data into a MySQL table, convert it to hex. You can then pass this to your query and it will convert it to binary upon insertion.

try{
    echo "Trying Hex...\n";
    $pdo->query("INSERT INTO Test.Testing SET binary_field=0x". bin2hex( inet_pton("97.32.169.39") ) );
}
catch(\Exception $e){
    echo $e->getMessage()."\n";
}

Summary

We would always recommend someone to prepare their query first. If you can’t, go with the 3rd option. You can probably clean it up and optimize the code some more, but try to stay away from manually escaping your values.


5 Tips for Working with IPv6 in PHP

IPv4 vs IPv6

With IPv4, it was simple enough to use ip2long and long2ip. These two functions would translate a dotted decimal address to an integer and the reverse respectively. In IPv6, we don’t have such luxury.

An IPv4 address consists of 32-bits, which most operating systems and programming languages are able to natively support. Since 32-bit platforms support unsigned integers between “ and 4,294,967,295, which is also the maximum number of IPv4 IP’s, working with IP address pragmatically would not exhaust memory and processing capabilities. This would be the equivalent of saying 232. The ip2long function only supports integers up to the max of the operating system and architecture.

IPv6 is a different story. Most machines currently in production are on 64-bit architecture and running a 64-bit operating system. The largest unsigned integer possible on 64-bit platforms is 18,446,744,073,709,551,616, or 264. This falls short of the possible 340,282,366,920,938,463,463,374,607,431,770,000,000 or 2128 addresses available in IPv6. These numbers are VERY large and cumbersome and it’s easy to see some of the restrictions when working with them.

Because of the issues addressing memory management and the simple fact that working with that many bits in a number is cumbersome even for modern day programming languages, IPv6 support and algorithms are commonly misunderstood.

Emphasis: IPv6 does not have a broadcast per-se. In IPv4, the last available address in a range would be reserved for broadcast. In IPv6, there is no concept of a broadcast, instead it would use a multicast on the link-local for all nodes, ff02::1.

Tip #1: Validating IPv4 and IPv6

This is a very simple and straightforward tip. We have been seeing many people using strpos( $ip , ":") to determine if an IPv6 address is identified or substr_count( $ip , ".") == 3 to validate an IPv4 address.

While both of them are possible, they are not 100% accurate. They can lead to security holes in your code and bigger problems in the long run.

PHP provides a filter_var function which can be supplied with up to 3 arguments to filter and validate input. To validate an IP address, you can pass FILTER_VALIDATE_IP. To specifically validate IPv4 vs IPv6, for the 3rd flag you can pass FILTER_FLAG_IPV4 or FILTER_FLAG_IPV6.

Note: It is also good to note that the filter_var function can validate email addresses, url, and more. It is a very useful function.

For example:

if( filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) ){
    // Yes it's valid IPv4
}

This is a surefire and safe way while not wasting resources and re-inventing the wheel. You will see a practical use of these filters later on.

Tip #2: IPv6 Conversions

The following two functions were introduced in PHP 5.1.0, inet_pton and inet_pton. Their purpose is to convert human readable IP addresses into their packed in_addr representation. Since the result is not pure binary, we need to use the unpack function in order to apply bitwise operators.

Both functions support IPv6 as well as IPv4. The only difference is how you unpack the address from the results. With IPv6, you will unpack with contents with A16, and with IPv4, you will unpack with A4.

To put the previous in a perspective here is a little sample output to help clarify:

// Our Example IP's
$ip4= "10.22.99.129";
$ip6= "fe80:1:2:3:a:bad:1dea:dad";

// ip2long examples
var_dump( ip2long($ip4) ); // int(169239425)
var_dump( ip2long($ip6) ); // bool(false)

// inet_pton examples
var_dump( inet_pton( $ip4 ) ); // string(4) "  c"
var_dump( inet_pton( $ip6 ) ); // string(16) "� �"

We demonstrate above that the inet_* family supports both IPv6 and v4. Our next step will be to translate the packed result into an unpacked variable.

// Unpacking and Packing
$_u4 = current( unpack( "A4", inet_pton( $ip4 ) ) );
var_dump( inet_ntop( pack( "A4", $_u4 ) ) ); // string(12) "10.22.99.129"

$_u6 = current( unpack( "A16", inet_pton( $ip6 ) ) );
var_dump( inet_ntop( pack( "A16", $_u6 ) ) ); //string(25) "fe80:1:2:3:a:bad:1dea:dad"

Note: The current function returns the first index of an array. It is equivelant to saying $array[0].

After the unpacking and packing, we can see we achieved the same result as input. This is a simple proof of concept to ensure we are not losing any data.

Tip 3: Ready-Made Functions

We are huge proponents of DRY. DRY is a mentality in coding where you Don’t Repeat Yourself. Functions, classes and more are perfect examples on how to apply the DRY coding ideology. As a result, we created the two functions below to clean-up userland code.

Note: We are also creating a PHP extension for those that don’t wan’t userland garble in their source. Updates will be provided as that time draws nearer.

The dtr_pton function will apply the logic we learned. It will validate your input and return false/throw an exception on error:

/**
 * dtr_pton
 *
 * Converts a printable IP into an unpacked binary string
 *
 * @author Mike Mackintosh - mike@bakeryphp.com
 * @param string $ip
 * @return string $bin
 */
function dtr_pton( $ip ){

    if(filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)){
        return current( unpack( "A4", inet_pton( $ip ) ) );
    }
    elseif(filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)){
        return current( unpack( "A16", inet_pton( $ip ) ) );
    }

    throw new \Exception("Please supply a valid IPv4 or IPv6 address");

    return false;
}

The dtr_ntop function is the inverse to the dtr_pton function. It will validate your input to make sure it conforms to the A4/A16 byte pattern and return false/throw an exception on error:

/**
 * dtr_ntop
 *
 * Converts an unpacked binary string into a printable IP
 *
 * @author Mike Mackintosh - mike@bakeryphp.com
 * @param string $str
 * @return string $ip
 */
function dtr_ntop( $str ){
    if( strlen( $str ) == 16 OR strlen( $str ) == 4 ){
        return inet_ntop( pack( "A".strlen( $str ) , $str ) );
    }

    throw new \Exception( "Please provide a 4 or 16 byte string" );

    return false;
}

Here are some examples using the newly defined functions. First is a successful IPv6 back and forth:

try{

    var_dump( dtr_ntop( dtr_pton( "fe80:1:2:3:a:bad:1dea:dad") ) );
    // Returns: 'string(25) "fe80:1:2:3:a:bad:1dea:dad"'
}
catch(\Exception $e){
  echo $e->getMessage(). "\n";
}

Second is a successful IPv4 back and forth

try{

    var_dump( dtr_ntop( dtr_pton( "10.22.99.129") ) ); // String too short: Throws 'Please provide a 4 or 16 byte string'
    // Returns: 'string(12) "10.22.99.129"'

}
catch(\Exception $e){
  echo $e->getMessage(). "\n";
}

Third, we have appended an extra character to the dtr_ntop function to make the input an extra byte long:

try{

    var_dump( dtr_ntop( dtr_pton( "10.22.99.129").'a' ) ); // String too short: Throws 'Please provide a 4 or 16 byte string'

}
catch(\Exception $e){
  echo $e->getMessage(). "\n";
}

Lastly, we have an invalid IPv6 address:

try{

    var_dump( dtr_ntop( dtr_pton( "ffff:feee:fg::") ) ); // Invalid IP: Throws 'Please supply a valid IPv4 or IPv6 address'

}
catch(\Exception $e){
  echo $e->getMessage(). "\n";
}

Tip #4: AND’ing and OR’ing

Here are a few sample formulas used to calculate different aspects of a network:

v4 Subnet Mask:        long2ip( ((1<<32) -1) << (32 - CIDR ) )
v4 Wildcard:           long2ip( ~(((1<<32) -1) << (32 - CIDR )) )

Network:               IP Address & Mask
Broadcast:             IP Address | ~Mask

Available Hosts:       Broadcast - Network -1
v4 Available Networks: 2^24 - Available Hosts  +2

Once you have mastered, or at least studied the above, you can obtain network variables from an IP and a CIDR.

Using our dtr_pton and dtr_ntop, we will be looking for the network and broadcast for 10.22.99.199/28. To accomplish this, we would use the following code:

$ip = dtr_pton("10.22.99.199");
$mask = dtr_pton(long2ip( ((1<<32) -1) << (32 - 28 ) ));

var_dump( dtr_ntop( $ip & $mask ) );
var_dump( dtr_ntop( $ip | ~ $mask ) );

The response would be:

string(12) "10.22.99.192"
string(12) "10.22.99.207"

An example with IPv6, fe80:1:2:3:a:bad:1dea:dad/82:

$ip = dtr_pton("fe80:1:2:3:a:bad:1dea:dad");
$mask = dtr_pton("ffff:ffff:ffff:ffff:ffff:fff0::");

var_dump( dtr_ntop( $ip & $mask ) );
var_dump( dtr_ntop( $ip | ~ $mask ) );

The response would be:

string(18) "fe80:1:2:3:a:ba0::"
string(26) "fe80:1:2:3:a:baf:ffff:ffff"

Tip #5: Working with Databases

If you have a dynamic need to store IP addresses in string notation to your database, your best bet would be a VARCHAR(39). If you want to store the binary version of the IP, use a VARBINARY(39). This allows for enough storage to be provisioned to hold an entire IPv6 address.

If you have the ability to install UDF’s (User-Defined Functions) in MySQL, and are running version 5.6.2 or lower, I would highly suggest you download an install these from WatchMouse: INET6_PTON and INET6_NTOP. These functions along with a mask function allow you to store and retrieve the binary interpretation of an IP through MySQL statements.

Note: MySQL only recently added support for these functions in 5.6.3 and above.

This is a stable and safe way of storing addresses in a database table. As you can see in the examples on WatchMouse’s page, you can validate IP’s are within a range and even resolve and perform reverse lookups on IPv6 addresses.

If you cannot install UDF’s in MySQL, you can always store dtr_pton‘s output. By doing so, you can perform bitwise operations in your MySQL statement and return the result within your query. This is very helpful for large databases and applications which need IPv6 support.

Putting It All Together

With our primary focus being on networking, security and device management, we have been been engulfed in IPv6 for quite some time. We have hit the same hurdles as everyone else, and face-palmed just as much. You can download a .tar.gz version (coming soon) of all the functions or install the package and stay updated using Composer.

To install with Composer:

Add the following require to your composer.json file:

"dtr/ip": "2.2.4.*@dev"

You can find some of the source files for this project on GitHub: dTR-IP and on GitHub Pages: GitHub Pages: dTR-IP.


Manage Growing InnoDB Databases

InnoDB File Size Management

We are a PerconaDB shop here. We love MySQL, InnoDB and many of the benefits they bring to the table. We have umteen number of articles written for them as well. An intelligently designed database can save you time and money, and a poorly designed one can hurt your bottom line.

A Few Solutions

Note: Not all solutions are applicable to your issue. Please use logic and your best judgement in the event one of the below solutions is applicable.

Solution 1: Starting off on the right foot

To keep yourself from getting into a sticky situation, it is best to add innodb_file_per_table to your /etc/my.cnf. What this command does, is tell InnoDB to store table data in separate files, like MyISAM, rather than an uncontrollable ibdata1 file.

Solution 2: Recovering Reclaimed Diskspace

This solution is best done BEFORE you run out of diskspace. The most important note is that you must have enough space on the drive to backup your databases. The reason for this is because we will be exporting ALL your data, dropping your databases, changing the InnoDB storage settings, and then recreating and importing your data.

This is usually a pretty safe process if done during a maintenance window and you are comfortable with databases and your platform.

$ mysqldump -u<user> -p<pass> --routines --triggers --quick --all-databases > mysql_backup.sql
$ mysql -u<user> ...
MySQL Vesion ... yadda yadda
> drop database <dbname>; /*repeat for all tables */
> exit
$ sudo /etc/init.d/mysql stop

At this point, I would encourage you to make the changes provided in Solution 1. Once complete, continue with the below:

$ sudo rm -rf <path_to_mysql>/data/ib{data1,_log*}
$ sudo /etc/init.d/mysql start
$ mysql -u<user> < mysql_backup.sql

If you are not confident with using rm -rf within your MySQL directory, you can delete the ib_logx files and just rename your ibdatax files to ibdatax.old. You can delete the backup once your data is reimported.

Summary

We use this method fairly regularly on some polling systems within our network. We reach around 3 Million tables a month, and the ibdata1 file has reached upwards of 290GB. Let us know if this came in handy for you!


Limit Results From SHOW TABLES

Someone asked, We answered

Not too long ago, a user asked on StackOverflow: How to limit SHOW TABLES query. Essentially, the user wanted to know how to limit the number of results from SHOW TABLES. Being nose deep in MySQL just about everyday, we thought we would take a stab at answering.

Unfortunately, SHOW TABLES does support the LIMIT constraint. Although this would be the simplest, and most logical way to do it, you can’t due to limitations with MySQL. So, we thought we would use the next best thing: INFORMATION_SCHEMA.

Information Schema

A lot of coders always see the INFORMATION_SCHEMA database, but not many actually use it. Some of the information you can get out of it includes, column names, triggers, views, statistics, engines and more.

In reality, most people probably won’t have a need for it. Keep in mind that there is always an exception, especially in very large projects.

Limiting Table Searches

To achieve this though, you need to use the following:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '<DB_TO_SEARCH>' 
AND TABLE_NAME LIKE "table_here%" 
LIMIT 0,5;

You need to set two strings:

<DB_TO_SEARCH>  the database you want to search and 
<TABLE_PREFIX>  which is the you want to match, or omit it

libncurses Shared Object Not Found

Package Dependencies

For a while, we got approached daily by some contractors who were Debian-bred. Unfortunately, for them, we had a customer who needed to deploy Fedora/RedHat to support a specific software suite. They were not used to the yum package manager as their taste leaned more towards aptitude.

With yum, different architectures have dedicated packages. For example, an Intel 32-bit platform would use i386 and native 64-bit AMD would use x86_64. If you need support for 32-bit packages on a 64-bit platform, all you need to do is specify it!

This issue is not specific to libncurses but being a common package, it is likely frequently appear.

The Error

error while loading shared  libraries: libncurses.so.5: 
    cannot open shared object file: No such file or  directory

The Fix

You can fix this issue by installing the 32-bit libraries for ncurses. You can do so by running the following command:

yum install ncurses-devel.i686

Summary

We hope this helps shed some light on possible No such file or directory errors you may encounter.



Fatal Error: Could not find ./bin/my_print_defaults

Background

I was installing new servers in my lab for an application that’s about to be deployed. I was taking the normal steps which include MySQL, PHP and Apache which I have documented in the Web Server Basics article. While installing Percona, I ran across the following error:

root@ubuntu:/home/splug/Percona-Server-5.5.17-rel22.1# /usr/local/mysql-5.5/bin/mysql_install_db

FATAL ERROR: Could not find ./bin/my_print_defaults

If you compiled from source, you need to run 'make install' to
copy the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.

The Fix

If you use my article, the MySQL page documents how to circumvent the issue. To get the location of your ‘my_print_defaults’ binary, use the ‘which’ command:

# which my_print_defaults
/usr/local/mysql-5.5/bin/my_print_defaults

Then you can re-run the ‘mysql_install_db’ script passing the ‘–basedir’ option, leaving off the ‘/bin/my_print_defaults’:

root@ubuntu:/home/splug/Percona-Server-5.5.17-rel22.1# ./scripts/mysql_install_db --basedir=/usr/local/mysql-5.5 --user=mysql
Installing MySQL system tables...
120302 10:03:08 [Note] Flashcache bypass: disabled
120302 10:03:08 [Note] Flashcache setup error is : ioctl failed

OK
Filling help tables...
120302 10:03:09 [Note] Flashcache bypass: disabled
120302 10:03:09 [Note] Flashcache setup error is : ioctl failed

OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql-5.5/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql-5.5/bin/mysqladmin -u root -h ubuntu password 'new-password'

Alternatively you can run:
/usr/local/mysql-5.5/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql-5.5 ; /usr/local/mysql-5.5/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql-5.5/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql-5.5/scripts/mysqlbug script!

Percona recommends that all production deployments be protected with a support
contract (http://www.percona.com/mysql-suppport/) to ensure the highest uptime,
be eligible for hot fixes, and boost your team's productivity.

Hope this relieves someones headache.


SQLSTATE[HY000]: General error

General Error

Wanted to add a quick note on the following error. You will get this when trying to do a fetch on a non-fetchable query.

Example, if you try to do an INSERT, UPDATE or DELETE, and run a fetch() or fetchAll() on the result, it will throw a General Error.

The Error

PDOException Object
(
    [message:protected] => SQLSTATE[HY000]: General error
    [string:Exception:private] => 
    
=> HY000 [file:protected] => /usr/local/glaze/Zepnik/Core/Database.php [line:protected] => 242 [trace:Exception:private] => Array (...

A Fix

Let it be know that this is one of many fixes:

<?php
try{
    $res = $pdo->fetch();
}
catch(PDOException $e){
     // $e->getMessage();
}

Enjoy.


Installing MySQL Engine(s) Post-Install

Background – My Need For Federated

I was working on a proof-of-concept the other day and ran into a situation. I separated my databases based off it’s perceived functionality. Example, Billing vs Content. The front-end application will only talk to the Content database, while the Billing database is accessed via the backend. This is a perfect concept except for the fact that my Users table was linked to the Billing database but not the Content database. That is an easy fix.

MySQL supports an engine called Federated. The purpose of Federated is to allow access to a remote database/table without the need to synchronization or replication. All you have to worry about is making sure your table descriptions are the same.

Find your plugin shared libraries. I know that federated was needed and searched on that:

splug@vanilla:~/mysql-5.5.15$ sudo find / -name "*federated.so"
/usr/local/mysql-5.5.15/lib/plugin/ha_federated.so
/home/splug/mysql-5.5.15/storage/federated/ha_federated.so

Now it’s time to install it:

//Example:
// mysql> install plugin <name> soname '<filename>.so';
mysql> install plugin federated soname 'ha_federated.so';

If you see the engine listed, but support is set to ‘NO’, you are missing the configuration options in my.cnf.

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                             | NULL         | NULL | NULL       |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
1 rows in set (0.00 sec)

Open my.cnf in your favorite editor and add ‘federated’ to the ‘[mysqld]‘ portion.

[mysqld]
federated
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                             | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
1 rows in set (0.00 sec)

Congrats!


Web Server Basics – Part 1: MySQL

Background – MySQL

A few months ago, I stumbled onto a product called Percona. Percona is based off MySQL’s open source code, so everything you may already be familiar with is still the same. Essentially, it is a matured, nurtured version of MySQL on a sugar rush. The idea behind Percona is to pretty much switch your database engine to InnoDB and tweak some settings.

Note: I skipped a tutorial on installing Linux as I am very distribution agnostic. I love Gentoo, Ubuntu, Mandriva, Fedora and NetBSD; All walks of life. When I talk about installing packages/dependencies, they may not match the naming convention or package name on your system, especially if I refer to them as aptitude packages and you’re using yum. I’ve done my best from memory to accommodate both environments. Any issues, please post a comment below.

Step 1 – Dependencies

Before we start, we need to make sure that our system has all the required packages installed. The following commands will install what you need:

On Deb:

sudo apt-get install libncurses5-dev automake libtool cmake g++ build-essential libncurses5-dev bison

On RPM:

sudo yum install ncurses-devel bison-devel automake g++ libtool

Step 2 – Get The Source

First things first, get the source. You can do so by following this link: Source Code or by following the steps below on your server.

wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.17-22.1/source/Percona-Server-5.5.17-rel22.1.tar.gz
tar xvzf Percona-Server-5.5.17-rel22.1.tar.gz
cd Percona-Server-5.5.17-rel22.1

Step 3 – Compile

If you followed the steps above, you have downloaded, extracted and moved into your new directory. Next we need to execute the build file and configure.

sh BUILD/autorun.sh
./configure --prefix=/usr/local/mysql-5.5 --without-plugin-innobase --with-plugin-innodb_plugin
make
sudo make install

Step 4 – Postmortem

After you have completed the steps above, it’s time to make some changes to your system to support this new application.

export PATH=$PATH:/usr/local/mysql-5.5/bin
sudo groupadd mysql
sudo useradd -r -g mysql mysql
chmod +x script/mysql_install_db
sudo scripts/mysql_install_db --basedir=/usr/local/mysql-5.5 --user=mysql --ldata=/usr/local/mysql-5.5/data
sudo cp support-files/my-innodb-heavy-4G.cnf /etc/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mysql
sudo chmod +x /etc/init.d/mysql
sudo chown -R root:root /usr/local/mysql-5.5
sudo chown -R mysql:mysql /usr/local/mysql-5.5/data

The above commands complete the following tasks:

  1. Create a group called ‘mysql’
  2. Create a user ‘mysql’, assign to group ‘mysql’
  3. Execute mysql db install, install database schema
  4. Copy configuration file to /etc/my.cnf
  5. Copy server daemon to /etc/init.d/mysql
  6. Change directory
  7. Change owner to root
  8. Change owner of directory ‘data’ to root
  9. Add mysql binaries to your PATH

If you want MySQL to start automatically on system boot, you can run the following command:

sudo update-rc.d mysql defaults

To start the server, run:

sudo /etc/init.d/mysql start

Step 5 – Verify It’s Running

Run netstat to check to make sure your system is listening on port 3306/mysql:

netstat -a | grep mysql

The output should look similar to the following:

sixeightzero@v3x.highonphp.com:/usr/local/apache-2.2/bin$ netstat -a | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     54260    /tmp/mysql.sock

The first line shows MySQL is listening on port 3306 for incoming connections, and the second is the Unix socket, used for local connections to the database.

Security Note: To prevent MySQL from listening on port 3306, add ‘skip-networking’ to your /etc/my.cnf file and restart.

Time for Apache

Continue to Part 2: Apache

Gotacha’s

If you run into the following error:

FATAL ERROR: Could not find ./bin/my_print_defaults

Check out this article: Fatal Error: my_print_defaults

Enjoy.


Pages:12