my_sql [laulau's wiki]

Ceci est une ancienne révision du document !

création user + base

CREATE DATABASE username; CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON username.* TO 'username'@'localhost' WITH GRANT OPTION; CREATE USER 'username'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON username.* TO 'username'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

changer l'encodage par défaut d'une base (vers UTF-8)

ALTER DATABASE <database> CHARACTER SET 'utf8' COLLATE 'utf8_bin';

mysql symlink

http://dev.mysql.com/doc/refman/5.0/fr/symbolic-links.html
stockage mysql par défaut : /var/lib/mysql , un répertoire par database.
dans le cadre d'un hébergement multi utilisateurs, on peut déplacer la base d'un user dans son home directory /home/X/mysql_data à l'aide d'un lien symbolique (ln -s /home/X/mysql_data /var/lib/mysql/X)
respecter les droits : le mieux est de créer la base et de la déplacer + linker ensuite.

ubuntu & mysql symlink

bug : http://forums.mysql.com/read.php?24,211657,225700#msg-225700
apparmor : http://fr.wikipedia.org/wiki/AppArmor

editer /etc/apparmor.d/usr.sbin.mysqld
modifier :
/var/lib/mysql/ rl, /var/lib/mysql/ rwkl, ajouter : /home/*/mysql_data/ r, /home/*/mysql_data/ rwk,
et relancer :
/etc/init.d/apparmor restart

drop tables like

→ http://stackoverflow.com/questions/1589278/sql-deleting-tables-with-prefix
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name SEPARATOR ', ') ) AS statement FROM information_schema.tables WHERE table_name LIKE 'myprefix_%';

drop table like avec procédure stockée

→ http://dba.stackexchange.com/questions/1018/mysql-drop-table-starting-with-a-prefix/122955

DELIMITER $$
\\ 
DROP PROCEDURE IF EXISTS DropTablesWithPrefix $$
CREATE PROCEDURE DropTablesWithPrefix(db VARCHAR(64), prfx VARCHAR(20))
StoredProcedure:BEGIN
    DECLARE ndx, maxid INT;
    DECLARE giventable, SQLSTMT VARCHAR(500);
    SELECT 'declare done';
\\ 
    CREATE TABLE TableZapList
    (
        droptablesql VARCHAR(512),
        idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ) ENGINE=MyISAM;
    SELECT 'table created';
\\ 
    INSERT INTO TableZapList (droptablesql)
	SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name)
	FROM information_schema.tables
	WHERE table_schema = db
	AND SUBSTR(table_name, 1, LENGTH(prfx)) = prfx;
	SELECT 'insert done';
	
    SELECT COUNT(1)
	INTO maxid
	FROM TableZapList;
	SELECT 'count done';
	
	SELECT *
	FROM TableZapList;
\\ 
    IF maxid = 0 THEN
    	SELECT 'leave stored procedure';
        LEAVE StoredProcedure;
    END IF;
   
\\ 
    SET ndx = 1;
    WHILE ndx <= maxid DO
    	SELECT * FROM TableZapList;
        SELECT droptablesql
	    INTO SQLSTMT
	    FROM TableZapList
	    WHERE idx = ndx;
        SET @sql = SQLSTMT;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET ndx = ndx + 1;
    END WHILE;
    SELECT 'while done';
    
    SELECT droptablesql FROM TableZapList;
    DROP TABLE TableZapList;
    SELECT 'clean done';
END; $$
\\ 
DELIMITER ;

et du coup exécution super simple une fois installée :
call <html>DropTablesWithPrefix</html>('test', 't_');

afficher valeur config

SHOW VARIABLES like 'binlog_%';