2台mysql集群_mysql集群几台MySQL多实例实现半同步复制

MySQL多实例实现半同步复制

W WW.002pc .COM认为此文章对《mysql集群几台MySQL多实例实现半同步复制》说的很在理。

MySQL多实例实现半同步复制主库1：192.168.40.150主库2：192.168.40.161从库(2个MySQL实例)：192.168.40.162一：分别在192.168.40.161和192.168.40.150使用【show master status;】记录当前的复制位置如下所示mysql> show master status;+--------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+--------------+----------+--------------+------------------+-------------------+| mysql.000006 | 11426362 | | | |+--------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)二：master(分别在192.168.40.161和192.168.40.150进行如下操作)1.启用二进制日志log_bin=mysqllog-bin-index=mysql-index2.为master选择一个在当前复制架构中惟一的server-idserver-id={0-2^32}3.创建一个具有复制权限的用户帐号mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.40.162' IDENTIFIED BY 'replpass';mysql> FLUSH PRIVILEGES;4.分别master的配置文件中的mysqld段添加如下一行，并重启服务rpl_semi_sync_master_enabled=ON (或者：mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';)mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON;mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;5.配置主从复制时的事务安全:在主服务器上mysqld段配置：sync_binlog=16.重启mysql服务二、slave(在192.168.40.162的两个mysql实例中进行如下操作)1.在192.168.40.162配置两个实例:socket端口分别为3306和3307，具体配置请参考配置文件。2.创建所需要的目录mkdir -p /data/{mysql3306,mysql3307}3.分别对192.168.40.161和192.168.40.150的数据目录打包，然后分别上传至192.168.40.162的/data/mysql3306和/data/mysql3307目录下例如：# cd /data/mysql/# tar zcf mysql_162.tar.gz ./*# scp mysql_162.tar.gz 192.168.40.162:/data/mysql3306/4.启用中继日志(并关闭二进制日志)relay-log=/data/mysql3306/relay-3306.logrelay-log-index=/data/mysql3306/relay-log-index-3306.log5.为slave选择一个在当前复制架构中惟一的server-idserver-id={0-2^32}6.为slave选择一个在当前复制架构中惟一的server-uuid修数据目录下的auto.cnf 修数据目录下的auto.cnf 的server-uuid如将server-uuid=3fd1f0a1-b34e-11e4-996a-000c29b1b59d修改为server-uuid=3fd1f0a1-b34e-11e4-996a-000c29b1b52d5、复制过滤器slave:replicate-ignore-db=mysqlreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schema8.重启mysql服务9.开启半同步复制从服务器：mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';mysql> SET GLOBAL rpl_semi_sync_slave_enabled=ON;在主服务器验正半同步复制是否生效：mysql> SHOW GLOBAL STATUS LIKE '%semi%';9.连接至主服务器mysql> CHANGE MASTER TO MASTER_HOST='192.168.40.161', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_PORT=3306, MASTER_LOG_FILE='mysql.000006', MASTER_LOG_POS=11426362;mysql> START SLAVE; 10.检查主从是否成功mysql> show slave status;

主服务器配置文件：

[root@db_peizi1 ~]# more /etc/my.cnf

[client]

port=3306

socket = /usr/local/mysql/mysql.sock

[mysql]

default-character-set=utf8

[mysqld]

port = 3306

socket = /usr/local/mysql/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql

server_id=1

user=mysql

skip-name-resolve

log_bin=mysql

expire_logs_days = 30

sync_binlog=1

rpl_semi_sync_master_enabled=ON

#slow_log

slow-query-log=On

slow_query_log_file=/data/logs/mysql/peizi-slow.log

long_query_time=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

character-set-server=utf8

default-storage-engine=InnoDB

explicit_defaults_for_timestamp=true

skip-external-locking

max_connections=300

query_cache_size=1048576

performance_schema_max_table_instances=600

table_definition_cache=400

table_open_cache=256

tmp_table_size=64M

max_heap_table_size=64M

thread_cache_size=16

myisam_max_sort_file_size=16G

myisam_sort_buffer_size=32M

key_buffer_size=25M

read_buffer_size=128K

read_rnd_buffer_size=256K

sort_buffer_size=256K

join_buffer_size=16M

max_allowed_packet=4M

innodb_file_per_table=1

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M

innodb_buffer_pool_size=64M

innodb_log_file_size=16M

innodb_thread_concurrency=8

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysqld_safe]

log-error=/data/logs/mysql/mysqld.log

pid-file=/usr/local/mysql/tmp/mysqld.pid

多实例从服务器配置文件：

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysql]

default-character-set=utf8

[mysqld1]

port = 3306

socket = /usr/local/mysql/mysql-3306.sock

basedir=/usr/local/mysql

datadir=/data/mysql3306

pid-file=/var/lock/subsys/mysql3306/mysq-3306.pid

server_id=11

user=mysql

skip-name-resolve

expire_logs_days = 30

master-info-file=/data/mysql3306/master-3306.info

read-only

relay-log=/data/mysql3306/relay-3306.log

relay-log-index=/data/mysql3306/relay-log-index-3306.log

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

character-set-server=utf8

default-storage-engine=InnoDB

explicit_defaults_for_timestamp=true

skip-external-locking

max_connections=300

query_cache_size=1048576

performance_schema_max_table_instances=600

table_definition_cache=400

table_open_cache=256

tmp_table_size=64M

thread_cache_size=16

myisam_max_sort_file_size=16G

myisam_sort_buffer_size=32M

key_buffer_size=25M

read_buffer_size=128K

read_rnd_buffer_size=256K

sort_buffer_size=256K

join_buffer_size=16M

max_allowed_packet=4M

innodb_file_per_table=1

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M

innodb_buffer_pool_size=64M

innodb_log_file_size=8M

innodb_thread_concurrency=8

lower_case_table_names=1

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysqld2]

port = 3307

socket = /usr/local/mysql/mysql-3307.sock

basedir=/usr/local/mysql

datadir=/data/mysql3307

pid-file=/var/lock/subsys/mysql3307/mysql-3307.pid

server_id=6

user=mysql

skip-name-resolve

expire_logs_days = 30

master-info-file=/data/mysql3307/master-3307.info

read-only

relay-log=/data/mysql3307/relay-3307.log

relay-log-index=/data/mysql3307/relay-log-index-3307.log

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

character-set-server=utf8

default-storage-engine=InnoDB

explicit_defaults_for_timestamp=true

skip-external-locking

max_connections=300

query_cache_size=1048576

performance_schema_max_table_instances=600

table_definition_cache=400

table_open_cache=256

tmp_table_size=64M

thread_cache_size=16

myisam_max_sort_file_size=16G

myisam_sort_buffer_size=32M

key_buffer_size=25M

read_buffer_size=128K

read_rnd_buffer_size=256K

sort_buffer_size=256K

join_buffer_size=16M

max_allowed_packet=4M

innodb_file_per_table=1

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M

innodb_buffer_pool_size=64M

innodb_log_file_size=8M

innodb_thread_concurrency=8

lower_case_table_names=1

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

更多:mysql集群几台MySQL多实例实现半同步复制

https://www.002pc.comhttps://www.002pc.com/mysql/1791.html

你可能感兴趣的MySQL,实例,复制,同步,实现

No alive nodes found in your cluster

0踩

赏

0 赞