Installing and Configuring MySQL as StoreGrid Backend Database
on Linux
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 provider 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
Note : The following steps are also applicable for StoreGrid Replication Server using the MySQL database as a backend database.
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.
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=86400
9. Execute 'mysql_install_db' to install and set up the system tables.
10. Copy the MySQL daemon script file by executing the command 'cp support-files/mysql.server
/etc/init.d/mysql' as root user.
11. Change mode for the daemon file by executing the command 'chmod 755 /etc/init.d/mysql' as root user.
12. Add the MySQL daemon by executing the command '/sbin/chkconfig --add mysql' as root.
13. Start MySQL Server by executing '/etc/init.d/mysql start'.
14. 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.
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. Login as root user and execute yum install unixODBC.i386
2. 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.
Note : Make sure the database name does not exceed 30 characters.
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
In the above example file, a DSN with the name 'sgserver' is created for the database 'StoreGridDB' in the MySQL server running in 'lintest' machine with the user name as 'root' and password as 'password123'.
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
Execute the following command 'isql -v [dsn name] [user name] [password]' and check if you are able to connect the
MySQL Server from the backup 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.
Solution : Include '--with-mysql-path=/path/to/mysql' option in the ./configure
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 :
1. Login as root 2. cd /usr/ports/devel/libtool15 3. make 4. make install 5. cd /usr/ports/devel/libtool15/work/libtool-1.5.22 6. make 7. 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) repeatly 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 :
1. 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.
2. Data source name not found...
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.
3. 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.
4. 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.
