Installing and Configuring MySQL as StoreGrid Backend Database on Linux

• Overview
• Install MySQL Server
• Install MySQL Client
• Install UnixODBC
• Install MySQL Connector
• Configure MySQL Connector
• Create StoreGrid database
• Create System DSN
• Test MySQL Server Connectivity
• Troubleshooting tips

Overview

StoreGrid now supports MySQL as a backend database to store all the clients' backup metadata information. Unlike StoreGrid using SQLite embedded database as its backend database, the MySQL database Server is external to the StoreGrid backup server application. Using a client-server database as the backend database rather than an embedded database would provide better scalability and performance. It also enables third party applications to directly query StoreGrid's metadata for integration with those applications. To get StoreGrid to work with MySQL as a backend database, you need to install MySQL Server either in the same machine where StoreGrid backup server is installed or in another dedicated machine where you wish to store the StoreGrid's metadata information

Install MySQL Server

Install MySQL Server 5.1.30 in the machine where you want to store the StoreGrid Backup Server's metadata.

1. Download the MySQL server (mysql-5.1.30.tar.gz) file from the URL http://downloads.mysql.com/archives.php?p=mysql-5.1&v=5.1.34
2. Extract the tar file by executing 'tar -xzvf mysql-5.1.30.tar.gz'
3. Go the extracted folder by executing 'cd mysql-5.1.30'
4. Execute './configure --enable-thread-safe-client --enable-gui=no --with-plugins=max --with-gnu-ld' (Assuming you have all the appropriate stuff like gcc, g++, make, etc.,)
5. Execute 'make'
6. Execute 'make install' as root user. By default, this will install MySQL in /usr/local.

NOTE: StoreGrid's metadata stored in the MySQL databases could use up certain amount of space. To give a rough estimation of the usage, if the backup server stores 1 million backed up files, then the MySQL DB could be of size 1.5 GB. For this disk size estimation, each version file is also considered as a separate file. Therefore, for example, if a client is backing up 1 million files and for each file there are 3 versions stored in the backup server then there will be 3 million backed up files for that client machine. For these 3 million backed up files, the MySQL DB size could be about 4.5 GB. Please make sure that you have enough space in this location for all future backups from all the client machines.

7. Copy the MySQL configuration file by executing 'cp support-files/my-medium.cnf /etc/my.cnf' as root user.
8. Open the '/etc/my.cnf' file and edit the settings as follows :

   [mysqld]
   socket=/tmp/mysql.sock
   user=root
   default-storage_engine=innodb
   max_connections=800
   slow_query_log = 1
   innodb_log_file_size = 512M
   innodb_flush_method=O_DIRECT
   wait_timeout=604800
   

9. Comment out the lines 'log-bin=mysql-bin' and 'binlog_format=mixed' in '/etc/my.cnf' file as shown below to disable MySQL binary log files.

   #log-bin=mysql-bin

   #binlog_format=mixed

10. Execute 'mysql_install_db' to install and set up the system tables.
11. Copy the MySQL daemon script file by executing the command 'cp support-files/mysql.server /etc/init.d/mysql' as root user.
12. Change mode for the daemon file by executing the command 'chmod 755 /etc/init.d/mysql' as root user.
13. Add the MySQL daemon by executing the command '/sbin/chkconfig --add mysql' as root.
14. Start MySQL Server by executing '/etc/init.d/mysql start'.
15. Set the root password for MySQL Server by executing 'mysqladmin -u root password [mysql_root_password]' where [mysql_root_password] is the password for the MySQL root user.

Install MySQL Client

StoreGrid Backup server uses the MySQL client program to connect to the MySQL Server. Hence, you need to install the MySQL 5.1.30 client in the backup server.

Note: If you have installed the MySQL Server in your StoreGrid backup server machine itself, then please ignore this step.

For backup/replication server running in 32 bit machine :

1. Extract the MySQL installer file by executing 'tar -xzvf mysql-5.1.30.tar.gz'
2. Go to the extracted folder by executing 'cd mysql-5.1.30'
3. Execute the command './configure --enable-thread-safe-client --enable-gui=no --without-server --with-plugins=max --with-gnu-ld'
4. Execute 'make'
5. Execute 'make install' as root user

