02-MySQL主从复制原理。

都是用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. 测试
在主库新建数据库然后到从库看有没有。