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.

Professionnel - Page 40

  • Méthode de réplication MySQL dite "de snapshot"

    Cette méthode est celle utilisée par Percona :

    1) Connect to MySQL and run FLUSH TABLES WITH READ LOCK
    Note – this command may take a while to complete if you have long running queries. The catch here is FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects. So be careful if you have any long running queries. If you’re using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step.

    2) While holding connection open run: lvcreate -L16G -s -n dbbackup /dev/Main/Data – This will create snapshot nameddbbackup for Logical Volume Main/Data . You should specify enough of undo space to hold modifications during backup process – I’ve specified 16GB in this case. If your undo size is not large enough snapshot will get invalidated and backup will be aborted.

    Sometimes you might run into the errors on this step, The most common one I’ve resently seen is: snapshot: Required device-mapper target(s) not detected in your kernel – This means snapshot module is not loaded in your kernel by default and you need to load it, which is done by running modprobe dm-snapshot

    3) Now you have created logical volume and can unlock the tables, but before that you should probably record binary log position which is done by running SHOW MASTER STATUS – This is binary log position you’ll need to point your MySQL Slaves created from this snapshot.

    4) Snapshot created, now you want to let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection.

    5) Mount backup Filesystem: mount /dev/Main/dbbackup /mnt/backup

    6) Copy data to backup. Normally you can skip slow query logs and error log while taking backup. You also can skip most of binary logs – however if some of your slaves are far behind you might want to keep some of last binary logs just in case, or you can assume in case of recovery from the backup you will need to restore slaves as well and skip binary logs in your backup process.

    7) Unmount filesystem umount /mnt/backup

    8) Remove snapshot: lvremove -f /dev/Main/dbbackup

    If you want to create slave based on such snapshot you need to perform couple of more simple steps

    9) Extract/Copy database to the slave database directory.

    10) Start MySQL Server. Wait for it to perform recovery.

    11) Use CHANGE MASTER TO to point slave to saved binary log position:

     

    12) Run SLAVE START to restart replication.

    With slightly modified process you can clone slaves from the slaves without stopping them – you just need to use SHOW SLAVE STATUS instead of SHOW MASTER STATUS to find out appropriate binary log position. Be careful however – cloning slave from the slave also clones inconsistences in data which slave could have accomulated – especially if you useslave_skip_errors or sql_slave_skip_counter. Cloning master you’re starting from consistent copy.

    https://www.percona.com/blog/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

     

  • 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~