MySql主从部署_mysql主从架构部署

MySql主从部署

1、操作环境

硬件环境：香橙派5 aarch64架构软件环境：Ubuntu 22.04.3 LTS软件版本：mysql-8.0.42操作方式：mysql_1,mysql_2容器主节点：mysql_1启动命令：docker run --name mysql_master \\ -p 3308:3306 \\ -v /data/Test/mysql_master/data:/var/lib/mysql \\ -v /data/Test/mysql_master/conf:/etc/mysql/conf.d \\ -v /data/Test/mysql_master/logs:/var/log/mysql \\ -v /etc/localtime:/etc/localtime:ro \\ -e MYSQL_ROOT_PASSWORD=test0123 \\ -e MYSQL_DATABASE=myappdb \\ -e MYSQL_USER=myuser \\ -e MYSQL_PASSWORD=test0123 \\ --restart=always \\ -d mysql:8.0.42 \\ --default-authentication-plugin=mysql_native_password \\ --character-set-server=utf8mb4 \\ --collation-server=utf8mb4_unicode_ci从节点：mysql_2启动命令：docker run --name mysql_slave \\ -p 3309:3306 \\ -v /data/Test/mysql_slave/data:/var/lib/mysql \\ -v /data/Test/mysql_slave/conf:/etc/mysql/conf.d \\ -v /data/Test/mysql_slave/logs:/var/log/mysql \\ -v /etc/localtime:/etc/localtime:ro \\ -e MYSQL_ROOT_PASSWORD=test0123 \\ -e MYSQL_DATABASE=myappdb \\ -e MYSQL_USER=myuser \\ -e MYSQL_PASSWORD=test0123 \\ --restart=always \\ -d mysql:8.0.42 \\ --default-authentication-plugin=mysql_native_password \\ --character-set-server=utf8mb4 \\ --collation-server=utf8mb4_unicode_ci

2、配置操作

2.1、主节点配置

（1）、my.cnf配置（容器/etc/mysql/conf.d/my.cnf，宿主机/data/Test/mysql_master/conf/master.cnf）

[client]default-character-set=utf8mb4 [mysql]default-character-set=utf8mb4 [mysqld]# 设置时区default-time_zone = \'+8:00\'#设置密码验证规则authentication_policy=mysql_native_password# 限制导入和导出的数据目录# 为空，不限制导入到处的数据目录；secure_file_priv=init_connect=\'SET collation_connection = utf8mb4_general_ci\'init_connect=\'SET NAMES utf8mb4\'character-set-server=utf8mb4collation-server=utf8mb4_general_ciskip-character-set-client-handshakeskip-name-resolve # 开启logbinlog_bin=binlog# binlog日志格式binlog_format=ROW# mysql主从备份serverIdserver_id=1 #参数优化，避免mysql占用太多内存# 减少缓冲池大小innodb_buffer_pool_size = 64M# 降低临时表大小tmp_table_size = 16M# 设置最大连接数max_connections = 10

可选参数

# 0表示读写 (主机)，1表示只读(从机)read-only=0#设置日志文件保留的时长，单位是秒binlog_expire_logs_seconds=6000#控制单个二进制日志大小。此参数的最大和默认值是1GBmax_binlog_size=20#设置不要复制的数据库binlog-ignore-db=test#设置需要复制的数据库,不写参数则默认全部记录，可以填写多个binlog-do-db=需要复制的主数据库名字例如:binlog-do-db=dbtest01binlog-do-db=dbtest02#设置binlog格式binlog_format=STATEMENT

（2）、主节点数据库创建用户

#进入容器内部docker exec -it mysql_master /bin/bash#登录mysqlmysql -u root -p#查看数据库show databases;#切换到mysql库use mysql;#创建master用户create USER \'master\'@\'%\' IDENTIFIED BY \'root\';grant all on *.* to \'master\'@\'%\';FLUSH PRIVILEGES;

2.2、从节点配置

（1）、server.cnf配置（容器/etc/mysql/conf.d/server.cnf，宿主机的/data/Test/mysql_2/conf/server.cnf）

