Archive for the ‘MySQL’ Category.

How to install MySQL on FreeBSD

Note: Tested on FreeBSD 9

Step 1 – Install FreeBSD

  1. First install FreeBSD. Instructions for installing FreeBSD is contained in this article.
    How I install FreeBSD 9?
    (Legacy) How I install FreeBSD?
  2. Second update FreeBSD and install the ports tree. Instructions for this are in this article.
    What are the first commands I run after installing FreeBSD?

Step 2 – Installing MySQL

Install MySQL from Ports

  1. Change to the directory of the mysql55-server port.
    # cd /usr/ports/databases/mysql55-server
  2. Now install mysql55-server with ‘make install’.
    # make install

    MySQL 5.5 Server (and MySQL 5.5 client) will download, compile, and install automagically for you.

    Note: You may be wondering about the WITH_CHARSET option that used to exist. This is not necessary during compile and install and we will set the character set in a later step. Don’t start the MySQL service until we make these changes.

Installing MySQL from Packages

  1. Install easily as a binary package with this simple command.
    pkg_add -r mysql55-server

Step 3 – Configure MySQL

Configuration of MySQL is done in the my.cnf file.

Example 1 – Configuring mysql to use UTF8

For this example, we will change our server to use UTF8.

  1. Change to the /usr/local/etc/ directory. This is the default location for the my.cnf file.
    cd /usr/local/etc/
  2. Add the following to the my.cnf file.
    # # # > /usr/local/etc/my.cnf echo '[mysqld]' >> /usr/local/etc/my.cnf echo character-set-server=utf8 >> /usr/local/etc/my.cnf echo collation-server=utf8_general_ci

Note: FreeBSD has multiple example my.cnf files here: /usr/local/share/

  • my-huge.cnf
  • my-innodb-heavy-4G.cnf
  • my-large.cnf
  • my-medium.cnf
  • my-small.cnf

Step 4 – Configure MySQL to start on boot

  1. Add the following lines to the /etc/rc.conf file.
    #
    #
    echo # MySQL 5.5 Server >> /etc/rc.conf
    echo 'mysql_enable="YES"' >> /etc/rc.conf
  2. Now start your server.
    # /usr/local/etc/rc.d/mysql-server start

Step 5 – Secure your MySQL installation

MySQL documentation covers this and I’ll not repeat it here. Instead, go here:
2.2 Securing the Initial MySQL Accounts

Integration with Apache and PHP

If you want to integrate Apache and PHP see these articles.

How to configure Subversion to use Cyrus-SASL2 to authenticate to a MySQL database?

Ok, so I want to have Subversion authentication work from a MySQL database. I am going to try to use Cyrus SASL for this.

I already have instructions for installing the necessary parts:

  1. Install FreeBSD.
    How do I install FreeBSD?

  2. Update FreeBSD and download the ports tree.
    What are the first commands I run after installing FreeBSD

  3. Then install Subversion, however, one difference you need to make to the install instructions for subversion. You need to install with SASL2 support. When you run make install it is an option.
  4. How to install subversion 1.6.6 on FreeBSD 7.2

Ok, now that you have everything is installed, you are were I am and ready to try to get this configured.

Configuring Subversion to use SASL to Authenticate to a MySQL database

  1. Create a simple MySQL database. The following is a simple database creation script that creates a database with one table and two rows.
    CREATE DATABASE UserDB;
    USE UserDB;
    CREATE TABLE `users` ('username' varchar(255), 'password' varchar(255) )
    INSERT INTO users VALUES ('user1','pw1');
    INSERT INTO users VALUES ('user2@MyReal.com','pw2');
    INSERT INTO users VALUES ('user3@myemailaddress.com','pw3');
    

    Note: I use these accounts to show what works and what does not work because the idea of “realms” is confusing.

    You may be asking why I don’t have three rows, one for each item: User, Password, Realm.

    Well, if you really are creating a new database to handle SVN Users then that is how you should do it and here is it is.

    CREATE DATABASE UserDB;
    USE UserDB;
    CREATE TABLE `users` ('username' varchar(255), 'password' varchar(255) , 'realm' varchar(255))
    INSERT INTO users VALUES ('user1','pw1','realm');
    INSERT INTO users VALUES ('user2@MyReal.com','pw2','realm');
    INSERT INTO users VALUES ('user3@myemailaddress.com','pw3','realm');
    

    However, because I am assuming that you want to authenticate to users that are in an already existing database, realm won’t really exist. However, you may have usernames that are in email format, or not in email format an that makes a difference because Subversion splits the username at an @ symbol and the username is only what is before the @ symbol. See the troubleshooting realms section below.

  2. Edit the following file:
    /home/svn/repos/MyApp/conf/svnserve.conf

    # ee /home/svn/repos/MyApp/conf/svnserve.conf

    The following are the lines that should NOT be commented out.

    [general]
    anon-access = none
    auth-access = write
    realm = MyDomain.com

    [sasl]
    use-sasl = true

  3. Create and edit the following file:
    /usr/local/lib/sas2/svn.conf

    # ee /usr/local/lib/sas2/svn.conf

    The following are the lines that should NOT be commented out.

    [general]
    pwcheck_method: auxprop
    mech_list: plain
    auxprop_plugin: sql
    sql_hostnames: localhost
    sql_engine: mysql
    sql_user: root
    sql_passwd: pw
    sql_database: UserDB
    sql_select: SELECT password FROM users WHERE username='%u'

    Note: For debugging add log_level: 7 to this file and then watch the /var/log/debug file.

