主库操作
1）配置主库
|
1 2 3 4 5 |
[root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=mysql-bin |
2）授权一个主从用户
|
1 2 3 4 |
mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123'; #grant replication slave 是一个全局授权，不能指定单个库授权，如果想同步单个库使用过滤复制 |
3）查看主库的binlog信息
|
1 2 3 4 5 6 7 8 |
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 326 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) |
从库操作
1）配置从库
|
1 2 3 4 |
[root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 |
2）配置主从复制
|
1 2 3 4 5 6 7 8 |
change master to master_host='172.16.1.51', master_user='rep', master_password='123', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=326; |
3）开启IO线程和sql线程
|
1 2 3 |
mysql> start slave; Query OK, 0 rows affected (0.01 sec) |
4）查看主从状态
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.1.51 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 326 Relay_Log_File: db02-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes |
主从复制故障分析
1. IO线程故障
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#Slave_IO_Running: No 或者 Connecting 1.检测网络 [root@db02 ~]# ping 172.16.1.51 2.检测端口 [root@db02 ~]# telnet 172.16.1.51 3306 3.防火墙是否开启 4.主从的用户名或者密码错误 #测试使用主从用户的用户名和密码连接主库 [root@db02 ~]# mysql -urep -p123 -h172.16.1.51 5.反向解析 [root@db01 ~]# mysql -uroot -p123 -h172.16.1.51 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES) #解决 [root@db01 ~]# vim /etc/my.cnf [mysqld] skip_name_resolve |
2.sql线程故障
|
1 2 3 4 5 6 |
#Slave_SQL_Running: No 或者 Connecting 1.主库有的数据，从库没有 2.从库有的数据，主库没有 3.主库数据与从库不一致 |
1）解决办法一
|
1 2 3 4 5 6 7 8 9 10 11 12 |
1.停止主从复制 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) 2.跳过一个错误 mysql> set GLOBAL sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) 3.开启主从 mysql> start slave; Query OK, 0 rows affected (0.00 sec) |
2）解决办法二
|
1 2 3 4 5 6 7 8 9 10 11 12 |
1.停止主从复制 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) 2.清空主从复制的信息 mysql> reset slave all; Query OK, 0 rows affected (0.00 sec) 3.同步主库所有数据 4.重新配置主从复制 |