Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

RDBMS Expertise - Page 46

  • Pgbouncer [postgres]

    Installer pgbouncer , configurer le .ini :

    cat /etc/pgbouncer/pgbouncer.ini

     

    [databases]
    <aliasBDD> = dbname=test user=pg_test port=5433 pool_size=48

    [pgbouncer]
    logfile = /var/log/pgbouncer/pgbouncer.log
    pidfile = /var/run/pgbouncer/pgbouncer.pid
    auth_file = /etc/pgbouncer/userlist.txt
    listen_addr = <hostname>-pgb
    listen_port = 5432
    auth_type = trust
    admin_users = pgbouncer
    stats_users = monitor

    pool_mode = transaction

    #server_reset_query =
    server_reset_query =
    max_client_conn = 1800
    default_pool_size = 20
    unix_socket_dir = /tmp

    ignore_startup_parameters = extra_float_digits, application_name
    server_check_query = select 1
    server_check_delay = 10
    log_connections = 1
    log_disconnections = 1
    log_pooler_errors = 1

     

    Se connecter au pgbouncer :

    su - pgbouncer

    psql

    show databases : affiche les databases liés

     

  • Get Op

    #!/bin/bash
    function usage
    {
     if [ $# -gt 0 ]; then
            echo $@ >&2
     fi
     cat << EOF >&2
    usage : t [-v][-h host_name][-P port_num][-u user_name][-p password][-D db_name]
    Other parameters (-h, -P, ... -D) = Same as mysql command (refer to mysql documentation).
    EOF
     exit 1
    }
    function cat_args
    {
     echo $msql $1 $2
    }
    msql='/usr/local/mysql5/bin/mysql '

    called_with="$@"
    while getopts "vh:P:u:p:D:" arg
    do
            case $arg in
                    h|P|u|p|D)
                            msql=$(cat_args -$arg$OPTARG)
                            ;;
                    v)
                            verbose=1
                            ;;
                    *|?)
                            usage incorrect parameter
                            ;;
            esac
    done

    echo $msql
    echo show tables|$msql

  • Mysql : mettre en place une répli (pour confirmé)

    Valide sur 5.1.

     

    Sur le master faire un dump des bases avec :

    mysqldump -uroot -p --single-transaction --routines --triggers <database> > <database>.sql

     

    Sur le master faire un :

    show master status; (récupérer le info sous le coude)

     

    Sur le slave , réimporter les bases :

    mysql -p -h127.0.0.1 <database> < <database>.sql

    Sur le slave dans le fichier my.cnf :

    # Master parameter

    #log-bin                        = mysql-bin
    #binlog_format                  = mixed
    #max_binlog_size                = 256M

    # Slave parameter

    skip-slave-start
    server-id                       = 3  #doit être unique
    relay-log                       = <slave_hostname>-relay-bin
    master-host                     = <master-hostname>
    master-info-file                = master.info
    master-port                     = 3306
    master-user                     = <replicauser>
    master-password                 = <pwdreplicauser>
    master-connect-retry            = 60

    replicate_do_db                  = <database1>
    replicate_do_db                  = <database2>

    ...
    replicate_do_db                  = <databasen>


    read-only


    innodb_data_home_dir             = /var/lib/mysql_3306
    innodb_data_file_path            = ibdata1:10M:autoextend
    innodb_log_group_home_dir        = /var/lib/mysql_3306
    innodb_buffer_pool_size          = 256M
    innodb_additional_mem_pool_size  = 20M
    innodb_log_file_size             = 64M
    innodb_log_buffer_size           = 8M
    innodb_file_per_table

     

    Sur le master créé le user de réplication :

    mysql>CREATE USER 'replicuser'@'ip_slave' IDENTIFIED BY '<pwdreplicauser>';

    mysql>GRANT REPLICATION SLAVE ON *.* TO 'replicuser'@'ip_slave' IDENTIFIED BY PASSWORD 'MDP EN HEXA';

    mysql> flush privileges;

    Sur le slave créé le user de réplication :

    mysql>CREATE USER 'replicuser'@'ip_master' IDENTIFIED BY PASSWORD 'pwd en Hexa';

    mysql>GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'replicuser'@'ip_master' IDENTIFIED BY PASSWORD 'pwd en Hexa';

    mysql> flush privileges;

     

    Synchroniser le slave :

    mysql> change master to MASTER_HOST='ip_master', MASTER_PORT=3306, MASTER_USER='replicuser', MASTER_PASSWORD = '<pwdreplicauser>', MASTER_LOG_FILE='numéro de binlog', MASTER_LOG_POS=position dans le binlog;

    mysql> start slave;

     

     enjoy~