mysql 主從複制

mysql主從複制原理：

1.兩個檔案：master.info 上次從master上取到bin-log記錄

                  relay-log.info 存放取來的log

2.三個線程：

2個I/O線程1個SQL線程

3.配置過程

主庫操作：

1>開啟binlog 設定server-id

[root@localhost mysql]# vi /etc/my.cnf 

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin //開啟binlog

# binary logging format - mixed recommended

binlog_format=mixed

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1   //設定 id 

2>授權通路

mysql> grant replication slave on *.* to wyg@'%' identified by '123456';

mysql> flush privileges;

3>主庫鎖表備份

mysql> flush tables with read lock;

測試鎖表成功

mysql> create database qq;         

ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

備份資料

[root@localhost mysql]# mysqldump -uroot -p123456 -A -B >/tmp/all.sql 

檢視主庫記住log點及解鎖

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| mysql-bin.000005 |      459 |              |                  |

1 row in set (0.00 sec)

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

4.從庫操作

1>設定server-id = 3 與主庫不同

2>恢複主庫資料 

[root@localhost data]# mysql -uroot -p123456 </tmp/all.sql 

mysql> CHANGE MASTER TO MASTER_HOST='172.31.82.83',MASTER_PORT=3306,MASTER_USER='wyg',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=459;

3>開啟slave

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

4>檢視slave 

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.31.82.83

                  Master_User: wyg

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 459

               Relay_Log_File: localhost-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000005

             Slave_IO_Running: Yes  //一定是yes

            Slave_SQL_Running: Yes  //一定是yes

              Replicate_Do_DB: 

如有不是yes檢查兩台主機是否通訊，檢視iptables 和selinux

5.主庫建立資料庫，從庫檢視

主庫建立

mysql> create database nihao;

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

從庫檢視

mysql> show databases;    

+--------------------+

| Database           |

| information_schema |

| funtalk            |

| mysql              |

| nihao              |

| test               |

| tt                 |