Creating the MySQL database for PanDA server

This section will focus on a MySQL DB creation. Creating the Oracle DB is out of the scope of this exercise and we would have to request the help of the ATLAS DBA. The MySQL DB can be on the same box or another.

MySQL installation

> yum install mysql-server
> service mysqld start
> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution
...

---+++ MySQL configuration

> cat /etc/my.cnf #You might want to configure MySQL to your liking. This is the LSST configuration
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Disabling reading local files via LOAD DATA LOCAL INFILE or SELECT load_file()
set-variable=local-infile=0
# Atypic port for MySQL
port=23306
# Do not show databases unless privileges given.
skip-show-database
#collation-server=utf8_unicode_ci
#init-connect='SET NAMES utf8; SET autocommit=0;'
init-connect='SET autocommit=0;'
#character-set-server=utf8
default-storage-engine=innodb
lower_case_table_names=1
#lower_case_table_names=0
connect-timeout=60

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
#default-character-set=utf8

[mysql]
#default-character-set=utf8

MySQL schema

The current database schema can be found here. https://github.com/PanDAWMS/panda-server/blob/master/templates/DB/pandaDB_MySQL.sql Warning, important: We need to figure out the best way to generate the database schema automatically from the ATLAS Oracle master and periodically update it on github.

Some MySQL dump instructions... Hide

To generate a dump of the database:

mysqldump -d --single-transaction --host=localhost --user=<your user> --password=<your password> --port=23307 pandadb1 > /tmp/dbschema

To generate a dump of the data in the tables:

mysqldump --no-create-info --single-transaction --complete-insert --host=localhost --user=<your user> --password=<your password> --port=23307 pandadb1 > /tmp/dbdata
The important flags are: --no-create-info: don't generate the CREATE TABLE etc. statements --complete-insert: generate INSERT statements with explicit column names

To import the data dump into another database:

mysql -u <your user> -p<your password> -h localhost pandadb2 < /tmp/dbdata

Database and table creation

To create the database from a dump:

> service mysql stop
> service mysql start
> export DBUSER=pansrv
> export DBPWD=XXXXXXXX
> mysql -u root -e "use mysql;
  GRANT ALL PRIVILEGES ON ATLAS_PANDA.* TO '$DBUSER'@localhost IDENTIFIED BY '$DBPWD';
  GRANT ALL PRIVILEGES ON ATLAS_PANDA.* TO '$DBUSER'@'%' IDENTIFIED BY '$DBPWD';
  GRANT ALL PRIVILEGES ON ATLAS_PANDA.* TO '$DBUSER'@'%' IDENTIFIED BY '$DBPWD';
  GRANT SUPER ON *.* TO '$DBUSER' IDENTIFIED BY '$DBPWD';
  GRANT SUPER ON *.* TO '$DBUSER'@'%' IDENTIFIED BY '$DBPWD';
  DELETE FROM user WHERE user='';
  FLUSH PRIVILEGES;
  DROP DATABASE IF EXISTS ATLAS_PANDA;
  CREATE DATABASE ATLAS_PANDA";
  
> mysql -u pansrv -pXXXXXXXX -h localhost -P 3306 ATLAS_PANDA < /tmp/dbschema
> # See you can connect with the new user and the tables have been created
> mysql -u pansrv -p<your password> -h localhost -P 3306 ATLAS_PANDA 
mysql> show tables;
+--------------------------------+
| Tables_in_ATLAS_PANDA          |
+--------------------------------+
| auth_group                     |
| auth_group_permissions         |
| auth_message                   |
| auth_permission                |
| auth_user                      |
...

(obviously whatever information you put in here -port, user, password-, has to match the information you put in the panda server config file)

Warning, important: STILL TO BE CONFIRMED! THIS NEEDS TO BE PUT INTO A SCRIPT TO BE CALLED AT INSTALLATION TIME.
Warning, important: THE SCRIPT SHOULD ALSO PUT MYSQL TO BE STARTED WHENEVER THE MACHINE REBOOTS

Edit | Attach | Watch | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r4 - 2015-07-24 - FernandoHaraldBarreiroMegino
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PanDA All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback