2023-02-17 MYSQL主從筆記

10.60.143.32主

10.60.143.33從

set global validate_password.mixed_case_count=0;

set global validate_password.number_count=0;

set global validate_password.length=0;

set global validate_password.policy=0;

set global validate_password.special_char_count=0;

set password='1';

Start slave io_thread;

Stop slave io_thread;

Start slave SQL_thread;

Stop slave SQL_thread;

非GTID

create user 'repl'@'%' identified by '1';

grant replication slave on *.* to 'repl'@'%' ;

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '1';

create database t;

use t;

create table t (id int,a int);

insert into t values (1,1),(2,2),(3,3);

show master status\G;

mysqldump -uroot -p1? --all-databases? >/setup/h.sql

scp /setup/h.sql? root@10.60.143.33:/setup/

mysql </setup/h.sql

change master to master_host='10.60.143.32',master_user='repl',master_password='1',master_log_file='mysql-bin.000002',master_log_pos=1958;

stop slave ;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

start slave ;

show slave status\G;

MYSQL8.0 的復制用戶必須要用 mysql_native_password

GTID

create user 'repl'@'%' identified by '1';

grant replication slave on *.* to 'repl'@'%' ;

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '1';

create database t;

use t;

create table t (id int,a int);

insert into t values (1,1),(2,2),(3,3);

mysqldump -uroot -p1 --set-gtid-purged=on? --triggers --routines --events? --all-databases? >/setup/h.sql

mysql </setup/h.sql

change master to master_host='10.60.143.32',master_port=3306,master_user='repl',master_password='1',master_auto_position=1;

stop slave;

SET gtid_next = '973d41ac-c84d-11ec-b614-0050569684a9:4';

BEGIN;COMMIT;

SET gtid_next = 'AUTOMATIC';

START SLAVE;

show slave status\G;

多源復制

10.60.143.91

10.60.143.138

10.60.143.136

mysqldump -uroot -p1 --set-gtid-purged=on? --triggers --routines --events l? >/setup/l.sql

mysqldump -uroot -p1 --set-gtid-purged=on? --triggers --routines --events y? >/setup/y.sql

CHANGE REPLICATION SOURCE to SOURCE_HOST='10.60.143.91',SOURCE_USER='repl',SOURCE_PASSWORD='1',SOURCE_AUTO_POSITION=1 FOR CHANNEL 'C1';?

CHANGE REPLICATION SOURCE to SOURCE_HOST='10.60.143.138',SOURCE_USER='repl',SOURCE_PASSWORD='1',SOURCE_AUTO_POSITION=1 FOR CHANNEL 'C2';?

//change replication filter replicate_wild_do_table=('t.t,'x.x','y.y');

克隆

10.60.143.32

10.60.143.33

default_authentication_plugin=mysql_native_password

plugin-load-add=mysql_clone.so

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

CREATE USER clone@'%' IDENTIFIED by '1';

GRANT BACKUP_ADMIN ON *.* TO 'clone';

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'clone';

從庫

grant clone_admin on *.* to clone;

set global clone_valid_donor_list ='10.60.143.32:3306';

clone instance from clone@'10.60.143.32':3306 identified by '1';

傳統(tǒng)MGR

10.60.143.137

mkdir -p /home/mysql3307

mkdir -p /home/mysql3308

mkdir -p /home/mysql3309

mysqld? --basedir=/usr --datadir=/home/mysql3307/ --initialize

mysqld? --basedir=/usr --datadir=/home/mysql3308/ --initialize

mysqld? --basedir=/usr --datadir=/home/mysql3309/ --initialize

chown mysql.mysql -R mysql33*

mysqld_multi --defaults-extra-file=/etc/mym.cnf report

grep 'temporary password' /var/log/mysqld.log

[mysqld_multi]

mysqld =/usr/bin/mysqld_safe

mysqladmin =/usr/bin/mysqladmin

user =root

pass =1

[client]

socket=/var/lib/mysql/mysql.sock

[mysqld1]

basedir=/usr

datadir = /home/mysql3307? ? ?

port = 3307

server_id = 2

socket = /tmp/mysql3307.sock

character-set-server=utf8

log-error= /home/mysql3307/error.log

pid-file = /home/mysql3307/mysql3307.pid

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

gtid_mode=ON

log-slave-updates=ON

enforce-gtid-consistency=ON

binlog_format = ROW

log-bin=mysql-bin

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "10.60.143.137:3317"

loose-group_replication_group_seeds= "10.60.143.137:3317,10.60.143.137:3318,10.60.143.137:3319"

loose-group_replication_bootstrap_group=off

loose-group_replication_single_primary_mode=FALSE

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum = NONE

expire_logs_days = 3

#group_replication_allow_local_disjoint_gtids_join = ON

[mysqld2]

basedir=/usr

datadir = /home/mysql3308?

port = 3308

server_id = 3

socket = /tmp/mysql3308.sock

character-set-server=utf8

log-error= /home/mysql3308/error.log

pid-file = /home/mysql3308/mysql3308.pid

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

gtid_mode=ON

log-slave-updates=ON

enforce-gtid-consistency=ON

binlog_format = ROW

log-bin=mysql-bin

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "10.60.143.137:3318"

loose-group_replication_group_seeds= "10.60.143.137:3317,10.60.143.137:3318,10.60.143.137:3319"

loose-group_replication_bootstrap_group=off

loose-group_replication_single_primary_mode=FALSE

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum = NONE

expire_logs_days = 3

#group_replication_allow_local_disjoint_gtids_join = ON

[mysqld3]

basedir=/usr

datadir = /home/mysql3309? ? ?

port = 3309

server_id = 4

socket = /tmp/mysql3309.sock

character-set-server=utf8

log-error= /home/mysql3309/error.log

pid-file = /home/mysql3309/mysql3309.pid

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

gtid_mode=ON

log-slave-updates=ON

enforce-gtid-consistency=ON

binlog_format = ROW

log-bin=mysql-bin

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "10.60.143.137:3319"

loose-group_replication_group_seeds= "10.60.143.137:3317,10.60.143.137:3318,10.60.143.137:3319"

loose-group_replication_bootstrap_group=off

loose-group_replication_single_primary_mode=FALSE

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum = NONE

expire_logs_days = 3

#group_replication_allow_local_disjoint_gtids_join = ON

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@'%' IDENTIFIED BY '1';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='1'? FOR CHANNEL 'group_replication_recovery';

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

SHOW PLUGINS;

主庫

set global group_replication_bootstrap_group=ON;

START group_replication;

set global group_replication_bootstrap_group=OFF;

從庫

START group_replication;

SELECT * FROM performance_schema.replication_group_members;

mysqlsh

mysqlsh -uroot -p1 -S /tmp/mysql3307.sock

一般SQL

沙箱

dba.deploySandboxInstance(4410)

dba.deploySandboxInstance(5510)

dba.deploySandboxInstance(2210)

\connect root@localhost:4410

var cluster = dba.createCluster('xxg')

cluster.addInstance('root@localhost:5510')

cluster.addInstance('root@localhost:2210')

cluster.describe()

var s1 = mysql.getClassicSession('root@localhost:3307', '1');

var s1 = mysql.getClassicSession('root@localhost:3308', '1');

var s1 = mysql.getClassicSession('root@localhost:3309', '1');

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容