環(huán)境：

專注于為中小企業(yè)提供網(wǎng)站建設(shè)、成都網(wǎng)站制作服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)濱湖免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了1000+企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。

3306 主庫

3307 從庫

1 設(shè)置server-id值并開啟binlog功能參數(shù)

編輯MySQL的配置文件/data/3306/my.cnf

[mysqld]

server-id = 6              ----用于同步的每臺機器或?qū)嵗齭ever-id都不能相同

log_bin =/data/3306/mysql-bin  ----可以省略

重啟服務(wù)

/data/3306/mysql restart

檢查思路一

[root@db02 data]# egrep "log_bin|server-id" 330*/my.cnf

3306/my.cnf:log_bin = /data/3306/mysql-bin

3306/my.cnf:server-id = 6

3307/my.cnf:server-id = 7

檢查思路二：

檢查思路2：

登錄：

[root@db02 data]# mysql -S /data/3306/mysql.sock 

查看變量：

mysql> show variables like 'log_bin%';

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

| Variable_name                   | Value                      |

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

| log_bin                         | ON                         |

| log_bin_basename                | /data/3306/mysql-bin       |

| log_bin_index                   | /data/3306/mysql-bin.index |

| log_bin_trust_function_creators | OFF                        |

| log_bin_use_v1_row_events       | OFF                        |

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

5 rows in set (0.00 sec)

2 建賬號授權(quán)【主庫】

grant replication slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123';

flush privileges;

3鎖表導(dǎo)出數(shù)據(jù)

mysql> flush table with read lock;

Query OK, 0 rows affected (0.00 sec)

查看位置：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      405 |              |                  |                   |

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

1 row in set (0.00 sec)

新開窗口備份：

mysqldump -uroot -p'oldboy123' -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz

原窗口解鎖：

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      405 |              |                  |                   |

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

1 row in set (0.00 sec)

mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

如下命令可替代3的所有步驟

mysqldump -uroot -p'oldboy123' --master-data=2 -S /data/3306/mysql.sock -A -B

4、將數(shù)據(jù)導(dǎo)入到從庫

[root@db02 backup]# mysql -S /data/3307/mysql.sock

5、讓從庫從主庫鎖表時刻記錄的binlog位置點開始向下同步

CHANGE MASTER TO  

MASTER_HOST='172.16.1.52', 

MASTER_PORT=3306,

MASTER_USER='rep', 

MASTER_PASSWORD='oldboy123', 

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=405;