02-MySQL主从复制原理。

客官°小女子只卖身不卖艺 2021-12-20 01:34 249阅读 0赞

都是用Ubuntu16.04系统，直接使用sudo apt-get install mysql 安装好数据库就好。

*主库：192.168.3.191  
从库：192.168.3.193*

1. 开启mysqllogbin  
vi /etc/mysql

server-id = 1 \# 2台mysql要设置不同的ID  
log-bin=mysqlbin\_test.log \# 开启mysqllogbin

2. 在主服务器192.168.3.191上面的数据库配置从库的登陆用户。  
mysql> grant replication slave on \*.\* to 'rep'@'192.168.0.%' identified by 'test123';  
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on \*.\* to 'rep'@'192.168.3.%' identified by 'test123';  
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

mysql> flush table with read lock;  
Query OK, 0 rows affected (0.00 sec)

3. 打开一个新的窗口登录192.168.3.191，先把数据库备份导出。  
mysqldump -uroot -ptest456 -A -B --events --master-data=2 >/opt/mysql\_test1.sql \# 测试用没设置压缩

vim mysql\_test1.sql  
\-- CHANGE MASTER TO MASTER\_LOG\_FILE='mysqlbin\_test.000009', MASTER\_LOG\_POS=1044; \# 查看binlog点。

4. 回到第一窗口  
mysql> unlock tables;  
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;  
\+------------------------+----------+--------------+------------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
\+------------------------+----------+--------------+------------------+  
| mysqlbin\_test.000009 | 1044 | | |  
\+------------------------+----------+--------------+------------------+  
mysql> show master logs;  
\+------------------------+-----------+  
| Log\_name | File\_size |  
\+------------------------+-----------+  
| mysqlbin\_test.000001 | 548 |  
| mysqlbin\_test.000002 | 2537 |  
| mysqlbin\_test.000003 | 126 |  
| mysqlbin\_test.000004 | 126 |  
| mysqlbin\_test.000005 | 557 |  
| mysqlbin\_test.000006 | 4297 |  
| mysqlbin\_test.000007 | 126 |  
| mysqlbin\_test.000008 | 355 |  
| mysqlbin\_test.000009 | 1046 |  
\+------------------------+-----------+

5. 登陆到从服务器192.168.3.193的数据库  
先恢复数据,先全量恢复一次：  
mysql -uroot -p'test456' </opt/mysql\_test1.sql

然后登陆数据库进行配置：  
mysql -uroot -p'test456'

mysql> CHANGE MASTER TO  
MASTER\_HOST='192.168.3.191',  
MASTER\_PORT=3306,  
MASTER\_USER='rep',  
MASTER\_PASSWORD='test123',  
MASTER\_LOG\_FILE='mysqlbin\_test.000009',  
MASTER\_LOG\_POS=1044;

mysql> start slave;  
mysql> flush privileges;  
mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.3.191  
Master\_User: rep  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysqlbin\_test.000009  
Read\_Master\_Log\_Pos: 1218  
Relay\_Log\_File: mysql-relay-bin.000002  
Relay\_Log\_Pos: 432  
Relay\_Master\_Log\_File: mysqlbin\_test.000009  
Slave\_IO\_Running: Yes \# 开  
Slave\_SQL\_Running: Yes \# 开  
Replicate\_Do\_DB:  
Replicate\_Ignore\_DB:  
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: 1218  
Relay\_Log\_Space: 587  
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 \# 为0  
Master\_SSL\_Verify\_Server\_Cert: No  
Last\_IO\_Errno: 0  
Last\_IO\_Error:  
Last\_SQL\_Errno: 0  
Last\_SQL\_Error:  
1 row in set (0.00 sec)

6. 测试  
在主库新建数据库然后到从库看有没有。

![1149596-20190520110529799-2941821.png][]

转载于:https://www.cnblogs.com/hejianping/p/10892749.html

[1149596-20190520110529799-2941821.png]: /images/20211220/a8894e064d684ad6ab7a213389b1d643.png