My (SQL) WorkLog: July 2009

Thursday, July 30, 2009

Step-by-Step guide for Installing MySQL on RHEL5

Installation of MySQL Server on Linux (RedHat Enterprise Linux 5)

The recommended way to install MySQL on RPM-based Linux distributions is by using the RPM packages. The RPMs that MySQL/Sun provide to the community should work on all versions of Linux that support RPM packages and use glibc 2.3. You can download it from http://mirrors.sunsite.dk/mysql/downloads/mysql/5.1.html

Checking if MySQL is already installed & would be uninstalling it to install latest MySQL version

[root@localhost mysql]# rpm -qa | grep -i '^mysql-'
MySQL-python-1.2.1-1
mysql-5.0.22-2.1.0.1
mysql-server-5.0.22-2.1.0.1
mysql-connector-odbc-3.51.12-2.2

Un-installing older version of MySQL

[root@localhost mysql]# rpm --nodeps -ev MySQL-python-1.2.1-1
[root@localhost mysql]# rpm --nodeps -ev mysql-5.0.22-2.1.0.1
[root@localhost mysql]# rpm --nodeps -ev mysql-server-5.0.22-2.1.0.1
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
[root@localhost mysql]# rpm --nodeps -ev mysql-connector-odbc-3.51.12-2.2
[root@localhost mysql]#


First, using a web browser or wget utility, you should download the server RPM from the location: http://www.mysql.com/Downloads/MySQL-*.rpm
Once you download the packages, install it as shown below.


[root@localhost mysql]# ls -la
total 39572
drwxr-xr-x 2 root root 4096 Jul 26 17:26 .
drwxr-xr-x 3 root root 4096 Jul 26 16:40 ..
-rw-r--r-- 1 root root 6664128 Jun 11 14:07 MySQL-client-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 10616677 Jun 11 14:17 MySQL-devel-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 19613508 Jun 11 14:26 MySQL-server-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 3532055 Jun 11 14:31 MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm

Here is brief details of packages that we are going to install

MySQL-server-community-5.0.83-0.rhel5.i386.rpm: The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.
MySQL-client-community-5.0.83-0.rhel5.i386.rpm: The standard MySQL client programs. You probably always want to install this package.
MySQL-devel-community-5.0.83-0.rhel5.i386.rpm: The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules.
MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm: This package includes the shared libraries for MySQL 3.23, 4.0, and so on, up to the current release. It contains single-threaded and thread-safe libraries. Install this package instead of
MySQL-shared if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.


To perform a standard minimal installation, you can only install the server and client RPMs and for complete installation you have to install all the packages.



[root@localhost mysql]# rpm -ivh MySQL-*.rpm
Preparing... ########################################### [100%]
1:MySQL-shared-compat ########################################### [ 25%]
2:MySQL-client-community ########################################### [ 50%]
3:MySQL-devel-community ########################################### [ 75%]
4:MySQL-server-community ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/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.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Starting MySQL..[ OK ]
Giving mysqld 2 seconds to start
[root@localhost mysql]#


The server RPM places data under the /var/lib/mysql directory. The RPM also creates a login account for a user named mysql (if one does not exist) to use for running the MySQL server, and creates the appropriate entries in /etc/init.d/ to start the server automatically at boot time.


If you want to install the MySQL RPM on older Linux distributions that do not support initialization scripts in /etc/init.d (directly or via a symlink), you should create a symbolic link that points to the location where your initialization scripts actually are installed. For example, if that location is /etc/rc.d/init.d, use these commands before installing the RPM to create /etc/init.d as a symbolic link that points there:
shell> cd /etc
shell> ln -s rc.d/init.d .

However, all current major Linux distributions should support the new directory layout that uses /etc/init.d, because it is required for LSB (Linux Standard Base) compliance.
If the RPM files that you install include MySQL-server, the mysqld server should be up and running after installation. You should be able to start using MySQL.



Securing the Server


[root@localhost mysql]# /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...



All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


[root@localhost mysql]#




Test mysql connectivity

Type the following command to connect to MySQL server:


[root@localhost mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.83-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Tuesday, July 28, 2009

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Platform: Linux/Unix

If you come across below errors when trying to connect MySQL then there might be couple of reasons behind it.




-bash-3.2$ mysql --user=root --password
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

One of the reason could be that MySQL server isn't running. So how do you check whether or not MySQL is running? try running below shell command and see what it reports


-bash-3.2$ ps -e|grep mysqld
30878 ? 00:00:00 mysqld_safe
30939 ? 00:58:06 mysqld
-bash-3.2$



If you see at least 2 process are running/listed from the above command then it is confirmed that MySQL is running.

What if you don't see any process(as shown below)? then the MySQL services is not at all running then try to start it and watch for errors(you can check error log for same) and resolve them before making an attempt to start.
-bash-3.2$ ps -e|grep mysqld
-bash-3.2$

Starting MySQL(easiest way :-))

/etc/init.d/mysql start
If there are no errors, re-enter the process status(ps) command once again & if the service is now running, then try connecting to MySQL and If still you are receiving the error then another reason(wasn't first one) could be if the location of the Unix socket has been changed or is different from the default location.

It might be that the location of the socket file has been changed so first one has to check the MyQL config file (/etc/my.cnf) and locate the the value for the socket parameter(you may see two socket details - for client and mysqld groups).
-bash-3.2$ more /etc/my.cnf |grep socket
socket=/var/lib/mysql/mysql.sock
-bash-3.2$

It could be that the value for the --socket option is set at the command-line when MySQL is started is different than what the client mysql is expecting in that case you have to either change the socket file path in the config file or you will have to supply the socket parameter with its path for the the client
-bash-3.2$ mysql --user=root --password --socket=/var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 421830
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Tuesday, July 21, 2009

Resetting the Root Password on Unix/Linix Systems

Log on to your system as the Unix mysql user that the mysqld server runs as.

Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:

shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

Create a text file and place the following statements in it. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;


The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users.

Start the MySQL server with the special --init-file option:

shell> mysqld_safe --init-file=/home/me/mysql-init &

The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

After the server has started successfully, delete /home/me/mysql-init.

Reference: MySQL Manual

MySQL - Which engine to use?

it is good to have an idea of the advantages and disadvantages of the various storage engines. The following table provides an overview of some storage engines provided with MySQL





Reference: MySQL Manual

Thursday, July 09, 2009

Choosing Which Version of MySQL to Install

The first decision to make is whether someone want to use a production (stable) release or a development release. In the MySQL development process, multiple release series co-exist, each at a different stage of maturity:

At the time of writing this:


  • MySQL 5.4 and 6.0 are the current development release series.


  • MySQL 5.1 is the current General Availability (Production) release series. New releases are issued for bugfixes only; no new features are being added that could affect stability.


  • MySQL 5.0 is the previous stable (production-quality) release series.


  • MySQL 4.1, 4.0, and 3.23 are old stable (production-quality) release series. MySQL 4.1 is now at the end of the product lifecycle. Active development and support for these versions has ended.

The MySQL naming scheme uses release names that consist of three numbers and a suffix; for example, mysql-5.0.12-beta. The numbers within the release name are interpreted as follows:

  • The first number (5) is the major version and describes the file format. All MySQL 5 releases have the same file format.


  • The second number (0) is the release level. Taken together, the major version and release level constitute the release series number.


  • The third number (12) is the version number within the release series. This is incremented for each new release. Usually you want the latest version for the series you have chosen.


Release names also include a suffix to indicates the stability level of the release. Releases within a series progress through a set of suffixes to indicate how the stability level improves. The possible suffixes are:

  • alpha indicates that the release is for preview purposes only. Known bugs should be documented in the News section . Most alpha releases implement new commands and extensions. Active development that may involve major code changes can occur in an alpha release. However, we do conduct testing before issuing a release.


  • beta indicates that the release is appropriate for use with new development. Within beta releases, the features and compatibility should remain consistent. However, beta releases may contain numerous and major unaddressed bugs.
    All APIs, externally visible structures, and columns for SQL statements will not change during future beta, release candidate, or production releases.


  • rc indicates a Release Candidate. Release candidates are believed to be stable, having passed all of MySQL's internal testing, and with all known fatal runtime bugs fixed. However, the release has not been in widespread use long enough to know for sure that all bugs have been identified. Only minor fixes are added. (A release candidate is what formerly was known as a gamma release.)


  • If there is no suffix, it indicates that the release is a General Availability (GA) or Production release. GA releases are stable, having successfully passed through all earlier release stages and are believed to be reliable, free of serious bugs, and suitable for use in production systems. Only critical bugfixes are applied to the release.

MySQL uses a naming scheme that is slightly different from most other products. In general, it is usually safe to use any version that has been out for a couple of weeks without being replaced by a new version within the same release series.