如何制作和设计公司网站,天津网络营销,灰色项目网站代做,娄底建设局网站双主多从架构原理介绍IP端口账号密码服务名10.1.1.23306root/syncabc123!数据库A10.1.1.33306root/syncabc123!数据库B10.1.1.43306root/syncabc123!数据库C1.两台mysql都可读写#xff0c;互为主备#xff0c;默认只使用一台#xff08;masterA#xff09;负责数据的写入…双主多从架构原理介绍IP端口账号密码服务名10.1.1.23306root/syncabc123!数据库A10.1.1.33306root/syncabc123!数据库B10.1.1.43306root/syncabc123!数据库C1.两台mysql都可读写互为主备默认只使用一台masterA负责数据的写入另一台masterB备用2.masterA是masterB的主库masterB又是masterA的主库它们互为主从3.两台主库之间做高可用,可以采用MyCAT中间件暴露一个统一的服务给client,同时当2主任意一台宕机时还能提供服务。宕掉的机器重启恢复之后数据可以同步得到4.所有提供服务的从服务器与masterB进行主从同步双主多从;#创建目录 mkdir -p /home/docker-compose/mysql cd /home/docker-compose/mysql/ mkdir data conf vi docker-compose.ymlversion: 3.1
services:mysql:restart: alwaysimage: mysql:5.7container_name: mysqlports:- 3306:3306environment:TZ: Asia/ShanghaiMYSQL_ROOT_PASSWORD: abc123! ##密码command:--character-set-serverutf8mb4--collation-serverutf8mb4_general_ci--explicit_defaults_for_timestamptrue--lower_case_table_names1--max_allowed_packet128M--sql-modeSTRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZEROvolumes:- ./data:/var/lib/mysql- ./conf:/etc/mysql/mysql.conf.d进入目录cd /home/docker-compose/mysql/conf编写MySQL配置文件vi mysqld.cnf主节点1配置[mysqld]
pid-file /var/run/mysqld/mysqld.pid
socket /var/run/mysqld/mysqld.sock
datadir /var/lib/mysql
default-time-zone8:00
symbolic-links0
server-id 40
log-bin mysql-bin
enforce_gtid_consistency ON
gtid_mode ON
auto_increment_increment1
auto_increment_offset1
log-slave-updates
sync_binlog1
max_connections1000
innodb_file_per_table1
innodb_file_formatBarracuda
innodb_page_size32K
innodb_log_file_size148M主节点2配置[mysqld]
pid-file /var/run/mysqld/mysqld.pid
socket /var/run/mysqld/mysqld.sock
datadir /var/lib/mysql
default-time-zone8:00
symbolic-links0
server-id 66
log-bin mysql-bin
enforce_gtid_consistency ON
gtid_mode ON
auto_increment_increment2
auto_increment_offset2
log-slave-updates
sync_binlog1
max_connections1000
innodb_file_per_table1
innodb_file_formatBarracuda
innodb_page_size32K
innodb_log_file_size148启动mysqldocker-compose up -d
docker-compose down //直接删除容器
docker-compose stop //不删除容器
docker-compose start //直接启动进入容器docker exec -it mysql /bin/bash
mysql -uroot -pabc123!设置2个主节点的主从关系查看10.1.1.2的日志信息: File: mysql-bin.000005Position: 1495mysql show master status G;
*************************** 1. row ***************************File: mysql-bin.000005Position: 1495Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set: c83eb7e9-2556-11eb-b4ad-0242ac120002:20-21,
d52d0074-2556-11eb-93b5-0242ac120002:1-20
1 row in set (0.00 sec)ERROR:
No query specifiedmysql 查看10.1.1.3的日志信息: File: mysql-bin.000004Position: 1796mysql show master status G;
*************************** 1. row ***************************File: mysql-bin.000004Position: 1796Binlog_Do_DB: Binlog_Ignore_DB:
Executed_Gtid_Set: c83eb7e9-2556-11eb-b4ad-0242ac120002:1-21,
d52d0074-2556-11eb-93b5-0242ac120002:20
1 row in set (0.00 sec)ERROR:
No query specifiedmysql 在两个节点执行create user sync% identified by password;
grant replication slave on *.* to sync;##Mysql复制错误error_code: 1045 Access denied for usergrant replication slave on *.* to sync% identified by password;flush privileges; //刷新MySQL的系统权限相关表否则会无法用账号链接修改master_host与master_log_file与master_log_pos三个值#节点设置 需要同步的节点主库节点 #在10.1.1.2设置FLUSH TABLES WITH READ LOCK;
change master to master_host10.1.1.3,master_port3306,master_usersync,master_passwordpassword,master_log_filemysql-bin.000004,master_log_pos1796;
UNLOCK TABLES; #在10.1.1.3设置FLUSH TABLES WITH READ LOCK;
change master to master_host10.1.1.2,master_port3306,master_usersync,master_passwordpassword,master_log_filemysql-bin.000005,master_log_pos1495;
UNLOCK TABLES;开启主从复制 :start slave;检查mysql show slave status G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.1.1.3Master_User: syncMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 1796Relay_Log_File: a389ed970e74-relay-bin.000005Relay_Log_Pos: 822Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 以下三项为正常Slave_IO_State: Waiting for master to send eventSlave_IO_Running: Yes Slave_SQL_Running: Yes未完待续
