mysql 实现主从数据库同步

mysql实现主从同步,我这里的环境是

| 服务器 | IP |版本 | ------------- | ------------------------------- | |主库 | 192.168.21.1 |mysql8.0 | 从库 | 192.168.21.158 |mysql8.0

从库安装mysql步骤省略 我这里是主库已经存在数据库,首先需要同步旧数据

mysqldump --single-transaction --all-databases --master-data=1 --host=192.168.21.1 --user=root --password=root --apply-slave-statements | mysql -uroot -proot -hlocalhost

主库配置/etc/my.cnf增加如下几行

server-id=1
read-only=0
log_bin = mysql-bin
log-slave-updates = true

重启主库

systemctl restart mysqld
进入mysql

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |   195169 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

从库配置

server-id=2 #不能与主库的相同

重启从库

systemctl restart mysqld

进入mysql,执行

stop slave
reset slave
change master to master_host= '192.168.21.1', master_user='root', master_password='root', master_log_file='mysql-bin.000002', master_log_pos=195169; 
start slave
查看状态:
show slave status\G;

在输出的结果中查找必须都为YES

  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

遇到的问题:

 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

我看网上都是重新设置

change master to master_log_file='mysqlbin.000001', master_log_pos=1000;

我这并不起作用, 使用如下命令解决的:

stop slave
reset slave
start slave

发表评论

评论列表,共 0 条评论

    暂无评论