mysql数据库主从同步（交互式）

古城微笑少年丶 2022-02-05 06:11 256阅读 0赞

mysql数据库主从同步交互式配置步骤，本文以一台mysql数据库多实例3306和3307为例进行配置，3306为主库，3307为从库（多台单实例与一台多实例配置是一样的）

[![wKioL1lbQLuiR6P9AABxEud024U642.jpg-wh\_50][wKioL1lbQLuiR6P9AABxEud024U642.jpg-wh_50]][wKioL1lbQLuiR6P9AABxEud024U642.jpg-wh_50]

**一.my.cnf****文件配置**

1.修改my.cnf配置文件，主数据库3306要打开log-bin，server-id不能一样

\[root@mysql ~\]\# egrep"log-bin|server-id" /data/\{3306,3307\}/my.cnf

/data/3306/my.cnf:log-bin =/data/3306/mysql-bin

/data/3306/my.cnf:server-id = 1

/data/3307/my.cnf:\#log-bin =/data/3307/mysql-bin

/data/3307/my.cnf:server-id = 3

2.重启数据库3306和3307

\[root@mysql ~\]\#/data/3306/mysql stop

\[root@mysql ~\]\#/data/3306/mysql start

\[root@mysql ~\]\#/data/3307/mysql stop

\[root@mysql ~\]\#/data/3307/mysql start

3.进入主数据库3306，查询log\_bin是否打开，server\_id是多少

\[root@mysql ~\]\# mysql -uroot-p123456 -S /data/3306/mysql.sock

mysql> show variables like"log\_bin";

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| log\_bin | ON |

\+---------------+-------+

1 row in set (0.01 sec)

mysql> show variables like"server\_id";

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| server\_id | 1 |

\+---------------+-------+

1 row in set (0.00 sec)

4.创建一个专门用来同步数据库的用户

mysql> grant replicationslave on \*.\* to rep@'10.0.0.%' identified by '123456';

\#\#\#\*.\*代表所有库和所有表

mysql> flush privileges;

mysql> select user,hostmysql.user;

mysql> show grants forrep@'10.0.0.%';

**二.主库3306备份**

mysql> flush table with readlock; //锁表，这时为只读，不能写，此时mysql窗口不能退出，退出则锁表会失败，超出默认的锁表时间会自动解锁

mysql> show variables like'timeout%'; //查看默认的锁表时间

mysql> show master status; //查看binlog位置

\+------------------+----------+--------------+------------------+-----------------------------------+

\+------------------+----------+--------------+------------------+-----------------------------------+

| mysql-bin.000004 | 328| | |

\+------------------+----------+--------------+------------------+-----------------------------------+

1 row in set (0.00 sec)

\[root@mysql ~\]\# mysqldump-uroot -p123456 -S /data/3306/mysql.sock -A -B|gzip >/opt/bak\_$(date+%F).sql.gz //从新打开一个CRT窗口，进行备份

\[root@mysql ~\]\# ls /opt

bak\_2017-06-28.sql.gz

mysql> show master status; //备份数据后，再次查看binlog位置，确认这期间没有新数据写入

mysql> unlock tables; //解锁

**三.把主库3306备份出来的数据还原到从库3307**

1.登陆3307，确认logbin是关闭状态，server id与3306不冲突

\[root@mysql ~\]\# mysql -uroot-p123456 -S /data/3307/mysql.sock

mysql> show variables like"log\_bin";

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| log\_bin | OFF |

\+---------------+-------+

1 row in set (0.01 sec)

mysql> show variables like"server\_id";

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| server\_id | 3 |

\+---------------+-------+

1 row in set (0.00 sec)

2.将3306备份的数据还原到3307上

\[root@mysql ~\]\# cd /opt

\[root@mysql opt\]\# ls

bak\_2017-06-28.sql.gz

\[root@mysql opt\]\# gzip -dbak\_2017-06-28.sql.gz

\[root@mysql opt\]\# ls

bak\_2017-06-28.sql

\[root@mysql opt\]\# mysql -uroot-p123456 -S /data/3307/mysql.sock <bak\_2017-06-28.sql

3.登陆3307，执行以下命令，连接主库3306

\[root@mysql opt\]\# mysql -uroot-p123456 -S /data/3307/mysql.sock

mysql> CHANGE MASTER TO

\-> MASTER\_HOST='10.0.0.20',

\-> MASTER\_PORT=3306,

\-> MASTER\_USER='rep',

\-> MASTER\_PASSWORD='123456',

\-> MASTER\_LOG\_FILE='mysql-bin.000004',

\-> MASTER\_LOG\_POS=328;

mysql> start slave; //从库3307开启主从复制开关

mysql> show slave status\\G //检查同步状态

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1.row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 10.0.0.20

Master\_User: rep

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000004

Read\_Master\_Log\_Pos: 328

Relay\_Log\_File: relay-bin.000002

Relay\_Log\_Pos: 253

Relay\_Master\_Log\_File: mysql-bin.000004

Slave\_IO\_Running:Yes //确认这两条是yes状态

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB: mysql

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: 328

Relay\_Log\_Space: 403

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: 0 //复制过程中从库比主库延迟的秒数

Master\_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

1 row in set (0.00 sec)

或者这样检查同步状态

\[root@mysql ~\]\# mysql -uroot-p123456 -S /data/3307/mysql.sock -e"show slave status\\G"|egrep -i "running|\_behind" //-e的作用是不需要登陆mysql交互敲入命令

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Seconds\_Behind\_Master: 0

查看线程状态

\[root@mysql ~\]\# mysql -uroot -p123456 -S/data/3307/mysql.sock -e "show processlist\\G"

\[root@mysql ~\]\# mysql -uroot -p123456 -S/data/3306/mysql.sock -e "show processl

**四.测试**

在主库3306上创建数据库，然后再登陆3307查看是否正常同步，经测试已正常同步^\_^

本文转自 茁壮的小草 51CTO博客，原文链接：http://blog.51cto.com/wn2100/1944426，如需转载请自行联系原作者

[wKioL1lbQLuiR6P9AABxEud024U642.jpg-wh_50]: /images/20220205/e8733c7297414a02974be90841f01507.png

mysql数据库主从同步（交互式）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql设置主从数据库的同步

相关 mysql多主主从数据库_MySQL多实例，主从同步

相关 mysql数据库主从同步配置

相关 MySQL主从同步，及Canal实时数据库同步

相关 MySQL数据库主从同步

相关 linux mysql数据库主从同步配置

相关 mysql主从同步，设置同步部分数据库

相关 mysql数据库主从同步（交互式）

相关 mysql数据库主从同步（交互式）

相关 mysql数据库备份之主从同步配置