今天阅读了 MySQL 复制相关的内容，MySQL 的复制比较灵活，手册上介绍说可以实现基于实例级，数据库级，表级别的复制， 今天实验的是实例级主从复制。
一 环境信息 主库 192.168.2.37/3306
二 主节点配置 修改主库 /etc/my.cnf 增加以下参数
1 2 server_id = 1 log_bin = "/database/mysql/data/binlog/bin-log"
重启 MySQL
1 [root@db1 ~]# service mysql restart
创建复制用户
1 2 3 4 5 root@localhost:(none )>create user 'rep1' @'192.168.2.%' identified by 'rep1abcd1243d' ; Query OK, 0 rows affected (0.02 sec) root@localhost:(none )>GRANT REPLICATION SLAVE ON *.* TO 'rep1' @'192.168.2.%' ; Query OK, 0 rows affected (0.01 sec)
备注: 创建新用户并赋予 REPLICATION SLAVE 权限，这个用户用来读取主节点的 binary log.
开启 session1: 锁定数据库所有表
1 2 root@localhost:(none )>flush tables with read lock; Query OK, 0 rows affected (0.08 sec )
开启 sesson2: 查看 binary 信息
1 2 3 4 5 6 7 root@localhost:(none) >show master status; +---------------- +---------- +-------------- +------------------ +------------------- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------- +---------- +-------------- +------------------ +------------------- + | bin-log.000023 | 552 | | | | +---------------- +---------- +-------------- +------------------ +------------------- + 1 row in set (0.00 sec)
备注：记住这些信息，备节点配置时要用到。
复制数据并传到从库对应目录
主节点解锁
1 2 root@localhost: (none)>unlock tables; Query OK, 0 rows affected (0.00 sec )
三 从节点配置 修改备库 /etc/my.cnf 增加以下参数
重启 MySQL
1 [root@db2 ~]# service mysql restart
设置主节点信息
1 2 3 4 5 6 7 8 9 10 CHANGE MASTER TO MASTER_HOST ='192.168.2.37' , MASTER_PORT =3306, MASTER_USER ='rep1' , MASTER_PASSWORD ='rep1abcd1243d' , MASTER_LOG_FILE ='bin-log.000023' , MASTER_LOG_POS =552; mysql> start slave; Query OK, 0 rows affected, 1 warning (0.00 sec)
发现日志报错
1 2 3 2014 -09 -15 15 :23 :08 8699 [Note] Slave I/O thread: connected to master 'rep1@192.168 .2 .37 :3306 ',replication started in log 'bin-log .000023 ' at position 552 2014 -09 -15 15 :23 :08 8699 [ERROR] Slave I/O: 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. Error_code: 1593 2014 -09 -15 15 :23 :08 8699 [Note] Slave I/O thread exiting, read up to log 'bin-log .000023 ', position 552
备注：这是因为在复制主节点数据目录 /database/mysql/data 时，将文件 auto.cnf 也复制过来了，这时只需要修改备节点的 auto.cnf 文件，参照格式修改 server-uuid 即可。
修改后的 auto.cnf
1 2 3 [mysql@db2 data]$ cat auto.cnf [auto] server-uuid=0 c130d48-22 bb-11e4 -aaaa-000 c2986ac80
备注：修改后重启数据库，日志没有出现异常信息。
查看备节点详细信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 mysql> show slave statusG * 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 192.168 .2 .37 Master_User: rep1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin-log.000023 Read_Master_Log_Pos: 552 Relay_Log_File: db2-relay-bin.000003 Relay_Log_Pos: 281 Relay_Master_Log_File: bin-log.000023 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: 552 Relay_Log_Space: 452 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 Master_UUID: 0 c130d47-22bb-11e4-aaaa-000c2986ac80 Master_Info_File: /database/mysql/data/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.01 sec)
四 验证 主节点创建表
1 2 3 4 5 6 7 8 9 10 11 12 root@localhost:francs>show tables; +------------------+ | Tables_in_francs | +------------------+ | test_1 | | test_2 | | test_3 | | test_4 | +------------------+ root@localhost:francs>create table test_5 as select * from test_1; Query OK, 10001 rows affected (0 .38 sec) Records: 10001 Duplicates: 0 Warnings: 0
备节点验证
1 2 3 4 5 6 7 8 9 10 11 mysql> show tables; +------------------+ | Tables_in_francs | +------------------+ | test_1 | | test_2 | | test_3 | | test_4 | | test_5 | +------------------+ 5 rows in set (0.00 sec)
备注： 验证成功。
五 参考 六 相关链接 原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/239607.html
