小男孩‘自慰网亚洲一区二区,亚洲一级在线播放毛片,亚洲中文字幕av每天更新,黄aⅴ永久免费无码,91成人午夜在线精品,色网站免费在线观看,亚洲欧洲wwwww在线观看

搜索
分享

CentOS6.5搭建MySQL5.1主從復(fù)制

 WindySky 2016-04-11
  • 環(huán)境：
1
2
3
4
5
6
7
mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

　　

1
2
[root@10-4-14-168 ~]# cat /etc/redhat-release
CentOS release 6.5 (Final)

　　

1：已經(jīng)事先購買了兩臺云主機(jī)

10.4.14.168      101.69.178.208 [聯(lián)通] Master

10.4.5.9         101.69.178.219 [聯(lián)通] Slave 

2：分別安裝MySQL

 

 
#yum install mysql-server –enablerepo=remi
 
#yum install mysql-devel

 

3：查看MySQL安裝情況

 [root@10-4-14-168 ~]#  rpm -qa | grep mysql*

mysql-5.1.73-3.el6_5.x86_64
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-server-5.1.73-3.el6_5.x86_64
mysql-devel-5.1.73-3.el6_5.x86_64

 

4：啟動MySQL

 

service mysqld start

 

 

5：登陸MySQL并且修改密碼并且刪除空用戶

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> UPDATE mysql.user SET password = PASSWORD('ge0513.mysql')
    -> WHERE user = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> SELECT user,host,password FROM mysql.user;
+------+-------------+-------------------------------------------+
| user | host        | password                                  |
+------+-------------+-------------------------------------------+
| root | localhost   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |
| root | 10-4-14-168 | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |
| root | 127.0.0.1   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |
|      | localhost   |                                           |
|      | 10-4-14-168 |                                           |
+------+-------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> DROP user ''@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP user ''@'10-4-14-168';
Query OK, 0 rows affected (0.00 sec)

 

6 ：主庫需改配置文件，在/etc/my.cnf中添加

1
2
3
4
port = 3306
log_bin = /var/lib/mysql/mysql-binlog
server-id = 1 //只要主從不一樣就行
binlog_do_db = test//要做同步的數(shù)據(jù)庫名字，可以是多個數(shù)據(jù)庫，之間用分號分割。

　　從庫的配置文件中添加

1
2
3
4
5
6
7
server-id = 2
master-host = 10.4.14.168
master-user = gechong
master-password = gechong
master-port = 3306
master-connect-retry = 5
replicate-do-db = test

　　分別重啟服務(wù)，登陸數(shù)據(jù)庫。

 

7：主庫上創(chuàng)建復(fù)制用戶

1
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO gechong@'%' IDENTIFIED BY 'gechong';

8：在主庫上執(zhí)行

1
2
3
4
5
6
7
mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      412 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

從庫上執(zhí)行

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.14.168
                  Master_User: gechong
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-binlog.000001
          Read_Master_Log_Pos: 325
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 473
        Relay_Master_Log_File: mysql-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 325
              Relay_Log_Space: 629
              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)

 

可以看到：Slave_IO_Running | Slave_SQL_Running兩個值都是YES，說明配置成功了。可以在主庫的test庫里執(zhí)行DML或者DDL驗證下。

 

 

 

如果同步不成功：

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1：停掉從庫
mysql> slave stop
2：主庫上找到日志和位置
mysql> show master status;
3：手動同步
mysql> change master to
> master_host='master_ip',
> master_user='gechong',
> master_password='gechong',
> master_port=3306,
> master_log_file='mysql-bin.000020',
> master_log_pos=135617781;
1 row in set (0.00 sec)
4：啟動從庫
mysql> slave start;
1 row in set (0.00 sec)

 

　　

 

 

如果有異常需要跳過：

1
2
3
>slave stop;
>SET GLOBAL sql_slave_skip_counter = 1;
>slave start;

　　

主從搭建一般步驟：

求知若渴, 虛心若愚……

    本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點擊一鍵舉報。
    轉(zhuǎn)藏 分享 獻(xiàn)花（0）

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多