MySQL Replication

Passer par l'interface PhpMyAdmin

A : Configurer Master / My.cnf :


- Restart Mysql : service mysql restart

- Verification du log binaire : mysql> show master status;

- Blocage des transactin Mysql : mysql> FLUSH TABLES WITH READ LOCK;

- Arret du service Mysql : service mysql stop

- Sauvegarder bases: recupération des bases dans /var/lib/mysql et upload sur serveur Slave.

- Démarrage du service Mysql : service mysql start

- Déblocage des transactions Mysql : mysql> UNLOCK TABLES;


B : configurer Slave / My.cnf


- Arret du service Mysql : service mysql stop

- Démarrage du service Mysql : service mysql start --NOMDHOTE-host
    
- Démarrage du porcess Slave : mysql> START SLAVE;


---------------
Commande Mysql|
---------------

    - mysql -u root -p
    - SHOW MASTER STATUS;
    - SHOW SLAVE STATUS;

My.cnf-Master :
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log
log-error=/var/log/mysql/mysql-error.log
server-id=1
binlog-do-db = test1

user       = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket       = /var/run/mysqld/mysqld.sock
port       = 3306
basedir       = /usr
datadir       = /var/lib/mysql
tmpdir       = /tmp
lc-messages-dir   = /usr/share/mysql
skip-external-locking

bind-address = IP Local

key_buffer       = 16M
max_allowed_packet   = 16M
thread_stack       = 192K
thread_cache_size       = 8

myisam-recover = BACKUP

query_cache_limit = 1M
query_cache_size = 16M

expire_logs_days = 10
max_binlog_size = 100M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

My.cnf-Slave

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
log-bin=mysql-bin
server-id=1414677401

bind-address = 127.0.0.1

key_buffer       = 16M
max_allowed_packet   = 16M
thread_stack       = 192K
thread_cache_size       = 8

myisam-recover = BACKUP

query_cache_limit = 1M
query_cache_size = 16M

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

expire_logs_days = 10
max_binlog_size = 100M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

IBDATA1 ou comment libérer de l’espace sur sa racine !!!

Arrêter Mysql : service mysql stop

Créer un dossier Mysql dans le dossier home : mkdir /home/mysql

Donner les droits sur le dossier : chown mysql:mysql /home/mysql

Copier le contenu des bases vers ce nouveau dossier : cp -pr /var/lib/mysql/* /home/mysql

Faire une sauvegarde du dossier contenant les bases (ou ailleur que dans /var/lib, si vous ne disposez plus de place !): mv /var/lib/mysql/ /var/lib/mysqlOLD

Créer un lien symbolique : ln -s /home/mysql /var/lib/mysql

Donner les droits sur le dossier : chown mysql:mysql /var/lib/mysql

Relancer le serveur Mysql : service mysql start

Memo

Posts categoriezed as Mysql

Synology : désactiver « synology_password_check »

Mysql MDP