For backup/replication server running in 64 bit machine :

1. Login as root user and execute yum install mysql.i386
2. This will install 32 bit MySQL client in your 64 bit machine.

Install UnixODBC

Install the unixODBC v2.2.14 in the backup server

For backup/replication server running in 32 bit machine :

1. Download the unixODBC installer file (unixODBC-2.2.14.tar.gz) from the URL : http://www.unixodbc.org/
2. Extract the installer file by executing 'tar -zxvf unixODBC-2.2.14.tar.gz'
3. Go the extracted folder by executing 'cd unixODBC-2.2.14'
4. Execute './configure --enable-gui=no --enable-threads=yes'
5. Execute 'make'
6. Execute 'make install' as a root user

For backup/replication server running in 64 bit machine :

1. For Redhat flavours: RHEL, SUSE, CentOS and Fedora 64bit machines - Login as root user and execute yum install unixODBC.i386

NOTE: If unixOBDC was installed through a binary package ('yum'), the odbc information files odbcinst.ini and odbc.ini will be located in '/etc/' but StoreGrid requires the ini files to be in '/usr/local/etc'. Therefore please create a symbolic links for these two files by executing below shown commands as a root user.

/bin/ln -s /etc/odbcinst.ini /usr/local/etc/odbcinst.ini

/bin/ln -s /etc/odbc.ini /usr/local/etc/odbc.ini

2. For Debian flavours: Debian or Ubuntu 64bit machines. Download unixODBC 32bit debian package: unixodbc_2.2.12-94.1_i386.deb
Login as root user and execute dpkg --install --force-architecture unixodbc_2.2.12-94.1_i386.deb

NOTE: If unixOBDC was installed through a binary package ('dpkg'), the odbc information files odbcinst.ini and odbc.ini will be located in '/etc/unixODBC/' but StoreGrid requires the ini files to be in '/usr/local/etc'. Therefore please create a symbolic links for these two files by executing below shown commands as a root user.

/bin/ln -s /etc/unixODBC/odbcinst.ini /usr/local/etc/odbcinst.ini

/bin/ln -s /etc/unixODBC/odbc.ini /usr/local/etc/odbc.ini

3. This will install 32 bit unixODBC library in your 64 bit machine under /usr/lib/libodbc.so.

Install MySQL Connector

Install the MySQL Connector v3.51.27 in the backup server

1. Download the MySQL Connector file (mysql-connector-odbc-3.51.27-linux-x86-32bit.tar.gz) from the URL : http://dev.mysql.com/downloads/connector/odbc/3.51.html
2. Extract the installer file by executing 'tar -zxvf mysql-connector-odbc-3.51.27-linux-x86-32bit.tar.gz'
3. Go the extracted folder by executing 'cd mysql-connector-odbc-3.51.27-linux-x86-32bit'
4. Copy the exacted connector binary files by executing 'cp -f lib/* /usr/local/lib/.'
5. This will create the MySQL Connector file '/usr/local/lib/libmyodbc3S-3.51.27.so'.

Configure the MySQL Connector

Configure the MySQL Connector in the backup server as follows :

1. Execute the command 'odbcinst -j'
2. Note the path of the 'odbcinst.ini' file for DRIVERS. By default, it is '/usr/local/etc/odbcinst.ini'
3. Login as root and open the file 'odbcinst.ini'
4. Append the following content to the file.

[ODBC]
Trace = No
Trace File = /tmp/sql.log
Pooling = Yes

[MySQL]
Description =
Driver = /usr/local/lib/libmyodbc3-3.51.27.so
Driver64 =
Setup = /usr/local/lib/libmyodbc3S-3.51.27.so
Setup64 =
UsageCount =1
CPTimeout =300
CPReuse =1
Threading =0


Note : If the ODBC Driver [ODBC] is already added in the file, please enable connection pooling by adding 'Pooling=Yes'.

Create StoreGrid database

Create a separate database for StoreGrid in the MySQL server.

1. Login to the machine where MySQL Server is running.
2. Execute the command 'mysql -u root -p' and enter the password.
3. Create the StoreGrid database by executing 'create database [database name] collate latin1_general_cs;' where [database name] is the name of the database.

Create System DSN

Create a system DSN in the backup server to access the StoreGrid database in the MySQL Server.

