个人网站需要什么内容,临沂网站排名优化,广告营销方式,上市公司网站维护因项目需求需要部署主从MySQL集群#xff0c;继续采用上次的部署的MySQL镜像arm64v8/mysql:latest#xff0c;版本信息为v8.1.0。计划部署服务器192.168.31.100和192.168.31.101
部署MySQL主节点
在192.168.31.100上先创建好/data/docker/mysql/data和/data/docker/mysql/l…因项目需求需要部署主从MySQL集群继续采用上次的部署的MySQL镜像arm64v8/mysql:latest版本信息为v8.1.0。计划部署服务器192.168.31.100和192.168.31.101
部署MySQL主节点
在192.168.31.100上先创建好/data/docker/mysql/data和/data/docker/mysql/logs目录创建好/data/docker/mysql/my.cnf文件内容如下
[mysqld]
server-id100
log-binmysql-bin
port3306
max_connections200
max_connect_errors10
character-set-serverutf8
default-storage-engineINNODB
default_authentication_pluginmysql_native_password
sql_modeNO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances600[mysql]
default-character-setutf8[client]
port3306
default-character-setutf8然后创建MySQL镜像执行
docker run --networkhost -d -p 3306:3306 \
--name mysql --privilegedtrue \
-e MYSQL_ROOT_PASSWORD888888 -e MYSQL_USERadmin -e MYSQL_PASSWORD888888 \
-v/data/docker/mysql/my.cnf:/etc/my.cnf \
-v/data/docker/mysql/data:/var/lib/mysql \
-v/data/docker/mysql/logs:/var/log \
-v/etc/localtime:/etc/localtime \
arm64v8/mysql:latest --server-id100执行成功后检查MySQL是否启动成功执行docker ps出现如下内容表示启动成功
[rootserver01 mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
50214a2de4f8 arm64v8/mysql:latest docker-entrypoint.s… 17 minutes ago Up 17 minutes mysql进入镜像docker exec -it mysql /bin/bash 登录MySQLmysql -uroot -p 依次执行如下命令
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 888888;
CREATE USER slave% IDENTIFIED BY 888888;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave%;
ALTER USER slave% IDENTIFIED WITH mysql_native_password BY 888888;
FLUSH PRIVILEGES;然后执行show master status命令
mysql show master status;
----------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
----------------------------------------------------------------------------
| binlog.000002 | 2925 | | | |
----------------------------------------------------------------------------
1 row in set (0.00 sec)注意这里的File和Position很重要后面会用到。
部署MySQL从节点
在192.168.31.101上先创建好/data/docker/mysql/data和/data/docker/mysql/logs目录创建好/data/docker/mysql/my.cnf文件内容如下
[mysqld]
server-id101
log-binmysql-bin
port3306
max_connections200
max_connect_errors10
character-set-serverutf8
default-storage-engineINNODB
default_authentication_pluginmysql_native_password
sql_modeNO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances600[mysql]
default-character-setutf8[client]
port3306
default-character-setutf8然后创建MySQL镜像执行
docker run --networkhost -d -p 3306:3306 \
--name mysql --privilegedtrue \
-e MYSQL_ROOT_PASSWORD888888 -e MYSQL_USERadmin -e MYSQL_PASSWORD888888 \
-v/data/docker/mysql/my.cnf:/etc/my.cnf \
-v/data/docker/mysql/data:/var/lib/mysql \
-v/data/docker/mysql/logs:/var/log \
-v/etc/localtime:/etc/localtime \
arm64v8/mysql:latest --server-id101执行成功后检查MySQL是否启动成功执行docker ps出现如下内容表示启动成功
[rootserver02 mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f6793b5e6ded arm64v8/mysql:latest docker-entrypoint.s… 21 minutes ago Up 21 minutes mysql进入镜像docker exec -it mysql /bin/bash 登录MySQLmysql -uroot -p 依次执行如下命令
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 888888;
FLUSH PRIVILEGES;配置从节点
change master to master_host192.168.31.100, master_userslave, master_password888888, master_port3306, master_log_filebinlog.000002, master_log_pos2925, master_connect_retry30;注意这里的master_log_file和master_log_pos需要和前面主节点匹配起来具体参数说明如下
# master_host Master库的地址
# master_portMaster的端口号指的是容器的端口号
# master_user用于数据同步的用户
# master_password用于同步的用户的密码
# master_log_file指定 Slave 从哪个日志文件开始复制数据即上文中提到的 File 字段的值
# master_log_pos从哪个 Position 开始读即上文中提到的 Position 字段的值
# master_connect_retry如果连接失败重试的时间间隔单位是秒默认是60秒然后开通同步执行start slave; 如果需要关闭同步执行stop slave; 然后执行show slave status命令
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Waiting for source to send event | 10.16.39.9 | slave | 3306 | 30 | binlog.000002 | 2925 | ecs02-relay-bin.000002 | 1804 | binlog.000002 | Yes | Yes | | | | | | | 0 | | 0 | 2925 | 2014 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 100 | 7ec2b8d1-ae21-11ef-9e2a-fa163e637a5c | mysql.slave_master_info | 0 | NULL | Replica has read all relay log; waiting for more updates | 10 | | | | | | | | 0 | | | | | 0 | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------关键看Slave_SQL_Running_State列没有报错即可。 至此MySQL主从配置已经全部部署好。
验证测试
在主节点上创建一个数据库test并创建一个表插入几条数据 从节点上截图如下
