mysql的主從復制及其問題處理

helpying

mysql的安裝

準備：
     根據(jù)甲方提供的my.cnf修訂其數(shù)據(jù)庫所在目錄(其他不變)后放置于缺省位置:/etc/my.cnf
     建立業(yè)務所需目錄：mkdir -p /data/mysql ;chown -R mysql:mysql /data
     修訂權(quán)限chmod 700 /data/mysql

A 安裝
    master是45 backup是40
    [root@r510 mysql]# rpm -ivh MySQL-*.rpm
    Preparing...               
     /etc/init.d/mysql start
     
     /usr/bin/mysql_secure_installation設置root密碼和移除匿名用戶，test庫

B 遠程登錄授權(quán)
 
    use mysql;select host,user from user ;
    
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '2011mlnsh' WITH GRANT OPTION ;
     
    FLUSH PRIVILEGES;
    
    select host,user,password from user;
 
     mysql> show databases; show engines; show matser status; show status\G; show processlist\G;
     --------------------
     | Database           |
     --------------------
     | information_schema |
     | milanoo            |
     | myphpbb            |
     | mysql              |
     | performance_schema |
     --------------------
     5 rows in set (0.01 sec)   
     
     測試登錄：mysql -h x.x.x.x -u root -p
     
C 配置同步
    
  c1 在master上：
        cat  /etc/my.cnf 確保有以下的行存在，也是要唯一。
             server-id=1
             log-bin=mysql-bin
             
        mysql>use mysql;
              GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '2011mlnshrep';
              select host,user from user ;
              
        mysql> show master status; #查看主節(jié)點的狀態(tài) 記錄log（file）和Position
                  mysql> show master status;
              --------------- ---------- -------------- ------------------
              | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
              --------------- ---------- -------------- ------------------
              | binlog.000005 |      107 |              |                  |
              --------------- ---------- -------------- ------------------
              1 row in set (0.00 sec)   
              
        mysql> show processlist;
             ---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
             | Id | User | Host                | db   | Command     | Time    | State                                                                 | Info             |
             ---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
             |  4 | rep1 | 10.10.10.238:35021 | NULL | Binlog Dump | 1121604 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
             | 36 | root | localhost           | NULL | Query       |       0 | NULL                                                                  | show processlist |
             ---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
             2 rows in set (0.00 sec)   
          
  c2 在Slave服務器上：
        cat  /etc/my.cnf,確保有以下的行存在,也是要唯一。 不過據(jù)網(wǎng)上消息介紹，Mysql版本從5.1.7以后開始就不支持“master-host”類似的參數(shù);
             [mysqld]
             server-id=2
             report-host=10.10.10.238
             replicate-ignore-db=mysql
             skip-slave-start
         
        mysql>change master to master_host='10.10.10.237',master_user='rep1',master_password='2011mlnsh',master_log_file='',master_log_pos=107;
              Query OK, 0 rows affected (0.02 sec)
        
        
        mysql> Slave start;
        
        mysql> show slave status\G;
                   Slave_IO_Running: Yes
                   Slave_SQL_Running: Yes
         如果出現(xiàn)錯誤：
                   Slave_IO_Running: No
                   Slave_SQL_Running: Yes
                   Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
                   
         用以下步驟解決：
       
         slave stop;
         reset slave;
         slave start;
         
         如果出現(xiàn)錯誤：
                Slave_IO_Running: Yes
                Slave_SQL_Running: No
         用以下步驟解決：
         解決辦法一、

Slave_SQL_Running: No
1.程序可能在slave上進行了寫操作

2.也可能是slave機器重起后，事務回滾造成的.

一般是事務回滾造成的：
解決辦法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

 

解決辦法二、

首先停掉Slave服務：slave stop
到主服務器上查看主機狀態(tài)：
記錄File和Position對應的值

進入master

mysql> show master status;
---------------------- ---------- -------------- ------------------
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---------------------- ---------- -------------- ------------------
| localhost-bin.000094 | 33622483 |              |                  |
---------------------- ---------- -------------- ------------------
1 row in set (0.00 sec)

 

然后到slave服務器上執(zhí)行手動同步：

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)

 

mysql> show slave status\G
*************************** 1. row ***************************
........
            Master_Log_File: localhost-bin.000094
        Read_Master_Log_Pos: 33768775
             Relay_Log_File: localhost-relay-bin.000537
              Relay_Log_Pos: 1094034
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

手動同步需要停止master的寫操作！
                
   c3  這時在主服務器上有binlog dump的進程：
        mysql> show processlist;
             ---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
             | Id | User | Host                | db   | Command     | Time    | State                                                                 | Info             |
             ---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
             |  4 | rep1 | 10.10.10.238:35021 | NULL | Binlog Dump | 1121604 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
             | 36 | root | localhost           | NULL | Query       |       0 | NULL                                                                  | show processlist |
             ---- ------ --------------------- ------ ------------- --------- ----------------------------------------------------------------------- ------------------
             2 rows in set (0.00 sec)   
          
                                           
  c4 測試
         在master上：
         #測試庫1
         CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
         use `testdb`;
         CREATE TABLE `testtab` (
           `name` char(64) NOT NULL DEFAULT '',
           `phone` char(64) NOT NULL DEFAULT '',
           PRIMARY KEY (`name`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test tables';
         
         insert into `testtab`(`name`,`phone`) VALUES('suanXing','15608188873');
         
         在master和slave上查詢應該是以下相同結(jié)果：
         use testdb;         select * from testtab;   
         mysql>          select * from testtab;
                        ---------- -------------
                        | name     | phone       |
                        ---------- -------------
                        | YuanXing | 15608188773 |
                        ---------- -------------
                        1 row in set (0.00 sec)
     

   
  c4 導入milanoo庫:由甲方操作我們配合
 
      在master上：
      建庫：
      mysql> use mysql;
      mysql>CREATE DATABASE `milanoo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
      授權(quán)：
      mysql>GRANT ALL ON `milanoo`.* TO 'milanoo'@'%' IDENTIFIED BY 'milanootest';
      
      確認：mysql> use mysql;select host,user,password from user ;show databases;

  c5 讀寫分離：
     在從庫上建立設只讀用戶以及授權(quán)：由甲方操作我們配合
----------------------------------------------------------------------------------------------