mysql中怎么設(shè)置主從

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)mysql中怎么設(shè)置主從，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

　　1、確保主數(shù)據(jù)庫與從數(shù)據(jù)庫一模一樣。

　　　　例如：主數(shù)據(jù)庫里的a的數(shù)據(jù)庫里有b，c，d表，那從數(shù)據(jù)庫里的就應(yīng)該有一個(gè)模子刻出來的a的數(shù)據(jù)庫和b，c，d表

　　2、在主數(shù)據(jù)庫上創(chuàng)建同步賬號(hào)。

　　　　GRANT REPLICATION SLAVE ON *.* to 'djy'@'%' identified by '12345678';

　　　　djy：是新創(chuàng)建的用戶名

　　　　12345678：是新創(chuàng)建的用戶名的密碼

　　　　以上命令的詳細(xì)解釋，最好百度一下，寫太多反到更加更不清思路。

　　3、配置主數(shù)據(jù)庫的my.ini(因?yàn)槭窃趙indow下，所以是my.ini不是my.cnf)。

　　　   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必須]啟用二進(jìn)制日志
       server-id=222      //[必須]服務(wù)器唯一ID，默認(rèn)是1，一般取IP最后一段

其他擴(kuò)展配置項(xiàng)(無下面配置默認(rèn)全庫主從備份)：　

binlog-do-db=djydjy #需要備份的數(shù)據(jù)庫名，如果備份多個(gè)數(shù)據(jù)庫，重復(fù)設(shè)置這個(gè)選項(xiàng) 即可
binlog-ignore-db=mysql #不需要備份的數(shù)據(jù)庫名，如果備份多個(gè)數(shù)據(jù)庫，重復(fù)設(shè)置這 個(gè)選項(xiàng)即可
log-slave-updates=1 #這個(gè)參數(shù)一定要加上，否則不會(huì)給更新的記錄些到二進(jìn)制文件 里
slave-skip-errors=all #是跳過錯(cuò)誤，繼續(xù)執(zhí)行復(fù)制操作(可選)
expire_logs_days=2 #設(shè)置log保留天數(shù)

　　配置從數(shù)據(jù)庫的my.cnf。
　　　　 #vi /etc/my.cnf
       [mysqld]
       #log-bin=mysql-bin   //[不是必須]啟用二進(jìn)制日志
       server-id=226     //[必須]服務(wù)器唯一ID，默認(rèn)是1，一般取IP最后一段
log-slave-updates=1 #這個(gè)參數(shù)一定要加上，否則不會(huì)給更新的記錄些到二進(jìn)制文件 里
slave-skip-errors=all #是跳過錯(cuò)誤，繼續(xù)執(zhí)行復(fù)制操作(可選)
expire_logs_days=2 #設(shè)置log保留天數(shù)

4、重啟兩臺(tái)服務(wù)器的mysql
   /etc/init.d/mysql restart

5、在主服務(wù)器上建立帳戶并授權(quán)slave:
   #/usr/local/mysql/bin/mysql -uroot -pmttang  
   mysql>GRANT REPLICATION SLAVE ON *.* to 'djy'@'%' identified by '12345678'; //一般不用root帳號(hào)，“%”表示所有客戶端都可能連，只要帳號(hào)，密碼正確，此處可用具體客戶端IP代替，如192.168.145.226，加強(qiáng)安全。

6、登錄主服務(wù)器的mysql，查詢master的狀態(tài)
   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   注：執(zhí)行完此步驟后不要再操作主服務(wù)器MYSQL，防止主服務(wù)器狀態(tài)值變化

7、配置從服務(wù)器Slave：
   mysql>change master to master_host='10.20.4.10',master_user='djy',master_password='12345678',master_log_file='mysql-bin.000001',master_log_pos=154;    //注意不要斷開，308數(shù)字前后無單引號(hào)。

   Mysql>start slave;    //啟動(dòng)從服務(wù)器復(fù)制功能

8、檢查從服務(wù)器復(fù)制功能狀態(tài)：

   mysql> show slave status\G

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

              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服務(wù)器地址
              Master_User: mysync   //授權(quán)帳戶名，盡量避免使用root
              Master_Port: 3306    //數(shù)據(jù)庫端口，部分版本沒有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步讀取二進(jìn)制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此狀態(tài)必須YES
              Slave_SQL_Running: Yes     //此狀態(tài)必須YES
                    ......

注：Slave_IO及Slave_SQL進(jìn)程必須正常運(yùn)行，即YES狀態(tài)，否則都是錯(cuò)誤的狀態(tài)(如：其中一個(gè)NO均屬錯(cuò)誤)。

