下面一起來(lái)了解下配置MySQL數(shù)據(jù)庫(kù)主從同步交互式的操作方法，相信大家看完肯定會(huì)受益匪淺，文字在精不在多，希望配置mysql數(shù)據(jù)庫(kù)主從同步交互式的操作方法這篇短內(nèi)容是你想要的。
成都創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供義馬網(wǎng)站建設(shè)、義馬做網(wǎng)站、義馬網(wǎng)站設(shè)計(jì)、義馬網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、義馬企業(yè)網(wǎng)站模板建站服務(wù)，10余年義馬做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
一.my.cnf文件配置
1.修改my.cnf配置文件，主數(shù)據(jù)庫(kù)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.重啟數(shù)據(jù)庫(kù)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.進(jìn)入主數(shù)據(jù)庫(kù)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.創(chuàng)建一個(gè)專門用來(lái)同步數(shù)據(jù)庫(kù)的用戶
mysql> grant replicationslave on *.* to rep@'10.0.0.%' identified by '123456';
###*.*代表所有庫(kù)和所有表
mysql> flush privileges;
mysql> select user,hostmysql.user;
mysql> show grants forrep@'10.0.0.%';
二.主庫(kù)3306備份
mysql> flush table with readlock; //鎖表，這時(shí)為只讀，不能寫，此時(shí)mysql窗口不能退出，退出則鎖表會(huì)失敗，超出默認(rèn)的鎖表時(shí)間會(huì)自動(dòng)解鎖
mysql> show variables like'timeout%'; //查看默認(rèn)的鎖表時(shí)間
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 //從新打開一個(gè)CRT窗口，進(jìn)行備份
[root@mysql ~]# ls /opt
bak_2017-06-28.sql.gz
mysql> show master status; //備份數(shù)據(jù)后，再次查看binlog位置，確認(rèn)這期間沒有新數(shù)據(jù)寫入
mysql> unlock tables; //解鎖
三.把主庫(kù)3306備份出來(lái)的數(shù)據(jù)還原到從庫(kù)3307
1.登陸3307，確認(rèn)logbin是關(guān)閉狀態(tài)，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備份的數(shù)據(jù)還原到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 3.登陸3307，執(zhí)行以下命令，連接主庫(kù)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; //從庫(kù)3307開啟主從復(fù)制開關(guān) mysql> show slave status\G //檢查同步狀態(tài) *************************** 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 //確認(rèn)這兩條是yes狀態(tài) 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 //復(fù)制過程中從庫(kù)比主庫(kù)延遲的秒數(shù) 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) 或者這樣檢查同步狀態(tài) [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 查看線程狀態(tài) [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 四.測(cè)試 在主庫(kù)3306上創(chuàng)建數(shù)據(jù)庫(kù)，然后再登陸3307查看是否正常同步，經(jīng)測(cè)試已正常同步^_^ 看完配置mysql數(shù)據(jù)庫(kù)主從同步交互式的操作方法這篇文章后，很多讀者朋友肯定會(huì)想要了解更多的相關(guān)內(nèi)容，如需獲取更多的行業(yè)信息，可以關(guān)注我們的行業(yè)資訊欄目。
網(wǎng)站名稱：配置mysql數(shù)據(jù)庫(kù)主從同步交互式的操作方法
URL鏈接：http://weahome.cn/article/gdshdi.html
重慶分公司
重慶分公司
