一、部署实例
1.下载2个软件
http://ftp.kaist.ac.kr/mysql/Downloads/MySQL-5.7/mysql-5.7.35-winx64.ziphttp://ftp.kaist.ac.kr/mysql/Downloads/MySQL-8.0/mysql-8.0.26-winx64.zip
2.解压一下
3.初始化
- 记录下密码
C:UsersAdministrator>D:testmysql-5.7.35-winx64binmysqld --initialize --console2022-01-20T03:37:59.769162Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2022-01-20T03:38:03.808644Z 0 [Warning] InnoDB: New log files created, LSN=457902022-01-20T03:38:03.901775Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2022-01-20T03:38:04.079914Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5fd0428e-79a2-11ec-b336-00155d9ebf27.2022-01-20T03:38:04.092275Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2022-01-20T03:38:05.824394Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.2022-01-20T03:38:05.824844Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.2022-01-20T03:38:05.826505Z 0 [Warning] CA certificate ca.pem is self signed.2022-01-20T03:38:07.018691Z 1 [Note] A temporary password is generated for root@localhost: pE.Yu96:XQEp
- 初始化数据如下图，默认是在安装程序的data目录下面
- 把初始化的数据拷贝到自定义的数据目录。
- 添加下my.ini配置
[mysqld]port=3306character_set_server=utf8basedir=D:/test/mysql-5.7.35-winx64datadir=D:/test/data3306server-id=1sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONlower_case_table_names=1innodb_file_per_table = 1log_timestamps=SYSTEMlog-error = error.logslow_query_log = 1slow_query_log_file = slow.loglong_query_time = 5log-bin = binlogbinlog_format = rowexpire_logs_days = 15log_bin_trust_function_creators = 1[client] default-character-set=utf8
4.安装服务
C:UsersAdministrator>D:testmysql-5.7.35-winx64binmysqld --install mysql3306 --defaults-file=D:testdata3306my.iniService successfully installed.
5.开启
C:UsersAdministrator>net start mysql3306mysql3306 服务正在启动 .mysql3306 服务已经启动成功。
-----------------以上安装了一个3306，依葫芦画瓢再安装一个3307---------------------
D:testmysql-5.7.35-winx64binmysqld --initialize --console2022-01-20T05:38:55.054718Z 1 [Note] A temporary password is generated for root@localhost: E)+H2UUpeJRfD:testmysql-5.7.35-winx64binmysqld --install mysql3307 --defaults-file=D:testdata3307my.inimysql3307 服务正在启动 .mysql3307 服务已经启动成功。
- 查看服务信息
6.修改密码
C:UsersAdministrator>cd /d d:testmysql-5.7.35-winx64bind:testmysql-5.7.35-winx64bin>mysql -uroot -ppE.Yu96:XQEpmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2Server version: 5.7.35-logCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'GreatSQL';Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)# 然后重新登陆一下d:testmysql-5.7.35-winx64bin>mysql -uroot -p"GreatSQL" -P3307
二、建立主从
1.主节点建表等
mysql> create table t1(id int(1) not null auto_increment , name varchar(64),primary key(id));Query OK, 0 rows affected (0.06 sec)mysql> insert into t1 values(1,'GreatSQL');Query OK, 1 row affected (0.01 sec)
2.迁移数据
d:testmysql-5.7.35-winx64bin>mysqldump.exe -uroot -pGreatSQL -P3306 --single-transaction --master_data=2 --set-gtid-purged=OFF --databases test > d:test.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.d:testmysql-5.7.35-winx64bin>mysql -uroot -pGreatSQL -P3307
3.主节点赋权
CREATE USER `sync`@`127.0.0.1` IDENTIFIED BY 'sync';GRANT Replication Slave ON *.* TO `sync`@`127.0.0.1`;flush privileges;
4.看下dump文件的pos值
d:testmysql-5.7.35-winx64bin>more d:test.sql-- MySQL dump 10.13 Distrib 5.7.35, for Win64 (x86_64)......-- Current Database: `test`-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1350;
5.从节点建立同步
change master to master_host="127.0.0.1",master_port=3306,MASTER_USER='sync',MASTER_PASSWORD='sync',MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1350;start slave;
6.完成同步
mysql> show slave statusG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 1959 Relay_Log_File: YOS-DAAJTV9J5RF-relay-bin.000003 Relay_Log_Pos: 926 Relay_Master_Log_File: binlog.000002 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: 1959 Relay_Log_Space: 1143 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: 5fd0428e-79a2-11ec-b336-00155d9ebf27 Master_Info_File: D:testdata3307master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave 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:1 row in set (0.00 sec)ERROR:No query specified
三、升级8.0
1.部署mysql8
d:testmysql-5.7.35-winx64bin>D:testmysql-8.0.26-winx64binmysqld --installmysql8_3307 --defaults-file=D:testdata3306my.iniService successfully installed.
2.停掉原来的
d:testmysql-5.7.35-winx64bin>net stop mysql3307mysql3307 服务正在停止.mysql3307 服务已成功停止。
3.修改下配置
[mysqld]port=3307character_set_server=utf8basedir=D:/test/mysql-8.0.26-winx64 #路径改了datadir=D:/test/data3307 server-id=2sql_mode=NO_ENGINE_SUBSTITUTION #sql_mode 改了lower_case_table_names=1innodb_file_per_table = 1log_timestamps=SYSTEMlog-error = error.logslow_query_log = 1slow_query_log_file = slow.loglong_query_time = 5log-bin = binlogbinlog_format = rowexpire_logs_days = 15log_bin_trust_function_creators = 1[client] default-character-set=utf8
4.开启服务，会自动升级
net start mysql8_3307
5.查看日志升级成功了
2022-01-20T17:33:33.598491+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.2022-01-20T17:33:37.652733+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80026' started.2022-01-20T17:33:53.602051+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80026' completed.
总结
- 篇幅所限暂时写这么多，受限于个人能力，内容难免错漏，若有错误欢迎评论区指出更正。
Enjoy GreatSQL 🙂
