作者：手机用户2502880821 | 来源：互联网 | 2023-09-16 13:50
一、安装mysql软件
1.上传并解压mysql-8.0.28-winx64.zip安装包
2.初始化
8.0版本，初始化，以管理员方式运行cmd
C:\Mysql8.0\bin\mysqld
3.编辑C:\Mysql8.0\my.ini 配置文件
[mysqld]
basedir = C:\Mysql8.0
datadir = C:\Mysql8.0\Data
port=3306
server_id=138
character-set-server = utf8mb4
default-time-zOne= '+8:00'
log_timestamps = SYSTEM
secure_file_priv=
default_authentication_plugin=mysql_native_password
skip_name_resolve
log_bin_trust_function_creators = 1
open_files_limit = 65535
max_cOnnections= 2000
max_connect_errors = 10000
table_open_cache = 1024
max_allowed_packet = 32M
log-bin
binlog_format = row
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
innodb_buffer_pool_size = 10240M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项
expire_logs_days = 30
#配置GTID同步参数
gtid-mode=ON
enforce-gtid-cOnsistency=on
relay_log_recovery = 1
log_slave_updates=1
master_info_repository = TABLE
relay_log_info_repository = TABLE
report_host=xxx.xxx.xxx
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
4.安装mysql服务
服务名称设置为Mysql_Server8.0
C:\Mysql8.0\bin\mysqld install Mysql_Server8.0
5.启动Mysql_Server8.0服务
net start Mysql_Server8.0
6.配置环境变量
setx PATH "C:\Mysql8.0\bin"
7.修改密码和允许远程登陆
C:\Users\Administrator>mysql -uroot -p
Enter password:
ERROR 1130 (HY000): Host &＃x27;::1&＃x27; is not allowed to connect to this MySQL server
解决办法：在my.ini文件中加入下面参数，然后重启mysql服务
skip-grant-tables
shared-memory
修改密码
C:\Users\Administrator>mysql -uroot -p
mysql> flush privileges;
mysql> alter user root@&＃x27;localhost&＃x27; identified with mysql_native_password by &＃x27;root123&＃x27;;
mysql> grant all on *.* to root@&＃x27;localhost&＃x27; with grant option;
mysql> create user root@&＃x27;%&＃x27; identified with mysql_native_password by &＃x27;root123&＃x27;;
mysql> grant all on *.* to root@&＃x27;%&＃x27; with grant option;
mysql> flush privileges;
mysql> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
注：mysql 8.0远程连接，在参数文件的[mysqld]下添加：
default_authentication_plugin=mysql_native_password
二、搭建主从
1. 配置master my.cnf文件
[mysqld]
basedir = C:\Mysql8.0
datadir = C:\Mysql8.0\Data
port=3306
server_id=137
character-set-server = utf8mb4
default-time-zOne= &＃x27;+8:00&＃x27;
log_timestamps = SYSTEM
secure_file_priv=
default_authentication_plugin=mysql_native_password
skip_name_resolve
log_bin_trust_function_creators = 1
open_files_limit = 65535
max_cOnnections= 2000
max_connect_errors = 10000
table_open_cache = 1024
max_allowed_packet = 32M
log-bin
binlog_format = row
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
innodb_buffer_pool_size = 10240M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
expire_logs_days = 30
gtid-mode=ON
enforce-gtid-cOnsistency=on
relay_log_recovery = 1
log_slave_updates=1
master_info_repository = TABLE
relay_log_info_repository = TABLE
report_host=xxx.xxx.xxx.xxx
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
2. 配置slave my.cnf文件
[mysqld]
basedir = C:\Mysql8.0
datadir = C:\Mysql8.0\Data
port=3306
server_id=138
character-set-server = utf8mb4
default-time-zOne= &＃x27;+8:00&＃x27;
log_timestamps = SYSTEM
secure_file_priv=
default_authentication_plugin=mysql_native_password
skip_name_resolve
log_bin_trust_function_creators = 1
open_files_limit = 65535
max_cOnnections= 2000
max_connect_errors = 10000
table_open_cache = 1024
max_allowed_packet = 32M
log-bin
binlog_format = row
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
innodb_buffer_pool_size = 10240M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
expire_logs_days = 30
gtid-mode=ON
enforce-gtid-cOnsistency=on
relay_log_recovery = 1
log_slave_updates=1
master_info_repository = TABLE
relay_log_info_repository = TABLE
report_host=xxx.xxx.xxx.xxx
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
3. 重启master. slave 服务
mysql> flush privileges;
mysql> show master status;
4. 主库创建复制用户
mysql> create user repl@&＃x27;%&＃x27; identified with mysql_native_password by &＃x27;repl123&＃x27;;
mysql> grant all on *.* to repl@&＃x27;%&＃x27; with grant option;
mysql> select user,host,grant_priv,password_last_changed,authentication_string from mysql.user;
5. 主库创建测试库
mysql> create database testdb;
mysql> use testdb;
mysql> create table mytb1(id int,name varchar(30));
mysql> insert into mytb1 values(1,&＃x27;a&＃x27;),(2,&＃x27;b&＃x27;);
6.主库导出
– 搭建期间，导出时不能加–set-gtid-purged=off参数
mysqldump -uroot -proot123
注意，8.0版本，不要用master-data参数，否则备库导入会抛出如下错误：
C:\Users\Administrator>mysql -uroot -proot123 mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &＃x27;WARNING: --master-data is deprecated and will be removed in a future version. Us&＃x27; at line 1
WARNING:
7. 备库导入
因为不是干净的备库，需要先执行reset master，否则导入会报错
mysql> reset master;
mysql> show master status;
mysql -uroot -proot123 mysql> show databases;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+
5 rows in set (0.00 sec)
8. 备库配置GTID同步
mysql> change master to
master_host=&＃x27;xxx.xxx.xxx.xxx&＃x27;,
master_port=3306,
master_user=&＃x27;repl&＃x27;,
master_password=&＃x27;repl123&＃x27;,
master_auto_position=1;
9. 备库启动同步
mysql> start slave;
mysql> show slave status \G;
10. 测试同步状态
– 主库更新数据：
mysql> insert into mytb1 values(3,&＃x27;c&＃x27;),(4,&＃x27;d&＃x27;);
–备库检查同步:
mysql> select * From testdb.mytb1;
结论： 同步正常。
京公网安备 11010802041100号