1. Execute the command 'odbcinst -j'
2. Note the path for 'odbc.ini' file for SYSTEM DATA SOURCES.
3. Login as root and open the file 'odbc.ini' (By default, file is will be '/usr/local/etc/odbc.ini').
4. Append the following content to the file

[sgserver]
Driver = MySQL
DATABASE = StoreGridDB
PWD = password123
SERVER = lintest
UID = root

Test connectivity with MySQL Server

After creating the MySQL driver and DSN in the backup server, check the connectivity from backup server to the MySQL Server

Specify the attributes during StoreGrid installation

After creating the DSN in the backup server, you need to specify DSN name, database name, user name and password during the StoreGrid installation in the backup server.

Troubleshooting Tips

	Getting error "cannot find correct Qt headers" while compiling unixODBC/MySQL Server/mysql connector
Solution : Include '--enable-gui=no' option while ./configure after extracting the installer file.
	Error: Can't find X includes. Please check your installation and add the correct paths or run configure with --enable-gui=no!
Solution : Include '--enable-gui=no' option during ./configure
	'make error : ltdl.h: No such file or directory' while compiling mysql-connector in Linux machines.
Solution : Install libltdl3-dev package in your machine and then try installing MySQL Server. You can download libltdl from the URL : http://rpmfind.net/linux/rpm2html/search.php?query=libltdl.so.3 or try the command yum install libtool-ltdl.i386
	'make error : ltdl.h: No such file or directory' while compiling mysql-connector in FreeBSD machines.
Solution : Install libtool15 package in your machine by the following steps : Login as root cd /usr/ports/devel/libtool15 make make install cd /usr/ports/devel/libtool15/work/libtool-1.5.22 make make install
	No curses/termcap library found
Solution : Install ncurses library by executing the following command as root       yum install ncurses-devel  (for redhat)       yum install ncurses-devel.i386  (for redhat i386)       apt-get install libncurses5-dev  (for debian)
	StoreGrid is not started (or) repeatedly crashes after startup
Solution : Stop StoreGrid application and start StoreGrid in command line by executing './bin/StoreGrid' from [StoreGrid_Home] folder. Note the console print from the terminal. Please follow the solutions when the console print are as follows : Can't connect to local MySQL server... Make sure that the MySQL server is running in the machine where MySQL is installed. Execute 'netstat -an | grep 3306' (for linux) or 'netstat -an | find "3306"' (for Windows) in the machine where MySQL server is running and check if the MySQL port 3306 (default mysql port) is in LISTEN state. If MySQL is not running, please start the MySQL Server and then try starting StoreGrid. Data source name not found... Make sure that you have given the correct DSN name during the StoreGrid installation. Please check the DSN by executing the command 'isql -v [dsn name] [user name] [password]' and check if you are able to connect to the MySQL server with the DSN name. Access denied for user... Check if you have given the correct user name and password for the MySQL Server during StoreGrid installation. Please check this by executing the command 'isql -v [dsn name] [user name] [password]' and check if you are able to connect to the MySQL server with the user name and password. Host 'xxxxx' is not allowed to connect to this MySQL server... Provide the required permission in the MySQL to allow the backup server to connect the database. You can do this by executing 'GRANT ALL PRIVILEGES ON *.* TO '[user name]'@'[backup server machine name]' IDENTIFIED BY '[password]' WITH GRANT OPTION;' in the MySQL prompt in the machine where MySQL Server is installed. NOTE: If you are deploying the backup servers in a cluster, you need to execute the above command for each of the backup server in the cluster replacing the 'backup server machine name' with the machine name of the backup server. If you find it cumbersome to execute this for each backup server, you could replace the 'backup server machine name' with '%' like: 'GRANT ALL PRIVILEGES ON *.* TO '[user name]'@'[%]'
	Error: Starting MySQL...Manager of pid-file quit without updating.
Cause: If SELinux is enabled in your Linux system then you could get this error when SELinux aborts MySQL manager while it is updating the pid file. To confirm that SELinux is enabled, execute the command '/usr/sbin/sestatus' as a root user. Solution : Execute the command 'setsebool -P mysqld_disable_trans=1 allow_user_mysql_connect=1' as root user and restart the MySQL server.