User:Emijrp/Creating your own database for your tool

This tutorial explains the steps for creating your own database for your tool. You also can create a database with your personal account (just skip the tool creation steps).

Creating the database

Create an account for your tool ("Create New Tool" link)

Login into tools-dev.wmflabs.org with your personal account:
- mosh -a yourusername@tools-dev.wmflabs.org

Become your tool:
- become yourtoolname

Read the user parameter from your replica.my.cnf. Use it as prefix for your database name:
- sname1234__YOURDATABASENAME

Open a new terminal to upload the schema and write:
- scp createdatabase.sql yourusername@tools-dev.wmflabs.org:/data/project/yourtoolname

Finally, import the database schema:
- mysql --defaults-file="${HOME}"/replica.my.cnf -h tools-db < createdatabase.sql

Using the database

From shell

sql local
use s51138__heritage_p;
select * from table1 where 1;

From PHP

$dbmycnf = parse_ini_file("../replica.my.cnf");
$dbuser = $dbmycnf['user'];
$dbpass = $dbmycnf['password'];
unset($dbmycnf);
$dbhost = "tools-db";
$dbname = "s51138__heritage_p";

$db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname.';charset=utf8', $dbuser, $dbpass);

Backup your database

Schema

Example of createdatabase.sql file:

CREATE DATABASE IF NOT EXISTS `sname1234__YOURDATABASENAME`;
USE `sname1234__YOURDATABASENAME`;

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`param1` int(10) unsigned NOT NULL DEFAULT '0',
`param2` varbinary(255) NOT NULL DEFAULT '',
PRIMARY KEY (`param1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*
INSERT IGNORE INTO `table1` SET `param1` = 1, `param2` = 'Hola';
INSERT IGNORE INTO `table1` SET `param1` = 2, `param2` = 'Adios';
INSERT IGNORE INTO `table1` SET `param1` = 3, `param2` = 'Más cosas';
*/