{"id":79,"date":"2020-03-19T09:47:08","date_gmt":"2020-03-19T08:47:08","guid":{"rendered":"https:\/\/www.funkgui.it\/?p=79"},"modified":"2024-10-23T15:01:49","modified_gmt":"2024-10-23T13:01:49","slug":"mysql-replication","status":"publish","type":"post","link":"https:\/\/www.funkgui.it\/index.php\/mysql-replication\/","title":{"rendered":"MySQL replication"},"content":{"rendered":"\r\n

Appunti veloci senza troppi commenti….<\/p>\r\n\r\n\r\n\r\n

Sul MASTER<\/p>\r\n\r\n\r\n\r\n

skip-innodb
default-storage-engine = MyISAM<\/p>\r\n\r\n\r\n\r\n

Si NON AMO INNODB!<\/p>\r\n\r\n\r\n\r\n

server-id=1<\/p>\r\n\r\n\r\n\r\n

log-bin = mysql-bin
binlog-ignore-db = “mysql”
binlog-ignore-db = “sys”
binlog-ignore-db = “performance_schema”<\/p>\r\n\r\n\r\n\r\n

systemctl restart mysql.service<\/p>\r\n\r\n\r\n\r\n

grant replication slave on .<\/em> TO ‘slaveuser’@’%’ identified by ‘xxxxxxxxxxxxxxx’;
SE mysql \u00e8 versione 8
CREATE USER ‘thesalveusername’@’%’ identified by ‘XXXXX’;
grant replication slave on .<\/em> TO ‘thesalveusername’@’%’;<\/p>\r\n\r\n\r\n\r\n

FLUSH TABLES WITH READ LOCK;<\/p>\r\n\r\n\r\n\r\n

creare i dump dei db e copiarli sulla macchina slave<\/p>\r\n\r\n\r\n\r\n

SHOW MASTER STATUS;
ci dar\u00e0 le informazioni sul nome file di log e sulla posizione da cui far partire la replicazione<\/p>\r\n\r\n\r\n\r\n

UNLOCK TABLES;<\/p>\r\n\r\n\r\n\r\n

Sullo SLAVE<\/p>\r\n\r\n\r\n\r\n

skip-innodb
default-storage-engine = MyISAM<\/p>\r\n\r\n\r\n\r\n

server-id = 2<\/p>\r\n\r\n\r\n\r\n

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST=’192.168.1.54′,
MASTER_PORT=3306,
MASTER_USER=’thesalveusername’,
MASTER_PASSWORD=’ddddddddd’;
START SLAVE;
SHOW SLAVE STATUS\\G<\/p>\r\n\r\n\r\n\r\n

on mysql8<\/p>\r\n\r\n\r\n\r\n

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST=’30.31.32.33′,
MASTER_PORT=3306,
MASTER_USER=’thesalveusername’,
MASTER_PASSWORD=’xxxxxxxx’;
START SLAVE;
SHOW SLAVE STATUS\\G<\/p>\r\n\r\n\r\n\r\n

CHANGE MASTER TO MASTER_HOST=’30.31.32.33′,
MASTER_PORT=3306,
MASTER_USER=’thesalveusername’,
MASTER_PASSWORD=’dddddddd’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 155;
START SLAVE;
SHOW SLAVE STATUS\\G<\/p>\r\n\r\n\r\n\r\n

Se lo slave non partisse utilizzare nella shell mysql il comando:<\/p>\r\n\r\n\r\n\r\n

slave reset<\/code><\/p>\r\n\r\n\r\n\r\n

Se la macchina dovessere essere un clone ricordarsi di eliminare il file \/var\/lib\/mysql\/auto.cnf in modo che i server abbiano UUID differente.<\/p>\r\n

Nel caso debba essere necessario modificare l’utente o l’ip dal quale lo slave si connette:<\/p>\r\n

DAL MASTER:<\/p>\r\n

select host,user from mysql.user;
RENAME USER ‘slaveuser’@’9.9.9.9’ TO ‘slaveuser’@’5.5.5.5’;
revoke replication slave on . FROM ‘slaveuser’@’9.9.9.9’;
grant replication slave on . TO ‘slaveuser’@’5.5.5.5’;<\/p>\r\n

se fosse necessario rifare il dump per ripristinare una replica:<\/p>\r\n

mysqldump –routines –triggers –source-data=1 -B —ELENCO DEI DB SEPARATO DA SPAZI— | gzip -1 > \/tmp\/master-$(date +%Y-%m-%d).sql.gz<\/p>\r\n

e poi sullo slave:<\/p>\r\n

stop slave; (o reset slave;)<\/p>\r\n

poi si importa il dump<\/p>\r\n

e poi start slave;<\/p>\r\n","protected":false},"excerpt":{"rendered":"

Appunti veloci senza troppi commenti…. Sul MASTER skip-innodbdefault-storage-engine = MyISAM Si NON AMO INNODB! server-id=1 log-bin = mysql-binbinlog-ignore-db = “mysql”binlog-ignore-db = “sys”binlog-ignore-db = “performance_schema” systemctl restart mysql.service grant replication slave on . TO ‘slaveuser’@’%’ identified by ‘xxxxxxxxxxxxxxx’;SE mysql \u00e8 versione 8CREATE USER ‘thesalveusername’@’%’ identified by ‘XXXXX’;grant replication slave on . TO ‘thesalveusername’@’%’; FLUSH TABLES WITH […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,4],"tags":[],"class_list":["post-79","post","type-post","status-publish","format-standard","hentry","category-generico","category-linux"],"_links":{"self":[{"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/comments?post=79"}],"version-history":[{"count":2,"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":117,"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/posts\/79\/revisions\/117"}],"wp:attachment":[{"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.funkgui.it\/index.php\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}