windows mysql 主从同步
2021/11/21 2:11:57
本文主要是介绍windows mysql 主从同步,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
安装数据库 1.安装多个MYSQL数据库(具体安装步骤,详见文档 “windows mysql8 安装” ) 主库 1.在主库中my.ini文件中添加配置信息log-bin = mysql-bin #主从复制 表示启动二进制文件 server_id = 3306 #server_id编号 编号唯一 可以默认为端口号 binlog-do-db=quartz_backjob #设置同步的数据库 #binlog-ignore-db=mysql #可以设置不需要同步的数据库具体的配置项目参照如下
For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server=utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # log_bin #MYSQL 安装目录 basedir = D:\mysql-8.0.25-winx64 #MYSQL 数据存放目录 datadir = D:\mysql-8.0.25-winx64\data #mysql端口 port = 3306 #主从复制 表示启动二进制文件 log-bin = mysql-bin #server_id编号 编号唯一 可以默认为端口号 server_id = 3306 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. join_buffer_size = 128M sort_buffer_size = 16M read_rnd_buffer_size = 16M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #同步的数据库 binlog-do-db=quartz_backjob #不需要同步的数据库 #binlog-ignore-db=mysql2.重启mysql服务 3.使用root用户登录,(注意:一定要先cmd到mysql安装目录中的bin目录下)
mysql -u root -p
4.创建slave账号,并赋值权限,命令如下
mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'slave123' mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' ; mysql> FLUSH PRIVILEGES;
5.查看主数据库状态,注意这里的参数:File与Position,后续从库配置中会使用到
show master status;
从库 my.ini具体的配置项如下
For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server=utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # log_bin #MYSQL 安装目录 basedir = E:\mysql #MYSQL 数据存放目录 datadir = E:\mysql\data #mysql端口 port = 3307 #server_id编号 编号唯一 可以默认为端口号 server_id = 3307 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. join_buffer_size = 128M sort_buffer_size = 16M read_rnd_buffer_size = 16M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #同步的数据库 binlog-do-db=quartz_backjob #不需要同步的数据库 #binlog-ignore-db=mysql1.登录从数据库
mysql -P3307 -uroot -p #3307为当前从数据库mysql端口2.查看从数据库状态
show slave status;
3.在从数据库中配置主数据库的信息
change master to master_host='192.168.0.63',master_port=3306,master_user='slave',master_password='slave123',master_log_file='mysql-bin.000001',master_log_pos=820;参数解析: master_host:Master数据库地址 master_port:Master数据库端口 master_user:用户名 master_log_file:对应Master数据库二进制日志文件 master_log_pos:对应Master数据库Position 4.启动slave
start slave;
5.查看运行状态
show slave status\G当这下面两个属性为Yes的时候,配置成功。 Slave_IO_Running: Yes Slave_SQL_Running: Yes 注意:若出现同步失败(上面没显示两个yes),请查看Last_Error的错误信息,若错误信息为“Unknown database 'xxx'”,则可以将主数据库数据和表结构全部导出,并将此数据在从据库中导入。导入成功后,执行下面的语句再确保是否出错
stop slave; start slave; show slave status\G6.测试。将主库中的数据修改,查看从库是否同步成功。
这篇关于windows mysql 主从同步的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-21MySQL集群部署资料:新手入门教程
- 2024-09-21MySQL集群资料:初学者入门指南
- 2024-09-21部署MySQL集群资料:新手入门教程
- 2024-09-20MySQL集群部署教程:新手入门指南
- 2024-09-20MySQL集群教程:初学者必备指南
- 2024-09-20部署MySQL集群项目实战:新手入门教程
- 2024-09-20如何部署MySQL集群:简单教程
- 2024-09-20MySQL集群部署:新手入门指南
- 2024-09-20部署MySQL集群学习:入门指南
- 2024-09-20部署MySQL集群入门教程