Open topic with navigation

Set Up a MySQL Database on Linux

To use Media Server with a MySQL database, you must install a MySQL server and ODBC driver, and configure Media Server to connect to the database through the driver.

The procedure describes how to set up a MySQL database on a CentOS 6 distribution.

To set up a MySQL Media Server database on Linux

1. Install a MySQL server. (Ensure that the package includes the mysql command-line tool.) For instructions, refer to the MySQL documentation on www.mysql.com.

2. Configure the database server for use with Media Server:

   1. Open the configuration or options file for the MySQL server (usually named my.ini).
   2. So that Media Server can send large amounts of binary data (images) to the database, set the configuration parameter max_allowed_packet=1073741824.
   3. Save and close the configuration file.

3. Add the MySQL bin directory path to the PATH environmental variable by running the command:

   export PATH=$PATH:binDirectoryPath

   NOTE:

   This step enables you to use the command mysql to start the mysql command-line tool from the terminal. If the directory path is not added to the PATH variable, you must specify the mysql.exe file path in the terminal to start mysql.

4. Start the mysql command-line tool. In the terminal, run the command:

   mysql

5. Run a CREATE DATABASE command to create a new database. Specify the following database settings.

   Database name	Any name.
   Character set	Must be Unicode–either UTF8 or UCS2.
   Collation	Any that is compatible with the encoding.

   For example:

   CREATE DATABASE myDatabase CHARACTER SET utf8 COLLATE utf8_unicode_ci;

6. Run the my.sql script provided in the Media Server installation directory. This script sets up the database schema that Media Server requires.

   1. Close the mysql command-line tool:

      quit

   2. In the terminal, run the command:

      mysql -u userName -p -v -D databaseName -e "source path/my.sql"

      where,

      userName	is the MySQL user name.
      databaseName	is the name of the database you created in Step 3.
      path	is the script file path.

      NOTE:

      Running the script non-interactively from the terminal ensures that the script terminates if an error occurs.

7. Grant privileges to the user that Media Server will connect to the MySQL server as. Required privileges are:

   Database	Create Temporary Tables
   All tables	Select, Insert, Update, Delete
   All functions and stored procedures	Execute

   If security is not a consideration, grant all privileges.

   1. Start the mysql command-line tool:

      mysql

   2. Run the GRANT commands:

      GRANT CREATE TEMPORARY TABLES ON databaseName.* TO userName;
      GRANT SELECT, INSERT, UPDATE, DELETE ON databaseName.* TO username;
      GRANT EXECUTE ON databaseName.* TO username;

      where,

      databaseName	is the name of the database you created in Step 2.
      userName	is the user name that Media Server will connect as.

   3. Close the mysql command-line tool:

      quit

8. Install unixODBC driver manager version 2.2.14 or later. If you have the relevant Yum repository, you can run the command in the terminal:

   sudo yum install unixODBC

9. Install the MySQL driver. If you have the relevant Yum repository, you can run the command in the terminal:

   sudo yum install mysql-connector-odbc

10. Configure the data source.

    1. Open the odbc.ini file with a text editor. This file is usually stored in the /etc directory.

    2. Add the data source name in square brackets. The name can be any string. For example:

       [MySQL_1]

    3. Under the data source name, set the following parameters.

       Parameter	Description
       Driver	The driver to use.
       Server	The IP address or hostname of the server that the database server is installed on.
       Port	The port to use to communicate with the database server.
       User	The user name to connect to the database server with.
       Password	The password for the user account that connects to the database server.
       Database	The name of the database that you created in Step 3.

       For example:

       [MySQL_1]
       Driver=MySQL
       Server=localhost
       Port=5432
       User=mysql
       Password=password
       Database=myDatabase
       

       NOTE:

       You can set other parameters in this file, but these have not been tested with Media Server.

    4. Save and close the file.

11. Configure the ODBC driver.

    1. Open the odbcinst.ini file with a text editor. This file is usually stored in the /etc directory.

    2. If not already present, add the database server name in square brackets. For example:

       [MySQL]

    1. Set the following parameters.

       Parameter	Description
       Description	A description of the driver instance.
       Driver64	The location of the MySQL driver library file.
       Setup64	The location of the driver installer file.
       FileUsage	Set this parameter to 1.

       For example:

       [MySQL]
       Description=ODBC for MySQL
       Driver64=/usr/lib64/libmyodbc5.so
       Setup64=/usr/lib64/libodbcmyS.so
       FileUsage=1

       NOTE:

       You can set other parameters in this file, but these have not been tested with Media Server.

    2. Save and close the file.
12. You can now configure Media Server to connect to the database (see Configure Media Server).