Sylvain Deloux web architect

Installez pure-ftp :

sudo apt-get install pure-ftpd-mysql

Connectez-vous au serveur MySQL :

mysql -u root -p

Créez l’utilisateur MySQL ftp (en changeant le mot de passe) :

CREATE USER 'ftp'@'localhost' IDENTIFIED BY 'mot_de_passe';
GRANT USAGE ON *.* TO 'ftp'@'localhost' IDENTIFIED BY 'mot_de_passe';
CREATE DATABASE IF NOT EXISTS `ftp` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON `ftp`.* TO 'ftp'@'localhost';

Ensuite, créez la table qui contiendra les utilisateurs :

USE `ftp`;
CREATE TABLE `users` (
`login` varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`is_active` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`password` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`uid` varchar(11) COLLATE utf8_unicode_ci NOT NULL DEFAULT '-1',
`gid` varchar(11) COLLATE utf8_unicode_ci NOT NULL DEFAULT '-1',
`home` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`up_bandwidth_kbps` smallint(5) NOT NULL DEFAULT '0',
`down_bandwidth_kbps` smallint(5) NOT NULL DEFAULT '0',
`comment` tinytext COLLATE utf8_unicode_ci NOT NULL,
`quota_megabytes` smallint(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
exit;

Pour créer un utilisateur FTP, il faut exécuter la requête suivante :

INSERT INTO `users` (`login`, `is_active`, `password`, `uid`, `gid`, `home`, `up_bandwidth_kbps`, `down_bandwidth_kbps`, `comment`, `quota_megabytes`) VALUES('login', '1', md5('password'), '1000', '1000', '/home/login', 0, 0, '', 0);

Maintenant que MySQL est prêt, il faut configurer pure-ftp en éditant le fichier /etc/pure-ftpd/db/mysql.conf avec les paramètres MySQL crées précédement :

# Optional : MySQL server name or IP. Don't define this for unix sockets.
# MYSQLServer     127.0.0.1

# Optional : MySQL port. Don't define this if a local unix socket is used.
# MYSQLPort       3306

# Optional : define the location of mysql.sock if the server runs on this host.
MYSQLSocket      /var/run/mysqld/mysqld.sock

# Mandatory : user to bind the server as.
MYSQLUser       ftp

# Mandatory : user password. You must have a password.
MYSQLPassword   mot_de_passe

# Mandatory : database to open.
MYSQLDatabase   ftp

# Mandatory : how passwords are stored
# Valid values are : "cleartext", "crypt", "md5" and "password"
# ("password" = MySQL password() function)
# You can also use "any" to try "crypt", "md5" *and* "password"
MYSQLCrypt      md5

# In the following directives, parts of the strings are replaced at
# run-time before performing queries :
#
# \L is replaced by the login of the user trying to authenticate.
# \I is replaced by the IP address the user connected to.
# \P is replaced by the port number the user connected to.
# \R is replaced by the IP address the user connected from.
# \D is replaced by the remote IP address, as a long decimal number.
#
# Very complex queries can be performed using these substitution strings,
# especially for virtual hosting.

# Query to execute in order to fetch the password
MYSQLGetPW      SELECT password FROM users WHERE login="\L" AND is_active="1"

# Query to execute in order to fetch the system user name or uid
MYSQLGetUID     SELECT uid FROM users WHERE login="\L" AND is_active="1"

# Optional : default UID - if set this overrides MYSQLGetUID
#MYSQLDefaultUID 1000

# Query to execute in order to fetch the system user group or gid
MYSQLGetGID     SELECT gid FROM users WHERE login="\L" AND is_active="1"

# Optional : default GID - if set this overrides MYSQLGetGID
#MYSQLDefaultGID 1000

# Query to execute in order to fetch the home directory
MYSQLGetDir     SELECT home FROM users WHERE login="\L" AND is_active="1"

# Optional : query to get the maximal number of files
# Pure-FTPd must have been compiled with virtual quotas support.
# MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE login="\L" AND is_active="1"

# Optional : query to get the maximal disk usage (virtual quotas)
# The number should be in Megabytes.
# Pure-FTPd must have been compiled with virtual quotas support.
MySQLGetQTASZ  SELECT quota_megabytes FROM users WHERE login="\L" AND is_active="1"

# Optional : ratios. The server has to be compiled with ratio support.
# MySQLGetRatioUL SELECT ULRatio FROM users WHERE login="\L" AND is_active="1"
# MySQLGetRatioDL SELECT DLRatio FROM users WHERE login="\L" AND is_active="1"

# Optional : bandwidth throttling.
# The server has to be compiled with throttling support.
# Values are in KB/s .
MySQLGetBandwidthUL SELECT up_bandwidth_kbps FROM users WHERE login="\L" AND is_active="1"
MySQLGetBandwidthDL SELECT down_bandwidth_kbps FROM users WHERE login="\L" AND is_active="1"

# Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :
# 1) You know what you are doing.
# 2) Real and virtual users match.
# MySQLForceTildeExpansion 1

# If you upgraded your tables to transactionnal tables (Gemini,
# BerkeleyDB, Innobase...), you can enable SQL transactions to
# avoid races. Leave this commented if you are using the
# traditionnal MyIsam databases or old (< 3.23.x) MySQL versions.
# MySQLTransactions On

Il reste à configurer certaines options de pure-ftp en créant un fichier par paramètre avec sa valeur. Les noms sont assez explicites :

cd /etc/pure-ftpd-conf
sudo echo ,21 > Bind
sudo echo 4500 4600 > PassivePortRange
sudo echo yes > ChrootEveryone
sudo echo yes > ProhibitDotFilesRead
sudo echo yes > ProhibitDotFilesWrite
sudo echo yes > NoChmod
sudo echo yes > BrokenClientsCompatibility
sudo echo 4 > MaxClientsPerIP
sudo echo 20 > MaxClientsNumber
sudo echo no > PAMAuthentication
sudo echo no > UnixAuthentication
sudo /etc/init.d/pure-ftpd restart

Et c’est tout. Source : How to install and configure pure-ftpd

Voici un petit script bash qui permet de versionner sur un dépôt SVN des dumps de bases de données MySQL. Il crée un dump au format SQL par base.

L’option --skip-extended-insert permet d’avoir un enregistrement par ligne, ce qui sera très pratique pour les diff SVN et l’option --skip-dump-date retire le timestamp à la fin du fichier, et évite ainsi de créer une nouvelle version du fichier si il n’y a eu aucune modification sur la base.

J’ai fait ce script vite fait hier soir, et il fonctionne bien pour moi, je pense pouvoir l’améliorer à l’avenir et suis ouvert à toute proposition ;) Il suffit juste de configurer les 6 variables au début du fichier et de le mettre dans une crontab, et le tour est joué!

#!/bin/bash

export SVN_USERNAME='svn_user'
export SVN_PASSWORD='svn_pwd'
export SVN_REPOSITORY='http://my_svn_repository.com/'
export MYSQL_USERNAME='mysql_user'
export MYSQL_PASSWORD='mysql_pwd'
export TMP_DIRECTORY='/tmp/mysqlsvn_backup_tmp'

if [ -d $TMP_DIRECTORY ]; then
        echo "Cleaning tmp directory"
        rm -Rf $TMP_DIRECTORY
fi;

echo "Initializing tmp directory"
mkdir $TMP_DIRECTORY
cd $TMP_DIRECTORY

echo "Checking out repository"
svn co $SVN_REPOSITORY . --username=$SVN_USERNAME --password=$SVN_PASSWORD

for DB_NAME in `echo 'SHOW DATABASES;' | mysql -u$MYSQL_USERNAME -p$MYSQL_PASSWORD --batch -N`; do
        export filename=$DB_NAME.sql

        echo "Dumping '$DB_NAME' database"
        mysqldump -u$MYSQL_USERNAME -p$MYSQL_PASSWORD --skip-extended-insert --skip-dump-date $DB_NAME > $filename

        svn add $filename
done

echo "Sending files to repository"
svn ci -m "Automatic backup $(date +%Y)-$(date +%m)-$(date +%d) $(date +%H):$(date +%M)" 

echo "Removing tmp directory"
rm -Rf $TMP_DIRECTORY

Un petit aide-mémoire pour que toutes les tables d’une base de données soient en UTF-8, la solution la plus courante étant de rajouter ceci en haut de son fichier schema.yml :

options:
  collate: utf8_unicode_ci
  charset: utf8

Cela fonctionne très bien… mais si on utilise plusieurs fichiers schema.yml (ou si on utilise un plugin ayant son propre schéma, sfDoctrineGuard par exemple), on se retrouve avec des tables en latin1_swedish_ci mélangées avec d’autres en utf8_unicode_ci, et ça, c’est moche (tout le contraire d’une bonne pratique). Pire encore, l’utilisation d’encodages différents génère l’erreur MySQL « Illegal mix of collations ».

Pour y remédier, il faut forcer Doctrine à utiliser l’UTF-8 partout, en modifiant le fichier /config/ProjectConfiguration.class.php (merci à Damien pour le tip) :

<?php

class ProjectConfiguration extends sfProjectConfiguration
{
  public function configureDoctrine(Doctrine_Manager $manager)
  {
    $manager->setCollate('utf8_unicode_ci');
    $manager->setCharset('utf8');

    Doctrine_Migration_Base::setDefaultTableOptions(array(
      'type' => 'INNODB',
      'charset' => 'utf8',
      'collate' => 'utf8_unicode_ci'
    ));
  }
}

UPDATE `table` SET `row` = REPLACE(`row`, 'from_string', 'to_string');

To start, we have a Criteria, with a Criterion for each condition :

$criteria = new Criteria();

$a = $criteria->getNewCriterion( /* A */ );
$b = $criteria->getNewCriterion( /* B */ );
$c = $criteria->getNewCriterion( /* C */ );
...

A AND B

$a->addAnd($b);

A AND (B OR C)

$b->addOr($c);
$a->addAnd($b);

(A AND B) OR (C AND D)

$a->addAnd($b);
$c->addAnd($d);
$a->addOr($c);

A AND (B OR (C AND D))

$c->addAnd($d);
$b->addOr($c);
$a->addAnd($b);

And, don’t forget to link your main Criteria with your first condition ($a in these examples) :

$criteria->add($a);

You can generate more complicated cases with the Propel Criteria Builder.

If you use Propel 1.5 (or newer), you can avoid using criterias, check Propel Documantation to learn more. Get more advanced examples on Propel 1.5 lead developer’s blog.

Here are some examples of triggers creation syntax (MySQL user needs the SUPER privilege) :

DELIMITER //

CREATE TRIGGER `ai_trigger_name` AFTER INSERT ON `related_table_name`
  FOR EACH ROW BEGIN
    INSERT INTO some_table (row1, row2, row3) VALUES (NEW.row1, NEW.row2, NEW.row3);
    INSERT INTO another_table (row1, row2, row3) VALUES (NEW.row1, NEW.row2, NEW.row3);
  END;
//

CREATE TRIGGER `au_trigger_name` AFTER UPDATE ON `related_table_name`
  FOR EACH ROW UPDATE some_table SET row1 = NEW.row1, row2 = NEW.row2, row3 = NEW.row3 WHERE id = NEW.id;
//

CREATE TRIGGER `ad_trigger_name` AFTER DELETE ON `related_table_name`
  FOR EACH ROW DELETE FROM some_table WHERE row1 = OLD.row1;
//

DELIMITER ;

To create a MySQL database with its own user :

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_password';

GRANT USAGE ON *.* TO 'user_name'@'localhost' IDENTIFIED BY 'user_password';

CREATE DATABASE IF NOT EXISTS `database_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

GRANT ALL PRIVILEGES ON `database_name`.* TO 'user_name'@'localhost';

Voici un petit récapitulatif pour migrer des données MySQL d’un serveur à un autre ou tout simplement pour faire des sauvegardes.

Exporter une base

mysqldump -u LOGIN_MYSQL -pMOT_DE_PASSE -rNOM_FICHIER NOM_BASE

Attention entre les attributs -p, -r et leurs valeurs respectives, il ne faut pas d’espace.

Importer une base

mysql -u LOGIN_MYSQL -pMOT_DE_PASSE NOM_DE_LA_BASE < NOM_FICHIER

Pour que l’importation fonctionne, il faut que la base de données soit crée et que l’utilisateur y ait accès :)