程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MHA實現mysql主從數據庫手動切換的方法,mhamysql主從數據庫

MHA實現mysql主從數據庫手動切換的方法,mhamysql主從數據庫

編輯:MySQL綜合教程

MHA實現mysql主從數據庫手動切換的方法,mhamysql主從數據庫


本文實例講述了MHA實現mysql主從數據庫手動切換的方法,分享給大家供大家參考。具體方法如下:

一、准備工作

1、分別在Master和Slave執行如下,方便mha檢查復制:
復制代碼 代碼如下:grant all privileges on *.* to 'root'@'10.1.1.231' identified by 'rootpass';
grant all privileges on *.* to 'root'@'10.1.1.234' identified by 'rootpass';
grant replication slave on *.* to 'jpsync'@'10.1.1.231' identified by 'jppasswd';
grant replication slave on *.* to 'jpsync'@'10.1.1.234' identified by 'jppasswd';
flush privileges;
2、將master設置為只讀
 
復制代碼 代碼如下:mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)
交互模式:
復制代碼 代碼如下:#masterha_master_switch --master_state=alive --conf=/etc/masterha/app1.cnf    --new_master_host=10.1.1.231  --new_master_port=63306
或非交互模式:
復制代碼 代碼如下:#masterha_master_switch --master_state=alive --conf=/etc/masterha/app1.cnf    --new_master_host=10.1.1.231  --new_master_port=63306 —interactive=0

二、切換完以後,如何讓10.1.1.231為主,10.1.1.234為從,操作步驟:

1、主上執行:
復制代碼 代碼如下:mysql> show master status;
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                     | Executed_Gtid_Set |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| mysql-master-bin.000013 |      120 | denovo_ng    | mysql,denovo,test,information_schema |                   |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
1 row in set (0.00 sec)
2、在10.1.1.234上執行如下sql命令;
復制代碼 代碼如下:change master to master_host='10.1.1.231',master_port=63306,master_user='jpsync',
master_password='jppasswd', master_log_file='mysql-master-bin.000013',master_log_pos=120;
 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.1.231
                  Master_User: jpsync
                  Master_Port: 63306
                Connect_Retry: 60
              Master_Log_File: mysql-master-bin.000013
          Read_Master_Log_Pos: 120
               Relay_Log_File: compute-0-52-relay-bin.000002
                Relay_Log_Pos: 290
        Relay_Master_Log_File: mysql-master-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
3、查看master狀態,並測試
復制代碼 代碼如下:mysql> show slave hosts;
+-----------+------+-------+-----------+--------------------------------------+
| Server_id | Host | Port  | Master_id | Slave_UUID                           |
+-----------+------+-------+-----------+--------------------------------------+
|      1052 |      | 63306 |      1025 | e25a3e4a-39c0-11e4-80cb-00259086c4b6 |
+-----------+------+-------+-----------+--------------------------------------+
1 row in set (0.00 sec)
主庫10.1.1.231上插入記錄
復制代碼 代碼如下:mysql> insert into  test_slave_002 values(555551111,1,55555,99999,44.11,2222,91919);
Query OK, 1 row affected (0.00 sec)
從庫查詢記錄已經存在
復制代碼 代碼如下:mysql> select * from test_slave_002 where id=555551111;
+-----------+-----+-----------+--------------+----------+----------------+--------------+
| id        | tag | ticket_id | candidate_id | duration | source_file_id | source_start |
+-----------+-----+-----------+--------------+----------+----------------+--------------+
| 555551111 |   1 |     55555 |        99999 |    44.11 |           2222 |        91919 |
+-----------+-----+-----------+--------------+----------+----------------+--------------+
1 row in set (0.00 sec)
4、更新配置文件:
更新主庫my.cnf配置添加
復制代碼 代碼如下:skip_slave_start
注意:防止重啟數據庫,啟動slave進程,導致數據不一致。
更新從庫my.cnf配置添加,設置slave庫為只讀:
復制代碼 代碼如下:read_only=1
relay_log_purge=0
然後重啟主庫和從庫,觀察庫的信息:
主庫信息:
復制代碼 代碼如下:mysql> show processlist;
+----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User   | Host             | db   | Command     | Time | State                                                                 | Info             |
+----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  1 | jpsync | 10.1.1.234:49085 | NULL | Binlog Dump |   17 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  2 | root   | localhost        | NULL | Query       |    0 | init                                                                  | show processlist |
+----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
 
mysql> show master status;
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                     | Executed_Gtid_Set |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| mysql-master-bin.000014 |      120 | denovo_ng    | mysql,denovo,test,information_schema |                   |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
1 row in set (0.00 sec)
從庫信息:
復制代碼 代碼如下:mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.1.231
                  Master_User: jpsync
                  Master_Port: 63306
                Connect_Retry: 60
              Master_Log_File: mysql-master-bin.000014
          Read_Master_Log_Pos: 120
               Relay_Log_File: compute-0-52-relay-bin.000005
                Relay_Log_Pos: 290
        Relay_Master_Log_File: mysql-master-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   58 | Waiting for master to send event                                            | NULL             |
|  2 | system user |           | NULL | Connect |   58 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  3 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

希望本文所述對大家的MySQL數據庫程序設計有所幫助。


mysql怎實現主從同步數據庫備份?

1.主服務器:
#Master start
   log-bin="d:/log/mysql/mysql_log_bin"
  server-id=1
   #Master end
  2.從服務器:
#Slave start
log-bin="D:/log/mysql2/log-bin.log"
relay_log="D:/log/mysql2/relay-log-bin"
#從機id,區別於主機id
server-id=2
#主機ip,供從機連接主機用
#master-host=localhost
#主機端口
#master-port=3300
#剛才為從機復制主機數據新建的賬號
#master-user=slave
#剛才為從機復制主機數據新建的密碼
#master-password=654321
#重試間隔時間10秒
#master-connect-retry=10
#需要同步的數據庫
#replicate-do-db=test
#啟用從庫日志,這樣可以進行鏈式復制
log-slave-updates
#從庫是否只讀,0表示可讀寫,1表示只讀
read-only=1

#只復制某個表
#replicate-do-table=tablename
#只復制某些表(可用匹配符)
#replicate-wild-do-table=tablename%
#只復制某個庫
#replicate-do-db=dbname
#不復制某個表
#replicate-ignore-table=tablename
#不復制某些表
#replicate-wild-ignore-table=tablename%
#不復制某個庫
#replicate-ignore-db=dbname
#Slave end
3.對從服務器制定主服務器使用CHANGE MASTER 語句
注意:1.一定要在主服務器上創建一個可以執行replication的用戶
2.該用戶名在從服務器上可遠程登錄到主服務器。
3.開啟MySQL的log-bin日志功能
參考資料:blog.163.com/...31959/
 

使用MySQL數據庫主從結構時當從服務器太多可可以會遇到什問題?

不是很清楚,我不是用這個數據庫。
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved