一、环境介绍
使用虚拟机搭的两个 linux 服务器，通过修改 ssh 端口来实现同个 ip 两个服务器
这里使用的是无历史数据的主从复制搭建，主要参考官方文档，有历史数据的操作方法也在里面可以找到，有空再弄了
1. master 环境
- 操作系统: centos x64
- 数据库版本: 5.6.38
- ip: 116.7.40.10
- ssh port: 22
2. slaver 环境
- 操作系统: centos x64
- 数据库版本: 5.6.38
- ip: 116.7.40.10
- ssh port: 5000
3. 必要条件
做主从复制的所有 mysql 版本必须保持一致
二、设置主库
修改 /etc/my.cnf 或 my.ini 文件，在 [mysqld] 模块中添加 log-bin 和 server-id，其中 log-bin 必须以 mysql-bin 开头。这里还有更多的参数可以设置，包括指定复制或不复制哪些库等，自行查阅
[mysqld]
log-bin=mysql-bin-lujw
server-id=80
保存，重启 mysql
三、设置从库
同样修改 /etc/my.cnf 或 my.ini 文件，添加 server-id，此 id 只要保证唯一就好
[mysqld]
server-id=81
保存，重启 mysql
四、获取主库日志信息
在主库中执行 SHOW MASTER STATUS; 查看日志信息并记起来
mysql> SHOW MASTER STATUS;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-lujw.000001 | 120 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
五、复制历史数据（略）
六、从库设置主库信息
为了实现主从复制，肯定需要告诉从库有关主库的信息，才能够成功连接，打开从库 mysql 并执行下列语句
mysql> change master to
-> master_host='116.7.40.10',
-> master_user='root',
-> master_password='password',
-> master_port=3305,
-> master_log_file='mysql-bin-lujw.000001',
-> master_log_pos=120,
-> master_connect_retry=3;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
关于 log 文件的信息是从第四步中获取到的，它决定了从库从哪里开始复制主库
七、开启从库复制
在从库中执行 start slave 来启动复制，然后执行 show slave status\G 来查看是否启动成功
mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 116.7.40.10
Master_User: root
Master_Port: 3305
Connect_Retry: 3
Master_Log_File: mysql-bin-lujw.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin-lujw.000001
Slave_IO_Running: No
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: 120
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 80
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 171025 00:26:39
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
是否启动成功主要看 Slave_IO_State、Slave_IO_Running和Slave_SQL_Running，这里可以看到我的从库状态为空，Slave_IO_Running 为 NO，肯定是出了问题
往下看 Last_IO_Error，可以看到报错
The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
出现这个错误的原因是从库的服务器是直接复制主库服务器生成的，所以 UUID 重复了
通过查阅官方文档可以得到错误解决方法
通过修改 auto.cnf 文件中的 UUID 后重启 mysql 即可
vi /var/lib/mysql/auto.cnf
再次查询从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 116.7.40.10
Master_User: root
Master_Port: 3305
Connect_Retry: 3
Master_Log_File: mysql-bin-lujw.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 288
Relay_Master_Log_File: mysql-bin-lujw.000001
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: 120
Relay_Log_Space: 465
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: 80
Master_UUID: c571f8b0-7890-11e7-aa60-000c29cf7ee0
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
这就表示主从复制配置成功了
八、测试
在主库中创建一个数据库
mysql> create database lujw_replication_test;
Query OK, 1 row affected (0.03 sec)
然后在从库中查询是否复制过来了
mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| db_forum |
| lujw_replication_test |
| mysql |
| performance_schema |
| test |
+-----------------------+
6 rows in set (0.00 sec)
测试成功。
网友评论