程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> Mysql主從配置實例

Mysql主從配置實例

編輯：關於MYSQL數據庫

系統環境: RHEL5.4 2.6.18-164.el5 x86_64 192.168.1.243/244
軟件環境: 系統自帶Mysql MySQL-5.0.77-3.el5 Master操作:
# mkdir -P /opt/MySQL_M/logs
# chown -R mysql.mysql /opt/MySQL_M
> grant replication slave,reload,super on *.* to 'slave'@'192.168.1.%' identifIEd by '123456'
> slave stop; slave start;
> show slave status; 主配置文件解析(my.cnf)
    [clIEnt] 此段把指令傳遞給與MySQL服務器相關的客戶
    port            = 3306
    socket          = /opt/mysql_M/mysql.sock     [mysqld] #此段有關MySQL守護進程相關的命令
    server-id       = 1 #主庫,從庫或為2 3 ...
    port            = 3306
    socket          = /opt/mysql_M/MySQL.sock
    datadir = /opt/MySQL_M/
    default-character-set = utf8 #數據庫缺省以utf8存儲
    init_connect = 'SET NAMES utf8' #MySQL啟動時用的語系
    lower_case_table_names = 1 #讓MySQL不區分大小寫
    skip-locking #多個客戶可能會訪問同一個數據庫，防止外部客戶鎖定MySQL服務器
    key_buffer = 16M #MySQL可用的數據緩沖區,決定索引處理的速度，尤其是索引讀的速度
    max_allowed_packet = 1M #數據庫相關的信息的數據大小
    table_cache = 64 #限定一個數據庫中打開表的數量
    sort_buffer_size = 512K #在處理一個庫時，用戶可能需要內存中附加的緩沖區空間。
    net_buffer_length = 8K #MySQL服務器給傳入的請求保留的空間大小
    read_buffer_size = 256K #MySQL讀入緩沖區大小
    read_rnd_buffer_size = 512K #MySQL的隨機讀緩沖區大小
    myisam_sort_buffer_size = 8M #命令允許MySQL索引數據庫
    skip-federated
    log-bin = MySQL-bin #數據庫的操作日志,用於主從復制(名字可以改，如果不改，默認為主機名字)
    log = /opt/mysql_M/logs/MySQL.log
    long_query_time = 2
    log-slow-querIEs = /opt/MySQL_M/logs/slowquery.log
    binlog-do-db = test #聲明要同步的庫
    #binlog-ignore-db = MySQL #聲明不同步的庫,不記binlog.
    binlog-do-db = wo #記錄binlog 未在此記錄的數據庫將不記錄binlog
    binlog-do-db = wo_icmdp #與binlog-ignore-db先其一,不共用     [MySQLd_safe]
    log-error = /opt/MySQL_M/logs/error.log
    pid-file = /var/run/mysqld/mysqld.pid     [MySQLdump]
    quick #支持較大數據庫的轉儲
    max_allowed_packet = 16M #用來傳輸數據庫表到其它數據庫的最大數據包     [MySQL]
    no-auto-rehash #該配置段設置啟動MySQL服務的條件；確保這個服務啟動得比較快。     [isamchk] #檢查表和修復有問題的表
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M     [myisamchk] #檢查表和修復有問題的表
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M     [MySQLhotcopy]
    interactive-timeout #數據傳輸的最大時間量設置為28800秒(8個小時) Slave操作：
# mkdir -P /opt/MySQL_S/logs
# chown -R mysql.mysql /opt/MySQL_S
> MySQL -u slave -p123456 -h 192.168.1.243 測試slave用戶連接
> load data from master;
> start slave; 從配置文件解析(my.cnf)
    [clIEnt]
    port            = 3306
    socket          = /opt/mysql_S/mysql.sock     [MySQLd]
    server-id       = 2
    datadir=/opt/MySQL_S/
    port            = 3306
    socket          = /opt/mysql_S/MySQL.sock
    default-character-set = utf8
    skip-locking
    key_buffer = 16M
    max_allowed_packet = 1M
    table_cache = 64
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    skip-federated
    log-bin=MySQL-bin
    log = /opt/mysql_S/logs/MySQL.log
    long_query_time = 2
    log-slow-querIEs = /opt/MySQL_S/logs/slowquery.log
    master-host     =   192.168.1.243
    master-user     =   slave
    master-passWord =   123456
    replicate-do-db = test #定義要復制的數據庫
    #replicate-do-db = wo
    #replicate-do-db = wo_icmdp     [MySQLd_safe]
    log-error = /opt/MySQL_S/logs/error.log
    pid-file = /var/run/mysqld/mysqld.pid     [MySQLdump]
    quick
    max_allowed_packet = 16M     [MySQL]
    no-auto-rehash     [isamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M     [myisamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M     [MySQLhotcopy]
    interactive-timeout 注：防火牆的設置，權限設置。