mysql主从复制配置

一、复制两个mysql

二、配置两个my.ini

1.主库my.ini

[mysqld] [mysqld] server-id=1 log-bin=mysql-bin binlog-format=STATEMENT expire_logs_days=7 gtid_mode=ON enforce_gtid_consistency=ON innodb_file_per_table=1 default-time-zone='+8:00' sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2.从库my.ini

[mysqld] port=3307 server-id=2 #relay-log=relay-bin gtid_mode=ON enforce_gtid_consistency=ON #不复制用户、权限等，从库有自己的用户权限。其他都全部复制 replicate_ignore_db=mysql innodb_file_per_table=1 default-time-zone='+8:00' sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

注意，从库配置了3307端口，因为我在一台机器上测试。端口不能重复。

3.分别启动两个数据库

可以开黑框启动，也可以作为服务启动。我这里用的是mysqld --console启动黑框。

注意观察端口，主库是3306，从库是3307

三、配置用户（在主库上操作）

我用navcate操作。

1.新增用户

也可以用命令行创建用户
CREATE USER ‘username’@‘ip’ IDENTIFIED BY ‘password’;

2.给用户授权可以复制

也可以用命令行授权
#授权用户复制的权限（从服务器ip）
GRANT REPLICATION SLAVE ON.TO ‘username’@‘ip’;

3.查看log的位置

执行如下两个命令，并且记住其中的内容

FLUSH PRIVILEGES; show master status;

四、配置从库（新方式GTID，推荐）

可以采用开启GTID-自动点位复制，不用自己写binlog文件名+pos点位

（1）主库、从库都要开启GTID

在主库、从库的ini文件中添加以下两行

[mysqld] gtid_mode=ON enforce_gtid_consistency=ON

重启主库、从库

（2）从库change master to

STOP SLAVE;-- 彻底清除master.info、relay‑log.info，清空中继日志（replay-bin.*)文件-- 内存清除主库连接配置，**必须重新执行 CHANGE MASTER TO**RESET SLAVEALL;CHANGE MASTERTOMASTER_HOST='主库ip',MASTER_USER='repl',MASTER_PASSWORD='Repl@123456',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;STARTSLAVE;SHOWSLAVESTATUS\G

其中Reset salve all的作用,清除之前的日志相关的文件。

四、配置从库（老方式，不推荐）

切换到从库，

1.执行配置命令

#master_host 主数据库地址 CHANGE MASTER TO master_host ='localhost', #设置主数据库的端口号 master_port=3306, #上一步中主数据库创建的从库用户 master_user ='jason', #上一步中主数据库创建的从库密码 master_password ='123456', #上一步中保存的file master_log_file ='master-bin.000002', #上一步中保存的position master_log_pos = 938;

2.看状态

show slave status

3.启动slave

start slave

4.再看状态

显示成功yes,yes

五、主从复制延时问题解决

通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：数值越大，延时越严重。

修改从库的配置文件
vi /etc/my.cnf

[mysqld] #关闭从库日志写入 log_slave_updates=off innodb_flush_log_at_trx_commit=0 global sync_binlog=500;

六、常见问题

1.Slave_IO_Running: No解决办法：

因为我是两个mysql拷贝的
因为我是两个mysql拷贝的
因为我是两个mysql拷贝的，

里面的一个uuid一样。所以要删除auto.cnf。并且重启mysql。

2.Slave_SQL_Running: No

产生的原因是主从库不一致，造成某些语句不能执行。一般有两种方法：

跳过错误的语句

stop slave; set global sql_slave_skip_counter=5; #跳过几条就写几 ，这个例子是跳过5条语句 start slave;

重新同步数据，重新设置master_log_pos

#数据库同步完成后执行 change master to master_log_file=xxxx,master_log_pos=xxxxx