[client]default-character-set=utf8mb4 [mysql]default-character-set=utf8mb4 [mysqld]# 设置时区default-time_zone = \'+8:00\'#设置密码验证规则authentication_policy=mysql_native_password# 限制导入和导出的数据目录# 为空，不限制导入到处的数据目录；secure_file_priv=init_connect=\'SET collation_connection = utf8mb4_general_ci\'init_connect=\'SET NAMES utf8mb4\'character-set-server=utf8mb4collation-server=utf8mb4_general_ciskip-character-set-client-handshakeskip-name-resolve #server_id不要和主库的server_id相同就行server_id=2#一般从数据库作为读数据库default-storage-engine=MyISAM

（2）、从节点数据库创建用户

#进入容器内部docker exec -it mysql_slave /bin/bash#登录mysqlmysql -u root -p#查看数据库show databases;#切换到mysql库use mysql;#创建slave用户create USER \'slave\'@\'%\' IDENTIFIED BY \'root\';grant all on *.* to \'slave\'@\'%\';FLUSH PRIVILEGES;

2.3、配置数据同步

（1）、登录master节点数据库

#查看master节点binlog日志状态mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000003 | 841 |  |  |  |+---------------+----------+--------------+------------------+-------------------+

（1）、登录slave节点数据库

#停止slavestop slave;#查看master节点binlog日志状态，MASTER_LOG_FILE、MASTER_LOG_POS根据master数据库查询信息配置 CHANGE MASTER TO MASTER_HOST = \'172.17.0.5\', #host MASTER_PORT = 3306,# MASTER_USER = \'master\',#binlog MASTER_PASSWORD = \'passwd\',#binlog MASTER_LOG_FILE = \'binlog.000003\',#binlog1 MASTER_LOG_POS = 841; #启动slavestart slave;#查看slave库状态mysql> show slave status\\G;*************************** 1. row ***************************  Slave_IO_State: Waiting for source to send event  Master_Host: 172.17.0.5  Master_User: slave  Master_Port: 3306 Connect_Retry: 60  Master_Log_File: binlog.000003 Read_Master_Log_Pos: 841  Relay_Log_File: 4b87ab620b91-relay-bin.000002 Relay_Log_Pos: 323 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes  Replicate_Do_DB:  Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:  Last_Errno: 0  Last_Error:  Skip_Counter: 0 Exec_Master_Log_Pos: 841  Relay_Log_Space: 540  Until_Condition: None  Until_Log_File:  Until_Log_Pos: 0  Master_SSL_Allowed: No  Master_SSL_CA_File: Master_SSL_CA_Path:  Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0  Last_SQL_Error: Replicate_Ignore_Server_Ids:  Master_Server_Id: 1  Master_UUID: 61357c39-2e76-11f0-9f6f-0242ac110005 Master_Info_File: mysql.slave_master_info  SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates  Master_Retry_Count: 86400  Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set:  Auto_Position: 0 Replicate_Rewrite_DB:  Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.01 sec)

2.4、测试

（1）、master节点创建测试表及插入数据

mysql -u root -h 127.0.0.1 -p -P3308#登录master节点创建简单数据表CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT, `Test_str` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;mysql> show tables;+-------------------+| Tables_in_myappdb |+-------------------+| test  |+-------------------+1 row in set (0.00 sec)mysql> select * from test;Empty set (0.00 sec)mysql> INSERT INTO test (Test_str) VALUES( \'1\');Query OK, 1 row affected (0.02 sec)mysql> select * from test;+----+----------+| id | Test_str |+----+----------+| 1 | 1 |+----+----------+1 row in set (0.00 sec)

（2）、slave节点查看数据同步

mysql -u root -h 127.0.0.1 -p -P3309mysql> use myappdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-------------------+| Tables_in_myappdb |+-------------------+| test  |+-------------------+1 row in set (0.01 sec)mysql> select * from test;+----+----------+| id | Test_str |+----+----------+| 1 | 1 |+----+----------+1 row in set (0.01 sec)