MySQL主(Master)從(Slave)備份；

標籤：mysql   centos   

實驗環境：

CentOS release 6.6 (Final) 32位

Master:10.10.10.11

Slave1:10.10.10.7

Slave2:10.10.10.8

1) MySQL 安裝 （分別在Master,Slave1,Slave2 運行如下命令）

[[email protected] ~]# yum install mysql-server mysql -y

[[email protected] ~]# service mysqld start

2）配置my.cnf

[[email protected] ~]# mysqladmin -u root password 123.com  //修改mysql root的密碼為"123.com"

[[email protected] ~]# vi /etc/my.cnf    //修改my.cfg 設定檔,添加如下內容；

相關資訊如下：

#Master

[mysqld]

log-bin=MySQL-bin    //二進位檔案

server-id=1     //服務ID （唯一）

#Slave1

[mysqld]

log-bin=MySQL-bin    //二進位檔案

server-id=2     //服務ID

#Slave2

[mysqld]

log-bin=MySQL-bin    //二進位檔案

server-id=3     //服務ID

儲存退出之後，重啟MYSQL服務：

[[email protected] ~]# service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

3）在Master上建立備份帳號；

[[email protected] ~]# mysql -u root -p123.com

mysql> GRANT REPLICATION SLAVE ON *.* to ‘myslave‘@‘10.10.10.7‘ identified by ‘123.com‘;

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to ‘myslave‘@‘10.10.10.8‘ identified by ‘123.com‘;

Query OK, 0 rows affected (0.02 sec)

查看Master狀態

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| MySQL-bin.000002 |      261 |              |                  |

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

1 row in set (0.00 sec)

4）Slave1 Slave2上設定

[[email protected] ~]mysql -u root -p123.com

mysql> change master to

    -> MASTER_HOST=‘10.10.10.11‘,

    -> MASTER_USER=‘myslave‘,

    -> MASTER_PASSWORD=‘123.com‘,

    -> MASTER_LOG_FILE=‘MySQL-bin.000001‘,

    -> MASTER_LOG_POS=261;

在Slave1,Slave2上啟動Slave,並查看狀態；

mysql> slave start;

mysql> show slave status \G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.10.10.7

                  Master_User: myslave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: MySQL-bin.000002

          Read_Master_Log_Pos: 261

               Relay_Log_File: mysqld-relay-bin.000004

                Relay_Log_Pos: 406

        Relay_Master_Log_File: MySQL-bin.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes     

              Replicate_Do_DB:

          Replicate_Ignore_DB:

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

              Relay_Log_Space: 707

              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:

1 row in set (0.00 sec)

5）測試Master,Slave同步；

1.在Master上建立資料庫資訊;

mysql> CREATE DATABASE info;

Query OK, 1 row affected (0.00 sec)

mysql> use info

Database changed

mysql> CREATE TABLE user (id int(3),name varchar(10));

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO user values (001,‘zhangcheng‘);

Query OK, 1 row affected (0.01 sec)

2.在Slave1,Slave2上查看； 

mysql> show databases;

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

| Database           |

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

| information_schema |

| NINESTAR           |

| info               |

| mysql              |

| test               |

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

5 rows in set (0.00 sec)

mysql> use info

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from user;

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

| id   | name       |

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

|    1 | zhangcheng |

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

1 row in set (0.00 sec)

資訊已同步！

MySQL主(Master)從(Slave)備份；