配置mysql數據庫主從同步交互式的操作方法

下面一起來了解下配置mysql數據庫主從同步交互式的操作方法，相信大家看完肯定會受益匪淺，文字在精不在多，希望配置mysql數據庫主從同步交互式的操作方法這篇短內容是你想要的。

一.my.cnf文件配置

1.修改my.cnf配置文件，主數據庫3306要打開log-bin，server-id不能一樣

[root@mysql ~]# egrep"log-bin|server-id" /data/{3306,3307}/my.cnf

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

/data/3306/my.cnf:server-id = 1

/data/3307/my.cnf:#log-bin =/data/3307/mysql-bin

/data/3307/my.cnf:server-id = 3

2.重啟數據庫3306和3307

[root@mysql ~]#/data/3306/mysql stop

[root@mysql ~]#/data/3306/mysql start

[root@mysql ~]#/data/3307/mysql stop 

[root@mysql ~]#/data/3307/mysql start

3.進入主數據庫3306，查詢log_bin是否打開，server_id是多少

[root@mysql ~]# mysql -uroot-p123456 -S /data/3306/mysql.sock

mysql> show variables like"log_bin";

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

| Variable_name | Value |

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

| log_bin       | ON   |

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

1 row in set (0.01 sec)

mysql> show variables like"server_id";

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

| Variable_name | Value |

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

| server_id     | 1    |

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

1 row in set (0.00 sec)

4.創建一個專門用來同步數據庫的用戶

mysql> grant replicationslave on *.* to rep@'10.0.0.%' identified by '123456';

###*.*代表所有庫和所有表

mysql> flush privileges;

mysql> select user,hostmysql.user;

mysql> show grants forrep@'10.0.0.%';

二.主庫3306備份

mysql> flush table with readlock;          //鎖表，這時為只讀，不能寫，此時mysql窗口不能退出，退出則鎖表會失敗，超出默認的鎖表時間會自動解鎖

mysql> show variables like'timeout%';       //查看默認的鎖表時間

mysql> show master status;             //查看binlog位置

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

| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB       |

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

| mysql-bin.000004   |     328|              |                   |

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

1 row in set (0.00 sec)

[root@mysql ~]# mysqldump-uroot -p123456 -S /data/3306/mysql.sock -A -B|gzip >/opt/bak_$(date+%F).sql.gz     //從新打開一個CRT窗口，進行備份

[root@mysql ~]# ls /opt

bak_2017-06-28.sql.gz

mysql> show master status;     //備份數據后，再次查看binlog位置，確認這期間沒有新數據寫入

mysql> unlock tables;                       //解鎖

三.把主庫3306備份出來的數據還原到從庫3307

1.登陸3307，確認logbin是關閉狀態，server id與3306不沖突

[root@mysql ~]# mysql -uroot-p123456 -S /data/3307/mysql.sock

mysql> show variables like"log_bin";    

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

| Variable_name | Value |

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

| log_bin       | OFF |

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

1 row in set (0.01 sec)

mysql> show variables like"server_id";

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

| Variable_name | Value |

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

| server_id     | 3    |

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

1 row in set (0.00 sec)

2.將3306備份的數據還原到3307上

[root@mysql ~]# cd /opt

[root@mysql opt]# ls

bak_2017-06-28.sql.gz 

[root@mysql opt]# gzip -dbak_2017-06-28.sql.gz

[root@mysql opt]# ls

bak_2017-06-28.sql 

[root@mysql opt]# mysql -uroot-p123456 -S /data/3307/mysql.sock <bak_2017-06-28.sql

3.登陸3307，執行以下命令，連接主庫3306

[root@mysql opt]# mysql -uroot-p123456 -S /data/3307/mysql.sock

mysql> CHANGE MASTER TO 

    -> MASTER_HOST='10.0.0.20',

    -> MASTER_PORT=3306,

    -> MASTER_USER='rep',

    -> MASTER_PASSWORD='123456',

    -> MASTER_LOG_FILE='mysql-bin.000004',

-> MASTER_LOG_POS=328;

mysql> start slave;               //從庫3307開啟主從復制開關

mysql> show slave status\G       //檢查同步狀態

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

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 10.0.0.20

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 328

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 253

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running:Yes    //確認這兩條是yes狀態

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB: mysql

           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: 328

              Relay_Log_Space: 403

              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

1 row in set (0.00 sec)

或者這樣檢查同步狀態

[root@mysql ~]# mysql -uroot-p123456 -S /data/3307/mysql.sock  -e"show slave status\G"|egrep -i "running|_behind"     //-e的作用是不需要登陸mysql交互敲入命令

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

        Seconds_Behind_Master: 0

查看線程狀態

[root@mysql ~]# mysql -uroot -p123456 -S/data/3307/mysql.sock -e "show processlist\G"

[root@mysql ~]# mysql -uroot -p123456 -S/data/3306/mysql.sock -e "show processl

四.測試

在主庫3306上創建數據庫，然后再登陸3307查看是否正常同步，經測試已正常同步^_^

看完配置mysql數據庫主從同步交互式的操作方法這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。