You should now be able to connect with a client such as TortoiseSVN and connect

Troubleshooting Realms

I found some issues with realms that were really confusing.

Change your /usr/local/lib/sas2/svn.conf file to look like this:

[general]
log_level: 7
pwcheck_method: auxprop
mech_list: plain
auxprop_plugin: sql
sql_hostnames: localhost
sql_engine: mysql
sql_user: root
sql_passwd: pw
sql_database: UserDB
sql_select: SELECT password FROM users WHERE username='%u' or username='%u@%r'

I added two changes:

  1. Turned on logging.
  2. Changes the sql statement to look for username='username' or username='username@realm'

I had to do this because if the user was using an email address, such as john@domain.tld, then it actually makes %u only equal John and seems to drop the @domain.tld and replace it with the name of the realm. However, if your username is John@domain.tld and your realm is domain.tld (so realm and domain are the same), then the code above works. If you domain and realm are not the same, I haven't been able to get this to work.

Go ahead and run this command:

# tail -f -n 30 /var/log/debug

And then try to authenticate using a client, such as TortoiseSVN. Test all three users. You will see the SQL Queries that are run. It should work to authenticate as user1 or User2, but it cannot authenticate user3 because it just doesn't work due to the way it handles realms. If you have users that don't have email address in your realm, then you need them to have username that are not email addresses.

Final Question
If the password is stored as an md5, sha1, sha256 hash in the database, how do I make this work?


Copyright ® Rhyous.com - Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to install Bugzilla on a FreeBSD 7.2 with Apache + SSL and MySQL?

How to install Bugzilla 3.4.2 on FreeBSD 7.2.

The basic overivew.

  1. Install FreeBSD.
    How do I install FreeBSD?

  2. Update FreeBSD and download the ports tree.
    What are the first commands I run after installing FreeBSD

  3. Then install Apache + SSL.
    Installing an Apache + SSL on FreeBSD using the ports tree

  4. Then install MySQL.
    How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

  5. Configure MySQL to be Unicode.
    How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

  6. Then install Bugzilla

I have previous documents about installing each of the steps above installing Bugzilla. This document will over cover bugzilla.

Installing Bugzilla From Ports

You can install easily from Ports. Make sure your ports tree is up to date:

$

su

Password:

ServerName#
ServerName#
ServerName#
portsnap fetch
portsnap extract
portsnap udpate

Then just do this to install Bugzilla 3.4.2 on FreeBSD 7.2.

ServerName#
ServerName#
cd /usr/ports/devel/bugzilla
make BUGZILLADIR=/usr/local/www/apache22/data/bugzilla install

Note: Make sure you choose the correct install directory for the BUGZILLADIR parameter. By default Apache 2.2 is only serving up files in /usr/local/www/apache22/data/ so by install bugzilla there, you will be able to access bugzilla with this url: http://www.YourWebSite.com/bugzilla

You will be asked to select your compile options throughout. If you don’t want to be promtped, and you want to accept the defaults, use this command.

ServerName# make BATCH=yes install

Now that you have Bugzilla 3.4.2 on your FreeBSD 7.2 server, you are not finished. We now need to connect to connect it to a database, which I am assuming is MySQL but could just as easily be Postgresql.

Resetting the file ownership recursively on the bugzilla folder

Make sure that the bugzilla folder and all subfolders are owned by www:www.

ServerName# chown -R www:www /usr/local/www/apache22/data/bugzilla

Creating a MySQL Database

  1. Log into mysql. I use the command line and type in mysql -p, enter my password when prompted.
  2. Create a database for Bugzilla.
  3. Create a user that can access Bugzilla.
  4. I use the followiing SQL commands for these steps:

    CREATE DATABASE BugDB
    
    GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
               CREATE TEMPORARY TABLES, DROP, REFERENCES
               ON BugDB.* TO BugDBUser@localhost
               IDENTIFIED BY 'P@sswd!';
    FLUSH PRIVILEGES;
    