以上操作過程，主從服務(wù)器配置完成。
 
9、主從服務(wù)器測(cè)試：

主服務(wù)器Mysql，建立數(shù)據(jù)庫，并在這個(gè)庫中建表插入一條數(shù)據(jù)：

  mysql> create database hi_db;
  Query OK, 1 row affected (0.00 sec)

  mysql> use hi_db;
  Database changed

  mysql>  create table hi_tb(id int(3),name char(10));
  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into hi_tb values(001,'bobu');
  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db                |
   | mysql                |
   | test                 |
   +--------------------+
   4 rows in set (0.00 sec)

從服務(wù)器Mysql查詢：

   mysql> show databases;

   +--------------------+
   | Database               |
   +--------------------+
   | information_schema |
   | hi_db                 |       //I'M here，大家看到了吧
   | mysql                 |
   | test          |

   +--------------------+
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb;           //查看主服務(wù)器上新增的具體數(shù)據(jù)
   +------+------+
   | id   | name |
   +------+------+
   |    1 | bobu |
   +------+------+
   1 row in set (0.00 sec)
 

10、完成：
    編寫一shell腳本，用nagios監(jiān)控slave的兩個(gè)yes（Slave_IO及Slave_SQL進(jìn)程），如發(fā)現(xiàn)只有一個(gè)或零個(gè)yes，就表明主從有問題了，發(fā)短信警報(bào)吧。

11.精簡(jiǎn)命令及排錯(cuò)

GRANT REPLICATION SLAVE ON *.* to 'djy'@'%' identified by '12345678';
show master status;

change master to master_host='10.20.4.33',master_user='djy',master_password='12345678',master_log_file='mysql-bin.000003',master_log_pos=154;
start slave;
show slave status\G

查看MYSQL數(shù)據(jù)庫中所有用戶
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

start slave;報(bào)錯(cuò)：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
策略：使用reset slave all;清空所有的復(fù)制信息

MySQL報(bào)錯(cuò)：master and slave have equal MySQL server UUIDs
策略：
#  vi /main/mysql/data/auto.cnf
[auto]
server-uuid=f4863bcf-1d28-11e4-8657-000c29eec7bf
#隨便改動(dòng)一兩個(gè)值即可以，或者按照上面的格式重新寫一個(gè)

12.跳過錯(cuò)誤，查錯(cuò)

mysql主從復(fù)制，經(jīng)常會(huì)遇到錯(cuò)誤而導(dǎo)致slave端復(fù)制中斷，這個(gè)時(shí)候一般就需要人工干預(yù)，跳過錯(cuò)誤才能繼續(xù)
跳過錯(cuò)誤有兩種方式：
a.跳過指定數(shù)量的事務(wù)：
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳過一個(gè)事務(wù)
mysql>slave start

b.修改mysql的配置文件，通過slave_skip_errors參數(shù)來跳所有錯(cuò)誤或指定類型的錯(cuò)誤
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳過指定error no類型的錯(cuò)誤
#slave-skip-errors=all #跳過所有錯(cuò)誤

c.還原被刪除的數(shù)據(jù)

根據(jù)錯(cuò)誤提示信息，用mysqlbinlog找到該條數(shù)據(jù)event SQL并逆向手動(dòng)執(zhí)行。如delete 改成insert。

本例中，此事件在主服務(wù)器Master binlog中的位置是 mysql-bin.000003, end_log_pos 440267874。

1）利用mysqlbinlog工具找出440267874的事件

/usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 |grep -A 20 '440267874'

或者/usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 --stop-position=440267874 | tail -20

或者usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 > decode.log 

（ 或者加上參數(shù)-d, --database=name 來進(jìn)一步過濾）

#160923 20:01:27 server id 1223307  end_log_pos 440267874 CRC32 0x134b2cbc      Delete_rows: table id 319 flags: STMT_END_F### DELETE FROM `db_99ducj`.`tbuservcbgolog`### WHERE###   @1=10561502 /* INT meta=0 nullable=0 is_null=0 */###   @2=1683955 /* INT meta=0 nullable=0 is_null=0 */###   @3=90003 /* INT meta=0 nullable=0 is_null=0 */###   @4=0 /* INT meta=0 nullable=0 is_null=0 */###   @5='2016-09-23 17:02:24' /* DATETIME(0) meta=0 nullable=1 is_null=0 */###   @6=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */# at 440267874

上述就是小編為大家分享的mysql中怎么設(shè)置主從了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。