Run Install Check Script

  1. In a command prompt go to /usr/local/www/data/bugzilla
    ServerName# cd /usr/local/www/data/bugzilla
  2. Run the setup checking script.
    ServerName# ./checksetup.pl
  3. Now you are ready to open and edit the localconfig file.
    ServerName# ee localconfig
  4. Change the following values:

    $webservergroup = ‘www’
    $db_name = ‘BugDB’
    $db_user = ‘BugDBUser’
    $db_pass = ‘P@sswd!’

    Then close and save the localconfig file.

  5. Run ./checksetup.pl again.
  6. Note: If you have installled everything including MySQL using the defaults, you will see this warning:

    WARNING: You need to set the max_allowed_packet parameter in your MySQL configuration to at least 3276750. Currently it is set to 1048576. You can set this parameter in the [mysqld] section of your MySQL configuration file.

    Resolve this using the MySQL configuration file called my.cnf. I discussed the my.cnf earlier in this article, so you should already be familiar with it.
    How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

    Find the max_allowed_packet settings and change it to 4M.

    max_allowed_packet = 4M

    Restart MySQL.

    ServerName# /usr/local/etc/rc.d/mysql-server restart
  7. Run checksetup.pl again.

    I got this error:

    Creating ./lib/.htaccess…
    No such file or directory at Bugzilla/Install/Filesystem.pm line 445, line 275.

    I had to manually create the /usr/local/www/apache22/data/bugzilla/lib directory then this error disappeared when I ran checksetup.pl again.

  8. Now create an Apache configuration file for bugzilla and put it in /usr/local/etc/apache22/Includes. I name it bugzilla.conf.

    bugzilla.conf

    <Directory "/usr/local/www/apache22/data/bugzilla">
      Options +ExecCGI
      AllowOverride Limit
      DirectoryIndex index.cgi
      AddHandler cgi-script .cgi
    </Directory>
    

    Restart Apache

    ServerName# /usr/local/etc/rc.d/apache22 restart
  9. You should now be able to connect to your server: http://YourServer/bugzilla


    Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to start, stop, restart MySQL on FreeBSD or Red Hat?

FreeBSD

Starting MySQL


/usr/local/etc/rc.d/mysql-server start

Stopping MySQL


/usr/local/etc/rc.d/mysql-server stop

Restarting MySQL


/usr/local/etc/rc.d/mysql-server restart

Red Hat

Starting MySQL


/etc/init.d/mysql start

or


/sbin/service mysql start

Stopping MySQL


/etc/init.d/mysql stop

or


/sbin/service mysql stop

Restarting MySQL


/etc/init.d/mysql restart

or


/sbin/service mysql restart

How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

FreeBSD
There are two easy ways on FreeBSD:

From Ports

You can install easily from Ports. Make sure your ports tree is up to date:

$ su

Password:

ServerName#

ServerName#

ServerName#

portsnap fetch

portsnap extract

portsnap udpate

Then just do this to install MySQL on FreeBSD.

ServerName#

ServerName#

cd /usr/ports/databases/mysql51-server

make install

Or if you want to use utf8 by default, run this command:

ServerName# make WITH_CHARSET=utf8 install

MySQL 5.1 Server (and MySQL 5.1 client) will download, compile, and install automagically for you.

From Packages

You can also install easily as a binary package with this simple command.

ServerName# pkg_add -r mysql51-server

Make sure to secure you MySQL installation.
http://dev.mysql.com/doc/mysql-security-excerpt/5.1/en/default-privileges.html

Red Hat
Using RPM

You have to go to the MySQL site and download the MySQL 5.1 server RPM and install it.
http://dev.mysql.com/downloads/

It does not automatically install the MySQL client, you have to download that as a separate RPM and install it.

Using yum

Since I didn’t have a MySQL license, yum didn’t work, so I don’t know if it can be installed using yum.

Make sure to secure you MySQL installation.
http://dev.mysql.com/doc/mysql-security-excerpt/5.1/en/default-privileges.html


Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

How to create a UTF-8 Unicode Database on MySQL?

I am not going to cover installing, I have done that here:
How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

So when you open MySQL using the command line MySQL client, you can see what Character Set your server is configured to use with this command:

show variables like 'character_set_server';

Often the default is Latin-1. I wish UTF-8 was the default but it is not.

You can see the language your database is created with by using this command:

show create database dbname

Again, usually the default is Latin-1 and again, I wish the default were UTF-8 but it is not.

So how do I make my MySQL database UTF-8?
How do I make UTF-8 the default?

I am going to find out…

Ok, so I have MySQL installed on two different platforms:
FreeBSD 7.2 x64.
Red Hat 5.4 x64.

My question are these:
What level do you set the Unicode setting at? Install instance, database, or column type.

MySQL – Looks like it can be configured globally in the my.cnf or it can be database specific.

To configure globally

Add the following to the my.cnf file:

[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

Note: There are other options for collation besides utf8_general_ci such as utf8_unicode_ci. See this article:
http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

Do I have to create the database in a special way?

Not if you configure the setting globally. However, if you don’t configure unicode support globally then yes you have to create your database in a specific way.

I found this post that is for an applications that uses a MySQL Unicode database. I don’t care about the application, just the MySQL data.
http://dev.mysql.com/doc/refman/5.1/en/create-database.html
http://dev.mysql.com/doc/refman/5.1/en/charset-applications.html

So the syntax will be:

CREATE DATABASE mydb   DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Do I have to compile differently to get unicode support?

I didn’t have to recompile on either FreeBSD or Red Hat.

Is there differences for each platform?

Slight differences.

FreeBSD

FreeBSD has the MySQL client as a dependency so it gets installed with the server with out any extra work.

The Database folder is /var/db/mysql.

For the global configuration there is not a my.cnf file created by default.

FreeBSD has example my.cnf files located here:

/usr/local/share/mysql
/usr/local/share/mysql/my-huge.cnf
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
/usr/local/share/mysql/my-large.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-small.cnf

You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

#
#
#
?
?
?
?
?
?
?
?
#
cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf
chown mysql:mysql /var/db/mysql/my.cnf
cat << EOF >> /var/db/mysql/my.cnf
[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
EOF

Red Hat

Red Hat does not have the MySQL client installed with the server, you have to download a separate RPM and install it. But it is really easy. Download both RPMs and install them.

The Database folder is /var/lib/mysql.

For the global configuration there is not a my.cnf file created by default.

Red Hat has example my.cnf files located here:

/usr/share/mysql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Same as FreeBSD, there isn’t one used by default and you have to copy one and use it.
You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

#
#
#
?
?
?
?
?
?
?
?
#
cp /usr/share/mysql/my-medium.cnf /var/lib/mysql/my.cnf
chown mysql:mysql /var/lib/mysql/my.cnf
cat << EOF >> /var/lib/mysql/my.cnf
[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
EOF

Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

What is the Microsoft SQL equivalent to MySQL's "Limit" feature in a SQL query?

Here is a MySQL Query

SELECT * FROM Table LIMIT 10

Here is a Microsoft SQL Query to perform the same

SELECT TOP10 * FROM Table

What is the difference between a DataSet and a DataTable?

What is the difference between a DataSet and a DataTable?
Here is a link to the MSDN class information for both:
DataSet Class
DataTable Class

So a DataTable is just a table.

However, a DataSet is a collection of tables that can have their data linked together with a DataRelation Class.

So when accessing a database, which should you use?
Well, if you only need a single table, then just use a DataTable.
However, if you need multiple tables and those tables may have some type of relationship, use a DataSet.

How do I access a MySQL database with C#?

This was a little bit easier for me because I had just figured all this out on Microsoft SQL and (lo and behold), MySQL had created the exact same function structure for MySQL.

So I read through this document first:
Beginning MYSQL 5 with Visual Studio.NET 2005.pdf

It was very informative and showed this code to get into a MySQL database:

string connectionString = "server=ServerName; user id=UserName; password=P@sswd!; database=MyDatabase";
string query = "Select * from users";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
    connection.Open();
    MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet, "users");
}

I had been accessing tables in a Microsoft SQL database using a different set of functions, so I tested to see if the method I had been using for Microsoft SQL would work for MySQL, since the object and function names are almost identical.

The following code also accessed the database and I like it better because a DataTable seems more of an obvious choice to return data from table.

string connectionString = "server=ServerName; user id=UserName; password=P@sswd!; database=MyDatabase";
string query = "Select * from users";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
    connection.Open();
    MySqlCommand command = new MySqlCommand(query, connection);
    MySqlDataReader reader = command.ExecuteReader();;
    DataTable table = new DataTable();
    table.Load(reader);
}

So I left wondering how these two methods are different…I guess I need to answer the following:

  1. Which of the above two SQL database access methods should be considered best practice and why?
  2. Is one more efficient than the other?
  3. What is the difference between a DataSet and a DataTable?
    DataSet Class
    DataTable Class